Find the Root cause of a SQL Server Performance downgrade

Finding the root cause of a SQL Server performance downgrade requires a wide understanding of infrastructure . A common user request is to analyse a sudden performance downgrade. DBAs are very busy – sometimes a quick fix is applied . Although it gets the user off your back, long-term it’s not a great tactic , as the problem normally reappears! There are different reasons for performance downgrade. From network through to user input. Asking the right question can be useful for information gathering . Unless users are experienced in troubleshooting , the requests can be vague , little detail. Spending... Read more →

Troubleshoot high disk activity with STATISTICS IO

SQL Server BOL defines STATISTICS IO as “Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.” When performance troubleshooting and you’ve identified queries executing slower than normal , STATISTICS IO is a useful tool for quick summary of disk activity. It’s particularly useful to identify missing indexes, statistics out of date , and queries which don’t exploit the indexes Here is a sample output, from the query Backup history for a single SQL Server database . This output can assist to target certain tables – verify your analysis with the Execution Plan. Table... Read more →

Measuring SAN Performance with Avg Disk Transfer and disk bytes per second

Question: Using Perfmon counters is it possible to estimate whether it’s the SAN or SQL Server creating the bottleneck? The SAN administrator is reporting no delays on the SAN, and we need to feedback some useful information . He’s indicated the SAN should be able to handle > 160 mb per second. The question is whether the SAN is experiencing issues or the SQL Server is driving to much traffic Answer: The Perfmon counters Avg. disk sec/transfer and disk bytes per second can supply a good indicator . Avg.disk sec/transfer – (from MSDN) “Perfmon captures the total number of individual... Read more →

Troubleshooting SQL Transaction Log performance

Slow performance on SQL Transaction Logs can cause serious bottlenecks if throughput and latency is slow. Identifying the root cause requires some troubleshooting effort. Part of the troubleshooting includes gathering data , to give some clues. These methods assist in gathering some information, which could give some clues for a solution or further investigation Method 1 : Check the file latency within SQL Server using sys.dm_io_virtual_file_stats (db_id,file_id) This script reports on the latency for each file: select db_name(database_id), io_stall_read_ms/num_of_reads AS 'Disk Read Transfer/ms', io_stall_write_ms/num_of_writes AS 'Disk Write Transfer/ms' from sys.dm_io_virtual_file_stats (2,1) SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats Method... Read more →

Calculate sql row size

Question: How can I calculate the sql row size? Answer: Calculating the row-level row size is complicated and challenging. One of the key problems is SQL Server defines space at the extent level . An extent is made up of 8 x 8 KB physically contiguous pages . The complication for calculating a row size is : Even if one row exists – for example 4 bytes , an extent has to be allocated. Read this post for information on the difference between uniform and mixed extents - Disk IO performance , disk block size tuning and SQL Servers There... Read more →