Since the dawn of man, people have struggled with sharing execution plans with each other for performance tuning. Now, it’s easy.
First, get yourself a plan:
- Get the estimated execution plan by hitting control-L or clicking Query, Display Estimated Plan. Right-click on the graphical plan, and click View XML. Copy all of that.
- Or even better, get the actual plan by first clicking Query, Include Actual Execution Plan, and then running the query. After it finishes, click on the Execution Plan tab, right-click on the plan, and click View XML. Copy all of that.
- Don’t know which query to check out? Get your top 10 most resource-intensive queries using the totally free sp_BlitzCache.
Make sure it doesn’t have private data. Your company may consider database names, index names, queries, or parameters to be private data. (This same advice holds true whenever you script a database, give someone a query, or post a question online, but I feel like I just gotta repeat it here to be clear.)
Then go to PasteThePlan.com and paste it in:
It’s a free community service. We hope you love it as much as we do, especially all the cool new execution plan icons by our illustrator, Eric Larsen.
Down the road, we’re thinking about adding logins (so you can see past plans you’ve submitted), execution plan advice, image and HTML downloads (so you can embed the plan in your own blog or report), zooming, and more.
Show Us Your Worst Plans
and Win an Everything Bundle
We wanna see the worst queries you can cram under the upload size limit (2MB). We’re specifically looking for:
- Lots of different operators (like, as many different plan operators as you can get into a plan)
- Huge, ugly statements
- Large numbers of statements that do crazy things
They don’t have to be “in-the-wild” queries – you can totally design and build your own test queries to enter.
If you’re entering the contest, leave a comment here with your plan URL and what you think is bad about the plan. Entry deadline is Sunday, September 18th. We’ll judge what we think are the 5 worst execution plans, and we’ll give each winner a free Everything Bundle.
18 Comments. Leave new
Hi.
The actual T-SQL query queries actually several nested views and uses a lot of UDFs so this is really bad performing. I am kind of stuck trying to make this faster.
https://www.brentozar.com/pastetheplan/?id=S1uW0oS3
I see in the plan XML: QueryPlan DegreeOfParallelism=”0″ NonParallelPlanReason=”CouldNotGenerateValidParallelPlan”
I also see you have a MAXDOP hint on here — I assume you’re not hinting to MAXDOP 1, because the reason up there would be MaxDopSetToOne, or something similar. I’d start by figuring out what’s causing my plan to not go parallel, and then settle in to untangle all that nested view logic.
Thanks!
Hi,
yes I tried to force parallelism. I think it is the scalar UDFs that are preventing parallelism.
Reason as seen on plan is not very informative.
By the way, UDFs (and some views that use those) are from Microsoft CRM core and somebody has even left/forgot todo comment there saying ‘this udf is not good, make something else for performance’…
Haha.
Bunches of left joins, large tables. Note the number of rows as the proc approaches completion – are there really numbers this big??? Surprisingly, the procedure usually finishes executing in not an outlandish amount of time.
https://www.brentozar.com/pastetheplan/?id=HJMchor2
Nesting of Views, muliple Parallelism operator, Sort operator before Merge, too many joins, table scans, UDX and query compilation timeout.
https://www.brentozar.com/pastetheplan/?id=By-9WwL3
Hi, I uploaded query plan https://www.brentozar.com/pastetheplan/?id=H1fneOUn
I found it in the wild and got more of those nice queries. They are created by Business Objects’ reporting engine using an universe of the Deltek Maconomy ERP system. Unfortunately there are not many chances to optimize them since they contain views on views (no indexes allowed, you know), we don’t access to the source code, etc.
Enjoy
Stefan
Heyho, I also got a plan for you: https://www.brentozar.com/pastetheplan/?id=BJUmiuUh
It was a creation of one of our customers and should normalize some adresses for the logistics provider. This thing runs every 10minutes.
Consisting multiple levels of: “UPDATE … SELECT …Where in (Select …) OR …” and replaces all over the query and bad “LIKE %XXX%” comparissions. This things takes ages to complete and got an avg reads count of 72M and only updates an avg of 13 rows per run.
PS.: I did alter the xml file becuase it was to large to paste. I did linarize the xml. I hope this isn’t a problem.
Stephan – wow, that’s neat! That’s pretty bad alright.
Unfortunately, for the contest, we’re only taking plans as-is. (I can see a spot in the XML where changes were made – I’m surprised it actually validated! That’s cool.)
Hi Brent,
i mean plan is not changed in meanings of structure, query-information or anything else. I did only change the “formatting” of the xml (Notepadd++ -> XML-Tools-> Linarize XML) to get around the 2MB limit. Because this beauty beast was 3.3MB when i exported it from the ssms. It Basically removes the tabs and newline-chars around the xml-tags but don’t touches anything inside the tags.
If this realy is a problem, maybe i can file another one. I got plenty of these…unfortunately.
Heyho Guys,
i got another example of a really bad plan (https://www.brentozar.com/pastetheplan/?id=H1kSnVF2). This time i didn’t altered the xml. But it looks like the “Paste The Plan”-Query View is a bit wrong. There are missing multiple steps from the plan. Here is the corresponding sqlplan-file for comparission (https://1drv.ms/u/s!AtWeH_XCvBDMgaoXVD4t5r78tCyl3Q)
Maybe this one helps me enter the contest. 😉
https://www.brentozar.com/pastetheplan/?id=S1ibuWPh
A portion of a much longer stored procedure that is supposed to extract results for standardized test analysis. It takes about 10 minutes to run on a good day and I’m not entirely certain if the entire plan made it in (I can’t find the key lookups in the plan, for example).
What’s wrong with it? Really bad estimates (we’re only allowed to run Ola’s index optimize script once a week on Sundays and I don’t think it would help since we’re dealing with so many conversions anyway), 6 implicit conversions (what is an integer in one table is treated as a varchar in another and vice versa), casting to a varchar without specifying the length of the column, several select * sections of the query, 2 missing indexes (that are near-worthless because of the select * and it’s a large wide table), string manipulation, multiple ordering clauses, and all running non-parallel (the VM only has 4 cores allocated anyway)
In checking out the new site today, I noticed that the “our favorite Q&A sites” link on the Paste The Plan FAQ page returns a 404 at the moment. Thought I’d let you know.
It currently targets the following link.
https://www.brentozar.com/pastetheplan/getting-help-plan/
Hmm, working fine here?
OH! I see what you mean, fixed. Sorry about that!
I win the worst of them all… at a clients site, totally anonymized (thank you SQL Sentry Plan Explorer FREE VERSION!)
https://learnfrom.brentozar.com/pastetheplan/?id=rJzy4xER
Oh wow, that IS ugly. Wow.
I didn’t make that up… it’s production code… I have my gig made out for me.