Query to get the CPU Usage,memory usage details of server

Question

Post reply

Query to get the CPU Usage,memory usage details of server

sharadkkw

SSC Enthusiast

Points: 129
More actions
January 30, 2014 at 8:50 am

#287756

Can someone help me in providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
If someone has any idea please do share.
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?

Viewing 8 posts - 1 through 7 (of 7 total)

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

sqlsniper Right there with Babe Points: 734 More actions · Answer 1

You could also take a look at Windowstooling, like perfmon and the typeperf tooling.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

sqlsniper (2/2/2014)
You could also take a look at Windowstooling, like perfmon and the typeperf tooling.

Perfect! How do you store that in a table in SQL Server? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

sharadkkw (1/30/2014)
Can someone help me in providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
If someone has any idea please do share.
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?

More than half the battle is knowing what to look for. I don't have a script for this but they're easy enough to find. Google for "sys.dm_os_performance_counters". Lot's of folks have published different sorts of scripts for different reasons.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (2/2/2014)
sqlsniper (2/2/2014)
You could also take a look at Windowstooling, like perfmon and the typeperf tooling.
Perfect! How do you store that in a table in SQL Server? 😉

The easiest is to import the data on a schedule, the perfmon counters can be saved as .csv and a job written to load them up. Done that a number of times.

Since sys.dm_os_performance_counters only shows the SQL-related counters and not server-wide counters such as CPU usage and Memory usage, just querying that DMV won't meet the OP's requirements. They can get some blocking-related info from that DMV, but not all.

p.s. I hope I mis-read the tone of this post, it sounded a little mocking on the first read.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sharadkkw (1/30/2014)
Can someone help me in providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...

Could you give a little more info as to what exactly you're after? It's unlikely you'll get everything you want in one place. If you break down exactly what you want, we might be able to help more.

Do you need up-to-the-minute results in the table, or are you OK importing data from another location on a daily basis?

Also, check this out as there's a good amount of info on various counters and information sources: http://www.red-gate.com/community/books/accidental-dba

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlsniper Right there with Babe Points: 734 More actions · Answer 6

Jeff Moden (2/2/2014)
sqlsniper (2/2/2014)
You could also take a look at Windowstooling, like perfmon and the typeperf tooling.
Perfect! How do you store that in a table in SQL Server? 😉

If you look up information on MSDN, like here

http://technet.microsoft.com/nl-nl/library/cc753182(v=ws.10).aspx

Using the -f switch on typeperf let's you write to SQL Server

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 7

sqlsniper (2/3/2014)
Jeff Moden (2/2/2014)
sqlsniper (2/2/2014)
You could also take a look at Windowstooling, like perfmon and the typeperf tooling.
Perfect! How do you store that in a table in SQL Server? 😉
If you look up information on MSDN, like here
http://technet.microsoft.com/nl-nl/library/cc753182(v=ws.10).aspx
Using the -f switch on typeperf let's you write to SQL Server

Excellent and much better. Thanks for the tip.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)