Find potential locks caused by joined queries

  • 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?

  • 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

  • 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

  • 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.

  • 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!

  • Thanks for the quick responses. What is a .sqlplan? Is it the estimated execution plan from ssms?

  • 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/[/url]

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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!

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • The plan was running on our staging server, which has most of the data.

    My web server monitoring was indicating that this query was taking a long time during the heavy load and after reading that left outer joins lock the entire table on the left, I assumed this was the issue. Now knowing that the locking statement is false, I doubt this is the query I'm looking for.

    I read about the left outer join locking in a forum, which I suspected could be incorrect. This is why I started this thread to learn how to check on the locking.

    I have another query that is being flagged as slow and after analysis has 7 suggested indexes with a 26% improvement. I attached the .sqlplan for that one here. I would appreciate it if you could take a look.

    Thanks,

    Donnie

  • thre you go Donnie;

    actual rows 3, but the estimated rows is almost a quarter million.

    run this for the three tables involved in this query:

    UPDATE STATISTICS [dbo].[VW_DirectReports] WITH FULLSCAN ;

    UPDATE STATISTICS [dbo].[VW_Departments] WITH FULLSCAN ;

    UPDATE STATISTICS [dbo].[VW_Employee_Check_In_Status] WITH FULLSCAN ;

    and consider adding an update statistics script to run more often that whatever is doing it now.

    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!

  • Awesome! Thanks Lowell. The 3 tables you mentioned are actually views. I'm guessing I should actually update the statistics on the tables included in the views. Is this correct?

  • yes that is correct; find the underlying tables of those views, and update stats on them;

    you could also use something like this to blindly update all stats for all tables as a shotgun approach:

    DECLARE @Exec VARCHAR(MAX)

    SELECT @Exec = ''

    SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec

    FROM sys.tables

    ORDER BY name DESC

    PRINT LEN(@Exec)

    PRINT @Exec

    EXEC(@Exec)

    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!

  • Thanks again.

    Is the goal to get the actual number of rows and estimated number of rows to be equal?

    Donnie

Viewing 15 posts - 1 through 15 (of 23 total)

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