March 31, 2014 at 4:58 pm
I have a view which is a union of three select statements. It ran ok before but now it ran for ever. Running the three select statement separately worked fine and had returns within one minute.
Strangely, this long-running query has no impact on server. I have the sysadmin permission and saw no lock or waiting resource related to my session at all.
Where else should I look?
March 31, 2014 at 11:10 pm
What if you stop and re-run your query again?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 1, 2014 at 4:41 am
A UNION statement is an aggregation, so, unless you're doing UNION ALL, it's not the sum of the three queries, but the aggregation of the three. Check the execution plan to see if there are tuning opportunities. Consider using UNION ALL. Other than that, you should be able to see the query in sys.dm_exec_requests and you should be able to see what it is waiting on, because if it's running, it's waiting on something.
"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
April 5, 2014 at 12:27 pm
I use "Union All" in the code.
I looked deeper and saw there actually was a resource wait - PageIOLatch_sh. As far as I know, it is caused by large table scan. Certainly I need to improve the individual select query by adding some index.
What I am still curious about, is the fact that, the three individual query took less than 3 minutes each when running separately, while the union query ran for more than one hour without returning anything?
What could be the reason? Where should I look? I mean what DMVs should check?
Thanks.
April 5, 2014 at 3:02 pm
Check also the statistics of the tables that are taking part in the view.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
April 6, 2014 at 3:23 am
sys.dm_exec_requests will show the execution of the query and any waits that it's experiencing as well as any blocks.
I'd suggest looking at the combined execution plan. It might be different than the distinct execution plans (although I'd probably look at those as well).
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply