DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

An Introduction to SQL Server Wait Types and Stats

Posted on November 10, 2012 Written by Andy Hayes 1 Comment

sql server wait typesOk, so in this post, I wanted to go over SQL Server wait types and statistics  – what they are and how to check them.

My idea is then to start a series of posts which go into more detail about individual SQL Server wait types, their causes and how to improve them. At the time of writing this post, I do not know about all of the wait types in SQL Server and there are many of them.

Some of them I have experience with, some of them I haven’t so this is a partially a learning experience for me, as well as hopefully providing you with informative content so that you can better understand your own SQL Server wait types.

So let us begin…. 🙂

What are SQL Server Wait Types?

When troubleshooting performance issues in SQL Server, it is difficult to know where to start as there is so much to look at.

The best way is to look at your SQL Server at a high level and then drill down to the root causes. As well as capturing hardware/OS and SQL Server related counters using Perfmon, looking at the wait types on your SQL Server is essential to this process.

Ultimately this is more efficient than just assuming that something is, when it may not actually be.

If you, the DBA are more efficient, management gets more for their money and we all know that time is money. SQL Server wait stats provide you with that high level information which you need to succeed.

Whenever a process inside SQL Server has to wait for something, the time spent on that is tracked. So for example, the wait could be tracked because of delays reading data, writing data or some external process.

The waiting process is assigned a wait type. The wait types are not terribly descriptive and require translation into something more meaningful which I will attempt to do in later posts.

If you can reduce waits in SQL Server, you will achieve better overall performance.

How to view your SQL Server wait types and statistics

There is a DMV which you can use to return an aggregated view of system waits in milliseconds.

SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

The results will show many waits, some of them however are system waits and are not useful for performance tuning.

I prefer to use this version which was written by SQL Server expert Glenn Berry. It sorts the “wheat from the chaff” and returns the really useful wait stats from your SQL Server instance, with the worst offenders at the top of the list.

-- Isolate top waits for server instance since last restart or wait statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR'
,'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT'
,'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
,'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT'
,'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95 OPTION (RECOMPILE); -- percentage threshold
GO

If you want to view the current waits as they are occurring, you can do this too. Here I am applying the same filters as in Glenn’s script above:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR'
,'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT'
,'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE'
,'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT'
,'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY wait_duration_ms DESC;

Something to note is that if SQL Server is restarted, then all wait statistics are reset.

Additionally, you can run this to reset them:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Permissions needed….

The user must have VIEW SERVER STATE permission

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server, wait types

How to Delete Millions of Rows using T-SQL with Reduced Impact

Posted on October 9, 2012 Written by Andy Hayes 27 Comments

In this post, I talk about deleting millions of rows in SQL Server whilst keeping impact low.

Deleting millions of rows in one transaction can throttle a SQL Server

TRUNCATE TABLE – We will presume that in this example TRUNCATE TABLE is not available due to permissions, that foreign keys prevent this operation from being executed or that this operation is unsuitable for purpose because we don’t want to remove all rows.

When you run something like the following to remove all rows from your table in a single transaction,

DELETE FROM ExampleTable

SQL Server sets about the process of writing to the transaction log all of the changes to be applied to the physical data. It will also decide on how it lock the data. It’s highly likely that the optimizer will decide that a complete table lock will be the most efficient way to handle the transaction.

There are potentially some big problems here,

  • Your transaction log may grow to accommodate the changes being written to it. If your table is huge, you run the risk of consuming all the space on your transaction log disk.
  • If your application(s) still requires access to the table and a table lock has been placed on it, your application has to wait until the table becomes available. This could be some time resulting in application time outs and frustrated users.
  • Your transaction log disk will be working hard during this period as your transaction log grows. This could be decreasing performance across all databases which might be sharing that disk for their transaction logs.
  • Depending on how much memory you have allocated to your SQL Server buffer pool, there could be significant drops in page life expectancy, reducing performance for other queries.
  • The realisation that a big performance issue is occurring lends temptation to kill the query. The trouble with that is it can delay things even more as the server has to rollback the transaction. Depending on how far along the operation is, this could add on even more time to what was originally going to be.

For example, if you kill the query and it is 90{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de} done then the server has to rollback a 90{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de} completed transaction. This will vary but the rollback can take as much time as the delete operation was in progress! (check using KILL n WITH STATUSONLY)

Some ways to delete millions of rows using T-SQL loops and TOP

Use a loop combined with TOP and delete rows in smaller transactions. Here are a couple of variations of the same thing. Note that I have arbitrarily chosen 1000 as a figure for demonstration purposes.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (1000)
FROM LargeTable
END

