April 17, 2009 at 4:44 pm
Hey all....Wondering if you can help me out. I have a number of extremely complicated execution plans (about 21 icons across and say about 150 icons down...) and I need to add them to a word document that I'm writing. I have tried Print Screen using Snagit and Gadwin, but trying to merge the images is way too time consuming (and I'm really bad at it so they look horrible). When I use the "Zoom to fit", the icons are really really teeny tiny and the text is completely illegible. Is there a way I can easily incorporate these execution plans into my report?
I thought about using the Text-based plan, but the people that are going to be receiving this are not DBAs, in fact not even Geeks of any caliber, so the text-based plans I think are way to complex.
I really appreciate any help you can provide...
Thanks in advance
Chris
April 18, 2009 at 8:15 pm
Don't mind me asking if the people you are sending this document to will not understand the reasoning for it; why do you want to send them the execution plan?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 19, 2009 at 5:29 am
Hi Mohit,
Good point. I am trying to prove to them that we need the resources (people and time especially) to go back and restructure the database/rewrite the stored procedures. I have been trying to get them to realise that our application is not built properly (it was developed by a third-party company) and this is the clearest way I can. These managers have staff working for them that use our application, and the staff are constantly caught between having to meet goals and benchmarks set by the management and dealing with an application that is remarkably slow (for example, refreshing a list of orders can take up to 37 seconds - the list is limited to 150 rows by 5 columns). Having a "simple" query such as pulling a list of orders has an execution plan that is huge, and this is what I need them to see. I have done a brief explanation of each icon, so they'll have a basic understanding, but my goal is to have the complexity of these queries as the main driver for my report.
Thanks,
Chris
April 19, 2009 at 11:07 am
Hi Chris,
This is how approach it; I start up PSSDiag and start recording SQL Query times and KPI (Key-Performance-Indicators); I show them how long a query should take. So in your case that query should execute in maximum 10ms; most likely cause of missing indexes. Also interesting part of PSSDiag is you can see blocking and such. If you have blocking you can get the PSSDiag_Blocker script from MSDN site; but sounds like it you have a bigger problem is bad written code and indexes.
I am tuning a system right now that had no cluster indexes, 0 or 1 index on 95% of the tables with few tables having NON_NULL index created on them. The performance was fine for them when the server was located in the clients building on physical server; now we have moved the server to a virtual server. They are noting how bad the application runs; I have done following so far:
- Any table with more then 500+ records rebuild the PK (Unique) to Clustered. There are many considerations for cluster indexes but since the Developer doesn't know the system I choose this for now. I will change to another key if need arises.
- I got developer to run a query that performers slowly in production and test (both having same clustered indexes) and have been adding proper non-clustered indexes with proper covers.
- Re-writing the stored procedure, the procs I am dealing with developer (doesn't work for us any more) had some strange coding. In one example he dumped the results to the entire table into a temp table and then returned the temp table to the user. There is no processing being done on the temp table just results are dumped and returned. Taking those two lines out alone gave me 20ms.
This is what I am using to tune this serer in question:
- SQL Server Profiler (Events: RPC: Completed, RPC: Starting, Stmt: Completed:, Stmt: Starting) with all columns. I'll run it for maybe 5-10 min when developer is running something on test for me to see how slow it is.
- Then I'll save the profiler log and run it through ClearTrace (free online utility); it sums up the numbers very nicely. It is not as pretty as SQLNexus but works like a charm :D.
Thing is with managers it is alot easier to convince them with numbers; they understand it. Tell them how long the proc in question should take. And say how you can get there. Might even have to take it a $$ value; for example this one proc I tuned once took 2.5 hours; after tuning (rewriting/new indexes) I brought the execution time down to 25 seconds. So my answer was you can let me spend 8 hours one time cost; or you can continue to spend 2.5 hours/month for every and it will probably get worse. I find it it is easier to convince them when these kind of numbers are put forward.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 20, 2009 at 5:49 am
1) Output the query plan as text or XML and post THAT in your document. Those things get HUGEly bloated and make even simple queries look like a nightmare. 🙂
2) Hire a consultant to come and tell your management you need more help! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 20, 2009 at 5:54 am
Hey SQLGuru,
I'm thinking the TextBased plan will be the way I go. Although the graphic-based plan would have been ideal, the Text one will certainly get my point across! 😛
Mohit - Thanks for your reply! I'm playing with PssDiag right now and thinking that this will be a handy tool in my Utilities folder.
Later all........happy geeking!
Chris
April 20, 2009 at 11:07 am
Aye it will be a life savor :).
I been tuning for about 2.5 years now and can't live without it. In SQL 2005+ however use the one that comes with SQL server, SQLDiag.exe (By Default located in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ for 2008), instead of PSSDiag because it gives you more control. It's all XML based so thats a bit annoying but yaa both rocks.
One thing though they can be very heavy on CPU/memory so be cautious about that and don't run it for extended periods of time on heavy used server and by default it produces about 1GB of trace data/5 min on medium used server.
After I finish tuning my current server in question; I'll be making a huge post about things I discovered and things to avoid and why ;-). I hope it helps someone heh.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 20, 2009 at 11:35 am
Hey Mohit - I'll eagerly wait for your post about tuning, I'm very curious to see what you have found in your experience! If I ever get my thoughts organised, I may reply to that big topic with my own experiences!
Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply