Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Find potential locks caused by joined queries Expand / Collapse
Author
Message
Posted Thursday, August 01, 2013 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:35 AM
Points: 10, Visits: 39
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?
Post #1480003
Posted Thursday, August 01, 2013 11:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480041
Posted Thursday, August 01, 2013 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:35 AM
Points: 10, Visits: 39
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
Post #1480066
Posted Thursday, August 01, 2013 12:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480068
Posted Thursday, August 01, 2013 12:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480070
Posted Thursday, August 01, 2013 12:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:35 AM
Points: 10, Visits: 39
Thanks for the quick responses. What is a .sqlplan? Is it the estimated execution plan from ssms?
Post #1480084
Posted Thursday, August 01, 2013 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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)
Post #1480099
Posted Thursday, August 01, 2013 12:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480101
Posted Thursday, August 01, 2013 1:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:35 AM
Points: 10, Visits: 39
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.

  Post Attachments 
rpt.zip (3 views, 2.01 KB)
Post #1480107
Posted Thursday, August 01, 2013 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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)
Post #1480122
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse