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


DECLARE myInstances CURSOR FOR
SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
….

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

 
DECLARE myInstances CURSOR LOCAL FAST_FORWARD FOR
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
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

Understanding SQL Server Fast_Forward Server Cursors
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx

Comparing cursor vs. WHILE loop performance in SQL Server
http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741

Curious cursor optimization options
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
 
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.

Thanks,

Rudy
 

No comments:

Post a Comment

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