October 8, 2008 at 1:23 pm
This is purely theoretical as I don't have any solid information to base this on.
We have a new module under development for our ERP system by the vendor, and we have a major performance problem. The vendor says that our system is taking a long time to get locks. On their test bed they get very fast run times, but on our production system, in a database with no other user access, performance sucks.
Since this is vendor-supplied code and runs on an application server, I can't really see what's going on. So my question is this: our production server is SQL Server 2000 Enterprise on a plenty beefy box (quad core, 8gig ram, nice storage array, no memory issues). If their server, and this is the theoretical part, were SS2005 and they were running our database with compatibility level set to 80, would that theoretically give better performance? I'm assuming that SS2005 probably has better locking code than SS2000.
[commence grasping at straws] :hehe:
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 8, 2008 at 2:17 pm
I can't answer your question, exactly... but SQL 2005 does have more intelligent lock manager.
I've scoured the msdn blogs for the article I read about this, but couldn't find it... you'll have to trust my memory here. but in essence, SQL 2000's lock manager is a FIFO queue.
SQL 2005 calculates FIFO and lock compatibility levels so that even on the same data page if the lock types are compatible the new lock request is immediately granted, cutting to the front of the line in front of other locks that are incompatatible with the existing Lock (and are therefore blocked).
This was not always the case in 2000. You would have been out of luck, no cutting in line!!
~Craig
MCITP, MCDBA
Craig Outcalt
October 8, 2008 at 2:28 pm
Do you have any access to the database at all? If so, please run the following at regular intervals (every couple hours) and see which values are changing the fastest (the counters are cumulative since server restart)
DBCC SQLPerf(WaitStats)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2008 at 2:59 pm
Thanks for the replies. I expected 2005 to have made improvements in the lock manager, but since my experience with 2005 is minimal, I don't have research material for that. Gotta get out and buy Kalen's books! 😀
And thanks for the SQLPerf, Gail. We do a shutdown of SQL Server on that box every night for 15 minutes to clear stuck connections from the app server, so I'll find out when the next test run is and do a before/after copy of those stats.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply