In my opinion, disk activity is the second key general performance metric for SQL Server. To determine general disk activity, once again turn to Performance Monitor, which offers valuable disk drive counters for both physical and logical statistics. These include:
- Physical Disk: % Disk Read Time
- Physical Disk: % Disk Write Time
- Physical Disk: % Idle Time
- Physical Disk: Avg Disk Bytes/Read
- Physical Disk: Avg Disk Bytes/Transfer
- Physical Disk: Avg Disk Bytes/Write
- Physical Disk: Avg Disk Queue Length
- Physical Disk: Current Disk Queue Length
Another valuable resource that offers specific information related to the activity at a file level in SQL Server is
Requires Free Membership to View
master.dbo.fn_virtualfilestats. This function offers the disk IO statistics (reads, writes, etc.) for all database and transaction log files.
Hunt down SQL Server performance problems
Home: Introduction
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 |
This was first published in December 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation