July 22, 2015 at 12:10 pm
Hi All,
I am working on a legacy application and one of the view has degraded performance. I have to fix it,
but the query execution makes me mad as it is a very lengthy plan. Is there an alternative way to understand the plan?
July 22, 2015 at 12:46 pm
Have you tried a 3rd party tool like SQL Sentry?
July 22, 2015 at 8:16 pm
NO because they wont allow to install 3rd party tools
July 22, 2015 at 8:53 pm
I bet if you post the plan here that we can take a look at it and get an idea of what is killing your performance.
-- Itzik Ben-Gan 2001
July 23, 2015 at 7:40 am
thenewbee (7/22/2015)
NO because they wont allow to install 3rd party tools
If no 3rd party tools are allowed, you're stuck with the 3 versions of execution plans (plain text, xml, graphic).
As Alan said, you could share the plan and we could try to identify the problem.
You could also go step by step trying to identify the problems. Remember that GUI plans are read right to left and bottom to top and that xml and graphic plans are exactly the same with a different interface, so you can check both "side to side".
July 23, 2015 at 7:45 am
Luis Cazares (7/23/2015)
Remember that GUI plans are read right to left.
Left-to-right to see the control flow.
Right-to-left to see the data flow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 8:23 am
Hi thenewbee,
I have to review developpers' code in my new role hence I decided to read the ebook:
SQL Server Execution Plans, Second Edition by Grant Fritchey
http://www.sqlservercentral.com/articles/books/94937/
It is very helpful !
In your case I would say to you that you have ensure to use all the features available.
I don't know your level nor if you have read it already however I suggest you have a look at the short paragraph:
Chapter 8: Advanced Topics
Reading Large-scale Execution Plans
It tells:
"Click on the plus sign to open a little window, showing a representation of the entire
execution plan. Keep your mouse button depressed, and drag the cursor across the
window. You'll see that this moves a small "viewing rectangle" around the plan,"
I hope it'll help you.
Good luck.
July 23, 2015 at 10:08 am
Sorry that I couldn't post my plan here. I agree to Luis C. and decided to go step by step.
I wanted some experts commenting how to analyze a very big lengthy plan.( my first lengthy plan)
But I found XML plan very difficult to understand and found very difficult to find CPU time and table scan ( i actually searched table scan)
July 23, 2015 at 10:09 am
Thanks for sharing the link
July 23, 2015 at 10:14 am
Don't try reading the XML plan. Use the graphical one.
You won't find CPU time in the plan. You'll find costs, but they aren't times and can't be used to do anything other than compare to other operators or plans (and they're estimates)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 10:47 am
thenewbee (7/23/2015)
Sorry that I couldn't post my plan here. I agree to Luis C. and decided to go step by step.I wanted some experts commenting how to analyze a very big lengthy plan.( my first lengthy plan)
But I found XML plan very difficult to understand and found very difficult to find CPU time and table scan ( i actually searched table scan)
I periodically have to look at some pretty huge plans for poorly performing queries - there's a science to it and I'm still learning. The first thing I look for is the thing that's using the most resources E.g. If I see a sort with a 96% on it - I have often narrowed down the problem. But it's often not that easy. It's not advantageous to look at everything but there's are a lot of red-flag things I look for in addition to Table Scans such as lazy spools and big Sorts, especially one's with a yellow exclamation point - often an indication that memory is spilling onto the temp db. When you see an exclamation point on any event - it' worth investigating further. For Index Scans/seeks I like to hover over and compare the estimated vs actual rows, huge discrepancies might mean that your stats are out of date or you are experiencing parameter sniffing.
Maxime mentioned Grants Query Plan book which is excellent. I also highly recommend
Inside the SQL Server Query Optimizer by Benjamin Nevarez. It's a great book.
-- Itzik Ben-Gan 2001
July 24, 2015 at 2:50 am
Alan.B (7/23/2015)
Maxime mentioned Grants Query Plan book which is excellent. I also highly recommend
Inside the SQL Server Query Optimizer by Benjamin Nevarez. It's a great book.
These one is definitely in my reading list.
Thanks for pointing it out, maybe I should consider putting it at the top of it then. 🙂
Aside this (and out of subject, sorry about it) I would recommend to read How to become an Exceptional DBA
http://www.red-gate.com/community/books/exceptional-dba-book
This is about an exceptionnal DBA explaining the path on how to become an exceptionnal DBA. This book makes you want to know everything about SQL Server and to be recognized in the community. 😉
Max.
July 26, 2015 at 12:34 am
Attached a test plan, Is it possible to avoid the table scan & clustered index scan in this scenario
July 26, 2015 at 4:11 am
thenewbee (7/26/2015)
Attached a test plan, Is it possible to avoid the table scan & clustered index scan in this scenario
Yes. Create covering indexes for both tables, something like this:
CREATE INDEX ix_member_no ON dbo.member (member_no) INCLUDE (lastname)
CREATE INDEX ix_member_no ON dbo.payment (member_no) INCLUDE (payment_no, payment_dt, payment_amt)
No. Sql Server will still have to do a complete scan of these new indexes because your query returns every row from both tables.
The payment table is a heap i.e. it has no clustered index, and the clustered index on the member table may not be the best choice. If you're interested in advancing your knowledge of SQL Server indexes, then David Durant's series [/url]is a great place to start.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 26, 2015 at 4:14 am
thenewbee (7/22/2015)
Hi All,I am working on a legacy application and one of the view has degraded performance. I have to fix it,
but the query execution makes me mad as it is a very lengthy plan. Is there an alternative way to understand the plan?
Post it up. Folks are likely to recommend points in the query where it can be broken down into functional blocks, possibly using #temp tables.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply