excessive locks

Last Post 14 Dec 2006 05:50 PM by ufohj. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
TRACEYSQL
New Member
New Member

--
06 Dec 2006 12:40 PM
I have one program that is from our vendor erp system and
the locks on one table go up to 13018 if not higher than this.

That seems a high amount.....does each lock consume memory

Our CPU will sit at 100% aswell and this is the only thing running.


This is on one table...that it says 13018 locks.

Im trying to figure out why and what i need in order to support the SQL Server....Increase locks or increase memory

Any advice is appreciated
JHunter
New Member
New Member

--
06 Dec 2006 03:48 PM
Do you have access to the tSQL that is being executed? Have you had a look at the server with profiler to see whats going on?

Usually excessive locking is down to bad indexes, poorly designed cursors and/or long runing transactions.

Jamie
SQLUSA
New Member
New Member

--
07 Dec 2006 02:12 AM
Do you reindex all the tables every night?

Are you monitoring with SQL profiler? Missing indexes?

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
TRACEYSQL
New Member
New Member

--
07 Dec 2006 05:24 AM
The one table in question that has over 15237 locks today it goes to 17054 has no index on it at all.

Locks are X and IX types.....

Can't do the index as there is nothing there....
This table gets written to when the process is running then it clears it out....at end of it.

Should i add a new column to it ...and then make this indentity and clustered ? and try again.

im thinking that my SQL cannot handle this many locks at all...................if each lock uses memory
and memory is already max out.

TRACEYSQL
New Member
New Member

--
07 Dec 2006 10:05 AM
Thanks i forwarded it on to them today.
JHunter
New Member
New Member

--
07 Dec 2006 01:01 PM
If you're unsure what to index, capture the statements during the period of poor performance in profiler, then run the trace through the Index Tuning Wizard.

Its not brilliant but it can help when your not familiar with the queries and how the data is being manipulated.

Jamie
TRACEYSQL
New Member
New Member

--
08 Dec 2006 04:44 AM
So i sent the information to our vendor and said there is x number of locks on one table and no index.
Guess what there was 10 indexes missing for one function. I installed them and it took 4 minutes as apposed to 1.5 - 2 hours....I had to run it twice to make sure ....

Wow i know about indexes but really did not take into account it really does improved the process

Thank you all so much.

Im liking indexes now lol

SQLUSA
New Member
New Member

--
08 Dec 2006 05:30 PM
Yes Tracy, a DBA's life revolves around 3 items:

1. Backups
2. Indexes
3. GROUP BYs

All the other stuff a DBA does is just a sideline. These 3 you have to master.

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World
ufohj
New Member
New Member

--
14 Dec 2006 05:50 PM
quote:

Originally posted by: SQLUSA
Yes Tracy, a DBA's life revolves around 3 items:

1. Backups
2. Indexes
3. GROUP BYs

All the other stuff a DBA does is just a sideline. These 3 you have to master.

Kalman Toth DB, DW & BI Architect, SQL Server Training
URL: http://www.sqlusa.com/order2005grandprix
The Best SQL Server 2005 Training in the World


and Restore
You are not authorized to post a reply.

Acceptable Use Policy