SQL Server - How to check Disk Latency

Question: I have a SQL Server 2008 that has data , log and tempdb files placed in separate RAID 10 disks on SAN. In one data drive, which has a single database file (200 GB) there is relatively high avg. disk sec/ read (ranging 100 ms - 900 ms). The SAN system is optimized for high read rates , according to the storage guys Do you have any advice on how reduce the avg. disk sec/read for this drive?. Answer: I’m assuming you’re referring to the Physical Disk Counter. Therefore the avg. disk sec/read stat is captured at the Partition... Read more →

SQL Server – Check Data and Log files are on separate drives

A common requirement on SQL Server environments is to maintain the SQL Server database data files and log files on separate drives – mapped to different IO channels. There is much evidence and discussion on this performance issue. The DBA needs to ensure this policy is maintained. In a busy environment this can be challenging where there are many different DBAs and third party vendor products. An easy way to manage this process is by including the SQL code below into your nightly systems batch runs. The script checks to see if a data and log file from a database... Read more →

How to interpret SQLIO results

How to use SQLIO results for performance testing Read more →

SQL Server Latency

Latency is the time delay measure in the system. SQL Server can only be as fast as the slowest component in the system. Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads. Latency is about benchmarking certain limitations , such as disk speed , and acknowledging that relative to the speed of light – delays occur. Use Production data or SQL Server test data generation testing tools to benchmark systems. Database server latency is also about finding the acceptable level of delay.... Read more →

SQL Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk. The purpose of asynchronous IO is to release resources and for other tasks to progress. i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur. When this occurs , you’ll see an error message IO Requests taking longer than 15 seconds to complete IO writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server... Read more →