DBA Diaries

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

Header Right

SQL Server Performance

Reducing SQL Server CXPACKET Wait Type

by 10 Comments

cxpacket wait type parallelismIn my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server.

For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it.

This is one wait type that can be misjudged.

I mentioned before that the wait types need translation – MSDN describes this as:

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. The idea is that if there are more threads working on different parts of the data set, it can be more efficient.

Parallel execution can therefore be a highly effective way of dealing with large sets of data, for example in a data warehouse/reporting server. However, in OLTP systems, it could well have a negative impact on performance.

CXPACKET wait type occurs when there are threads taking longer to execute than the other threads in that query. The completed threads are left in waiting state until the last thread can complete.

I’ll go into more detail about these settings in the next section but by default your SQL Server is configured for parallelism as the “max degree for parallelism setting” is set at zero.

For a query then to qualify for parallel execution, its estimated (or cached) cost needs to exceed the “cost threshold for parallelism” setting.

So in an example scenario on a server with 8 cores, if a parallel query executes with “max degree of parallelism” set at zero, then up to 8 cores can be used in the query. As each core completes its work, it waits until all cores have completed.

The result is CXPACKET waiting and the potential to delay other SPIDs wanting a piece of CPU time to process their request.

Assessing causes of CXPACKET wait type is vitally important

You have to remember that CXPACKET waits are normal for multiprocessor servers because of parallel execution. So simply adjusting your server configuration in an effort to reduce this wait type could have an overall negative impact on performance.

Understanding whether the system is an OLTP, DSS or combination of both is critical.

It’s no good simply assuming that because you have CXPACKET waits, that it is automatically a problem. If CXPACKET is one of the top reported wait types on your SQL Server, the next thing to consider is, what are the causes and is this wait type normal for my workload?

There may be other contributing factors. Is CXPACKET a symptom of something else? For example, it could be as simple as a missing index or out of date statistics. Your SQL Server then tries to compensate by generating a query plan that uses parallelism in an effort to optimize the query, or rather, to make the scan operation go faster.

How to reduce CXPACKET wait type by changing settings

There are some settings which can affect how your queries use parallelism.

  1. Server level – “Max Degree of Parallelism”
  2. Server level – “Cost Threshold for Parallelism”
  3. Query hint – MAXDOP

Max Degree of Parallelism – specifies the number of CPU cores to be used in parallel query execution at the server level. The default value is 0 which means that SQL Server will use all available cores. Setting this to 1 turns off parallelism. Here is an example which instructs the SQL Server to use no more than 2 cores for parallel execution:


EXEC sp_configure 'max degree of parallelism',2
GO
RECONFIGURE WITH OVERRIDE
GO

Cost Threshold for Parallelism –  the estimated elapsed time in seconds before the server will consider parallelism in the query execution. Here is an example setting this value to the estimated elapsed time of 10 seconds:


EXEC sp_configure 'cost threshold for parallelism', 10
GO
RECONFIGURE WITH OVERRIDE
GO

MAXDOP – a query hint where the number of cores is specified for the query. Here is an example using MAXDOP for 2 cores:


SELECT Column1, Column
FROM SomeTable
WHERE (Column1 > @Value1 AND Column2 < @Value2)
OPTION (MAXDOP 2)

Note that you can also change max degree of parallelism and cost threshold for parallelism settings using Management Studio. These settings can be found under the Properties->Server Properties->Advanced section.

Summary and guidance points for CXPACKET wait type and parallelism

  • OLTP – parallelism can have a negative impact on performance.
  • OLAP/reporting benefits from parallelism.
  • Look at your plan cache, which of your queries use parallelism?
  • Check query plans – do you have missing indexes or out of date statistics?
  • What other waits are occurring that might be causing CXPACKET?
  • Consider using MAXDOP to fine tune specific queries.
  • Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
  • Do not adjust max degree of parallelism without proper analysis of your servers workload/queries.

An Overview of SQL Server Task Scheduling

by 1 Comment

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks.

On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads.

You can view the schedulers on your SQL Server by running this code:


SELECT * FROM sys.dm_os_schedulers;

Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there:

  • VISIBLE ONLINE (DAC) – thread scheduler used to manage the DAC (Dedicated Administator Connection)
  • VISIBLE ONLINE – user thread schedulers
  • HIDDEN ONLINE – internal operation thread schedulers

