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. Yes, the IO request may not be 3 ms – but 6 ms may be acceptable to the users.

In a large database server ecosystem – multiple requirements are considered. Balancing budgets, business requirements  and system management  are factors in finding the acceptable level of latency.

SQL Server since 2005 has offered some useful methods of starting the analysis.

Use these two DMVs to develop a quick overview of where some delays are occurring. Use the information to focus on bottlenecks. There are other ways of measuring Disk IO performance such as  SQLIO

sys.dm_io_pending_io_requests  - View pending I/O request(s) in SQL Server.

sys.dm_io_virtual_file_stats  Returns I/O statistics for Database  data and log files. Measure IO transaction rates and sys.dm_io_virtual_file_stats

Author: Jack Vamvas (http://www.sqlserver-dba.com)

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 memory region

When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.

Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging

The write-ahead logging method ensure the transaction log is written to disk  and is fundamental to the Recovery Manager.

How is the dirty page written to disk?

Eager writing   - Nonlogged bcp,  SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.

Checkpoint  writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.

Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

a)       net stop mssqlserver

b)       SHUTDOWN

c)       ALTER DATABASE adding a file

 

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Format Disk for SQL Server

Managing large SQL Server inventories requires standardisation. Do your research , create the scripts , and apply.

Occasionally, custom changes are required . This should be after careful testing.

One advantage of standardising is  for  Disaster Recovery. The rebuild is in the scripts.

If full server rebuilds are required – disk format for SQL Server performance is one area to focus on. Not formatting the disks in an optimal way – can lead to negative feedback from end users.

Disk format details is one of those areas that can easily be overlooked when diagnosing a SQL Server performance problem.

This is an example of  a procedure for  adding and formatting a new drive , in Windows 2003 32 bit  for a base build for SQL Server 2005 .  

Once you’ve standardised the approach , create a document or script  and add to script repository

 

C:\Users\Administrator>diskpart 
Microsoft DiskPart version 5.2.3790.3959
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: Server1

DISKPART> 

DISKPART> list disk


Disk ###  Status      Size     Free     Dyn  Gpt
--------  ----------  -------  -------  ---  ---
Disk 0    Online        67 GB      0 B
Disk 1    Online        20 GB      0 B
Disk 2    Online      1020 MB      0 B
Disk 3    Online      1020 MB      0 B
Disk 4    Online      1020 MB      0 B
Disk 5    Online       200 GB      0 B
Disk 6    Online       100 GB      0 B
Disk 7    Online        50 GB      0 B
Disk 8    Online       200 GB      0 B


DISKPART>select disk 5 

Disk 5 is now the selected disk.

DISKPART>list partition

Partition ###  Type              Size     Offset
-------------  ----------------  -------  -------
Partition 1    Primary            200 GB    32 KB



--To find the file allocation unit size 
fsutil fsinfo ntfsinfo i:

---Currently
NTFS Volume Serial Number :       0x1828b55d28b53a98
Version :                         3.1
Number Sectors :                  0x00000000063fbbbe
Total Clusters :                  0x0000000000c7f777
Free Clusters  :                  0x000000000061e7b2
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000000000c000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x000000000063fbbb
Mft Zone Start :                  0x00000000000c0000
Mft Zone End   :                  0x000000000024ff00


--Changing to 


NTFS Volume Serial Number :       0xcc82786d82785dc0
Version :                         3.1
Number Sectors :                  0x0000000018ffeabc
Total Clusters :                  0x000000000031ffd5
Free Clusters  :                  0x0000000000181bc7
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000050000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x00000000000c7ff5
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x0000000000070000

I deleted the partition and recreated the partition with the following steps. As this reformats the drive , consider moving all data onto another drive first

 

diskpart
   rescan
   list disk (figure out the new disk #)
   select disk X
   create partition primary align=1024
   select partition 1
   assign letter=I:
 
format /FS:NTFS /Q /A:64K I:\

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Storage Checklist FAQ

In a new data centre build, storage system or new equipment, the DBA should have an input into the architecture and configuration. 

This is an FAQ on Storage. Typical questions I might ask the storage team, or the storage team may ask me .

 Do you have  documentation from the SAN vendor?

Ask for SAN documentation and SQL Server performance guidelines.

Look for the recommendations the SAN vendor will outline for OLTP \ DSS . Documentation will normally include information about file locations and other configurables.

What if the SAN administrator wants to do things differently from the documentation?

Ask SAN administrator to justify why they are doing things differently.  Why are they using a particular stripe size? Why are they using a particular RAID level?

Do a  cost versus benefit analysis .  Often there isn’t an open cheque book. This means compromises are required. Produce explanations for every decision

 What RAID level should the DBA recommend?

Check post on RAID levels - SQL Server – RAID levels overview

RAID10 is generally faster . It is also more expensive. Analyse carefully the workload requirements.  Define the IOPS requirements per application. How does this compare with the capacity?

Consider defining different drives with different RAID levels

Weigh up costs versus performance versus storage space required. Produce a document outlining the relationships between performance, storage space and cost.

How does this fit into the budget?

If a RAID level compromise needs to occur , which IO activity types should be compromised and in what order?

Explain carefully the consequences to users.

1)  Tempdb data files .  

2)  Tempdb log files 

3)  User database files 

4)  User database log files

What Cache size and cache settings should be used?

Create a write activity IO profile of the database servers. Define the optimal speeds . Work with the storage guy to find the optimal configuration

Should database server drives use Dedicated Arrays or Shared Arrays?

Normally, I would say Dedicated Arrays. But based on experience – this is not always the best advice. You need to ask some hard questions , such as:

Is the SAN team skilled to deal with Dedicated Arrays ?

Can they monitor the SAN performance and present to DBA ?  

Are VLDBs used ?

If the answer is no to most of these questions , I’ve found Shared Arrays easier to implement. The Shared Arrays should be architected in a way to allow growth.

 

Should SAN snapshots be used?

Yes. If you have very large drives with large databases, the benefits of SAN snapshots are fantastic. Consider the benefit of copying all the files at a block level and then backing up to tape overnight. Automatically the Production databases are available for longer period over a 24 hr period.

Some vendors offer logical grouping of drives , which allows you to maintain data and log files on separate drives and still snapshot. 

Where should the TempDB be stored?

Always separate LUN if possible.  TempDB doesn’t require replication , consider SSD attached.Tempdb and solid state drives

What stripe size should be used?

SQL Server allocates 1 extent at a time – 64KB. Therefore a stripe size  64KB or bigger is optimal. Sequential is good for datawarehouses. Listen to advice from your vendor , test the workload and come up with a figure.

I was involved recently in a large SAN installation. The decision was made by the SAN expert to size at 256kb striping , on the basis it would assist in reads. He’s reasoning was that the segments are larger therefore much more likely the relevant data would be nearby.

Should Data Compression be used?

Data Compression – read Data compression in SQL Server 2008 .  For data sets that have few changes Data Compression is good. For OLTP it’s slower.  

Be specific as possible , when creating a storage Checklist. Read all the documentation is read . If during performance testing expectations are not met,  speak to the storage expert in your organisation and the vendor.

Analyse the performance stack – before you make a conclusion about storage. If you’re testing a new storage system , many factors influence the performance.

 

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Correctness and stress test

Stress test SQL Server for new hardware

 Download SQLIOSim    from http://download.microsoft.com    and install on server.

When detailing the  file location , use the same as the SQL Server files. SQLIOSim will attempt to simulate the SQL Server database  IO path.

The emphasis is to hammer the system but stay under the threshold.  SQLIOSim has the capacity to throttle the outstanding operations.  It decreases the load and IO completion time is standardised

 Running Average IO duration –

Response time under stress

Date files < 5 ms is optimal

Log file – 0-2 is optimal

Total IO time – if the If the values for the Accumulators are similar for two drives, but one drive has significantly less Total IO Time, then the latter performs much  because it was able to service the same I/O in less time.

 

Number of times IO throttled – lower the better

For performance testing check Disk IO performance and SQLIO

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)