Performance Tuning of Microsoft SQL Server 2008
I was just reviewing the performance of my Datawarehouse SQL server Instance. with very helpful utility Provided by Microsoft rep, which show which are the root cause impacting server performance.
All the wait type are out come of some issue on the server and not a problem by itself. hence shows us where to go and find a fix.
Get Following results:
As we see CXPACKET and PAGEIOLATCH_SH make for 90 % in this scenario.
for CXPACKET
CXPACKET
The SQL Server CXPacket waittype is involved in parallel query execution. It indicates that the SPID is waiting on a parallel process to complete or start. The CXPacket waittype occurs when trying to synchronize the query processor exchange iterator. Excessive CXPacket waits are typically resolved by DBAs but may indicate a problem with the WHERE clause in the SQL Server query.
For OLTP applications where optimal SQL Server performance is required, CXPacket greater than 5% of total query execution time indicates a problem. Parallelism reduces SQL Server performance for OLTP applications. CXPacket indicates the operation of multiple parallel CPUs, each executing a portion of the query. Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction.
Solutions
In SQL Server performance optimization, sometimes the cost of breaking apart a parallel query and putting the many results back together is more than the cost of running the query had parallelism not been used. In those cases, these wait types become numerous and long lasting. Queries which are heavily balanced to one sub query or another are a common cause of these. If, for example, your query retrieved records from four tables and one of them held the vast majority of records, and parallelism caused this to be spread across several threads, three of them would have to wait on the largest and you would see Exchange wait types. There are many suggestions for taking care of these types of SQL Server waits, with the most common to be to turn parallelism off, sometimes for just that query, sometimes for the whole server.
To check for parallelism: sp_Configure "max degree of parallelism".
If max degree of parallelism = 0, you might want to use one of the following options:
Turn off parallelism completely for OLTP workloads: set max degree of parallelism to 1
Limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
For PAGEIOLATCH_SH:
Found Solution at http://www.sqlserver-dba.com/2011/03/sql-server-pageiolatch_sh-and-how-to-reduce-it.html
SQL Server – Pageiolatch_sh and how to reduce it
SQL Server Books online defines as “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. “
The underlying causes typically relate to disk to memory , memory pressure and disk IO subsystem issues such as caching problems.
The following scenario illustrates how this wait type arises. A user process will request some data which is not currently in the buffer cache. At that point – SQL Server will attempt to allocate a buffer page – and while the data is moved from disk to buffer cache an exclusive PageIOLatch_ex is created on the buffer. At the same time a PageIOLatch_sh is created on the buffer from the user process perspective.
When data is written to cache the PageIOLatch_ex is released. As the user still has the PageIOLatch_sh – the user can read the data.
A few things to note:
1) PageIOLatch_ex and PageIOLatch_sh are often reported as high – if so, disk io subsystem will be your first target
2)The disk spin rate should not be the focus – but rather the workload. Therefore , for testing – run the workload and monitor disks
3)It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause.
4)General database tuning can help – but a single transaction is defined by : amount\speed of disks , memory amount availability , processor speed.
5)Index management
5)Differentiate between disk issues and memory issues – afterall ,if memory is experiencing pressure – than dealing with incoming data will be slower
6)Logical drive profiles – separating random file access with sequential files
I was just reviewing the performance of my Datawarehouse SQL server Instance. with very helpful utility Provided by Microsoft rep, which show which are the root cause impacting server performance.
All the wait type are out come of some issue on the server and not a problem by itself. hence shows us where to go and find a fix.
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
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', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
Get Following results:
| WaitType | Wait_S | Resource_S | Signal_S | WaitCount | Percentage | AvgWait_S | AvgRes_S | AvgSig_S |
| CXPACKET | 4105417.42 | 4057626.63 | 47790.79 | 296017960 | 45.41 | 0.0139 | 0.0137 | 0.0002 |
| PAGEIOLATCH_SH | 3464550.48 | 3456926.28 | 7624.2 | 198597479 | 38.32 | 0.0174 | 0.0174 | 0 |
| OLEDB | 465003.08 | 465003.08 | 0 | 285915658 | 5.14 | 0.0016 | 0.0016 | 0 |
| LATCH_EX | 355069.39 | 336352.59 | 18716.8 | 208470024 | 3.93 | 0.0017 | 0.0016 | 0.0001 |
| IO_COMPLETION | 159207.33 | 159101.98 | 105.34 | 10326423 | 1.76 | 0.0154 | 0.0154 | 0 |
| PREEMPTIVE_OLEDBOPS | 150260.25 | 150260.25 | 0 | 88452437 | 1.66 | 0.0017 | 0.0017 | 0 |
As we see CXPACKET and PAGEIOLATCH_SH make for 90 % in this scenario.
for CXPACKET
Found a useful solution here: http://www.confio.com/English/Tips/Sql_Server_Wait_Type_CXPACKET.php
The SQL Server CXPacket waittype is involved in parallel query execution. It indicates that the SPID is waiting on a parallel process to complete or start. The CXPacket waittype occurs when trying to synchronize the query processor exchange iterator. Excessive CXPacket waits are typically resolved by DBAs but may indicate a problem with the WHERE clause in the SQL Server query.
For OLTP applications where optimal SQL Server performance is required, CXPacket greater than 5% of total query execution time indicates a problem. Parallelism reduces SQL Server performance for OLTP applications. CXPacket indicates the operation of multiple parallel CPUs, each executing a portion of the query. Ordinarily a well tuned OLTP application would not parallelize unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction.
Solutions
In SQL Server performance optimization, sometimes the cost of breaking apart a parallel query and putting the many results back together is more than the cost of running the query had parallelism not been used. In those cases, these wait types become numerous and long lasting. Queries which are heavily balanced to one sub query or another are a common cause of these. If, for example, your query retrieved records from four tables and one of them held the vast majority of records, and parallelism caused this to be spread across several threads, three of them would have to wait on the largest and you would see Exchange wait types. There are many suggestions for taking care of these types of SQL Server waits, with the most common to be to turn parallelism off, sometimes for just that query, sometimes for the whole server.
To check for parallelism: sp_Configure "max degree of parallelism".
If max degree of parallelism = 0, you might want to use one of the following options:
Turn off parallelism completely for OLTP workloads: set max degree of parallelism to 1
Limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
For PAGEIOLATCH_SH:
Found Solution at http://www.sqlserver-dba.com/2011/03/sql-server-pageiolatch_sh-and-how-to-reduce-it.html
SQL Server – Pageiolatch_sh and how to reduce it
SQL Server Books online defines as “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. “
The underlying causes typically relate to disk to memory , memory pressure and disk IO subsystem issues such as caching problems.
The following scenario illustrates how this wait type arises. A user process will request some data which is not currently in the buffer cache. At that point – SQL Server will attempt to allocate a buffer page – and while the data is moved from disk to buffer cache an exclusive PageIOLatch_ex is created on the buffer. At the same time a PageIOLatch_sh is created on the buffer from the user process perspective.
When data is written to cache the PageIOLatch_ex is released. As the user still has the PageIOLatch_sh – the user can read the data.
A few things to note:
1) PageIOLatch_ex and PageIOLatch_sh are often reported as high – if so, disk io subsystem will be your first target
2)The disk spin rate should not be the focus – but rather the workload. Therefore , for testing – run the workload and monitor disks
3)It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause.
4)General database tuning can help – but a single transaction is defined by : amount\speed of disks , memory amount availability , processor speed.
5)Index management
5)Differentiate between disk issues and memory issues – afterall ,if memory is experiencing pressure – than dealing with incoming data will be slower
6)Logical drive profiles – separating random file access with sequential files
