Wednesday, March 21, 2012
Query execution priority
server, is it then possible to give the execution of one query a higher or
lower priority than another query?
Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?
|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?
|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?
Query execution priority
server, is it then possible to give the execution of one query a higher or
lower priority than another query?Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?
Query execution priority
server, is it then possible to give the execution of one query a higher or
lower priority than another query?Hi,
As far as I know no. The only priority that could be set deals with how the
query behave in case of deadlocking. see DEADLOCK_PRIORITY in BOL.
Lionel
"pehuan" wrote:
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||You can set the maximum degree of parallelism (the number of processors to
use) for a query on a multi processor machine, using OPTION (MAXDOP n). This
doesn't really set priority directly, but it gives you influence over the
amount of resources each query can use.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
> When you execute several long duration queries simultaneous on the SQL
> server, is it then possible to give the execution of one query a higher or
> lower priority than another query?|||There is no need for priority on SQL Server worker threads because SQL
Server does not use preemptive multi-tasking; it uses the cooperative
multi-tasking of the Win 3.0 days. UMS contexts yield as required, unless,
of corse, there is some sort of error.
Sincerely,
Anthony Thomas
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:29D00740-2821-4995-9312-11E3E5AA8207@.microsoft.com...
When you execute several long duration queries simultaneous on the SQL
server, is it then possible to give the execution of one query a higher or
lower priority than another query?sql
Monday, March 12, 2012
query duration using parameters vrs no parameters
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @.CustomerId',
N'@.CustomerId nvarchar(4000)', @.CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@.CustomerId is null or CustomerId = @.CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way?
Did you observe any difference in query plans?
Thanks
Query Duration
from the Duration column, it comes to approximately 28000 (28 seconds). Yet
the true elapased time is about 48 seconds.
I have run SET STATISTICS TIME and the cumulative duration of the individual
statements is still far less than the actual elapsed time.
Is there a way to account for vast discrepancy between the cumulative
duration in Profiler and the actual elapsed time?
NOTE:
I have "Include Actual Execution Plan" turned off in Management Studio just
to make sure it was not the overhead of creating this graphical plan when I
am performing these duration tests.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
If you're running Management Studio / Profiler locally against SQL Server on
another server, then you need to account for network latency, I/O, your
side's ability to render the results, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1
>
|||Not to mention the fact that Profiler's reported duration is totally out of
sync with reality in many cases
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>
Query Duration
from the Duration column, it comes to approximately 28000 (28 seconds). Yet
the true elapased time is about 48 seconds.
I have run SET STATISTICS TIME and the cumulative duration of the individual
statements is still far less than the actual elapsed time.
Is there a way to account for vast discrepancy between the cumulative
duration in Profiler and the actual elapsed time?
NOTE:
I have "Include Actual Execution Plan" turned off in Management Studio just
to make sure it was not the overhead of creating this graphical plan when I
am performing these duration tests.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1If you're running Management Studio / Profiler locally against SQL Server on
another server, then you need to account for network latency, I/O, your
side's ability to render the results, etc.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Are you collecting StartTime / EndTime? If so, what's the difference between
the two?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>|||Not to mention the fact that Profiler's reported duration is totally out of
sync with reality in many cases :)
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>> When I run a procedure and monitor in Profiler, and add up the
>> milliseconds
>> from the Duration column, it comes to approximately 28000 (28 seconds).
>> Yet
>> the true elapased time is about 48 seconds.
>> I have run SET STATISTICS TIME and the cumulative duration of the
>> individual
>> statements is still far less than the actual elapsed time.
>> Is there a way to account for vast discrepancy between the cumulative
>> duration in Profiler and the actual elapsed time?
>> NOTE:
>> I have "Include Actual Execution Plan" turned off in Management Studio
>> just
>> to make sure it was not the overhead of creating this graphical plan when
>> I
>> am performing these duration tests.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1
>
Query Duration
from the Duration column, it comes to approximately 28000 (28 seconds). Yet
the true elapased time is about 48 seconds.
I have run SET STATISTICS TIME and the cumulative duration of the individual
statements is still far less than the actual elapsed time.
Is there a way to account for vast discrepancy between the cumulative
duration in Profiler and the actual elapsed time?
NOTE:
I have "Include Actual Execution Plan" turned off in Management Studio just
to make sure it was not the overhead of creating this graphical plan when I
am performing these duration tests.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1If you're running Management Studio / Profiler locally against SQL Server on
another server, then you need to account for network latency, I/O, your
side's ability to render the results, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Are you collecting StartTime / EndTime? If so, what's the difference between
the two?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:7496c0e253cde@.uwe...
> When I run a procedure and monitor in Profiler, and add up the
> milliseconds
> from the Duration column, it comes to approximately 28000 (28 seconds).
> Yet
> the true elapased time is about 48 seconds.
> I have run SET STATISTICS TIME and the cumulative duration of the
> individual
> statements is still far less than the actual elapsed time.
> Is there a way to account for vast discrepancy between the cumulative
> duration in Profiler and the actual elapsed time?
> NOTE:
> I have "Include Actual Execution Plan" turned off in Management Studio
> just
> to make sure it was not the overhead of creating this graphical plan when
> I
> am performing these duration tests.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200707/1
>|||Not to mention the fact that Profiler's reported duration is totally out of
sync with reality in many cases
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23Hb$pYPvHHA.4520@.TK2MSFTNGP02.phx.gbl...
> If you're running Management Studio / Profiler locally against SQL Server
> on another server, then you need to account for network latency, I/O, your
> side's ability to render the results, etc.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:7496c0e253cde@.uwe...
>