PAGEIOLATCH_EX

Definition

In SQL Server, a latch is a short-term lightweight synchronization object.

Buffer latches including wait type Pageiolatch_EX are used to synchronize access to BUF structures and associated pages in the SQL Server database. The most frequently occurring buffer latching situation is when serialization is required on a buffer page. These buffer latches are not held for the complete period of the transaction. The PAGEIO latches are a subset of BUF latches used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers.

PAGEIOLATCH_EX (Exclusive mode page IO latch request)

When a SQL Server user needs a page that is not in buffer cache, the database must first allocate a buffer page, and then puts an exclusive PageIOLatch_ex latch on the buffer while the page is transferred from disk to cache. During this operation SQL Server puts a PageIOLatch_sh request on the buffer on behalf of the user. After the write to cache is complete, the PageIOLatch_ex latch is released.

Problem Indication

Excessive PageIOLatch_EX wait is an indication of disk subsystem problems.

 

Advertisements

ASYNC NETWORK IO

The “async network io” (in SQL 2005/2008) and “networkio” (in SQL 2000) wait types can point to network related issues, but most often are caused by a client application that is not processing results from the SQL Server quickly enough. This will result in filling the network buffers so that SQL Server cannot send more data to the client. Therefore, the process executing the batch will need to wait for the ability to continue sending results to the client.

Reducing Waits / Wait times:

If there are significant wait times on “async network io’, review the client applications. Most often, client applications will process rows one at a time using fetches. This may cause the server process to wait on “async network io” when serving up many rows. If this is the issue, there is nothing you can do to improve the SQL Server process performance. Instead, the client application (or middle tier if a web application) may need to be modified to allow for more efficient retrieval of data. Review the following list for client application issues:

  • Some applications, such as Microsoft Access, will ask for large result sets (typically identified by select statements with no where clause or full table scans), and then further filter the data on the client. If this is causing significant wait time, see if it’s possible to create a view for the client application to use instead. This will significantly reduce the amount of data being pushed to client since all of the filtering will done on SQL Server. Another fix could be to add a ‘where clause’ or further restrict the query so that less data is being sent to the client.
  • Identify large result sets and verify with the application or developer team how the data is being consumed.  If the application is querying large result sets but using only a few rows, consider only querying the rows that are needed or use ‘TOP n’ to reduce the number of rows returned.
  • If you are encountering high “async network io” wait times when performing data loads on the server, make sure the shared memory protocol is enabled for the SQL Server instance and the session is connected using net_transport = ‘Shared memory’. You can determine the net_transport for the connection by looking at the DMV – sys.dm_exec_connections.

If the above tuning tips are reviewed and applied, but the server is still is encountering high “async network io” times, then ensure there aren’t any network related issues:

  • Review counters such as ‘Batch Requests per second’. Any values over 3000 for a 100MB network card are getting excessive. ‘Current Bandwidth’ on the Network Interface with values over 0.6 are getting excessive.
  • Check network adapter bandwidth – 1 Gigabit is better than 100 megabits and 100 megabits is better than 10 megabits.
  • Look at your NIC configuration on the server to make sure there are no issues with the physical card. Also, check if autodetect is picking the fastest speed.
  • Validate all of the network components between the client application and the SQL Server instance (e.g. switches / routers).

DMV query:

As a side note, reviewing the resources that SQL Server sessions are waiting on (or using up) can be quite helpful when tuning SQL Server for performance. There are several DMVs (Dynamic Management Views) that report the wait types (or resources) each the session is waiting on while running specific SQLs. To view the sql text, the wait type and time waited for a session, run the query listed below:

SELECT st.text AS [SQL Text],
w.session_id,
w.wait_duration_ms,
w.wait_type, w.resource_address,
w.blocking_session_id,
w.resource_description FROM sys.dm_os_waiting_tasks AS w
INNER JOIN sys.dm_exec_connections AS c ON w.session_id = c.session_id
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(c.most_recent_sql_handle))
AS st WHERE w.session_id > 50
AND w.wait_duration_ms > 0

The ‘wait_duration_ms’ value will increase for the session_id as the session is waiting for the ‘wait_type’ (for a specific SQL). The statistics in the DMV query are cumulative since last Instance restart or since the statistics were last cleared. In order to get a clear picture of all the SQLs and what resources (or wait_types) they are waiting on perform deltas of this query and record them over a period of time. This will give you a better understanding of what sqls to work on first and how to tune them. To reset the wait statistics in the DMVs, enter the following command in SSMS: DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR).

Conclusion

