Search
Subscribe
Categories
- Backup and Restore
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- Deadlocks
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Jobs
- Links
- Locking
- Memory
- Monitor Performance
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Cachestore
- SQL Error Logs
- SQL Optimizer
- SQL problems
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- Virtualization
- Wait Stats
Recent Posts
- How to check Operating System type with Powershell
- ALTER TRACE and developers
- Predict ALTER INDEX REORGANIZE finish time
- Cannot connect to SQL Server
- Timeout occurred while waiting for latch: class 'LOG_MANAGER'
- The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
- How to create a SQL Server Security Audit
- Cannot use Large Page Extensions: lock memory privilege was not granted
- Compare two files with Powershell
- Export-CSV Powershell
SQL Server monitors\measures waits by threads. This is a very useful method of focusing on where the real performance issues are occurring . This works for overall performance and also for defined batches
Current Datawarehouse I’m working on is experiencing the following waits . The base information comes from the sys.dm_os_wait_stats DMV. There are plenty of ways in aggregating the data , which I’ll post in the next few days
CXPACKET
PAGEIOLATCH_SH
BACKUPBUFFER
ASYNC_IO_COMPLETION
BACKUPIO
IO_COMPLETION
ASYNC_NETWORK_IO
Definitions of these waits are:
CXPACKET – Synchronizing the query exchange iterator . Analyse the degree of parallelism
PAGEIOLATCH_SH – Waiting for a buffer latch in the I\O request mode. Analayse i\o subsystem
BACKUPBUFFER – Waiting for a buffer to be made available for storing data. Consider analysing the backup process – i.e tape
ASYNC_IO_COMPLETION – I\Os are waiting to finish
BACKUPIO - Waiting for a buffer to be made available for storing data. Consider analysing the backup process – i.e tape
IO_COMPLETION – Waiting for I\Os to finish of non – data page type
ASYNC_NETWORK_IO – Task is blocked on the network. How is the client processing the data?
Source:Jack Vamvas (http://www.sqlserver-dba.com)Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |