Solved

SQL Server Query - Log Stats

Posted on 2014-10-14
4
136 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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