When a session waits on the “async network io” event, it may be encountering network issues. More likely, however, it may be an issue with the client application not processing the data quickly enough. If the wait times for “async network io” are high, review the client application to see if large results sets are being sent to the client. If they are, work with the developers to understand if all the data is needed and reduce the size of result set if possible. Ensure that any data filtering is performed in SQL Server instead of the client by utilizing views or more specific where clauses. Use the ‘TOP n’ clause so that only the rows that the client needs are returned to the client. Investigate network issues, if client application tuning tips do not apply.

 

WRITELOG

Definition

When a SQL Server session waits on the WRITELOG wait type, it is waiting to write the contents of the log cache to disk where the transaction log is stored.

To explain the process in more detail, assume a session starts a transaction that will perform several INSERT statements. While the data is being inserted, two actions occur:

  1. The data page in the buffer cache is updated with the new data.
  2. Data is written to the log cache which is a segment of memory used to record data that will be used for rolling back the transaction or be written to the log file.

This process continues until the transaction has completed (committed) at which time the data in the log cache is immediately written to the physical log file. When SQL Server is in the act of flushing the log cache to disk, the session will wait on the WRITELOG wait type.

Getting More Information

If sessions are consistently waiting on the WRITELOG wait type, review the following Perfmon data for signs of disk bottlenecks where the transaction log is stored:

  1. PhysicalDisk Object
    • Avg. Disk Queue Length – the average number of IO requests that were queued. If this is consistently greater than one, this could indicate a disk bottleneck.
    • Avg. Disk sec/Read and Avg. Disk sec/Write – if either of these are higher than 15-20 ms, this could indicate the transaction log is stored on a slow device
  2. SQLServer: Buffer Manager
    • Checkpoint pages/sec – number of pages flushed by checkpoint operations that require all dirty buffers to be written to disk

Using Ignite for SQL Server or other tools, also determine the top SQL statements waiting on the WRITELOG event. If many statements are found waiting, this could indicate that one of the above items is the problem. If only a few SQL statements are found waiting on WRITELOG, it could indicate an inefficient use of transactions (discussed with examples below).

Fixing the Problem

Disk Subsystem Performance – In much of the documentation about the WRITELOG wait type, it seems the problem is often mischaracterized as solely a disk subsystem issue. In the cases where disks are the problem, counters from the PhysicalDisk Object in Perfmon will be high and fixes often include:

  1. Adding additional IO bandwidth to the disk subsystem where the transaction log is stored.
  2. Moving non-transaction log IO from the disk.
  3. Moving the transaction log to a less busy disk.
  4. Reducing the size of the transaction log has also helped in some cases.

Committing Data Too Often – In many cases that we see during performance consultations, excessive waits on the WRITELOG wait type can be caused by an over zealous usage of transactions, i.e. committing data too often. To illustrate this problem, consider the following code examples:

Example 1: The following code took 418 seconds to execute and waited 410 seconds on the WRITELOG wait type. Note how the COMMIT statement is located inside the loop and executed 100,000 times.

DECLARE @i INT
SET @i = 1
WHILE @i < 100000
BEGIN
BEGIN TRANSACTION
INSERT INTO [splendidCRM].[dbo].[product]
([productid],
[category],
[name],
[descn])
VALUES (@i,
floor(@i / 1000),
‘PROD’ + REPLACE(str(@i),’ ‘,”),
‘PROD’ + REPLACE(str(@i),’ ‘,”))
SET @i = @i + 1
COMMIT
END

Example 2: The following code, which also inserts 100,000 rows as above, took 3 seconds and waited less than a second on the WRITELOG wait type. Note how the COMMIT statement is located outside the loop and only executed once.

DECLARE @i INT
SET @i = 1
BEGIN TRANSACTION
WHILE @i < 100000
BEGIN
INSERT INTO [splendidCRM].[dbo].[product]
([productid],
[category],
[name],
[descn])
VALUES (@i,
floor(@i / 1000),
‘PROD’ + REPLACE(str(@i),’ ‘,”),
‘PROD’ + REPLACE(str(@i),’ ‘,”))
SET @i = @i + 1
END
COMMIT

Conclusion

When sessions are found waiting on the WRITELOG wait type, it is imperative to perform a full analysis of the situation. This analysis should include review of disk performance data but also a review of all SQL statements found waiting on WRITELOG. Only then can you be assured you are solving the correct problem and not just adding expensive hardware that will not fix the root cause.

Source:  http://www.confio.com

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.

Source: http://www.confio.com

Wait Stats – What do they mean!

Running…Runnable…Suspended

Thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called beingRUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed. You can see processes in these states using thesys.dm_exec_requests DMV.

