Query Slow Performance

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 10, 2009 at 8:02 am

  #1077288

  but please may i ask about the tools you used??

  In this particular case I think Chris mentioned that the query plan xml actually contains missing index information. But in general performance tuning is a HUGE topic that takes lots of study and lots of experience to become good at. Find some books, blogs, articles, classes and start learning. Hire a tuning consultant to mentor you while reviewing your system for you.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Chris Howarth-536003

  SSCommitted

  Points: 1775

  More actions

  November 10, 2009 at 8:11 am

  #1077292

  In terms of the tools that can be used to identify this missing index yourself, have a look at the execution plan that you posted to the forum.

  At the top you'll see a section of text starting with 'Missing Index' - if you right-click on the text and select 'Missing Index Details...' the index creation script will open in a new window.

  The suggestions that are offered are not always optimal, but they are often a good starting point.

  Other than reviewing and selectively implementing the suggestions that SQL Server offers, it's usually a case of learning to interpret execution plans and then how to optimise queries and your database design to help ensure that SQL Server chooses an optimal plan. This ebook is a good starting point if you're interested in learning more:

  http://www.red-gate.com/specials/Grant.htm?utm_source=simpletalk?utm_medium=email?utm_content=Grant080527?utm_campaign=sqltoolbelt

  Chris

• Paul White

  SSC Guru

  Points: 150457

  More actions

  November 10, 2009 at 2:27 pm

  #1077533

  sindbad7000 (11/10/2009)

  ---

  Thanks alot it works... now it retrieves the 5200 records in only one second...thanks alot again i appreciate that alot

  but please may i ask about the tools you used??

  I looked at the actual query plan you kindly uploaded. The stand-out features were the clustered index scan of the TransactionDetail table and the table scan of the temporary table.

  Full scans on the inner side of a loop join are rarely good news - especially when the outer input has many rows, and so does the table being scanned. If you hadn't been able to create the new indexes, I would have suggested forcing a HASH or MERGE join for those parts of the plan. Not as optimal as the index by any means, but at least TransactionDetail would only have been scanned once.

  I wrote the index definition by inspection: clearly the lookup was on the ID (which I mistakenly assumed was UNIQUE). Adding the included columns wasn't required, by they were listed as output columns in the plan so I thought I'd add them for neatness, and to avoid a key lookup.

  I could have saved myself 2 minutes' work by looking at the 'missing index' information in the plan - but the way forward was pretty clear anyway.

  As TheSQLGuru mentioned, it's most just a question of experience 🙂

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi