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
OK, a member of the application team rushes towards me and says “the database server is slow due to blocking” or “everything has gone really slow” etc. As a DBA ,I’m quite often beset upon by application owners – and answers are needed quickly.
One tool at my disposal is the dynamic management view - sys.dm_os_waiting_tasks. This will return information about the wait queue of tasks awaiting resource usage. Most importantly, this will display the list for this current time.
The thing I like about this dmv is it brings a performance problem down to its bare essentials. i.e server – application – users relationship.
If to many tasks are waiting then consideration is given to questions, such as:
1)Is the application user base greater than expected?
2)Is the SQL Server performance substandard?
3)Is the database size greater than originally specified?
4)Should more resources be allocated?
In a future blog , I’ll expand on how you can use this information to better analyse the processes.
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: |