CPU and memory monitor

Question

CPU and memory monitor

sqlfriend

SSC Guru

Points: 52781
More actions
June 12, 2011 at 9:24 am

#252669

Is there a way that I can pull CPU usage and memory usage at certain period of time in DVM views for example between 6:00 am and 6:15:am, and what caused the high CPU and memory consumption?
And I see in SSMS there is a graphic standard report of top CPU time query, top IO query etc.
I know it is captured in the default trace of SQL server, so it must be stored somewhere, I hope I can pull data in history.
Is that possible?
Thanks

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

AFAIK, this data isn't stored. I think the maxes are kept, but a very limited amount of data is stored overall. I think it's just related to the queries in the default trace.

It's possible that you can get all default trace entries around that time and see the CPU usage for those, but not overall machine usage.

You'd need to be logging perf mon counters, using something like Management Data Warehouse, or a third party tool like SQL Monitor from Red Gate that keeps this info.

Disclosure: I work for Red Gate.

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, if I setup a perf log on the sql server that has the slow response time, does the performance monitor itself affects the performance too?

Is there a way that I can set it up to run it on my local machine to monitor the server?

or it's not necessary to do this

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

Performance monitor has low overhead, and you can run it from your machine, but I'm not sure it's better. You want to log the data, not run it interactively.

Don Halloran SSCertifiable Points: 5937 More actions · Answer 4

We found performance data collection to be very useful (aka management data warehouse), but be warned: it consumes heaps and heaps of disk space. I've restricted it to 2 days history and tweaked the capture and cleanup jobs. With these settings the database holds a steady state at about 8 gig.

There are also some tweaks around on the net (adding an index and I think an alter proc) to improve its performance.

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

What counters should I monitor to get information what caused the slow response of applications that has their databases on the server?

Thanks

Don Halloran SSCertifiable Points: 5937 More actions · Answer 6

If you use the MDW / PDC it will tell you a good amount of detail about expensive queries in addition to CPU and IO. If you want to specifically monitor for locking you'd have to go with your own logging from WMI (the stuff in perfmon) or traces.

But slow application response of course points to slow queries, so PDC should give you pretty a good indication as to which queries to look at first.

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thanks, but this instance is on standard version of 2008, so I don't see data collector.

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

I found I can setup a sql profiler that run on my local computer to monitor a sql server, but for using windows performace monitor it seems it can only run on the server itself, is that correct?

Don Halloran SSCertifiable Points: 5937 More actions · Answer 9

It is indeed possible to connect perfmon to a remote machine, but it can be difficult to set up and isn't really necessary (as Steve already said, it has very low overhead).

sqlfriend SSC Guru Points: 52781 More actions · Answer 10

Thanks, that's what I experienced too. So I will use in the server then.

But for sql profiler trace, can you confirm it is easy to run on my local computer to monitor the server, or it should be done on the production server and has little overhead as Performance ?

Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

sqlfriends (6/12/2011)
Thanks, that's what I experienced too. So I will use in the server then.
But for sql profiler trace, can you confirm it is easy to run on my local computer to monitor the server, or it should be done on the production server and has little overhead as Performance ?
Thanks

It would not be a good idea to run profiler from your local computer for any length of time. It can and will cause issues, as well as possibly losing some of the trace data.

It is much better to build a server side trace and output to a file on the server. And, make sure you are tracing only what you need to see - as an wide open trace could cause issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 12

yes you can

First create the same table as DMV and then insert data after required interval at the end of day you can check CPU and memory usage for certain queries and then you can manage your resource pool as well with the help of resource manager in SQL Server

My Blog
www.aureus-salah.com

sqlfriend SSC Guru Points: 52781 More actions · Answer 13

Thanks, can you be more specific when you say:

First create the same table as DMV and then insert data ...

Thanks

Don Halloran SSCertifiable Points: 5937 More actions · Answer 14

Just some additional info: a server side trace is not the same as running profiler on the server. Look up "server side trace" in google (you'll probably get directed to an article on this site or msdn/books online).

To log raw data from a dmv you'll need a table that has the same "shape" as the dmv. One easy way to generate an empty table is with a select into:

select *

into dbo.my_logging_table

from some_dmv

where 1 = 0

You'll probably want to issue an alter table against that logging table and add a log_datetime column.

Then just insert into dbo.my_logging_table from some_dmv on whatever schedule you wish.