In order to stop some occasional blocking issues could I set the "query
execution time-out settings" to 30 seconds for example. I understand it will
kill the queries that exceed 30 seconds, but I would rather have that then t
o
have everyone "lockup". I also understand that blocking is caused by some
ineffecient queries, however untill we identify the problematic queries I
would like to kill the blocking automatically.
Thanks for any help.
JamesOf course you can make such a change.
Please recognize that several activities, including reporting, usually
require longer running queries, and your proposed change may cause those
other activities and reports to mal-function.
Using Profiler, you should be able to identify the blocking queries
relatively easily.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"James" <James@.discussions.microsoft.com> wrote in message
news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
> In order to stop some occasional blocking issues could I set the "query
> execution time-out settings" to 30 seconds for example. I understand it
> will
> kill the queries that exceed 30 seconds, but I would rather have that then
> to
> have everyone "lockup". I also understand that blocking is caused by some
> ineffecient queries, however untill we identify the problematic queries I
> would like to kill the blocking automatically.
> Thanks for any help.
> James|||Thanks Arnie for your reply
Do you know if this action will definitely kill blocking?
James
"Arnie Rowland" wrote:
> Of course you can make such a change.
> Please recognize that several activities, including reporting, usually
> require longer running queries, and your proposed change may cause those
> other activities and reports to mal-function.
> Using Profiler, you should be able to identify the blocking queries
> relatively easily.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "James" <James@.discussions.microsoft.com> wrote in message
> news:11D15332-D7BC-4694-A3BB-15EB8F428406@.microsoft.com...
>
>
Showing posts with label seconds. Show all posts
Showing posts with label seconds. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Query estimate cost and real run time different
I have a query, before I do any change, the estimate execute plan show me th
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?
e cost is 1400, the real run time is about 1 minute 33 seconds.
After I eliminated some concatinated column, the estimate execute plan shows
me cost reduce to 66, I expect the query will run quite faster than the ori
ginal one, but real run time still keep in 1 minute 27 seconds.
I know estimate execute cost is not accurate, but should not be such a big d
ifference.
Any body can tell me why, what is the good way to tunning my query?
Thanks in advance,
HGHG,
The best way to tune your query is to actually run it and see what it does.
Examine the execution plan and focus on the parts of the plan that are most
expensive. If one step takes 75% of the effort and five other steps each
take 5%, then examine the 75% and see what you can do about it. (Better
join criteria, another index, recasting the code to use a different
approach, etc.)
Estimated plan is sometimes mildly helpful, but (as you note) cannot be
relied upon. Also, even the actual execution plan has blind spots. For
example, it will not measure how much time is spent in a UDF, the IS_MEMBER
function, and so forth, viewing them as nearly free.
Therefore, in addition to the execution plan, test with timing statements
that show how much clock time the steps take. If you run several tests you
will get a good measure of the execution time.
Russell Fields
"HG" <anonymous@.discussions.microsoft.com> wrote in message
news:C01707AF-3B18-45AC-B188-52D313EBE860@.microsoft.com...
> I have a query, before I do any change, the estimate execute plan show me
the cost is 1400, the real run time is about 1 minute 33 seconds.
> After I eliminated some concatinated column, the estimate execute plan
shows me cost reduce to 66, I expect the query will run quite faster than
the original one, but real run time still keep in 1 minute 27 seconds.
> I know estimate execute cost is not accurate, but should not be such a big
difference.
> Any body can tell me why, what is the good way to tunning my query?
> Thanks in advance,
> HG|||I am new to sql. Can you provide an example of a timing statement that will
show me how long a querry took?|||Larry,
DECLARE @.StartTime DATETIME
SET @.StartTime = GETDATE()
Execute your code here
SELECT DATEDIFF(ms,@.StartTime, GETDATE()) AS ElapsedMilliseconds
Because there are variable, run this a few times to get a best time.
Compare best times of two different strategies to determine how they
compare.
Russell Fields
"Larrry Pensil" <anonymous@.discussions.microsoft.com> wrote in message
news:56CC0769-A7B1-4E97-ADE6-FEF9AF056A77@.microsoft.com...
> I am new to sql. Can you provide an example of a timing statement that
will show me how long a querry took?
Monday, March 12, 2012
Query Duration
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
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...
>
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
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/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
>
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
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/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...
>
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...
>
Subscribe to:
Posts (Atom)