SQL Server keeps track of the time that elapses between leaving the RUNNING state and becoming RUNNING again (called the “wait time”) and the time spent on the RUNNABLE queue (called the “signal wait time” – i.e. how long does the thread need to wait for the CPU after being signaled that its resource is available). We need to work out the time spent waiting on the SUSPENDED list (called the “resource wait time”) by subtracting the signal wait time from the overall wait time.

What are the wait stats on my system?

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

This will show the waits grouped together as a percentage of all waits on the system, in decreasing order. The waits to be concerned about (potentially) are those at the top of the list as this represents the majority of where SQL Server is spending it’s time waiting. You can see that a bunch of waits are being filtered out of consideration – as I said above, waits happen all the time and these are the benign ones we can usually ignore.

You can also reset the aggregated statistics using this code:

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

Full post can be found Here on Paul Randal’s Blog

 

 

Compatibility in SQL Server

Compatibility Levels

COMPATIBILITY_LEVEL is a database attribute; while creating the database you can change the compatibility level, by default it is set to 100 in SQL Server 2008.

Compatibility level affects behaviors only for the specified database, not for the entire server. Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.

Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.

For all installations of SQL Server 2008, the default compatibility level is 100. Databases created in SQL Server 2008 are set to this level unless the model database has a lower compatibility level. When a database is upgraded to SQL Server 2008 from any earlier version of SQL Server, the database retains its existing compatibility level if it is at least 80. Upgrading a database with a compatibility level below 80 sets the database to compatibility level 80.

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results.

Check Compatibility

There are two ways to check current compatibility Level of your database, you can use below script that will return the current compatibility Level.

First method:

sp_dbcmptlevel 'DatabaseName'

The output will be
The current compatibility level is 100.

Second Method:

Second way to check compatibility level is

use DatabaseName
select compatibility_level from sys.databases where name=db_name()

Or For all databases:

select name, compatibility_level from sys.databases
order by name

Changing the Compatibility

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. For example, if the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore, the problem may be compounded if the plan is placed in the plan cache and reused for subsequent queries. To avoid inaccurate query results, it is recommended to follow below steps to change the compatibility level of a database:

  1. Set the database to single-user access mode by using ALTER DATABASE DatabaseName SETSINGLE_USER
  2. Change the compatibility level of the database.
  3. Put the database in multiuser access mode by using ALTER DATABASE DatabaseName SETMULTI_USER

If you want to change your database Compatibility level, you can use stored proceduresp_dbcmptlevel for setting the database compatibility level. You can use below script for changing the Compatibility Level.

EXEC sp_dbcmptlevel @dbname = 'YourDatabaseName', @new_cmptlevel = 100;

@new_cmptlevel is the version of SQL Server with which the database is to be made compatible. Version is tinyint, with a default of NULL. The value must be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

In SQL Server 2008 you can user Alter Database script to change Database Compatibility Level, below is the script for that.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 60 |65 | 70 | 80 | 90 | 100 }

 

SQL Server ‘Denali’ AKA SQL Server 2011

At the PASS Summit 2010 the next version of SQL Server, code-named Denali, was announced and the CTP made available. Denali is also known as SQL Server 11.

Slightly annoyingly, the CTP version cannot be installed on Windows XP which is a shame as I cant see my company moving us onto vista any time soon. Server Installation requirements look to be Windows Server 2008 onwards.

A couple of the features I am looking forward to seeing:

SQL Server AlwaysOn

Denali’s AlwaysOn includes features to help simplify high-availability environments and maximize hardware investments. One of these features is support for multi-site clustering, also referred to as multi-subnet clustering. Windows Server Core support is also included. It attempts to reduce SQL Server downtime by eliminating 50 to 60% of the reboots required by OS patching.

Apollo

Apollo is the codename for Column-based Query Accelerator. According to Microsoft, performance gain is realized on very large result set queries. with claims of an approximate 10 times increase in query performance.

Columnstore Indexes are what is set up to provide for this increase in query performance. Essentially, instead of storing rows on pages, columns are stored individually on a set of pages. Algorithms can then be used to compress the redundancy out of the column. Some benefits:

  • only the columns needed to solve a query are fetched from disk (this is often fewer than 15% of the columns in a typical fact table),
  • it’s easier to compress the data due to the redundancy of data within a column, and
  • buffer hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, while infrequently used parts are paged out.

Contained Databases

Contained Databases is a concept where databases are not tied to the instance they reside on. Everything about a database is contained in that database without today’s coupling to the database engine (SQL Server instance) it’s running under.

Specifically, users are no longer tied to logins on the instance. This should save a lot of time, no longer needing to fix orphaned users in development, test, and stage when we pull down production databases to our non-production environments!!!

Introduction to Sequence : SQL Server finally has the concept of a “sequence” that Oracle has had for years!

New Role Management : Now we can create User defined Server level Roles

Full enhancement list can be found Here