April 24, 2012 at 9:16 am
Hi All,
I'm fairly confident of this answer, but I'm looking for some consensus feedback from my peers here on SQL Server Central.
QUESTION:
What would you say is a reasonable threshold for the amount of time a cursor should remain open in SQL Server?
WHY DO I ASK?:
I have a custom index maintenance job that I wrote that either REBUILDS or REORGANIZES indexes based on fragmentation levels for a given database. This job executes in the early morning hours at 1:00 AM, which is a nice maintenance window on our other SQL Servers. However, we have a third party application that is being disrupted by my index maintenance job, with the reason, "the schema has changed on the underlying object since the cursor last executed." After some investigation, I've discovered that this application is leaving cursors open for as long as 12 hours or more, with no actively running processes. I was told by the software development support folks, "This is by design". In the meantime, I have to avoid index maintenance on this server due to open cursors that fall under the radar unless I query the sys.dm_exec_cursors management view.
Just a penny for my fellow DBA's thoughts would be nice.
😀
April 24, 2012 at 9:24 am
There are no "reasonable threshold", as no-one in their right mind would keep cursors on the permanent tables opened for hours on every day...
They MUST change the design, or you will not be able to do maintenance of indexes.
April 24, 2012 at 9:24 am
First, why are they using cursors? Second, what does it buy them to keep them open?
April 24, 2012 at 9:30 am
somebody forgot to close and deallocate their cursor!! 😛
MVDBA
April 24, 2012 at 9:45 am
Lynn Pettis (4/24/2012)
First, why are they using cursors? Second, what does it buy them to keep them open?
Because they are third party vendors and as such have less than ability to write anything resembling decent code than most people their first year out of school. The amount of vendor produced crap that costs and arm and a leg is completely insane!!! Maybe this is just a sore topic due to my current project but sheesh!!!!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 9:52 am
Again, the main aim of the vendor is not to produce good software but to sell any crap one for the profitable price to maximum possible number of customers.
I would raise it as a serious flow in the 3-rd party software and insist they do something about it.
April 24, 2012 at 10:30 am
My threshold on cursors is simple, "As close to zero as possible"
Yeah, I've used cursors for maintenance scripts because you can't do batch backups or batch statistics updates (well, I could through threads in PowerShell, but that would be bad). So, yeah, cursors can and do get created & used, but it has to be minimized as much as possible and set as close as possible to zero.
BTW, I hope the vendor is not Red Gate, but if it is, please let me know which app.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2012 at 10:55 am
What I would do is ask the vendor how they prefer their application's database indexes get maintained without interfering with their cursors and then follow through with what they advise.
April 24, 2012 at 12:30 pm
This is not a Redgate application, nor any SQL Server management software for that matter. This is an application that supports our businesses core operations.
😀
April 24, 2012 at 10:49 pm
QUESTION:
What would you say is a reasonable threshold for the amount of time a cursor should remain open in SQL Server?
ANSWER:
0 msec. They shouldn't be using CURSORs at all. See my mantra.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 24, 2012 at 10:55 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply