SQL Server Basic Performance Issue Part - 3

3. Investigating I/O bottlenecks

SQL Server is usually a high I/O activity process and in most cases the database is larger than the amount of memory installed on a computer and therefore SQL Server has to pull data from disk to satisfy queries. In addition, since the data in databases is constantly changing these changes need to be written to disk. Another process that can consume a lot of I/O is the TempDB database. The TempDB database is a temporary working area for SQL Server to do such things as sorting and grouping. The TempDB database also resides on disk and therefore depending on how many temporary objects are created this database could be busier than your user databases.
Since I/O is such an important part of SQL Server performance you need to make sure your disk subsystem is not the bottleneck. In the old days this was much easier to do, since most servers had local attached storage. These days most SQL Servers use SAN or NAS storage or to further complicate things more and more SQL Servers are running in a virtualized environment.

There are basically two options that you have to monitor I/O bottlenecks, SQL Server DMVs and Performance Monitor counters.
There are other 3rd party tools as well, but these are two options that will exist in every SQL Server environment.

DMV - sys.dm_io_virtual_file_stats
This DMV will give you cumulative file stats for each database and each database file including both the data and log files. Based on this data you can determine which file is the busiest from a read and/or write perspective.
The output also includes I/O stall information for reads, writes and total. The I/O stall is the total time, in milliseconds, that users waited for I/O to be completed on the file. By looking at the I/O stall information you can see how much time was waiting for I/O to complete and therefore the users were waiting.
The data that is returned from this DMV is cumulative data, which means that each time you restart SQL Server the counters are reset. Since the data is cumulative you can run this once and then run the query again in the future and compare the deltas for the two time periods. If the I/O stalls are high compared to the length of the that time period then you may have an I/O bottleneck.

SELECT 
cast(DB_Name(a.database_id) as varchar) as Database_name,
b.physical_name, * 
FROM  
sys.dm_io_virtual_file_stats(null, null) a 
INNER JOIN sys.master_files b 
ON a.database_id = b.database_id and a.file_id = b.file_id
ORDER BY Database_Name

Performance Monitor
Performance Monitor is a Windows tool that let's you capture statistics about SQL Server, memory usage, I/O usage, etc.
This tool can be run interactively using the GUI or you can set it up to collected information behind the scenes which can be reviewed at a later time. This tool is found in the Control Panel under Administrative tools.
There are several counters related to I/O and they are located under Physical Disk and Logical Disk. The Physical Disk performance object consists of counters that monitor hard or fixed disk drive on a computer. The Logical Disk performance object consists of counters that monitor logical partitions of a hard or fixed disk drives. For the most part, they both contain the same counters. In most cases you will probably use the Physical Disk counters. Here is a partial list of the available counters.
Performance Monitor Physical Disk countersPerformance Monitor Logical Disk counters Now that storage could be either local, SAN, NAS, etc.These two counters are helpful to see if there is a bottleneck:
- Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
- Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.

The recommendation is that the values for both of these counters be less than 20ms. When you capture this data the values will be displayed as 0.000, so a value of 0.050 equals 50ms.

Resource Monitor
Another tool that you can use is the Resource Monitor. This can be launched from Task Manager or from the Control Panel. Below you can see the Disk tab that shows current processes using disk, the active disk files and storage at the logical and physical level. The Response Time (ms) is helpful to see how long it is taking to service the I/O request.

SQL Server Basic Performance Issue Part - 2

2. Tracing a SQL Server Deadlock
A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other's process to complete before moving forward. When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can finish the work.
The error message that SQL Server sends back to the client is similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace.

Trace Flags
If you want to capture this information in the SQL Server Error Log you need to enable one or both of these trace flags.
-- 1204 : this provides information about the nodes involved in the deadlock
-- 1222 : returns deadlock information in an XML format
To turn these on you can issue the following commands in a query window or you can add these as startup parameters. If these are turned on from a query window, the next time SQL Server starts these trace flags will not be active, so if you always want to capture this data the startup parameters is the best option.
DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

Profiler / Server Side Trace
Profiler works without the trace flags being turned on and there are three events that can be captured for deadlocks. Each of these events is in the Locks event class.
-- Deadlock graph - Occurs simultaneously with the Lock:Deadlock event class. The Deadlock Graph event class provides an XML description of the deadlock.
-- Lock: Deadlock - Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns.
-- Lock: Deadlock Chain - Is produced for each of the events leading up to the deadlock.
You can use the following query to find the object, substituting the object ID for the partition_id below.

SELECT OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = [Object_id];