Solved

SQL Server Query - Log Stats

Posted on 2014-10-14
4
132 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:
ScottPletcher 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:ScottPletcher
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now