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


Query Slow Performance


Query Slow Performance

Author
Message
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
A full optimization will take a few minutes, but the main problem seems to be a missing index, something like:


CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.TransactionDetail TransactionId (AccountId, Direction, Amount)]
ON [dbo].[TransactionDetail] (TransactionId ASC)
INCLUDE (AccountId, Direction, Amount)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, ONLINE = OFF);


and change the temporary table creation to:

CREATE TABLE #Accounts (AccountNumber nvarchar(20) PRIMARY KEY);





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 1158
Paul's suggestion is good, and is consistent with the missing index that was suggested by SQL Server in the execution plan that you provided.

If you almost always return rows from dbo.TransactionDetail by looking up the TransactionID column then you may wish to consider changing PK_TransactionDetail to be NONCLUSTERED, then creating a CLUSTERED index on the TransactionID column. This change would help you minimise additional disk space requirements and would also likely improve performance of other queries (those based on looking-up based on the TransactionID column). This would be done instead of creating the 'missing index' suggested previously.

Chris
sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
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??
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 1158
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??


Which suggestion did you choose to implement?

Chris
sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
Paul's Suggestion

CREATE NONCLUSTERED INDEX [UQ dbo.TransactionDetail TransactionId (AccountId, Direction, Amount)]
ON [dbo].[TransactionDetail] (TransactionId ASC)
INCLUDE (AccountId, Direction, Amount)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, ONLINE = OFF);


but i removed the UNIQUE from the index because this field 'TransactionId' is not UNIQUE
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6004 Visits: 8314
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 at GMail
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 1158
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
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