October 26, 2006 at 10:56 pm
hi there,
I am still running a large numbers of Visual basic 6 windows applications which use ADODB. We'd just migrated from SQL2000 to SQL2005 two weeks ago. So all SQL connection is done using ADODB object in the applications. There's no changes in the connection string and all locking settings stay the same on the server (no changes had been made to mirgate from SQL2000 to SQL2005). But we constantly get process block issues on the SQL2005 server. I got a couple of questions regarding the issue:
1. Is there any different in locking behaviour in SQL2000 and SQL2005? If there is, how do I change them?
2. Instead of using @@LOCK_TIME, is there another setting that I can use to let SQL server to release locked process automatically once it lock over 20 minutes?
We desperately need helps and advices. The SQL2005 is on live and even though we can solve the problem by identifying the locked process and kill it BUT it happens all the time! and we won't be able to handle them when there's hundreds of users accessing the data. Please help.
Cheers
Pammy
October 30, 2006 at 8:00 am
This was removed by the editor as SPAM
October 31, 2006 at 12:09 pm
are these spids that are not disconnecting or are they running long queries? Is there a difference in the query execution?
If you can restore a month old copy (pre-SQL 2005) of the database on a SQL2000 server and run the same queries.
maybe for some reason like fragmented indexes the queries are not performing as well and bumping in each other. also check the code to see what kind of lock resources they take up and see if you can change it.
November 1, 2006 at 12:08 am
Rebuild all your indexes and update all your statistics.
The upgrade from 2000 to 2005 doesn't do that and I have heard of several issues where queries run slower in 2005 than in 2000 because of this.
Hopefully that will solve your problems.
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
November 1, 2006 at 2:15 am
Thanks for all your reply. The locked query is not a long one and it is a simple update statement. Also all index and statistic had been rebuilt. This never happen before in SQL2000. Anyway, I have to wait for the same error to happen again... Thanks for the tip anyway.
November 1, 2006 at 2:46 am
Strange. I know the query optimiser has changed in 2005 and often picks different plans to what the 2000 one would have picked.
Can you check the execution plan of the query and see what it's doing? Make sure it's using the indexes that you expect.
In 2005 you can query one of the dm views to find the xml plan used. I can't remeber the exact one right now, but I'll hunt it down the query and post it in a bit.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply