Solved

SQL Server Query - Log Stats

Posted on 2014-10-14
4
134 Views
Last Modified: 2014-10-16
I want to come up with some way that I can collect and save certain statistics at both the start and end of a query.
I know I can query master.dbo.sysprocesses and get cpu time and physical_ios but I'd like to be able to break io into physical and logical and reads vs. writes. I would log results of whatever method is used to get a snapshot of the required data at the start of the query and the end.

For example, the value of cpu at the end of the query minus the value of cpu at the start should be the total cpu time used for my query. The stats I can think of that I would like to collect are cpu time, reads, writes (physical and logical), and perhaps total wait time.
0
Comment
Question by:dbbishop
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40380928
You can get I/O statistics from
SET STATISTICS IO ON

Be aware, though, that physical I/O could vary because it depends on SQL buffer contents.

You could also capture results from
sys.dm_db_index_operational_stats
before and after the statement(s), and delta them, to get more detailed I/O.  Of course if other statements are using the same tables their I/O would be included in those totals also.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 40381039
This will be running on a development server and will be the only activity against the database, and likely on the server.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40381047
Then the stats in the view should accurately reflect your activity.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 40385380
thx
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question