Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Or should perfmon be limited to a Dev/QA server with load tests that simulate production activity?

I'd like to run perfmon for two days (like Sql Server master Brent Ozar suggests) to get an overall feel of my web app's database performance.

share|improve this question
    
Some people have suggested using a SQL Trace - be cautious with SQL trace and never trace all activity on a prod server. –  Sam Jul 1 '10 at 23:02

7 Answers 7

up vote 16 down vote accepted

SQL Server, and most other products, generate the counters all the time, no matter if there are listeners or not (ignoring the -x startup option). Counter tracing is completely transparent on the application being monitored. There is a shared memory region on which the monitored application writes and from which monitoring sessions read the raw values at the specified interval. So the only cost associated with monitoring is the cost of the monitoring process and the cost to write of the sampled values to disk. Choosing a decent collection interval (I usually choose 15 sec) and a moderate number of counters (50-100), and writing into a binary file format usually leaves no impact on the monitored system.

But I'd recommend against using Perfmon (as in perfmon.exe). Instead get yourself familiar with with logman.exe, see Description of Logman.exe, Relog.exe, and Typeperf.exe Tools. This way you don't tie the collection session to your session. Logman, being a command line tool, can be used in scripts and scheduled jobs to start and stop collection sessions.

share|improve this answer

There's nothing wrong with running perfmon on production boxes. It's relatively low key, and can gather a lot of good info for you. And how would you accurately simulate production loads if you didn't run some analysis on the production server? From Brent Ozar in your own link:

Let Perfmon run for a day or two to gather a good baseline of the server’s activity. It’s not that invasive on the SQL Server being monitored, and the in-depth results will pay off. The more data we have, the better job we can do on analyzing the Perfmon results.

I've run perfmon on a number of production Exchange boxes with no adverse effects.

share|improve this answer
2  
Perfect. Thanks, @Holocryptic. –  Bill Paetzke May 11 '10 at 21:03
4  
Agreed - there's no overhead from running Perfmon. Another answer suggested running Profiler instead of Perfmon, but there is a very real overhead to running Profiler. I've seen Profiler traces take down production servers when the tracing box couldn't keep up with the load, or when the added load pushed the production server over the edge. –  Brent Ozar May 12 '10 at 9:03
    
Thanks for chiming in @Brent. By the way, I bought your 2008 internals book yesterday. –  Bill Paetzke May 12 '10 at 17:05
    
Ah, cool! Let me know what you think of it. –  Brent Ozar May 16 '10 at 15:45

We do it quite frequently. It is also essential for establishing a baseline in the real environment, so you can compare later if there are issues or you need to perform a capacity study.

I recommend not going below a 10-second interval though. If you are collecting many objects/counters and the interval is too frequent, it may impact operations.

Microsoft has a PerfMon Wizard that will setup the task for you.

http://www.microsoft.com/downloads/details.aspx?FamilyID=31FCCD98-C3A1-4644-9622-FAA046D69214&displaylang=en

share|improve this answer
    
Thanks for sharing, @Greg! –  Bill Paetzke May 11 '10 at 21:17
    
I see that the PerfMon Wizard was released in 2004. I wonder if it comes standard with SQL Server 2005. –  Bill Paetzke May 11 '10 at 21:20
    
I think that version is still current. –  Greg Askew May 11 '10 at 22:11

Ever since I listened to Clint Huffman, who wrote PAL a utility for analyzing Perfmon Logs, on a podcast once. I have setup what I call the Blackbox flight recorder on all of our production application servers. This practice has come in very handy for diagnosing problems and monitoring trends.

Feed a file listing the performance counters, one per line, you would like to monitor to the following batch file and it will configure and start Perfmon recording a snapshot every minute. The resulting round robin log file can be looked at manually or processed with tools like PAL.

@REM SetupBlackBoxLogging.bat
@ECHO OFF
ECHO Configures and starts a continuously running perfmon log that functions like an airplane's flight recorder.
ECHO.
ECHO The first parameter can be an alternative Counter File. 
ECHO You can even drag-n-drop a counter file on to this batch file.
ECHO.
ECHO Resulting logs can be interpreted with PAL (Performance Analysis of Logs)
ECHO    http://www.codeplex.com/PAL
ECHO. 

SET CounterFile=%~dp0Counters_SystemOverview.txt
IF NOT '%1' == '' SET CounterFile=%1
ECHO ON
LOGMAN stop Blackbox -fd
NET STOP SysmonLog
MKDIR C:\BlackBox\
CACLS C:\BlackBox /E /T /G "NT AUTHORITY\NetworkService:C" 
LOGMAN delete Blackbox
LOGMAN create counter BlackBox -o "C:\BlackBox\Blackbox_%computername%" -cf "%CounterFile%" --v -si 00:01:00 -f bincirc -max 250
SCHTASKS /Delete /TN BlackBox /F

ECHO @REM StartBlackBoxLogging.bat > "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO PUSHD C:\BlackBox\ >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO IF Exist Blackbox_%computername%_3.blg DEL Blackbox_%computername%_3.blg >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO IF Exist Blackbox_%computername%_2.blg REN Blackbox_%computername%_2.blg Blackbox_%computername%_3.blg >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO IF Exist Blackbox_%computername%.blg REN Blackbox_%computername%_1.blg Blackbox_%computername%_2.blg >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO IF Exist Blackbox_%computername%.blg REN Blackbox_%computername%.blg Blackbox_%computername%_1.blg >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO LOGMAN start Blackbox >> "%SystemRoot%\StartBlackBoxLogging.bat"
ECHO POPD >> "%SystemRoot%\StartBlackBoxLogging.bat"

SCHTASKS /Create /TN BlackBox /TR "%SystemRoot%\StartBlackBoxLogging.bat" /SC ONSTART /RU SYSTEM
SCHTASKS /Run /TN BlackBox
@ECHO OFF
ECHO Starting Perfmon
PAUSE
Perfmon
share|improve this answer

In an ideal world where a production server exactly mirrors what a dev server, does and is also an exact duplicate of the dev server, perfmon should never be required on the production server because the results would be the same as those on the dev server. Of course that mythical situation never happens, so we do need to run perfmon on production servers and there is absolutely nothing wrong with that. Amongst other things, we may need to use perfmon and other tools to learn why the production server isn't behaving the same as the dev server.

share|improve this answer

Nothing wrong with running Perfmon as many have suggested, but I would run Profiler instead or in addition, with the same caveats, don't capture too much too often, just capture long running queries, i.e. duration > x seconds, or cpu > xx, or reads > xxxx; very little impact, and you'll quickly see the queries that would benefit most from tuning.

share|improve this answer
    
what do you use as base thresholds of duration, cpu, and reads? –  Bill Paetzke May 12 '10 at 0:51
    
It depends on the app, but I would start with duration > (max time I would want any user to wait for anything); start too high, 10 seconds or more, if you get nothing, great, back it down a bit. Guaranteed you'll have some surprises "float" to the top. –  SqlACID May 12 '10 at 1:10
    
..and I would start with duration, only use CPU if you think you are CPU-bound, read or write counters if you are I/O-bound, but use duration if you're not sure where the bottlenecks lie, or just, like you said, trying to get a feel for what's going on. –  SqlACID May 12 '10 at 1:13

Why perfmon? I mean, recent versions of SQL server have their own method of doing that including building a (central) data warehouse of performance counters that can then be queried and reported against. There is zero sense in running perfmon there.

I am, like always, astonished by all the posts here of people who obviously never read the documentation ;)

http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/ is a good start. IMHO that should work on almost every sql server that is used for production purposes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.