SQL Server Performance

Deadlocking and Query Optimisation

Discussion in 'SQL Server 2005 General DBA Questions' started by viksar, Jan 23, 2007.

  1. viksar New Member

    Why would a "Select" statement be chosen as deadlock victim. I am running this single transaction in query analyzer and this query is got deadlocked. "Select" statements just do "Reads" on database , so why would they deadlocked.......Any idea

    select ordernumber, sum(ordered), s2.storename, left(sod.source, 4)
    from SalesOrder so
    join SalesOrderDetail sod on sod.SalesOrderID = so.ID
    join StyleColourSize scs on sod.Barcode = scs.Barcode
    join CTS_Store store on so.Branch = store.storename
    join CTS_Store s2 on s2.PerOrgCode = store.DefaultDistHub
    where styleno = '160275'
    and colour = 'kelp'
    and ordered <> 0
    group by ordernumber, s2.storename, left(sod.source, 4)
  2. MohammedU New Member

    In SQL server default ISOLATION is READ COMMITTED
    so SELECT statement also required to get the row/page/table locks...

    What is the other statement invovled in this dead lock...

    To avoid this behaviour you can use NOLOCK hint or ISOLATION LEVEL READ_UNCOMMITTED but you get dirty data....


    Mohammed U.
  3. viksar New Member

    Not sure for other statement as I was not running any profiler.

    Could you please brief out what you mean by "dirty data" and what kind of locks does "select" statement apply on database.

    Does READ COMMITED type also holds the data and cause deadlocks ?
  4. Luis Martin Moderator

    Did you look execution plan?
    What indexes are using?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. Luis Martin Moderator

  6. viksar New Member

    Attach is the index info from execution plan

    Its using 2 types of scan

    1. Clustered Index Scan on StyleColorSize which cost to (30%)
    2. Clustered Index Seek on SalesOrderdETAIL WHICH COST upto (41%)
    3. Clustered Index Seek on Sales order (pk_...) which cost upto (24%)

    I am not an expert, after you explain above can you also explain whats differnce between

    SCAN and SEEK and which one is better also how to read execution plan and figure out areas which are issues ?

    Thanks a lot for your help and advice
  7. Luis Martin Moderator

    After reading my last link:

    Did you try DTA to find better indexes?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  8. viksar New Member

    Hi Luis,

    Sorry didn't mean to duplicate the data.

    Apreciate your answers

    thanks
  9. viksar New Member

    Your last link refer to my duplicate forum and not to any Link .
  10. Luis Martin Moderator

    No problem.

    Run the query with first:

    SET STATISTIC IO ON

    and finish with:

    SET STATISTICS IO OFF

    and share you results.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  11. viksar New Member

    Its not deadlockingm may be other query or process at time is not running, but the execution plan for this is same as before
  12. Roji. P. Thomas New Member

    To resolve the deadlock issue, you need to find the other brocess that caused the deadlock. Most likely you have an UPDATE statement running on the StyleColorSize table.

    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com
  13. satya Moderator

    One question, is this related to SQL 2005. If so you can have better changes to optimize the query performance and avoid deadlocks in this case. Also mention about SQL service pack and no. of users on thsi server for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  14. viksar New Member

    Yes its on SQL server 2005 with service pack 1 installed on the server. These servers are clustered and are alsop being replicated. Database size is around 180 GB. Number of users connected are around 200.
  15. viksar New Member

    Also, I often run queries in Read Uncommitted mode when checking up on data, but when doing data updates I need to know the currently committed data. I know that read committed gets read locks, and so I expect to get blocked by update or exclusive locks from other processes. My real concern is that I should only be blocked; a single sql statement, or a transaction with only one sql statement, should never get deadlocked. It either gets the lock first and completes, or it gets blocked until the other process completes. But it is never holding any other locks open, because it's just a single statement, so it can't be both blocking and waiting (which is a requisite for deadlocking.)

    The only thing I can think of is that SQL Server grabbed the read locks incrementally:
    My process got the read lock for the first table in my query
    Then process 2 got an update lock on the second table
    Then my process tried to get a read lock on the second table it joined to, but was blocked by process 2
    Then process 2 tried to get an update lock on the first table

    That's the only possibility I can come up with, but it doesn't make sense, because I thought queries were atomic. If this sort of thing could happen, you'd expect to get deadlocks all the time, as people queried tables that stored procs were updating transactionally.
  16. satya Moderator

    The old copies of the updated row or rows are stored in the tempdb database. If the updated row is committed, the row version in the tempdb is deleted. If the update is aborted, the old values for the row(s) are restored in the original table, and the copy in the tempdb is deleted.

    The SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement is optional if you enable the READ COMMITTED isolation. So your existing applications can automatically make use of this feature without any change to stored procedures or any embedded SQL. Try to use this option for these queries.



    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  17. viksar New Member

    Thanks for the information Satya, however I still didn't understand because max a "select" statement can do is cause a block, however how can that be deadlocked ??

  18. Roji. P. Thomas New Member

    quote:Originally posted by viksar

    Thanks for the information Satya, however I still didn't understand because max a "select" statement can do is cause a block, however how can that be deadlocked ??

    Technically it is poosible that a select being deadlocked with an update. Can you post the code which updates the StyleColorSize table along with the indexes on that table.?

    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com
  19. MohammedU New Member

  20. Roji. P. Thomas New Member

  21. viksar New Member

    Thanks guys for such great info, these articles are definatelly informative.

    I want few more tips on issues related to deadlocking.

    Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.
  22. Luis Martin Moderator

    "Also How can i read out execution plan results and understand the output of execution plan and query cost."

    This one is for Yukon, but may be can help you.

    http://www.sql-server-performance.com/jc_yukon_beta1_execution_plans.asp



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  23. MohammedU New Member

  24. viksar New Member

    This book is yet to be released so can't view, are there any more online topics or books available online on better undertanding of Query optimization, Index seek and scan, execution plans and query cost. Probably which helps in making better queries and SP's

  25. MohammedU New Member

    It will be released in 2-3 months...I forgot about it...
    Check BOL topic: graphical execution plan [SQL Server]

    Mohammed U.
  26. Roji. P. Thomas New Member

    quote:Originally posted by viksar

    Thanks guys for such great info, these articles are definatelly informative.

    I want few more tips on issues related to deadlocking.

    Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.


    See Craig Freedman's blog posts on the topic.

    http://blogs.msdn.com/craigfr/archive/2006/06/26/647852.aspx

    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com
  27. viksar New Member

    hey guys, do you know any links on checking for performance.

    I want few more tips on issues related to deadlocking.

    Whats the difference between seek and scan and which is more useful, Also How can i read out execution plan results and understand the output of execution plan and query cost.
  28. MohammedU New Member

  29. satya Moderator

    Viksar
    I would suggest to refer the links above before asking any more question, hope you understand what I mean.

    http://www.sql-server-performance.com/query_execution_plan_analysis.asp is a good one on understanding Execution plan analysis.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page