I've written several times about using cursors and how, in most cases, it is more efficient to re-write your cursors using set-based logic.
I'm realistic, though.
I know that there are cases where cursors are "required" – you need to call another stored procedure or send an e-mail for every row, you are doing maintenance tasks against each database, or you are running a one-off task that simply isn't worth investing the time to convert to set-based.
How you are (probably) doing it today
Regardless of the reason you are still using cursors, you should at the very least be careful not to use the quite expensive default options. Most folks start their cursors off like this:
DECLARE c CURSOR FOR
SELECT whatever FROM ...
Now again, for ad-hoc, one-off tasks, this is probably just fine. But there are…
Other ways to do it
I wanted to run some tests using the defaults and compare them to different cursor options such as LOCAL, STATIC, READ_ONLY and FAST_FORWARD. (There are a ton of options, but these are the ones most commonly used as they are applicable to the most common types of cursor operations that people use.) Not only did I want to test the raw speed of a few different combinations, but also the impact to tempdb and memory, both after a cold service restart and with a warm cache.
The query I decided to feed to the cursor is a very simple query against sys.objects, in the AdventureWorks2012 sample database. This returns 318,500 rows on my system (a very humble 2-core system with 4GB RAM):
SELECT c1.[object_id]
FROM sys.objects AS c1
CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2;
Then I wrapped this query in a cursor with various options (including the defaults) and ran some tests, measuring Total Server Memory, pages allocated to tempdb (according to sys.dm_db_task_space_usage and/or sys.dm_db_session_space_usage), and total duration. I also tried to observe tempdb contention using scripts from Glenn Berry and Robert Davis, but on my paltry system I could not detect any contention whatsoever. Of course I'm also on SSD and absolutely nothing else is running on the system, so these may be things you want to add to your own tests if tempdb is more likely to be a bottleneck.
So in the end the queries looked something like this, with diagnostic queries peppered in at appropriate points:
DECLARE @i INT = 1;
DECLARE c CURSOR
-- LOCAL
-- LOCAL STATIC
-- LOCAL FAST_FORWARD
-- LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT c1.[object_id]
FROM sys.objects AS c1
CROSS JOIN (SELECT TOP 500 name FROM sys.objects) AS c2
ORDER BY c1.[object_id];
OPEN c;
FETCH c INTO @i;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @i += 1; -- meaningless operation
FETCH c INTO @i;
END
CLOSE c;
DEALLOCATE c;
Results
Duration
Quite arguably the most important and common measure is, "how long did it take?" Well, it took almost five times as long to run a cursor with the default options (or with only LOCAL specified), compared to specifying either STATIC or FAST_FORWARD:
Memory
I also wanted to measure the additional memory that SQL Server would request when fulfilling each cursor type. So I simply restarted before each cold cache test, measuring the performance counter Total Server Memory (KB) before and after each test. The best combination here was LOCAL FAST_FORWARD:
tempdb usage
This result was surprising to me. Since the definition of a static cursor means that it copies the entire result to tempdb, and it is actually expressed in sys.dm_exec_cursors as SNAPSHOT, I expected the hit on tempdb pages to be higher with all static variants of the cursor. This was not the case; again we see a roughly 5X hit on tempdb usage with the default cursor and the one with only LOCAL specified:
Conclusion
For years I have been stressing that the following option should always be specified for your cursors:
LOCAL STATIC READ_ONLY FORWARD_ONLY
From this point on, until I have a chance to test further permutations or find any cases where it is not the fastest option, I will be recommending the following:
LOCAL FAST_FORWARD
(As an aside, I also ran tests omitting the LOCAL option, and the differences were negligible.)
That said, this is not necessarily true for *all* cursors. In this case, I am talking solely about cursors where you're only reading data from the cursor, in a forward direction only, and you aren't updating the underlying data (either by the key or using WHERE CURRENT OF). Those are tests for another day.
Thank you for the great information Aaron. I have referred several people to this article already as I've run across cursor use in the wild.
Great article. For years I have been using LOCAL FAST_FORWARD but never had a chance to compare it against other options. Thank you for providing the statistics for each option.
Julie
I am just now seeing this post so forgive me for the late reply. One thing I want to point out is that even though Static may not have the smallest footprint out of the cursors and might be slightly slower under these conditions it can be dramatically faster in a heavy use / high concurrency one. Even LOCAL FAST_FORWARD cursors go back to the original source with new fetches and do take locks and latches which can interfere with a highly concurrent operation. This can be from both sides meaning that it can cause blocking and be blocked. Where as a STATIC cursor only hits the table once and all further actions are against the copy in tempdb which has no need for dealing with concurrency options. As such in a heavily used environment it can actually be much faster than any of the other options. In my experience it has proved to be the fastest option in general overall in real life use cases and can have the least amount of CPU usage as well. Just my 2 cents :)
Thanks Andy, yes it is true that STATIC can exhibit some benefits at a certain scale, but overall I would prefer to use a consistent approach until I have a reason to break consistency, and in most cases the transactional volume just doesn't justify using STATIC – especially in cases where we're already increasing the load on tempdb with things like snapshot isolation. Ideally, of course, we just get rid of the cursors altogether. :-)
Hi Aaron, this is a very interesting, I really enjoy reading your articles. I think you got very similar results between LOCAL FAST_FORWARD and LOCAL STATIC READ_ONLY FORWARD_ONLY because FAST_FORWARD it's a cursor model equivalent to READ_ONLY FORWARD_ONLY that compiles to a static-like or dynamic-like cursor plan. Seems as you said FAST_FORWARD is a little better because the way the decision is made to use a dynamic-like or a static-like plan.
Just keep in mind that FAST_FORWARD is DYNAMIC … FORWARD_ONLY you can use with a STATIC cursor.
Try using it on the Halloween problem to see what happens !!!
IF OBJECT_ID('Funcionarios') IS NOT NULL DROP TABLE Funcionarios GO CREATE TABLE Funcionarios(ID Int IDENTITY(1,1) PRIMARY KEY, ContactName Char(7000), Salario Numeric(18,2)); GO INSERT INTO Funcionarios(ContactName, Salario) VALUES('Fabiano', 1900) INSERT INTO Funcionarios(ContactName, Salario) VALUES('Luciano',2050) INSERT INTO Funcionarios(ContactName, Salario) VALUES('Gilberto', 2070) INSERT INTO Funcionarios(ContactName, Salario) VALUES('Ivan', 2090) GO CREATE NONCLUSTERED INDEX ix_Salario ON Funcionarios(Salario) GO -- Halloween problem, will update all rows until then reach 3000 !!! UPDATE Funcionarios SET Salario = Salario * 1.1 FROM Funcionarios WITH(index=ix_Salario) WHERE Salario < 3000 GO -- Simulate here with all different CURSOR declarations -- DYNAMIC update the rows until all of then reach 3000 -- FAST_FORWARD update the rows until all of then reach 3000 -- STATIC update the rows only one time. BEGIN TRAN DECLARE @ID INT DECLARE TMP_Cursor CURSOR DYNAMIC --DECLARE TMP_Cursor CURSOR FAST_FORWARD --DECLARE TMP_Cursor CURSOR STATIC READ_ONLY FORWARD_ONLY FOR SELECT ID FROM Funcionarios WITH(index=ix_Salario) WHERE Salario < 3000 OPEN TMP_Cursor FETCH NEXT FROM TMP_Cursor INTO @ID WHILE @@FETCH_STATUS = 0 BEGIN SELECT * FROM Funcionarios WITH(index=ix_Salario) UPDATE Funcionarios SET Salario = Salario * 1.1 WHERE ID = @ID FETCH NEXT FROM TMP_Cursor INTO @ID END CLOSE TMP_Cursor DEALLOCATE TMP_Cursor SELECT * FROM Funcionarios ROLLBACK TRAN GOFAST_FORWARD actually allows the optimizer to choose between dynamic and static types.
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx
Fantastic article, thank you!
Article is very useful for performance improvement when working with Cursors in SQL server
Thanks for the article
very good article thanks :-)
Nicely done Aaron. Thank you.
Which tool do you use to get this graphics of consumption of memory, tempdb, duration, etc???
Oh that's just Excel, one of the bar chart styles.
I repeated your test on my local PC and can validate, that FAST_FORWARD is slightly faster than an FORWARD_ONLY STATIC on BIG recordsets (avg. 17,251.9 ms vs. 18,429.2 for 10 runs). Your test query returns in my database ~2 mio records.
On smaller (more realistic) recordsets as a simple SELECT 1 FROM sys.all_objects (~8k rows, which is still a lot) FORWARD_ONLY STATIC wins with avg. 142 ms vs 182 ms (100 runs).
Another tip that has no performance impact, but helps the developers – I use always a WHILE 1 = 1 loop, fetch the records in the first statment and BREAK when no more records could be fetched. This way you have only to insert a single FETCH (even if you are using something as IF @ignore_this = 1 CONTINUE), which prevents infinite loops (CONTINUE without an explicite FETCH) or missing columns on the second, third … FETCH after you added a new column to the SELECT
[sql]
DECLARE CurName CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
SELECT …
OPEN CurName
WHILE 1 = 1
BEGIN
FETCH NEXT FROM CurName INTO @dummy
IF @@fetch_status 0 BREAK
SET @i += 1;
END
CLOSE CurName
DEALLOCATE CurName
[/sql]
PS:
– what is the syntax to insert code (and why can't I edit my posts)
– in the IF above there should be a not eqal (!= or lesser/greater sign), which was removed by the forum software, just before the 0
Very Informative article. Thanks
Good info and I like the tip. Thanks!