Favourite 10 Perfmon counters

With a boring intro of perfmon already written here, lemme get started with my favourite 10 perfmon counters.In my not so humble opinion, its a crime not to have these counters running on your production DB server.
Enabling perfmon has a negligible ( or almost none ) overhead and its no harm having these turned on as it serves as a black box to trace whats happening and what happened on our precious production servers.Quick look at all ten.Please click on the images to view the counter details.





The excel sheet prepared can be downloaded from here.

The solution column written is just a pointer indicating which area to look at. Each solution is a potential huge topic in its own right.There are few more interesting counters, but I have saved them for later discussions and just put ten most important of them.If you dont have the budget to go for those expensive monitoring tools,perfmon can certainly help.

Perfmon - Boredo Intro

Perfmon Counters:

Perform is perhaps the most popular Performance monitoring tool provided by Microsoft, and by far my favourite tool. Briefly put, Perfmon allows us to save the useful performance monitoring counters which come in handy while doing performance analysis , identifying hardware bottlenecks etc. Performance monitoring counters are key performance indicators which help us understand the current health and state of our server with respect to CPU,memory,locks,queue length and many more..

Lemme provide a quick intro on how to use perfmon.

To view system's current health:

> Start ->Run -> Type perfmon
> Pretty good looking screen showing some graphs pops up
> On the graph, you can right click, pick Add Counters
> On the Add counter screen, pick a performance object. Performance Object drop
down shows the different categories of counters available for us.
> Pick a counter listed below.

To store the perfmon counter values on a file:

> On the left expand Performance logs and Alerts
> Click on counter logs
> Right click and pick new log settings.
> Pick the path you want to save the counter and pick the counters using Add
counter .
> Under log file type, pick the type of file in which you want to save. I prefer
CSV as its viewable in excel. Default is binary.
> Under schedule tab pick the schedule that suits your needs.

With a copy of saved performance log file, one can perform the analysis of performance data of monitored server, on any windows machine.

For viewing the saved log for later analysis:

> open Perfmon.
> Click on system monitor.
> Right click on the graph area. Click on properties.
> On Source tab, pick log files and select the path where the performance log file
is located.
> Add the counters as explained earlier.

No screenshots on this one as its a fairly simple tool providing awesome info, and there are billion pages online explaining perform. Then dont ask me why this post?. I did it for the heck of it.The next one will definitely be lot more meaningful as I plan to discuss my favourite ten perform counters.

Database Server is slow. Can you please check?

As a DBA, you are going to hear the title of this many many times. This post will discuss the quick list of things a DBA does when such a question is thrown.

Step 1 - Task Manager: This one is very basic. Check the task manager whether SQL Server consuming most of the CPU/Memory. A overall CPU usage exceeding 65%, with SQL contributing the majority ( excess of 40% ) is perhaps a high usage. SQL can use only 50% of the RAM available( unless AWE is enabled).Any usage less than the maximum allowed implies that your server is doing okay on memory front.

Step 2 - SQL Error log: : Check SQL Servers error log for any abnormalities. Note that by default errors with Severity level between 19 and 25 are logged in sql error log.


Step 3 - Query sysprocesses/sys.dm_exec_query_stats: If your step 1 indicates high CPU or Memory pressure then check sysprocesses right away. Check the number of connections that are using SQL Server. This can be found by querying sysprocesses for spid greater than 50. On an average OLTP systems, I would expect 150 - 250 odd connections.Also Check for likely connection leaks with the script provided here.

After connections, find the list of long running queries.If on SQL 2000 then observe the connection(spid) with status = 'runnable' on sysprocesses. Fire DBCC inputbuffer(spid) to check the current statement running. If on sql 2k5 use dm_exec_query_stats DMV to identify the longest query running using the script provided here.

Also, note the connections that are consuming high CPU/IO/Memory using sysprocesses.

Step 4 - Blocking queries: Identify blocking queries, if any. If on SQL 2005 use the script provided here.If on SQL 2k use sp_lock/sp_who2 .

If you follow the above listed steps,it is very likely that you will find the reason for slowdown on performance. But in most of the cases, one is not at the DB server to identify the cause for the problem. By the time the DBA checks it, the server is back to normal, and the cause for the problem goes unidentified. Fortunately, Microsoft gives really good tools to counter such problems which I will be covering in my next post.

Checking detached data file details

DBCC CheckPrimaryfile


DBCC checkprimaryfile ( 'C:\Program Files\Microsoft SQL Server\MSSQL$RAJ\data\master.mdf' , 1 )


Result shown below.



If you have a data file which belongs to the Primary file group of the database, then DBCC CheckPrimaryfile command can provide details about the Database it belonged to. Provides the details such as Original Data file and log file location,other data files location details , physical names of the files in the database and size details. This info can be very useful when you perform migrations where databases have to be moved to different servers. After detaching the database, in case, if you want to figure out the location and other files attached to the original database then this command will help for sure.

Second parameter takes 0,1,2,3 as valid inputs. Each option provides a useful information.Not documented.So please do the research about the command yourself.

Works on 2000/2005. Note: Doesn't work if the Data file originally belonged to SQL 2005 and the command is issued from SQL 2000 Server. Other way around works. ie. SQL 2000 Data file can be checked from SQL 2005 server.

Windows accounts accessing SqlServer

xp_logininfo:

Useful sp to check the list of Windows logins/users that have access to the SQL Server. Provides the list of Windows accounts along with their permissions.
The option that can be very handy is its second parameter, which when passed as 'members' lists the members of a given Windows Group. EX:

EXEC MASTER.dbo.Xp_logininfo
  'Builtin\Administrators' ,
  'members'
GO


For a DBA, this option can be useful to find the list of Windows users on the Sql Server, who have aquired sysadmin rights indirectly thro Builtin\Administrators group ( Default Windows Admin group ). For more details refer BOL as its documented.

Works on 2000/2005/2008.