June 15, 2005 at 9:44 am
Hi everyone,
I'm trying to trouble shoot some blocking for a customer. The stored proc slows down the SQL Server significantly to the point where the users say it basically freezes on them.
My guess is that the stored proc, which just does selects, is taking a shared lock for the entire transaction, thus preventing users from doing any updates.
My question is, is there a way for me to determine the transaction isolation level without asking the customer or seeing their code?
I saw in one article that you can determine this by running @@isolation. However, SQL Server doesn't include this global variable.
I can see all of the lock information I need in sp_lock2, but that doesn't help me in regard to transaction levels. For example, serializable transaction isolation level won't allow an update/exclusive lock on pages/extents/tables with shared locks. Therefore, if sp_lock2 shows a shared lock, I don't know if this is compatible with a shared/exclusive lock without knowing the corresponding transaction isolation level.
Any help would be appreciated.
Laura
June 15, 2005 at 10:00 am
I would do the following PRIOR to going down that path:
1. Have the customer run CTRL+L with the guts of the SP in QA. This will show the "estimated" execution plan (without actually running it). You can see index scan, clustered index scan, table scan, etc... (BTW the ones I mentioned are indications of missing/incorrect indexes/code)
2. Have the customer run the SP from QA with SHOW execution plan ON. This will show you what it is really doing
3. Examine the joins, where statements, etc.. Something as simple as FieldX <> 'D' can dramatically cause your system to tank (especially if the only values are N or R). Spent approx. 60 straight days, flew MS in, etc.. because of this one.
4. How often are the indexes rebuilt? Is there a maintenance plan for this?
5. Do the indexes in place have proper fill factors to allow for sufficient growth between re-indexing?
6. Were the indexes built based off of 0 records are projected growth? The system can actually start doing table scans instead of using indexes once certain thresholds are met because those are faster.
The above steps helped us determine all sorts of different performance problems in our system.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 15, 2005 at 10:05 am
Thanks for your quick reply. I've done the trouble shooting from the index/execution plan level.
My main question is where can I find what transaction isolation level the customer is using? Does SQL Server put that somewhere while the query is running?
Laura
June 15, 2005 at 10:10 am
Can you elaborate on this one AJ?
3. Examine the joins, where statements, etc.. Something as simple as FieldX 'D' can dramatically cause your system to tank (especially if the only values are N or R). Spent approx. 60 straight days, flew MS in, etc.. because of this one.
June 15, 2005 at 10:13 am
What's going on is the select statements are blocking the users. Only repeatable read and serializable isolation levels will cause a shared lock to block an exclusive lock until the transaction is done.
If the customer is using read committed or read uncommitted, then there really shouldn't be contention between the shared lock and the update lock.
How do I tell which isolation level the sproc is using? This question isn't specific to the proc they are using.
I just want to know if SQL Server has something similar to what Sybase has with their @@isolation global variable.
Thanks,
Laura
June 15, 2005 at 10:18 am
To prove your suspicions, you could try changing the offending select statements by adding WITH (NOLOCK)? I do not know the answer to your @@isolation question.
Regards
Phil
June 15, 2005 at 10:20 am
Can't find anything on global variables that would ressemble @@isolation.
June 15, 2005 at 2:55 pm
DBCC USEROPTIONS will return a resultset with two columns, the first showing names of different SET options and the second with the current value. One of the rows will have "isolation level" as the SET option, and the second column for that row will show the current isolation level.
June 15, 2005 at 5:23 pm
Remi,
We had a developer (no longer with us) that coded a WHERE Statement (PARTIAL portion) to be WHERE fieldx 'D'. We app. passed a data amount (now have 35+ million rows in this table) that 4 months ago we passed 23+ million. When this happened the execution plan changed in SQL server.
It decided that TABLE scan was the best option. We wound up opening a ticket with MS, sending them a clone of our DB, countless reboots during the day, locking users out for 8 hours at times to allow the batch process that used to run in 30 minutes to finish in 8 hours, etc...
We do daily re-indexing, hourly t-logs, daily FULLs, etc.. could not figure it out. We knew which procedure but not WHY. In our DEV, TEST, Staging env. the proc ran fine. Of course they cannot hold the same volume of data to properly test (will vent on this later).
Anyway, back on point. Our internal Disaster team and MS found the line listed above as the problem. When we changed it to be WHERE fieldx IN ('N', 'R') instead of 'D' (the field can only be 1 of 3 values) go figure.
Of course this led to an 800 hour project to tear apart the entire system to change fro m this and SELECT COUNT(*) FROM table and IF COUNT > 0 to IF EXISTS, etc...
TRUST me when I say this caused a HUGE push to start using profiler, full data loads, etc.. (which our DBA's (former DBA myself)) were saying for a long time
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 16, 2005 at 7:42 am
Hehe, I guess it all comes down to operator precedence (from first to last) :
+ (Positif), - (Négatif), ~ (NOT au niveau du bit)
* (Multiplication), / (Division), % (Modulo)
+ (Addition), + (Concaténation), - (Soustraction)
=, >, =, <=, , !=, !>, !< (Opérateurs de comparaison)
^ (OR exclusif au niveau du bit), & (AND au niveau du bit), | (OR au niveau du bit)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Affectation)
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply