Collect SQL Profiler Data
You can best run SQL Profiler — and store its results — on your monitoring server, collecting production database events using a trusted connection.
Try to limit the data you log to a minimum, but you will at least want to monitor the following:
Events:
-
Stored procedures: RPC Completed
-
TSQL: BatchCompleted
Data Columns:
-
TextData
-
Duration
-
CPU
-
Reads
-
Writes
-
ServerName and ApplicationName (if multiple servers are monitored)
-
EndTime
Filters:
-
Duration >= 10 ms. Unless you want to save a workload file for replay, you’re not interested in queries that take less than 10 ms.
-
System ID’s
Remember, this set is designed for baselining which is useful over a longer period of time. For other types of tuning, other settings may be more advisable.
Tip 2: Store PerfMon and Profiler Results in a DB
I always log results into SQL Server database tables, including both PerfMon as SQL Profiler data. The big advantage of logging data into a file is that data can be accessed immediately using T-SQL.
This gives us — SQL users — the flexibility we need to:
-
Retrieve online information of current system behavior (e.g. to build an online dashboard).
-
Consolidate data from multiple sources in a batch process (PerfMon/profiler/other data sources) for trend-analysis (see tip 8).
-
Create online or analytical reports using a reporting tool like Reporting Services.
Save PerfMon Counters
When creating a PerfMon counter log, set Log File Type = SQL Database. This will generate a set of tables “automagically” to be filled in by PerfMon (see data model).
Make sure the security context your Performance Logs and Alerts service is logged on with, and has sufficient rights for both your monitoring AND production database systems.