Comment history from Wordpress
Pedro Bonilla
June 5, 2016 9:11 am
Short & sweet! I really like these small tips. Thanks Kendra!
Loading...
Reply
Eran K
September 18, 2016 5:29 am
Any way to eliminate all the extra lines in SET STATISTICS TIME? – It can give up to 4 rows per statement..
SQL Server parse and compile time – I just want a simple elapsed time for statement..:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(269 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Reply
Kendra Little
September 19, 2016 8:20 am
You can eliminate the parse and compile time by letting it use a cached plan– so running it twice, as long as it doesn’t have a recompile hint, would do that.
If you’re running a procedure I believe you’ll always get statement level results as well as a procedure level results, though.
Another option is to avoid statistics time altogether. If you want to do it programmatically, running an extended events trace and querying the results can be efficient for many load testing scenarios when collecting it with statistics time would be too cumbersome.
Loading...
Reply
Steve W
October 18, 2016 11:21 am
Using the print function to wrap the output is also a helpful trick, especially when you have a large output. My base template looks like this —
set statistics io on; set statistics time on; set nocount on; –set statistics xml on;
go
print’— begin ———————————————————————-‘
select *
from mytable
print’— end ————————————————————————‘
end
go
set statistics io off; set statistics time off; set nocount off; –set statistics xml off;
go
Loading...
Reply
Steve W
October 18, 2016 11:23 am
Whoops —
set statistics io on; set statistics time on; set nocount on; –set statistics xml on;
go
begin
print’— begin ———————————————————————-‘
select *
from mytable;
print’— end ————————————————————————‘
end
go
set statistics io off; set statistics time off; set nocount off; –set statistics xml off;
go
Loading...
Reply
Fernando Zamora
March 5, 2019 8:31 am
I couldn’t remember the name of the objects to turn on and off and a google search brought me here. That is very handy because even though I have been programming for a while I am just not getting a taste of having some data access responsibilities. Thanks for sharing. I am now a follower of your blog. Thanks.
Comment history from WordpressPedro Bonilla June 5, 2016 9:11 am Short & sweet! I really like these small tips. Thanks Kendra!Loading...
Reply Eran K September 18, 2016 5:29 am Any way to eliminate all the extra lines in SET STATISTICS TIME? – It can give up to 4 rows per statement.. SQL Server parse and compile time – I just want a simple elapsed time for statement..:
CPU time = 0 ms, elapsed time = 1 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
(269 row(s) affected)
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
https://msdn.microsoft.com/en-us/library/ms190287.aspx?f=255&MSPPError=-2147217396
Loading...
Reply Kendra Little September 19, 2016 8:20 am You can eliminate the parse and compile time by letting it use a cached plan– so running it twice, as long as it doesn’t have a recompile hint, would do that.
If you’re running a procedure I believe you’ll always get statement level results as well as a procedure level results, though.
One option is to use a formatter like http://statisticsparser.com/ to simplify the results.
Another option is to avoid statistics time altogether. If you want to do it programmatically, running an extended events trace and querying the results can be efficient for many load testing scenarios when collecting it with statistics time would be too cumbersome.
Loading...
Reply Steve W October 18, 2016 11:21 am Using the print function to wrap the output is also a helpful trick, especially when you have a large output. My base template looks like this —
set statistics io on; set statistics time on; set nocount on; –set statistics xml on; go
print’— begin ———————————————————————-‘ select * from mytable print’— end ————————————————————————‘ end go
set statistics io off; set statistics time off; set nocount off; –set statistics xml off; go
Loading... Reply Steve W October 18, 2016 11:23 am Whoops —
set statistics io on; set statistics time on; set nocount on; –set statistics xml on; go
begin print’— begin ———————————————————————-‘ select * from mytable; print’— end ————————————————————————‘ end go
set statistics io off; set statistics time off; set nocount off; –set statistics xml off; go
Loading... Reply Fernando Zamora March 5, 2019 8:31 am I couldn’t remember the name of the objects to turn on and off and a google search brought me here. That is very handy because even though I have been programming for a while I am just not getting a taste of having some data access responsibilities. Thanks for sharing. I am now a follower of your blog. Thanks.
Loading... Reply Greg May 3, 2019 12:01 pm Since the output of statistics io is so unpleasant to view I have this little tool for everyone to benefit from that runs right inside of SSMS https://analyticsbar.com/blog/statistics-reporter-ssms-extension/
So you no longer need to strain your eyes or go online to parse anymore:)
Loading...