How to troubleshoot and solve Index page locking or/and row locking

Question

How to troubleshoot and solve Index page locking or/and row locking

angelo.dasilva

Newbie

Points: 2
More actions
September 9, 2014 at 5:27 am

#311365

Hello All,
I am facing a lot of page and row locks on the indexes created for a specific table and database but I try to research this one and all the articles aren't that good or maybe I am missing something.
How can I troubleshoot this issue and how the best way to fix it if there is a fix or what can be causing this?
Thanks in advance,
Angelo

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

Without more information this is difficult to assist with, or even ascertain if there is a problem. What sort of locks are you receiving, what's the workload on the data, and what's the DDL in question?

Locking in SQL Server is by design in order to maintain the integrity of your data.

Some reading:- http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

i_amhers Mr or Mrs. 500 Points: 554 More actions · Answer 2

Hey Angelo,

If you ( or anyone ) are running into deadlocks you need to see the deadlock happening to really figure out how to resolve.

The very best way I have found is with an amazing tool in SQL Server Profiler: Errors and warnings -- Blocked Process Report

This captures EVERYTHING, and as such is very intensive. You can google all about setup on this tool, but let me clearly say this, if run in a production environment can be HEAVY, so you will only want to run it AS NEEDED.

But once you have a capture with this, you will have everything you need to review execution plans and find out where the deadlocks are happening and why. Even information to pass back to the developers ( unless you are him also ).

Hope that helps, and if not just ping back with more questions!

Kind Regards,

Jeremy

angelo.dasilva Newbie Points: 2 More actions · Answer 3

Hey Jeremy,

Thanks for the info. I think it could be useful.

Just one quick question where can I find that tool in the SQL Server profiler? or is it a add-on to the tool? I am using SQL Server 2012 and I don't see any trace or tool with that name.

I am the DBA so yes probably I will need to talk with the developers but first I need more info.

Yes I can see that I have a quite amount of Deadlocks(2089) since the live of this machine in 2012, but I am a DBA in here since February this year.

Can you send me more info about that tool?

Thanks,

Angelo

i_amhers Mr or Mrs. 500 Points: 554 More actions · Answer 4

Angelo,

SQL Server Profiler is something part of the whole Standard package. What level of SQL do you have? ( standard/enterprise )

Here is a great run through with lots of information that you should find useful: https://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/

Let me know if you still have questions!

Jeremy