Microsoft Sql Server 2005 Performance Analysis

Note: This requires both Performance Monitor and Sql Profiler.
What this is essentially targeting is I/O Temp Db and Blocking/locking issues

Performance Monitor:
These are just aimed at the tempdb.....
Otherwise would have include some Physical Disk Counters as well.

As mentioned: Both this and the sql profiler need to be put on all the servers involved

SQL Server Transactions:Free Space in tempdb
SQL Server:Access Methods\Workfiles Created /Sec
SQL Server:Access Methods\Worktables Created /Sec
SQL Server:Access Methods\Mixed Page Allocations /Sec
SQL Server:General Statistics\Temp Tables Created /Sec
SQL Server:General Statistics\Temp Tables for destruction

Sql Profiler:
Database: Filter by database id = 2 (tempdb)
Data File Auto Grow
Log File Auto Grow

Errors and warnings:
Sort Warnings
ErrorLog
EventLog
Exception

Locks:
Deadlock
Deadlock Chain
Timeout
OLEDB:
OLEDB Errors

StoredProcedures:
SP:StmtCompleted
TSQL
SQL:BatchCompleted
SQL:StmtCompleted

Looking at DMVs won't work as there are too many loads overnight.

Tom Bizannes
http://www.macroview.com.au/