And another way…

DoItAgain:
DELETE TOP (1000)
FROM ExampleTable

IF @@ROWCOUNT > 0
GOTO DoItAgain

These are simple examples just to demonstrate. You can add WHERE clauses and JOINS to help with the filtering process to remove specifics. You would add error handling/transactions (COMMIT/ROLLBACK)  also.

Summary

It’s a bad idea to delete millions of rows in one transaction 🙂 and whilst this might sound like a no-brainer, people do try and do this and wonder why things start to go bad.

Breaking the delete operation down into smaller transactions is better all round. This will help reduce contention for your table, reduce probability of your transaction log becoming too large for its disk and reduce performance impact in general.

Your transaction log disk will still be working hard as your refined delete routine removes the rows from your table. Try and run this task during maintenance windows which are typically done inside off peak periods.

Filed Under: All Articles, SQL Server Administration, SQL Server Performance Tagged With: performance, sql server

Using exec sp_who2 to help with SQL Server troubleshooting

Posted on May 20, 2012 Written by Andy Hayes 4 Comments

If you haven’t used sp_who2 before then it is a great utility to help in diagnosing a problem with your database application.

What is sp_who2 ?

It’s a stored procedure which is installed with SQL Server which when run, outputs a row for each “SPID”. SPID stands for Server Process ID and one is created every time an application needs to open a connection to SQL Server.

Each row contains a number of useful columns including information on things like the status of the SPID, what its resource usage is in terms of CPU/IO and what login is currently executing the command.

Permissions needed for sp_who2

A login can run sp_who2 and obtain information about its own connection. For a full list of SPID’s, either the login has to have sysadmin permission or VIEW SERVER STATE permission.

Using sp_who2 to help identify blocking queries

Lets say for example that the phone rings and everyone in the department using the sales system is complaining that their copy of the application is freezing when trying to access the customer sales order data.

So for this blocking demo, I have created a simple table in my database called “Orders” and I am going to start a transaction and leave it open without committing or rolling it back.

BEGIN TRAN
INSERT INTO Orders(AccountID, DatePlaced, Amount, Currency)
VALUES(1, GETDATE(), 100, '$');

Now I will try and read the orders table via another query

SELECT * FROM Orders;

and again via another query…

SELECT * FROM Orders;

Now I will run exec sp_who2 to check what is happening to these connections and it tells me that I have two blocked SPID’s (55 and 56) and that they are being blocked by SPID 54
Using exec sp_who2 to identify blocked queries
The DBA now has to decide how best to proceed with resolving this problem based on the cause and effect of taking action.

They will try and work out what the lead blocker is doing and one way they might do this would be to use “DBCC INPUTBUFFER(SPID)” where SPID is the number of the connection to be analysed and the results returned tell the DBA what the command/query was executing as that SPID.

Instead of using sp_who2, another way would be to look at the dm_exec_requests DMV. I’m going to look at these in a future post.

In my demo it is easy to resolve this as I can kill the connection, force a commit or force a rollback and it will be instantly resolved.

It might not be as clear cut as this in another scenario. Lets say that a process has been running for some time with a large update and this causes the blocking problem described in the demo.

When the DBA views the results of sp_who2 and notices that the process has been running for many hours, they know that by killing the process, a rollback has to finish before the resource will become available again for other queries to be able to complete.

The rollback could also take many hours in this instance and so such decisions cannot be taken lightly. The solution will vary depending on the scenario.

With this tool, you can check a specific connection. It takes an optional parameter SPID, for example:


exec sp_who2 54


Something I get asked is whether there is a way to check for active connections by running a filter – something like exec sp_who2 ‘active’. Sadly this doesn’t work but there is a simple way and that is to capture the results to a temporary table or temporary variable and then query that.

There is a nice piece on this over at Stack Overlow which is worth a read and demonstrates a few solutions to this.

Conclusion

sp_who2 should be part of every DBA’s troubleshooting toolbox.

It provides a great overview of what the connections are doing on the SQL Server and can quickly help the DBA find reasons for increases in application timeouts, high disk IO or high CPU pressure.

 

Filed Under: All Articles, SQL Server Administration Tagged With: performance, sql server, troubleshooting

SQL Server covering index and key lookup performance

Posted on April 15, 2012 Written by Andy Hayes 7 Comments

covering index performance increaseIn this post, I wanted to write about the covering index and key lookup and how adding covering indexes can help increase performance by reducing lookups to the table data.

It’s helpful to know what each of these terms mean and then we will look at an example scenario further down the post.

What is a covering index?

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

To be considered as a covering index, all columns referenced by the query must be contained in the index. So this means all columns in the SELECT, JOIN, WHERE, GROUP BY, HAVING etc etc.

What is a key lookup?

A key lookup is an extra read which the query has to make in the absence of a suitable covering index.

In the query, the number of columns involved in the select statement may exceed the number of columns in the non-clustered index and when this happens, for each row in the result set, a key lookup is performed. So 500 rows in the result set equates to 500 extra key lookups.

Key lookup medicine – AKA the covering index!

Lets look at a simple query to help explain what I was talking about above. Imagine that dbo.MyTable consists of just two columns “Col1” and “Col2”.

SELECT Col1 FROM dbo.MyTable WHERE Col2 = 1;

For this query to be considered covered by an index, columns “Col1” and “Col2” need to be part of the same index and you could create different variants of indexes which would act as a covering index.

For example, you could do:

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col1, Col2);

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col2, Col1);

You could also use included columns:

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col1) INCLUDE (Col2);

CREATE NONCLUSTERED INDEX IX_MyCoveringIndex
ON dbo.MyTable (Col2) INCLUDE (Col1);

So the columns need only be found in the index and the column order or whether they are an “included column” or not does not matter. It is important to remember that the execution plan and performance may vary considerably depending on which index is applied however, so choosing the correct covering index is important.

Lets look at how the covering index removes the need for a key lookup.

I am going to use this simple query against a copy of the AdventureWorks2012 database using SQL Server 2012 Express.

I’ll enable the execution plan output and enable STATISTICS IO output.

SET STATISTICS IO ON;

SELECT CustomerID, OrderDate
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
WHERE CustomerID = 11000;

There is an non-clustered index on the CustomerID column. The clustered index on this table is on a column not referenced by the query called SalesOrderID.

The output looks like this:

STATISICS IO output….

Table 'SalesOrderHeader'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now lets look at the execution plan

covering index key lookup

You can see that 68{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de} of the total cost of this query has been allocated to the key lookup against the clustered index.

Let’s see what happens to it after we modify the index to cover the columns in the query.

DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_CustomerID;

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_CustomerID_OrderDate
ON Sales.SalesOrderHeader(CustomerID, OrderDate);

covering index key lookup

The key lookup is gone (well you were expecting that right 🙂 ) and now the query is retrieving the data entirely from the covering index.

Lets look at STATISTICS IO again

Table 'SalesOrderHeader'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A big reduction can be seen in the number of logical reads compared to the first query. Logical reads are the pages read from the data cache regardless of whether the pages had to be read from disk or not. In both cases, they did not need to be read from disk (physical reads = 0), both queries read their pages direct from the data cache. As the second one read less pages from the data cache, that is still a good indicator that the covering index has improved performance.

If I ran DBCC DROPCLEANBUFFERS (do not do this on a production server as you could see massive performance reduction until the cache is re-populated) before each query and index change in order to force the optimizer to read from disk, then STATISTICS IO would look like:

--BEFORE covering index
Table 'SalesOrderHeader'. Scan count 1, logical reads 11, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--AFTER covering index
Table 'SalesOrderHeader'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can see the differences in the number of pages read from disk and this helps to show the benefit of having the covering index in place. Once the  data pages are read from disk, they enter the data cache. All queries get their results by reading the data cache.

The second query reads less data pages into the data cache and subsequently, there is more room in the data cache. More room means more pages can be read into cache and the frequency for disk reads becomes less with performance increasing.

Should you apply a covering index in all cases?

Indexing is a complex subject and there is no definitive “one size fits all” solution to indexing a database. You have to know what is possible and then assess your needs. You should always test your index changes properly before putting them into production.

An analysis of server workload will help you figure this out.  A few things to consider:

  1. Is existing query performance acceptable?
  2. How often is the query running?
  3. How selective is the query?
  4. Is the table you are adding the index to write intensive?

Remember, a query may perform well and return results in good time. What if it is running many hundreds or thousands of times a minute? Cumulatively without a covering index, it could be generating many more extra IO’s than necessary.

You need to assess whether the frequency and selectivity of the query warrants adding a covering index. Covering indexes have the greatest benefit to non-selective queries.

In addition, if your system is write intensive, adding an index could have the opposite affect of what you are trying to do by slowing down overall database performance. This is because for each update to the data, the indexes have to be updated as well.

For more information on SQL Server index creation, you can visit this link which talks about the covering index and included columns.

 

 

Filed Under: All Articles, SQL Server Performance Tagged With: mysql, performance, sql server 2000, sql server 2005, sql server 2008, sql server 2012

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close