Wednesday, April 27, 2005

HOWTO: SQL Server: Reporting on performance

Some resources to draw info on SQL Server memory usage, locks, fragmentation, etc. This is my favorite SQL Server tuning site:

SQL Server Perf Mon

SQLServer: Access Methods - Page Splits/sec: Number of page splits occurring as the result of index pages overflowing.
SQLServer: Databases - Data File Size (KB)
SQLServer: Databases - Log File Size (KB)
SQLServer: Databases - Log File Used Size (KB)
SQLServer: General Statistics - User Connections: Number of users connected to the system.
SQLServer: Locks - Average Wait Time (ms): The average amount of wait time (milliseconds) for each lock request that resulted in a wait.
SQLServer: Locks - Number of Deadlocks/sec: Number of lock requests that resulted in a deadlock.
SQLServer: Memory Manager - Total Server Memory (KB): Total amount of dynamic memory the server is currently consuming
Many, many others…


View Blocks
SELECT spid, blocked, waittype, getdate() AS 'Time'
FROM master..sysprocesses (nolock)
WHERE blocked <> 0

View a whole lot of info on processes
FROM master..sysprocesses (nolock)

View process info sorted by memory usage
FROM master..sysprocesses (nolock)
ORDER BY memusage DESC

How to monitor SQL Server 2000 blocking

Stored Procedures (system)

No comments: