October 31, 2018 at 8:22 am
Is it possible to execute a particular query with high priority (meaning let it take all resources available and let the other queries running at the same time wait till this particular query that i want to run with high priority releases the over consumed resources).
I just need to run this query (it takes 2 hours..) one or two times, and cannot find an info anywhere if it is even possible
in t-sql to specify 'Run this query with high priority and ALL SERVER RESOURCES IT CAN TAKE'.
Thank you!
Likes to play Chess
October 31, 2018 at 8:44 am
Yeah, if you were Microsoft, would you want to hand someone that kind of authority over a SQL Server? Think about how easy it would then be to take over the database by running a query that blocks everyone else from getting at anything at all. Not a particularly good idea. I certainly wouldn't recommend it, and if your management okayed this idea, they should probably be fired.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 31, 2018 at 11:10 am
You can increase the priority of a query in terms of deadlock victimisation (a query with a high priority will cause one with a lower to be the deadlock victim). You can't, however, do what you are describing; I agree with Steve just the idea of doing something like that seems like madness.
If you have a (very) slow running query, perhaps the problem is the query and that it needs performance tuning.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2018 at 12:44 pm
Thom A - Wednesday, October 31, 2018 11:10 AMYou can increase the priority of a query in terms of deadlock victimisation (a query with a high priority will cause one with a lower to be the deadlock victim). You can't, however, do what you are describing; I agree with Steve just the idea of doing something like that seems like madness.If you have a (very) slow running query, perhaps the problem is the query and that it needs performance tuning.
Thanks for everyone's feedback.
...and that's exactly what i am trying to do (to optimize query for performance, or its underlying objects..) , YET it takes at least one time to run the query to obtain the ACTUAL EXECUTION PLAN :), and it won't complete in 3, 4, 5 hours....
everything else on the server looks good, no blocks or deadlocks during the query run. I pretty much know where the issue is, its a view that returns 21 million rows if all is selected from it, and it selects from other views that , in turn, use other views, joined to same tables that the first 2 views, etc./.... But not looking for THAT MUCH of t-sql rewrite and retesting.
If i run the query with the ACTUAL exec plan on , and stop it in 5 minutes, would the query plan be generated? and or will it be sufficient to analyze? Or do i have to let the entire query run time complete?
THANK YOU !
Likes to play Chess
October 31, 2018 at 1:18 pm
VoldemarG - Wednesday, October 31, 2018 12:44 PMThom A - Wednesday, October 31, 2018 11:10 AMYou can increase the priority of a query in terms of deadlock victimisation (a query with a high priority will cause one with a lower to be the deadlock victim). You can't, however, do what you are describing; I agree with Steve just the idea of doing something like that seems like madness.If you have a (very) slow running query, perhaps the problem is the query and that it needs performance tuning.
Thanks for everyone's feedback.
...and that's exactly what i am trying to do (to optimize query for performance, or its underlying objects..) , YET it takes at least one time to run the query to obtain the ACTUAL EXECUTION PLAN :), and it won't complete in 3, 4, 5 hours....
everything else on the server looks good, no blocks or deadlocks during the query run. I pretty much know where the issue is, its a view that returns 21 million rows if all is selected from it, and it selects from other views that , in turn, use other views, joined to same tables that the first 2 views, etc./.... But not looking for THAT MUCH of t-sql rewrite and retesting.If i run the query with the ACTUAL exec plan on , and stop it in 5 minutes, would the query plan be generated? and or will it be sufficient to analyze? Or do i have to let the entire query run time complete?
THANK YOU !
If the you are running a query that is a view that uses views, that uses views and is linked to tables that those views use you have a performance issue, period. Where code reuse makes sense in application code, it doesn't in the database world. You really need to rewrite the main view to eliminate the nesting of views and relinking to tables used in views.
October 31, 2018 at 5:16 pm
VoldemarG - Wednesday, October 31, 2018 12:44 PMThom A - Wednesday, October 31, 2018 11:10 AMYou can increase the priority of a query in terms of deadlock victimisation (a query with a high priority will cause one with a lower to be the deadlock victim). You can't, however, do what you are describing; I agree with Steve just the idea of doing something like that seems like madness.If you have a (very) slow running query, perhaps the problem is the query and that it needs performance tuning.
Thanks for everyone's feedback.
...and that's exactly what i am trying to do (to optimize query for performance, or its underlying objects..) , YET it takes at least one time to run the query to obtain the ACTUAL EXECUTION PLAN :), and it won't complete in 3, 4, 5 hours....
everything else on the server looks good, no blocks or deadlocks during the query run. I pretty much know where the issue is, its a view that returns 21 million rows if all is selected from it, and it selects from other views that , in turn, use other views, joined to same tables that the first 2 views, etc./.... But not looking for THAT MUCH of t-sql rewrite and retesting.If i run the query with the ACTUAL exec plan on , and stop it in 5 minutes, would the query plan be generated? and or will it be sufficient to analyze? Or do i have to let the entire query run time complete?
THANK YOU !
You would NEVER want to do either of these in a production environment BUT assuming you are in a suitable test environment, there are a couple of options available...
#1 ) put the database into "single user mode". This will shut out all connections other than your own,
#2) execute sp_Who2 to see all activity on the instance. Use that to identify and kill any unwelcome SPIDs. [note: NEVER touch any SPID less than 50. These are internal processes than need to be running.]
Either of these approaches will effectivly give you the option to bully out other user processes that would otherwise be consuming resources.
Edit... Something else to keep in mind. Aside from the actual plan providing "estimates vs actuals", the estimated plan will be the exact same thing as the actual plan. So, unless you have a reason to suspect bad estimates, the estimated plan should suffice.
November 1, 2018 at 3:09 am
VoldemarG - Wednesday, October 31, 2018 12:44 PMI pretty much know where the issue is, its a view that returns 21 million rows if all is selected from it, and it selects from other views that , in turn, use other views, joined to same tables that the first 2 views, etc./.... But not looking for THAT MUCH of t-sql rewrite and retesting.
Having worked with several of those kind of queries in the past, a rewrite is probably going to be what you need to do to get it to behave.
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
November 1, 2018 at 3:23 am
We are actually going through a very similar problem at the moment with an inherited database. Each iteration of the DB was done so by another short term project-scoped contractor whom had no real care for what happens to others using this server. So there are view with 10 joins also joining to another view which has a handful of joins, then there are some UDF's thrown in and before you know it you're in to a 10 hour process that they want to run every hour.
Despite the initial "just give it more grunt" which didn't yield anything near the improvements they expected "its memory, double the memory, halve the time" and after constant arguing that no, having a vmware consultant 'tweak' the server will not really improve it to the point where you can run it hourly it was agreed we can take it to bits and rewrite it.
Knowing nothing about your environment I couldn't say this will be your finding but in this place, after dismantling all the views and looking actually at what bits we had to play with, we got this query down to using two tables. Took less than a minute to run. We hadn't changed the logic or the requirements or the quantity of data. But we did eliminate 15 joins that were superfluous from one view, and 8 or so from another.
All in all it boiled down to people being lazy (or ignorant, but I suspect lazy) and using a view because it was already written. They just had to write it up with 2 lines of code to another view that was also not designed for the task.
Once we had this awakening that things were cobbled together so badly we started making really easy improvements all over the place. These weren't just quick wins in their triviality but enabled the business that though it had reached some kind of hard limit or capacity in what business they could with this system to very easily double or triple their traffic almost overnight.
So this is my very long winded way of saying dont rule out rewriting/refactoring as you could very well discover improvements you never knew you wanted 🙂
November 1, 2018 at 6:17 am
VoldemarG - Wednesday, October 31, 2018 8:22 AMIs it possible to execute a particular query with high priority (meaning let it take all resources available and let the other queries running at the same time wait till this particular query that i want to run with high priority releases the over consumed resources).
I just need to run this query (it takes 2 hours..) one or two times, and cannot find an info anywhere if it is even possible
in t-sql to specify 'Run this query with high priority and ALL SERVER RESOURCES IT CAN TAKE'.Thank you!
ALTER DATABASE [The Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
SELECT whatever you want to select;
GO
ALTER DATABASE [The Database] SET MULTI_USER;
GO
This will certainly give you everything you're asking for.
But get yourself prepared for the consequences you did not ask for!
And don't say I did not warn you.;-)
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply