March 30, 2010 at 7:19 am
Thanks. Just realized you have a book, I will buy and read once my panic is over!
I was looking at covering indexes yesterday, I have to admit I hadn't heard of them before. Does adding a new index lock the table whilst it's generated or just slow the server down a bit?
I also discovered this command READ_COMMITTED_SNAPSHOT which would seem to solve my problem but looks like it could have all kinds of consequences I'm unaware of!
March 30, 2010 at 7:37 am
david meagor (3/30/2010)
Thanks. Just realized you have a book, I will buy and read once my panic is over!I was looking at covering indexes yesterday, I have to admit I hadn't heard of them before. Does adding a new index lock the table whilst it's generated or just slow the server down a bit?
I also discovered this command READ_COMMITTED_SNAPSHOT which would seem to solve my problem but looks like it could have all kinds of consequences I'm unaware of!
READ_COMMITTED_SNAPSHOT might not help in this instance because it generally works the other way, when a delete/insert/update is running you can still read. Your block is because of the read, not the delete, but it could still help.
Yes, creating an index will slow down the server on a large table. I'm pretty sure it wouldn't lock the rest of the table (except of course, it's reading the table, so shared locks will be there).
"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
March 30, 2010 at 1:03 pm
OK I've learned something new and irritating about SQL server/ADO which seems to be the cause of my problems (and likely other people's too.) It would seem that Profiler does not record the duration until the ADO Recordset is closed even if that's a long time after the query really finished so I've been barking up the wrong tree.
Basically the query does execute in under 1-4 seconds however the Recordset object in ASP is left open whilst other queries are run based on its results in a loop which takes up some time. When I run this script the table is write blocked.
rs1=cn.execute sql ' takes 1 second
do while not rs1.eof ' takes 10 minutes for loop to complete.
rs2=cn.execute anotherSql
do something...
loop
rs1.close ' profiler records duration for initial sql statement here.
I am also guessing that SQL server is maintaining the lock/block on the tblBigTable until the first recordset is closed. I have therefore tried using a #temp table to hold the results of the tblBigTable and tblSession first and then then in the same batch did the left join with the the #temp table instead. So far that 'appears' to have worked avoid the blocking issue. I guess it's just blocking the temp table now which doesn't effect anything else.
It's a poor ASP script I know but I'm still not sure why SQL server would block the whole table just because the recordset isn't closed.
I'm hoping this solution works whilst I rewrite the script itself properly.
March 30, 2010 at 1:07 pm
Cause you're holding open a transaction on the client. As long as that's working that way, you'll be waiting.
BTW, you can get lots more information out of profiler besides RPC complete, but you need to be careful when you use them. For example, in this situation, you could get the STMT Complete to see the individual statement run time, but STMT Complete is a huge resource pig that can seriously impact your system if it's used incorrectly.
"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
March 30, 2010 at 1:40 pm
Grant, you have been most helpful and I appreciate the time you have spent replying to my issues.
If you pm me with your address I'll post you a bottle of your favorite tipple as a small thank you.
March 31, 2010 at 12:21 am
david meagor (3/30/2010)
Grant, you have been most helpful and I appreciate the time you have spent replying to my issues.If you pm me with your address I'll post you a bottle of your favorite tipple as a small thank you.
Nah, no worries. All part of the highly paid service around here. :w00t:
Hopefully we made some progress and got you out of the wood.
"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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply