In this article we look at using Wait stats to discover performance issues in SQL server
Facing different types of troubles while working on a database like SQL is a common scenario. In fact, every time, the server is running a query, it starts tracking the duration of time required to be spend on all the bottlenecks. This is where the Wait Stats Feature plays its role. Wait Stats is an interesting and an often overlooked feature of SQL Server Database. These were first introduced in 2005 version of SQL Server. Initially, these stats were available on all the popular databases they are available on SQL Server mainly for troubleshooting the performance issues. In this article, you will get to learn in depth about different categories of Wait Stats Feature in SQL Server.
Signal Waits
As far as the signal wait stats are considered, they are gathered by the different processes that run of the SQL Server that wait for a processor to work upon them. In other words, these are the wait stats that are signaled ready for processing in an SQL Server.
Resource Waits
As the name suggests, the resource wait stats are the ones that get accumulated by the different processes that run on an SQL server and are waiting for a particular resource. Perhaps, this is the reason these are called as resource waits means waiting for the resources from the server.
Other Wait Stats in SQL Server
Apart from the Signal and Resource Wait Stats, there are a number of other smaller wait stats that often hamper the performance of an SQL Server Database. Here is a list of all such wait stats:
- BACKUP Wait: You may not even notice but sometimes your backups are very slow and are the top most wait stat that is hindering your server performance. You must check it out if your backups are taking time to process.
- SOS_SCHEDULER_YIELD Wait: It stands for SQL Operating System that waits for an apt CPU scheduler to give more time; however, it doesn’t means that the CPU needs to give more time, what it actually means is that a particular task on the server requires for CPU time.
- PAGEIOLATCH Wait: Here the server is found to be waiting for the data pages to get read from the storage and this basically happens when the pages were not caches in the memory of the server.
- ASYNC_NETWORD_IO Wait: This particular type of wait stat occurs when the query results are brought out by the server and they are yet waiting for the application on the other side to receive the results promptly.
Limitations of Wait Stats in SQL Server
Wait stats are useless when it comes to deal with non SQL server issues, when the issue is present in the application layer. Also, there are a number of places where these stats don’t appear relevant and should not be given much credence.
While we plan to optimize the SQL Server database, we often ignore planning for contingencies
In many organizations, effort is made to optimize the performance of the SQL Server database. However in many such organizations, equal effort is not shown in planning for contingencies such as a SQL crash. Ideally companies should always keep a sql server recovery tool like DataNumen SQL Recovery handy. This powerful tool in the event of a SQL crash can immediately bring back the corrupted data without compromising on data integrity. Further the tool can tackle an array of storage formats and extracts content from virtual drives without breaking a sweat.
Author Introduction:
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply