Optimize your database cursors (considering SQL Azure)

Yeah, I know most of the DBAs (if not all) say to avoid using cursors in your SQL Server code, but there are still some things, which you can only achieve via cursors. You can read a lot discussions on whether to use cursors or not, is it good, is it bad.

My post is not about arguing what is good and what is bad. My post is about a tiny little option, which, if your logic allows you can use to optimize how your cursor works.

So we are using cursors, for good or bad. Everything might work just fine if we are using on-premise SQL Server, and if the server is not under heavy load. Our stored procedures, which are using cursors are executing in a matter of seconds. There is nothing unusual. We deploy our application to The Cloud. And of course we utilize SQL Azure as our DB backend. Now strange things begin happening. Our stored procedures crash with timeout exceptions. If we login to the server and use the good “sp_who3” (yes, this works in SQL Azure!) to see the processes running, we notice that some procedures do report a  SOS_SCHEDULER_YIELD. You can read a lot of information on what does that mean. As by definition:

Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.

Most of the resources you will find explaining what does lot of SOS_SCHEDULER_YIELD mead, will suggest high CPU load, non-optimized queries, etc. But we look at our code and there is nothing unusual. Also, as this is SQL Azure, we can’t see the actual CPU load of the OS. We can’t add more CPU or more RAM. What do we do now ?

Well, review once again our cursor logic! If it is the case that we only read from the cursor’s data. We only read forward, never backward. We never change cursor’s data (update/delete). Then there is a pretty good chance that we can use the FAST_FORWARD keyword when declaring our cursors:

Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

It is amazing performance booster and load relief! And we, most probably, will never see again the SOS_SCHEDULER_YIELD process status for our procedures.

Most (if not all) of the cursors I’ve written are never reading backward or updating data, so I pretty amazed to see the performance differences using this keyword. I for sure will use it from now on, whenever possible!.