10 Baselining Tips for SQL Server: Lessons From the Field

Save SQL Profiler Data

Set up is simple but make sure you don't overwrite your data when Profiler is restarted (see more later in this document)

Tip 3: Adjustments to PerfMon Data Model

If you want to have a peek at your data online - one of the advantages you have when storing logged data in a database table -- you had better add some indexes to the data model PerMon has generated, otherwise your queries against the data will be slow. (See tip 2).

This is a query you might want to run online (shows most recent counter values):

SELECT details.ObjectName
, details.CounterName
, details.InstanceName
, data.CounterValue
, data.CounterDateTime
FROM CounterData data
INNER JOIN CounterDetails details ON data.CounterID = details.CounterID
WHERE (data.RecordIndex =
(SELECT MAX(recordindex)
FROM counterdata))
ORDER BY 1,details.counterid

These are indexes that will speed up queries, like the one mentioned above:

CREATE UNIQUE INDEX [ix_2] ON [dbo].[CounterData]([RecordIndex], [CounterID]) ON [PRIMARY]
GO
CREATE INDEX [ix_1] ON [dbo].[CounterData]([CounterID]) ON [PRIMARY]
GO
CREATE INDEX [ix_1] ON [dbo].[CounterDetails]([CounterName]) ON [PRIMARY]
GO

Tip 4: Trace All Counters From Each Object

Have you ever been in a situation like this: You are analyzing a problem, of course fully confident you logged 'all' the required performance counters. Then you read 'another' whitepaper that describes exactly the same set of symptoms you are experiencing, but for the solution, the paper focuses on just that one counter that is missing from your list?

One good piece of advice is to store all counters from all instances for each object you will be monitoring (the limited set of objects will be listed later in this document). Make sure you have enough disk space available and archive your data as needed. (See tip 8). Overhead should be acceptable, but if it isn’t, you can reduce your time interval (and log less often) instead of reducing the number of counters tracked, because sooner or later you'll need them. If your time interval is more than 5 minutes, and still you have a problem, buy more hardware.

Whitepapers I have read are not explicit on the amount of overhead that is generated by PerfMon when you trace more counters per object. I, at least, have never seen any figures on this subject. Apart from the increased volumes of data to be transferred, I haven’t seen much impact if you instantiate an object for only a few, or dozens of counters to be traced. In practice, I noticed when PerfMon was run on a remote system; the DCOM call had more impact than logging all counters in stead of only a few. Once again, network bandwidth must be checked because the data volumes will increase a lot.

Keep in mind that every process on your system -- logging your system behavior included -- takes resources. But believe me; the information that can result from good monitoring pays back your dedicated resources. In other words, your manager will make you feel very uncomfortable when you can’t explain what happened when something goes wrong.

Tip 5: Which PerfMon Counters Should be Logged

This is a list your humble servant uses when he is called in for a performance issue. This list focuses on OS and SQL Server counters (e.g. although I don't want to underestimate network counters, I feel they are not in the scope of this document because network guys have their own tools) and even some SQL Server counters are missing (e.g. replication) because not all systems use these features. In my opinion, this set covers most information of the global health of your system. Feel free to contact me if you feel some counters -- not in this list -- are indispensable to you.

• Memory

• Physical Disk

• Process

• Processor

• SQLServer:Access Methods

• SQLServer:Buffer Manager

• SQLServer:Cache Manager

• SQLServer:Databases

• SQLServer:General Statistics

• SQLServer:Latches

• SQLServer:Locks

• SQLServer:Memory Manager

• SQLServer:SQL Statistics

• SQLServer:SQL Settable

I will go deeper into detail on how to interpret these counters in a following paper.