?
Solved

SQL Server Query - Log Stats

Posted on 2014-10-14
4
Medium Priority
?
143 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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