March 2010 - Posts

Cursors 1 Sets 0

I had an interesting experience with a database I essentially know nothing about. On the server is a database which stores session state, Microsoft provide the code/database with their dot net, so I'm told.

Anyway this database has sat happily on the production server for the past 4 years I guess, we've finally made the upgrade to SQL 2008 and the ASPState database has also been upgraded. It seems most likely that the performance increase of our upgrade tipped the usage of this database into overload. I have no excuses to make - I've totally ignored the database and it's come back and bitten me on the bum!

We had a couple of issues but the interesting one was that the deletion of expired sessions was by a set based delete, the upshot was a table lock which blocked inserts and updates which in turn put the connection pooling into hyperdrive raising connections fourfold and bringing some chaos to our application.

I'd previously noted some increase of activity on this database from my monitoring and it was on my hit list to pay it a visit, but on the basis of  "if it ain't broke don't fix it" I'd merely noted the sql looked a bit suspect !!

Whist I attempted to resolve the production problem my colleague found a post by Greg Low  detailing essentially the same issue we had and suggesting that a cursor based solution was better due to the structure of the table/data.

Now in the right place I think cursors are useful - sometimes you really do need to processes data one row at a time, and a while loop is only a pretend cursor anyway. Like many others I expect I have a procedure which rebuilds indexes by building a list and stepping through the list. Out of morbid curiosity I rewrote the while to a cursor and ran a series of tests - consistently the cursor was  6% - 7%  faster, 8mins vs 8 mins 30 secs, anyway I digress.

The cursor based delete did indeed resolve this particular issue and at some stage I will have to give serious time to the code.  I don't want to pick fights but this isn't the first time I've encountered "dodgy sql databases" provided by a microsoft team - there is a belief that if it comes from Microsoft it must be good ( talking sql apps and stuff ) and one client claimed the application was "approved" by Micorsoft - however anyone who has examined some of the code within say the builtin SSRS reports or some of the system procs or who has had to patch system procs to make them work will know this isn't always the case - at best Microsoft suffers just as any corporate with multiple teams etc. And I won't even mention my experiences with Navision a couple of years ago.