So depending on how many CPUs you have in your SQL Server, you will see from this DMV that there can be quite a few schedulers available.

More CPUs = more schedulers = more work can get done 🙂

There are some other status’ which you might see:

  • HOT_ADDED – schedulers created in response to a CPU which was added whilst the server was online (hot added CPU)
  • VISIBLE OFFLINE – user schedulers which are mapping to CPUs which are offline in the affinity mask.
  • HIDDEN OFFLINE – schedulers assigned to internal operations mapped to CPUs which are offline in the affinity mask.

Thread status

If you’ve ever looked at your process list in SQL Server either by using sp_who2 or activity monitor, you may have noticed that there is a column in there called “Status” (activity monitor in Management Studio 2012 calls this “Task State”)

There are some status’ which help you to understand how the queries execute, they are RUNNING, SUSPENDED and RUNNABLE

RUNNING – the thread is actively running on the CPU.

SUSPENDED – thread is waiting for a resource to become available. Thread is on the “waiter list”.

RUNNABLE – resources are now available to the thread but as only one thread can execute on a CPU at any given time, the thread has to wait its turn. It goes to the bottom of the “RUNNABLE queue” and finally returns to a RUNNING state when it is its turn to run as controlled by the CPU scheduler.

It goes to the bottom of the “RUNNABLE queue”

Please note that this behaviour can change if using RESOURCE GOVERNOR (Enterprise Edition)

The size of the “RUNNABLE queue” for each scheduler can be seen by looking at the value for the “runnable_tasks_count” column from the sys.dm_os_schedulers DMV.

Threads can transition around the different states until their work is completed. When on the waiter list, a thread can wait for some time before it changes state. There is no set order in which they are then changed from SUSPENDED to RUNNABLE.

In order to view what each thread is waiting for, you can look at the sys.dm_os_waiting_tasks DMV:


SELECT * FROM sys.dm_os_waiting_tasks;

 Summary points

  • Each CPU (logical or physical) is assigned a scheduler.
  • Only one thread can be running on a CPU at any given moment.
  • Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
  • Use sys.dm_os_schedulers to view schedulers on your SQL Server.
  • Use sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available.

An Introduction to SQL Server Wait Types and Stats

by 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

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

by 21 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% done then the server has to rollback a 90% 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.

SQL Server covering index and key lookup performance

by 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% 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.

 

 

Will solid state hard drives negate the need to performance tune your database server?

by 4 Comments

Dodo BirdSolid state hard drives or SSD’s as they are also known are the latest generation of hard disk storage. They are far superior to traditional hard disk drives because of the absence of moving parts and so are blisteringly faster in comparison.

Due to the architecture of the platter based hard disk, it is much slower than other parts of the server and can typically be the source of a bottleneck in the system. Like with any problem, there are people who specialise in sorting the issue out.

There are many thousands of performance tuning experts who make a living from getting to the root cause of all the I/O issues in a system and I doubt that there is not one of them who has thought what a great evolution the SSD is but at the same time, feeling a little apprehensive about their own future as a performance tuning professional.

If all I/O issues could be eliminated by a hardware upgrade, then managers will seriously consider implementing SSD’s if the ROI is right for the business. Currently however SSD’s are far more expensive than the platter based hard disk so when considering purchasing new hardware, the SSD won’t be something which system managers will necessarily go for in favour of the older design.

Even if managers may not have the entire disk storage system running on SSD’s, the SSD is starting to make an appearance in the database server setup finding its place to store tempdb for example – a busy database which is often placed on its own disk storage system.

As with anything new, it’s always expensive in its infancy but as new versions of it are made and competing products are produced, costs are driven down and sooner or later it becomes “the norm”. When I first learnt about the SSD, my immediate thought was that it would one day be the drive of choice for managers when designing a server.

This recent article by Lucas Mearian on ComputerWorld.com has certainly provided food for thought on the subject as it indicates that SSD’s may not improve as expected to for years to come.

Personally I think that those clever technology inventors will find a way to make it work both from a technological and cost perspective ensuring that the SSD or variant of will one day become mainstream in server configurations across the globe. Whether this means that the performance tuning DBA goes the way of the Dodo is difficult to say but there will be some people out there who will be thinking this to be a possibility.

Perhaps it is all relative and that as server hardware becomes faster, we as humans find new ways to push it to its limits and the requirements of ensuring that data access best practices to ensure optimal performance will be no less important in the future as they are now.

 

 

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