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 ««12

Query Slow Performance Expand / Collapse
Author
Message
Posted Tuesday, November 10, 2009 2:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816368
Posted Tuesday, November 10, 2009 5:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 267, Visits: 1,057
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
Post #816424
Posted Tuesday, November 10, 2009 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
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??
Post #816454
Posted Tuesday, November 10, 2009 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 267, Visits: 1,057
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
Post #816514
Posted Tuesday, November 10, 2009 7:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
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
Post #816521
Posted Tuesday, November 10, 2009 8:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 4,134, Visits: 5,854
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
Post #816565
Posted Tuesday, November 10, 2009 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:44 PM
Points: 267, Visits: 1,057
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
Post #816570
Posted Tuesday, November 10, 2009 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816850
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse