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.
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.