March 27, 2008 at 7:46 am
Executing simple SELECT statements e.g. SELECT * FROM CONTACT is giving a customer of ours Table level shared locks instead of Page level shared locks. This is causing major problems as it is leading to other update processes on the CONTACT table are timing out. The DB is SQL 2000
This appears to have started following the customer updating to Service pack 4.
We could not recreate the problem on our copy of their DB until we rebuilt the Unique Clustered index.
March 27, 2008 at 7:52 am
March 27, 2008 at 8:16 am
Why is this a problem? If you want to select everything from a table, then you need a lock on the whole table. It doesn't matter whether that's one table lock or several page locks. Probably the best solution would be for you not to use SELECT * FROM queries: only return the columns you need, and use a WHERE clause to filter out the rows that you need, and make sure you have appropriate indexes in order to keep transactions as short as possible. You may consider using the NOLOCK hint or changing the transaction isolation level, but be sure that you understand the implications of this before you do it.
John
March 27, 2008 at 8:26 am
Hi Carolyn,
Do you mean
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?
We are experimenting with this, but I'm still unsure as to why the query optimizer is deciding to use table level locking rather than page level.
I have restored the DB to SQL 2005 and we do not get the problem in 2005
March 27, 2008 at 8:29 am
I'm using SELECT * from as a simple example. Doing SELECT SERIALNO, EMAILADRESS FROM CONTACT WHERE.....
gives us the same problem except that the query running speed is much faster and the problem is less noticeable unless you're looking for it.
March 27, 2008 at 8:38 am
- how often do you rebuild indexes on your db ?
bad indexes result in IO overhead resulting in longer need for locks.
IO is the slowest part of your server !!
Rebuild all indexes, and your problem may disappear.
Also, provide FK-indexes on your dependant tables ! (unless they really hurt performance)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2008 at 8:42 am
The weird thing is that originally we didn't get the problem on our copy of our customer's database.
....until we rebuilt all the indexes (the Unique Clustered Index on SERIALNO in particular).
It appears that for some reason the query optimizer is deciding to use Table level locking instead of Page level, but we don't understand why.
Running the DB in SQL2005 does not give us the problem.
March 27, 2008 at 9:03 am
If volume is increasing, desing flaws may escalate !
is this select * executed from Query analyser or are they using a cursor to walk throug it ?? :ermm:
- You may want to use sql profiler and start a trace to figure out what is exactly going on ..
- or use sp_blocker_pss80 (gives connection details)
http://support.microsoft.com/kb/271509/EN-US/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2008 at 9:26 am
We know what is going on in the sense that:
If We run the following in Query Analyser:
SELECT SERIALNO, TITLE, FIRSTNAME, SURNAME FROM
CONTACT WHERE SURNAME LIKE 'A%'
We get a shared table level lock on the CONTACT table
This will block any update process on the CONTACT table
e.g. UPDATE CONTACT SET SURNAME = 'Smith' FROM CONTACT WHERE SERIALNO = '1000'
until the SELECT has completed. Obviously, if the select statement completes quickly then there's not much of a problem as the update will not have to wait long. However, if the SELECT is slow (for instance we have de-duplication routines that use complex where clauses) then we get problems.
March 27, 2008 at 10:39 am
Are your statistics up to date? If not, then the query optimiser might not be able to make the correct decision about what type of lock to take for the SELECT statement. For instance, say your statistics were last updated when half of the contact names began with A, but now only one in 26 does. This might be why the locks are being escalated.
John
March 27, 2008 at 1:47 pm
ben.ashton (3/27/2008)
If We run the following in Query Analyser:SELECT SERIALNO, TITLE, FIRSTNAME, SURNAME FROM
CONTACT WHERE SURNAME LIKE 'A%'
If you run that in Query analyser with the execution plan enabled, what do you see for the read of contact? Index seek, index scan, clustered index seek, table scan?
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
March 27, 2008 at 2:50 pm
March 27, 2008 at 3:04 pm
Read committed is the default isolation level for SQL Server. There's no need to set it explicitly
If a query uses read committed and tries to read a row that's being modified by another transaction, the query will wait until the other transaction is complete and the lock is released.
The isolation level that behaves like you describe is Snapshot Isolation or Read-Committed Snapshot Isolation, but those are only in SQL 2005 and above.
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
March 28, 2008 at 12:52 am
GilaMonster (3/27/2008)
Read committed is the default isolation level for SQL Server. There's no need to set it explicitly....
Well, you know how it goes with defaults , someone may have tampered with the settings, and off you go.
btw Isn't a default oledb or sqlncli connection using repeatable read ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 28, 2008 at 3:07 am
Just to go over the problem -
We are running SELECT statements on the CONTACT table (using the default READ COMMITTED isolation level). When we do this we get shared locks as you would expect. However, instead of getting PAGE level locking, we are getting TABLE level locking. i.e. the Query Optimizer is deciding to escalate the locking to Shared TABLE level locking. This causes us a problem as some the SELECT statements take a long time to run. During this time any edits to the CONTACT table have to wait. This is leading to users getting timeouts.
All indexes have been re-built and statistics updated.
I guess my first question is would you expect this to happen? Is this just the way the Optimizer works?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply