Performance Monitors with TSQL

On occasion, I am pitted against a server that frustrates the living poop out of me, usually servers are well behaved when they see me coming, but sometimes they get reaaaal stubborn. The worst ones are those that won’t give up their SQL performance data. You log on, dive straight into PerfMon to see what brought you to the server in the first place, and what do you find? Nothing! Not one single SQL related counter. Sure, you can get by, looking at the server counters, see what is causing the pain, but after that where can you go??

Luckily, there is an answer. Sometimes you can get the counters back, as aptly described here. But if you have managers breathing down your neck for answers to their problems, then you can go directly to the System views and system table (in sql 2000, the table is available for backward capability only in SQL 2005, 2008). From these you can see all of the related counters that you would normally find in PerfMon.

SQL 2000

select * from master.dbo.sysperfinfo

SQL 2005 +

select * from sys.dm_os_performance_counters

There you have it, a one stop shop for all performance related woes. From these, you can build yourself a series of views and scripts that will allow you to quickly delve into the inner workings of your instance, without even having to log onto the server, which of course should be avoided.

Advertisements
This entry was posted in SQL Server and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s