June 6, 2008 at 2:11 pm
I have long running query it has many sql statements with joins etc.. when i try cheking for performance by Execution PLan, i got lot many diagrmaetical representions. I its real hard for me to check each n every thing, is there an easy way to check all those.
June 6, 2008 at 2:33 pm
Checking everything is part of the job. First things to look for are Table Scans and Clustered Index Scans(basically a table scan). If you can eliminate these you are off to a good start.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 6, 2008 at 2:43 pm
If you save the execution plan and attach it to a post someone might point something out; In the Excution plan window, right-click and Save Execution Plan As...
Also, have a look at Dissecting SQL Server Execution Plans by Grant Fritchey. Well worth the read.
http://www.simple-talk.com/sql/performance/execution-plan-basics/
June 6, 2008 at 2:46 pm
Yes, I could not remember where I had seen Grant's article. I thought it has been posted on SSC, but I could not find it. It is a good resource for this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 6, 2008 at 3:03 pm
Hey guys take a look at my Execution PLan I have attached as zip file.
thanks
June 8, 2008 at 8:58 pm
Holy Crap!
This may be the sickest thing I've ever seen!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2008 at 10:37 pm
Any chance of getting the fully expanded query?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 5:34 am
It looks like you have 292 index scans in your query. I'd start off looking to improve a small part of it and then work upwards....whatever your code is, it looks very long to solve in one go.
start with the table "sites" which looks to be at the top of the query.
post the SQL affecting that table, and also the DDL for the table.
You're doing something small wrong...a lot of times. Possibly no indices?, or just have indices of little value?
June 9, 2008 at 5:57 am
I don't know exactly what you are trying to do in your query, but have you considered doing it in different chunks? Sometimes, breaking it up by using temp tables or other can drastically improve performance.
---------------------------
|Ted Pin >>
June 9, 2008 at 6:22 am
I have to say, that's not a shape for an execution plan that I've seen before. Like the others have said, concentrate on the scans. You're missing JOIN predicates which is going to return tons of data. You'll just have to break it down. Reconsider your logic, are you doing things in the most operative way.
BTW, can I use this plan in an article I'm working on? It's kind of special.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2008 at 6:45 am
I saw a lot of possible redundancy in the query plan, so it is possible that either some consolidation using WITH, or using Temp tables could work here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 7:29 am
Querying the XML plan directly could help focus in on parts of that plan to look at.
-- Load the plan into an XML variable
DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET (BULK 'C:\temp\ep2.sqlplan', SINGLE_BLOB) TempXML
-- Sort by which operator has the highest estimated IO or CPU.
SELECT
RelOp.op.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
@NodeId', 'int') as NodeId,
RelOp.op.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
@PhysicalOp', 'varchar(50)') as PhysicalOp,
RelOp.op.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
@EstimatedTotalSubtreeCost ', 'float') as EstimatedTotalSubtreeCost,
RelOp.op.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
@EstimateIO ', 'float') as EstimateIO,
RelOp.op.value('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
@EstimateCPU ', 'float') as EstimateCPU
FROM @xml.nodes('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//RelOp') Relop(op)
ORDER BY EstimateIO DESC
References:
Loading XML from the File System : by Michael Coles
Welcome to Pro SQL Server XML : by Michael Coles
June 14, 2008 at 11:56 am
rbarryyoung (6/8/2008)
Any chance of getting the fully expanded query?
It's a shame we never got the query. I was hoping to work on it today.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply