October 24, 2002 at 9:56 am
Most of the locks on my SQL server 7.0 are on DB reosurces. I expect DB locks to be for backups and restores. Some users tend to have large number of locks when the simple query they are running does not require such large locks. For example one user at a point had 249 locks on the DB.
What is happening and how can I resolve the problem.? Unfortunatley the application has the querires built into the GUI so I don't have access to them.
Thanks for your help.
October 24, 2002 at 4:49 pm
What types of locks are you having? Executes sp_lock. Remember that shared locks (S) occur when a user connects to the database or access a table with a select statement. This lock prevents other users to delete or modified the database or table, but allow other users to access or executes selects in those tables.
The exclusive lock (X), occurs when you are updating or deleteing a table or row, and does'n allow other users to access with any statement.
October 25, 2002 at 3:28 am
They are shared locks as indicated when i run sp_lock.
Thanks
October 25, 2002 at 3:43 am
I have experiences something similar before. If you have a queries which is returning a large number of rows from your database and these rows exist in pages which are fragmented throughout your data file, it is possible for SQL to take a row lock for each record.
You have to remember that there is an overhead for the each lock which is taken and therefore minimising the total number of locks taken will increase performance.
You will have to review all queries on the specified table, but changing the clustered index can result in the rows being requesting being in a contiguous block and therefore SQL will take extant or pages locks.
October 25, 2002 at 9:57 am
Thanks for your help. This sounds possible.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply