Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GPGUY
Starting Member
2 Posts |
 Posted - 2007-11-13 : 17:18:17
|
| We just converted from SQL Server 2000 to SQL SERVER 2005 and it seems as though we are having trouble with our performance. Sets of queries that used to take about 15 seconds now take almost 2 minutes. We used a utility to find out what was taking so long and found that almost all of the wait time belonged to LCK_M_S. Does anyone have any suggestions?Here is a snippet of code I used to test the speed against 2000: declare @counter intset @counter = 1while @counter < 200BEGIN update UPR40500 set actindx = 96 where actindx = 96 set @counter = @counter+1END This takes 15 seconds when it used to take virtually no time at all. |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-14 : 04:12:15
|
| does UPR40500 have an index on actindx ?When you converted to 2005 did you keep the same server? |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-14 : 15:28:50
|
| Did you change to 90 compatibility mode and then re run the index rebuild on this and update the stats. |
|
|
|
GPGUY
Starting Member
2 Posts |
Posted - 2007-11-15 : 14:02:50
|
| Thank you for the replies. Actually UPR40500 did not have an index on actindx as I had originally thought, so thank you! That made it twice as fast. The compatability was 90 and I rebuilt all the indexes and updated all of the stats. Thank you both very much for your help and if anyone else has any ideas I welcome them. |
|
|
|
|
|
|
Poor Performance due to LCK_M_S?