Activity performance degradation

Question

Activity performance degradation

blue_inelush

Ten Centuries

Points: 1118
More actions
April 7, 2010 at 6:33 am

#146401

Hello,
I have a situation...
From time to time... there is a massive number of procceses waiting on deadlocks.
Please advise me what should I do in order to solve this issue steps by steps...
The SQL Server is escalating locks at a rate of 0.13/sec and bigger.
I don't know from where to start and what to do.
Thanks a lot!
Wish you good ideas! 🙂
Andreea

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

First of all, I'm not sure you really mean Deadlock because deadlocks will resolve with an error that tells you that your process was deadlocked with another process and your process was the victim.

It sounds more like you have excessive blocking because you have long running transactions and/or processes that are accessing more resources than they really need.

You can also look and see what process is the top level blocker using the last script in this blog post by Glenn Berry.

I'd start by finding the longest running queries and tuning them. sys.dm_exec_query_stats is the place to find that information.

You could also look at your indexing strategy, do you have any indexes? sys.dm_db_index_usage_stats, look for user scans.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

In case you can't get to Glenn's blog link (I couldn't launch it), here is an alternative script that I use. It is not a SQL2K5 script but still works to find the top level blocker.

http://www.databasejournal.com/scripts/article.php/1492751/Script-to-Display-Current-SQL-Activity.htm

Once you find what is involved in the blocking, I would look at optimizing the queries involved. You may also need to optimize indexes used by the queries.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

blue_inelush Ten Centuries Points: 1118 More actions · Answer 3

Yes, there was a little error in my description.

Thank you both for the info and links, I will read them and I'll decide what to do.

There is a warehouse application implemented in here. And the thing go like this

Regarding the most "expensive" query. the situation looks like this: there are 2 types of operators: the first group make inserts into some tables using (xlock, rowlock)

The second one makes updates on the objects on which the first one applies (xlock,rowlock).

And... there are moments when ALL the men from the second group are blocked (totally, the proccesses are all blocked) from the first group activity.

And I saw yesterday that on the most popular table involved in this situation I had at a certain moment around 3000 lock counts on the primary key clustered index of that table.

Thanks a lot 🙂

Wish you good ideas! 🙂
Andreea

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

When you say warehouse application, do you mean an application that runs a warehouse facility or a data warehouse?

It sounds like your queries are doing clustered index scans, which means they are taking more locks than may be necessary, so you may benefit from non-clustered indexes, or possibly changing your clustering key, especially if it is not sequential like a date or identity.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

blue_inelush Ten Centuries Points: 1118 More actions · Answer 5

When I say warehouse application, I mean an application that runs a warehouse facility, not a data warehouse.

🙂

Wish you good ideas! 🙂
Andreea

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I had to ask the warehouse question because it does make a difference.

Are the inserts/updates typically single row or sets?

Does the application use stored procedures or ad hoc sql?

Is this an in-house developed application or third-party?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question