SQL Profiler and PerfMon

When looking for slow queries using SQL Profiler these are the events and columns I like to collect:

• Stored Procedures / RPC:Completed
• Stored Procedures / SP:StmtCompleted
• TSQL / SQL:BatchCompleted
• TSQL / SQL:BatchStarting  (sometimes)

I've seen Performance / ShowplanXML recommended, but haven't really dug into it yet... be sure to select the BinaryData column for this column.

Columns:

• Event Class (auto-selected)
• Duration
• TextData
• SPID
• Reads
• RowCounts
• Writes
• StartTime
• EndTime
• ObjectName (useful to know context)
• EventSequence (sometimes helpful for context)

Other Columns to consider:

• CPU
• IntegerData
• DatabaseName
• LoginName
• ApplicationName (not always populated)
• BinaryData (for ShowplanXML)

Thanks to Brad McGehee's blog (here) for ShowplanXML events along with ApplicationName and EventSequence columns.

Once the Events & Columns are selected and organized I'll setup Column filters:

• Duration greater than or equal to a number, i.e. 1000 = 1 second in SQL 2008 Profiler.
• Database equals or not equals (if applicable).

Use PerfMon to check general performance with these key events:

• Memory – Available MBytes
• Paging File – % Usage
• Physical Disk – Avg. Disk sec/Read
• Physical Disk – Avg. Disk sec/Write
• Physical Disk – Disk Reads/sec
• Physical Disk – Disk Writes/sec
• Processor – % Processor Time
• SQLServer: Buffer Manager – Buffer cache hit ratio
• SQLServer: Buffer Manager – Page life expectancy
• SQLServer: General Statistics – User Connections
• SQLServer: Memory Manager – Memory Grants Pending
• SQLServer: SQL Statistics – Batch Requests/sec
• SQLServer: SQL Statistics – Compilations/sec
• SQLServer: SQL Statistics – Recompilations/sec
• System – Processor Queue Length