Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find potential locks caused by joined queries


Find potential locks caused by joined queries

Author
Message
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 51
I have an application that is slowing down under a fairly heavy load and I believe some of my relational logic are creating locks that is creating a queue. Is there a way to use SSMS to analyze a query to determine what type of lock the server will attempt to place on the resources?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35973 Visits: 18725
SSMS doesn't do this directly, but you can query sys.locks for information. You might start, however, by examining the execution plan and seeing if you have issues with your code.

Why do you suspect locks?

Note this isn't a design question. Moving to T-SQL

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 51
Thanks.

I suspect locking because we had a client run a test against the web application and we got very poor response times for a low number of requests, ~2000. After reviewing the queries in the web application, several of them are using left outer joins on the main tables that every request accesses. I've read that a left outer join will lock the entire table on the right. I was hoping to test my new queries by proving the old left outer join version locking is holding up the other requests and then deploy my refined query and see of the locking is still occurring at a table level.

I have checked the execution plan and I don't believe their are any issues. Any suggestions of things I should look for especially for left outer joins?

I found some scripts that will show the locking; however, I am new at reviewing the results. I'm guessing I can run a load against the application and then query sys.locks to see what comes up. Is that correct?

Donnie
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35973 Visits: 18725
left outer joins don't necessarily lock the table. Would like to know where you read that, since it's wrong.

The outer joins often scan the whole table because you may end up with pulling a large percentage of rows if there aren't other restrictions, so that can be an issue.

I'd look at code first, locks next.

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
agreeing with Steve, here; All SELECT queries take out Shared Locks for the period of time it takes to get the data; those shared locks might delay an update/insert/delete, but i don't think you'll see a SELECT block something, it's the other way around, an insert/update/delete can block other selects as well as insert/update/deletes.

if you have a left join, and the child table has a lot of rows, an index on the foreign key'd column might help, but to really know, we'd want to look at the .sqlplan, if you are able to post it.

performance problems for me are reviewed in the following order of probable pain points.:
SARG-ability of the queries ,
statistics being out of date
proper indexing to support the queries,
then maybe disk IO,, and the finally reviewing locking and blocking.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 51
Thanks for the quick responses. What is a .sqlplan? Is it the estimated execution plan from ssms?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
Donnie Carvajal (8/1/2013)
Thanks for the quick responses. What is a .sqlplan? Is it the estimated execution plan from ssms?


The estimated plan could be saved as a .sqlplan just like the actual plan. For our purposes we would want to see the actual plan, not the estimated plan.

Take a look at this article from Gail which explains how to find and post the execution plan.

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
Donnie Carvajal (8/1/2013)
Thanks for the quick responses. What is a .sqlplan? Is it the estimated execution plan from ssms?


both the estimated execution plan and the actual execution plan can be right clicked and saved as a .sqlplan, which is just an xml file with a custom extension.

The ACTUAL execution plan is much more valuable(it contains the estimates too!); estimates can be guess-timates, and the actual execution plan shows the differences between the estimated and actual...an example might be the estimate expected 10 rows, but the actual execution ended up getting a million rows due to bad statistics or something.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Donnie Carvajal
Donnie Carvajal
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 51
Here is the .sqlplan for the one of the queries that I anticipate may be causing a bottle neck as it is referenced on every request.
Attachments
rpt.zip (3 views, 2.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
Donnie Carvajal (8/1/2013)
Here is the .sqlplan for the one of the queries that I anticipate may be causing a bottle neck as it is referenced on every request.


Unless I am missing something I don't see how this could be a bottleneck. The estimated and actual number of rows is 1 for all three tables. Is this from running it on a dev system or something where most of the data has been removed?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search