Just another WordPress.com site

SQL Server Performance issue – wait types

Wait types

Any time a user connection is waiting, SQL Server accumulates wait time. For example, the application requests resources such as I/O, locks, or memory and can wait for the resource to be available. This wait information is summarized and categorized across all connections so that a performance profile can be obtained for a given workload. Thus, SQL wait types identify and categorize user (or thread) waits from an application workload or user perspective.

This query lists the top 10 waits in SQL Server. These waits are cumulative but you can reset them using DBCC SQLPERF ([sys.dm_os_wait_stats], clear).

select top 10 *

from sys.dm_os_wait_stats

order by wait_time_ms desc

Find Wait types in SQL server 2005/2008/2008R2

Execute the following query on SQL server 2005/2008/2008R2 instances and it isolates top waits for server instance since last restart or 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’))

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; — percentage threshold

The output as follows:

wait_type wait_time_s pct running_pct
CXPACKET 1058910.03 46.68 46.68
LATCH_EX 292771.37 12.91 59.58
BACKUPIO 166931.21 7.36 66.94
BROKER_EVENTHANDLER 162584.05 7.17 74.11
BACKUPBUFFER 151452.95 6.68 80.78
ASYNC_IO_COMPLETION 147510.80 6.50 87.29
EXECSYNC 73170.13 3.23 90.51
BROKER_RECEIVE_WAITFOR 70503.44 3.11 93.62
LCK_M_U 29658.90 1.31 94.93
SOS_SCHEDULER_YIELD 24253.27 1.07 96.00

CXPACKET

CXPACKET waits accounting for more than 5% of total relevant resource waits indicate a query parallelism bottleneck.

The SQL Server configuration option max degree of parallelism determines the maximum number of processors which can participate in query execution.  The default value is 0, allowing SQL Server to utilize all available cores at query runtime to contribute in parallel to query processing.

By dividing operations such as range scans into smaller chunks, and amalgamating the results, parallelism can provide performance benefits for queries used for, e.g., for reporting, DSS, & warehouse systems.

Yet, generally speaking, the aforementioned default value, 0, is not optimal for OLTP systems. So we need to concentrate on max degree of parallelism on this SQL server instance and avoid CXPACKET wait types.

LATCH

The latch waits types of mostly related with IO of the disk and SQL data or log file operation. We beed ti Analyze the disk utilization and database files growth level, shrinking of log files etc. and find out the issue and try to fix it.

BACKUPIO

Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data and This type is not typical, except when a task is waiting for a tape mount.

Refer the following links for more info about wait types in SQL server:

http://msdn.microsoft.com/en-us/library/ms179984.aspx

http://technet.microsoft.com/en-us/library/cc917690.aspx

The below query is useful to find out SQL server up time in days hours and minutes format.

 

Declare @theMinutes int

Set @theMinutes = (SELECT TOP 1 DATEDIFF(mi,login_time, GETDATE()) AS TotalUpTimeInMinutes
FROM master..sysprocesses (NOLOCK)
ORDER BY login_time)
Select @@SERVERNAME AS ServerName, convert(varchar(15), @theMinutes / 1440 )

+ ‘ Days, ‘ + REPLICATE(‘0’, 2 – DATALENGTH(convert(varchar(2), (@theMinutes % 1440) / 60 ))) + convert(varchar(2), (@theMinutes % 1440) / 60 )

+ ‘ Hours, ‘ + REPLICATE(‘0’, 2 – DATALENGTH(convert(varchar(2), (@theMinutes % 60)))) + convert(varchar(2), (@theMinutes % 60))

+ ‘ Minutes’ AS SQLUptime_Days_Hours_Minutes

 

The output is like below:

 

ServerName         SQLUptime_Days_Hours_Minutes
XXXXX                    0 Days, 22 Hours, 35 Minutes

 

 

 

SQL query:

— Calculates average stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);

Recover Suspect DB

Problem:

When a database went to suspect mode due to insufficient disk space or log file corruption (SQL 2005 and above)

Solution:

1. Put the suspected database into emergency mode using the below commands:

 alter database ‘database name’ set emeregency

2. Now the database in emergency (pink coloured) mode. Detach the database before that found out the mdf and ldf file location in the disks.

3. Attach the database ( if any disk space issue, move mdf or ldf file to another location)

4. If the log file corrupted, regenerate the log file . You can attach the database without ldf file, by default the sql engine will generate the ldf file

5. Or use force attach (undocumented command) for attaching the database ldf file.

Now the database come to online and take a backup or run dbcc checkdb command to verify.

We cannot directly install SQL 2008 RTM setup on Windows server 2008 R2. Microsoft accepted that there is a bug on this installation and particularly in Windows Cluster environment. First we should install Sql server 2008 service pack 1 through slipstream method then only we can install SQL 2008 on windows 2008 R2 failover cluster.

 PFB link for your reference about the same

http://www.mssqltips.com/sqlservertip/1902/slipstream-service-packs-and-cumulative-updates-on-a-sql-server-2008-installation/

HADR on SQL 2010 (Denali)

SQL 2010 (Denali) has given a feature called as HADR which is very well suits for our environment in high availability & DR Scenarios.

Microsoft made a number of improvements on this new release “Denali“. These are some of the key improvements

Manageability Enhancements

• Contained Databases: Users can connect to the database without authenticating a login at the Database Engine level. Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. [More Information][Video Tutorial]
• Availability Groups (HADR) : SQL Server Denali (2011) has introduced a new feature named “Availability Group”. “HADR” is a high-availability and disaster recovery solution introduced in SQL Server 2011 Named “Denali“. Deploying “HADR” involves configuring one or more availability groups. [More Information]
• Changed Startup Options : Changing SQL Services Startup Parameters is very easy in SQL Server Denali, No need to remember syntax and where to place (;) [More Information]
• TSQL Enhancements : The Database Engine Query Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense [More Information]

What is HADR ?

Built on database mirroring functionality from SQL Server 2005, HADR allows you to group databases together into availability groups, and configure automatic failover to up to 4 failover partners. Each failover group enables the constituent user databases to failover as a single unit. In addition, you can use the replicas for read-only access to take reporting load away from the production databases, and you can even use them for production backups, making maintenance window management significantly easier.

Please go through the below link to see about SQL server (Denali) HADR in detail.

http://channel9.msdn.com/Events/TechEd/Europe/2010/DAT303

sys.dm_os_schedulers is a useful DMV to find out the CPU pressure. It returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor.

We can use this DMV to monitor the condition of a scheduler or to identify runaway tasks. It will help us to identify if there is any CPU bottleneck in the SQL Server machine.

We have to check for “runnable_tasks_count” column value which indicates the count of workers that have tasks assigned to them that are just waiting for their share of time on the scheduler (logical CPU). The value of runnable_tasks_count should be as low as possible.

Permission
Requires VIEW SERVER STATE permission on the server
SELECT scheduler_id,
status,
cpu_id,
is_online,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count,
load_factor
FROM sys.dm_os_schedulers
GO

 
–If you find the the avg(runnable_tasks_count) greater then 0 that means system is waiting for CPU time. If  –Pending_disk_io_count is greater then 0, that means system is bound by IO you need to get disks to perform better.

Hello world!

Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.

Here are some suggestions for your first post.

  1. You can find new ideas for what to blog about by reading the Daily Post.
  2. Add PressThis to your browser. It creates a new blog post for you about any interesting  page you read on the web.
  3. Make some changes to this page, and then hit preview on the right. You can alway preview any post or edit you before you share it to the world.