The primary tool to capture detailed level information about individual transactions is SQL Profiler. Profiler...
has can be configured to capture all of the transactions on a server or only a subset based on filtering by application name, host, etc. The information captured by Profiler can be stored in a SQL Server table or a flat file for future reference. This image shows a snippet of Profiler's results.
Process-related information can also be captured by the following commands:
- Sp_monitor: Snapshot of SQL Server statistics
- Sp_who: Snapshot of SQL Server processes
- Master.dbo.sysprocesses: System table storing the SQL Server process-related information
- DBCC OPENTRAN:Open transactions internal to SQL Server
- DBCC INPUTBUFFER: Last set of statements sent by the client machine to SQL Server
Hunt down SQL Server performance problems
Step 1: CPU usage
Step 2: Disk IO queuing
Step 3: Memory consumption
Step 4: Network bandwidth
Step 5: Transaction-level performance
|ABOUT THE AUTHOR:|
| Jeremy Kadlec
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.
Copyright 2005 TechTarget