November 6, 2020 at 11:54 pm
SQL Server Enterprise
BUILD 13.0.5598.27
3 Queries that when run individually each complete in around 5 secs each. If they are highlighted and executed as 1 (in SQL Server Mgt Studio) it takes around 15 seconds to complete and the total number of rows returned is less than 500. If a UNION ALL is placed between each of the 3 queries and executed it does not completes before I manually stop the query after waiting 15 minutes.
This is all on a SQL Server in the cloud that we have very limited access to so troubleshooting is very limited. Each of the 3 queries are large and complex but they execute quickly when run individually and don't produce a lot of data. There is no ORDER BY involved.
Thoughts on what might could cause this or what I might would look at that someone with DBO level access (nothing above that) could check?
Apologies' but due to restrictions I can't post/share any DDL or the queries themselves and i realize that makes it a lot harder to give advice. Even if I could it would probably be a bad idea considering how lengthy each query is. When paste into SQL Server Mgt Studio such that you can see everything without having to scroll right/left (just up/down) you're looking at around 800 lines.
Thanks
Kindest Regards,
Just say No to Facebook!November 7, 2020 at 2:51 am
I have no reasonable explanation without being able to see an "actual execution plan". I also understand that circumstances won't allow you to post one for this problem.
However, a possible "fix" for this the same advice a doctor might give you. If doing something hurts, stop doing it until you figure out what's wrong and can fix it.
With that bit of mostly useless horse-doctor advice out of the way, you can work your way around this...
So stop what you're doing and try this, instead... You have 3 separate queries already... change them to SELECT INTO's into 3 different temp tables (so you can take advantage of some high performance minimal logging) and the do your Union All query on the 3 temp tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2020 at 1:36 am
Agreed, the best thing would be to review the query plan. Even the estimated might give you some idea of the differences between the two queries. To get the actual plan, you'd have to wait for the full query to complete.
This would make more sense if it were a UNION rather than a UNION ALL, since SQL would then have to do a sort.
Are the data types identical in all 3 queries, such that no implicit conversions are required? This would even include things like decimal(8, 2) vs decimal(9, 3) or varchar(10) vs varchar(20). Are any collations different?
I'm just trying to think of anything that might make a difference. Obviously I don't know if for this specific query these things would actually make a difference or not.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 8, 2020 at 11:34 am
you can use SQL Sentry (https://www.sentryone.com/plan-explorer to post a anonymized plan - that will keep your restrictions in place as all names / variable contents are replaced with identical names. e.g. tables become object1, object2 and columns become column1, column2 and so on.
with that plan (actual plan if possible) we may be able to help further - but as mentioned above many times the split onto temp tables is the best option.
November 9, 2020 at 5:07 pm
Thanks to everyone who replied to a post with no DDL. I cant post the code for security reasons and I had NO IDEA sql sentry could be used to atomize the code so BIG thank you for sharing that. Im mobile now but once I'm back at my rig I will use sql sentry. In the mean time you guys have given some very useful suggestions without the code.
Kindest Regards,
Just say No to Facebook!November 9, 2020 at 7:44 pm
When I read your post I assumed you were referring some online tool like thing similar to another online SQL tool that will format your query. I have DL'd and installed the plan explorer but for some reason its not in SSMS even though the install ran for it but I have launched the app separately, made the connection to get the plan.
When I did this including all 3 queries as one via UNION ALL I stopped the thing from getting the pan after waiting around 8 minutes. I next split the 3 queries up and place each into their own session and got a plan and in all 3 case this took less than 60 seconds. Clearly there is something at issue. I am going to try getting the plan again with all 3 queries.
Once I have that what's the best way to share that info here? Do I save the session as SentryOne Data Plan filetype and upload that?
thanks again for turning me onto this great free tool!
Kindest Regards,
Just say No to Facebook!November 9, 2020 at 8:14 pm
You can use this site: https://www.brentozar.com/pastetheplan/instructions/
I find it is much easier than having the .sqlplan posted here.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 9, 2020 at 8:15 pm
SQL Sentry will only "reveal" itself on SSMS when you have done a explain plan and have the window with it visible.
at that point right-click on it and you have the option "open with SQL Plan" - you can save it as normal as a .sqlplan and then open it with SQL Sentry or open directly with SQL Sentry from SSMS.
Either way once you have it on SQL Sentry you can then anonymize it and save it.
To post here is going to be the issue - there are problems uploading certain types of files here - not sure if .txt will work - if not I would advise you to put it on https://www.brentozar.com/pastetheplan/ and post the link to it here.
November 9, 2020 at 8:30 pm
Are there any performance or blocking issues with using the Plan Explorer? I had the thing running for all 3 queries with the UNION All and I started to get a few msgs from other users complaining about the program so I stopped the plan before it finished just in case. When I stopped it it had been running for well over 10 minutes. When I ran it for each of the 3 queries Individually it was less then a minute. I don't have any issue with letting the plan explorer running for as long as it needs as long as it won't causing issues for users trying to work with the DB. If there is a chance it could do so then I will need to run it after hours.
Thoughts?
Kindest Regards,
Just say No to Facebook!November 9, 2020 at 8:35 pm
when you are running the query you still not using Plan Explorer - that is all SQL Server on its own.
only once the query runs and SSMS produces the explain plan xml can you then open it with Plan Explorer.
Note that the issue you have is precisely because the original query has the union all - if not for that you would not be here asking for help - having the plan is how we can try and identify the issue and potential solution (other than the split as previously mentioned)
November 9, 2020 at 8:51 pm
So I tried sharing the plan (as XML) on Brent Ozars site but the result is grater then the 2MB max.
Once I have an estimated plan window in SSMS and right-click it does show me the option to "view with SQL Sentry plan Explorer" but when I do that I get errors saying several @Variables must be declared but no such variables are anywhere in the query and I did check using FIND in UltraEdit so I know its not just that i missed seeing these.
If I start a new session in Plan Explorer and paste the query into it I don't get those same variable errors and i can get the "get Estimated plan" to work and of course then anonymize it. Is that good enough the Get Estimated or do i need for the Get Actual Plan to complete and do its thing before then sharing the results?
Kindest Regards,
Just say No to Facebook!November 9, 2020 at 8:55 pm
estimate will most likely help - not as good as the real thing though.
as for the @variable - never saw that error on Plan Explorer - if you save the plan as a .sqlplan in SSMS and then open that file with Plan Explorer do you still get the error? if not then do it that way so we can have the actual plan.
November 10, 2020 at 4:01 pm
Yesterday I let PE (Plan Explorer) do its thing with get the actual plan. I knew it would take some time which was fine. I was working remotely and got disconnected after waiting for around 20 mins or so. This was an internet issue on my end so I had to wait till it was fixed. I checked back about 8 hours later an PE was gone; presumably it crashed b/c all other apps that were open when I got disconnected were still open/working.
I will have to try this again and if I can' get actual plan to complete then I will get the estimated and anonymize it and then we can figure out how best to share that since I can't use Ozars setup die to the size of the xml file the plan creates.
Kindest Regards,
Just say No to Facebook!November 10, 2020 at 4:26 pm
We're having some performance issues today and so I don't want to run the Get Actual Plan piece of Plan Explorer until those are resolved. Attached are the XML plan and anonymized query (both at txt files) for Get Estimated Plan. If its not enough to assit I understand but maybe it can provide some hgelp.
FYI... The query itself looks decently formatted for the first main query (of the 3 in it) because I started to cleaned up the code to try and review it but did not get it done so the last part is what I would call messy looking. If that's an issue let me know and I'll finish cleaning up the rest when I get time and re-do the files.
Kindest Regards,
Just say No to Facebook!November 10, 2020 at 4:30 pm
So could not upload the XML plan as a txt file so I'll see if it will let me do it if its archived (winrar)
UPDATE
So the site will not let me upload the XML plan file even if its archived. I have created a native SentryOne Data Explorer file type (.pesession) and changed it to .txt and attached.
UPDATE#2:
The last attempt did not work (changing to .txt file tyoe). At this point I'm not sure how to share this with you guys. Suggestions?
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply