About Me - Who is DR DBA?

Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: sqlsurgeon@outlook.com

Friday, October 25, 2013

Script Performance Increase with CURSOR Parameters

Recently I attended the PASS Summit 2013 and found an interesting performance tip with the CURSOR statement. Cursors are not always the best choice but when you have to use one; check out the parameters that can be used.

Below is part of my original cursor that I used in one of my script. The total script executed in 6 seconds

SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable

Making the following changes by adding LOCAL FAST_FORWARD after the CURSOR statement.

SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable

By adding this change, the same script executed in 3 seconds. That’s a 50% improvement in performance.

Books On Line and the following links provide more details as on this and additional parameters you can use with the CURSOR statement. 

Increase your SQL Server performance by replacing cursors with set operations

Understanding SQL Server Fast_Forward Server Cursors

Comparing cursor vs. WHILE loop performance in SQL Server

Curious cursor optimization options
Have a look at the above links and try some testing of your own. You too will be surprised at the performance increase you can get. My own testing showed that I can get from 10% - 70% performance increase with simple changes.

Ensure you test these parameters fully before applying to your production server.



No comments:

Post a Comment

Note: Only a member of this blog may post a comment.