Monday, February 9, 2015

Optimizing SQL Server I/O

In I/O terms SQL Server should be running on a platform that allows it to achieve a single figure latency (ms - millisecond) from the underlying storage.

Host Considerations

Out of scope for this post are hardware considerations, but I shall list those items as things to check or questions to pose for your IT team
What are the speeds and throughput (IOPs) of the drives in question?
If physical drives, how are they configured? (remembering the write performance overhead of a RAID 5 configuration).
If a SAN is involved, what tier storage are we on? Is it guaranteed?
Are any processes in place that move volumes across storage?
On a virtual environment, are the drives Thick or Thin provisioned?

Windows Considerations

Ensure the following 3 items are configured for Windows

  1. Configure the Service Account you will run SQL from with the "Perform Volume Maintenance Tasks" Group Policy. (See SQL Service Account Settings Summary)
  2. Ensure drives dedicated for SQL Data, Log and Backup files are formatted to a cluster size of 64K.
  3. If using an OS earlier than Windows 2008, ensure drives are aligned correctly. (See Disk Alignment for SQL Server)
Basically, before you even install SQL Server measure the capability of the host you've been provided with. The SQLIO tool can help you measure this.

File Placement

The access pattern for reading and writing Data is somewhat random in OLTP systems. Logs are written to sequentially however. Separating their files can be beneficial for performance. Separating the backups of data and logs from the data and logs themselves is common sense to prevent data loss.

Therefore, ensure separate drives for

  • SQL Data
  • SQL Logs
  • SQL Backups (Data & Transaction Log backup folders)

Ensure system databases are installed in those locations too (not just the default c: drive location)


In large environents it may be wise to situate tempdb on it's own drive.
To further optimize tempdb access, splitting it into equal files (start with 1 per core) is ideal to allieviate pressure on file access.

File Configuration

Managing growth is a DBA task and balancing between presizing databases and using auto growth settings is necessary.

Presizing datafiles

Manually growing data files so data grows into them is good practice.
It prevents file system fragmentation and I/O delays when autogrowth occurs for a data file.
It may confuse windows systems administrators however who will see a drive as being full and percieve that a system is low on space.

Autogrowth Settings

If using Autogrowth Settings (even as a backup) then a balance needs to be achieved.
A fixed size growth (e.g. the default of 1MB) will generate file system fragmentation. If several databases configured in this way are also expanding, a drive could become very inefficient.

Using percentage growth settings is initially fine, but a 150GB database that suddenly has to expand by 10% will stop everything as it tries to allocate the next 15GB.


Just say NO. If you find a database with this enabled, turn it OFF!
SQL server will end up repeatedly growing the database by the autogrowth settings when new data arrives, followed by shrinking it again.

VLDBs - Very Large Databases

If you are lucky enough to be the developer and DBA, then you can take advantage of filegroups. Filegroups enable objects within a database to be stored separately. Different filegroups could be placed on separate drives. This could be for capacity or performance reasons.

Filegroups can be used -

  • to separate heavily used tables
  • to separate data from indexes
  • to enable filegroup backups and implement a custom backup schedule on different types of data
  • for data Partitioning should you be using a capable SQL edition *

* Current data could be on the more expensive faster storage, whereas archive queries which aren't run as frequently could come from another source.

Thursday, January 29, 2015

SQL Server - Admin locked out ?

How to add a sql administrator if you find yourself locked out.
(replace the server name and service names with your own!)

1. Stop the SQL Service -

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>net stop mssql$sql2012
The following services are dependent on the SQL Server (SQL2012) service.
Stopping the SQL Server (SQL2012) service will also stop these services.

   SQL Server Agent (SQL2012)

Do you want to continue this operation? (Y/N) [N]: Y
The SQL Server Agent (SQL2012) service is stopping.
The SQL Server Agent (SQL2012) service was stopped successfully.

The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.

2. Start the SQL Service in single user mode -

C:\Users\Administrator>net start mssql$sql2012 /m
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.

3. Add a user -

C:\Users\Administrator>osql -E -Sxeros\sql2012
2> GO
1> ALTER SERVER ROLE sysadmin ADD MEMBER mylogin;
2> GO

4. Stop the Sql service (you'ved finished with single user mode)

C:\Users\Administrator>net stop mssql$sql2012
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.

5. Start the service fully again

C:\Users\Administrator>net start mssql$sql2012
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.

Thursday, January 15, 2015

Auditing SQL Server - The Basics

I've audited a lot of SQL Servers in my current role and have developed templates and scripts to assist me. So what do I check?

Audit Summary
  • Date & Time
  • Auditor (usually me!)
  • Location
  • Server name
  • IP Address
  • Physical or Virtual ?
  • Virtual Platform
  • Clustered ?
  • Processor Type
  • Clockspeed
  • 32/64 bit
  • OS Version
  • OS Edition
  • Service Pack
Questions this raises -
   Is the server patched? 
   Is the OS and Service Pack supported? 
   Is the edition appropriate? *  
   * Ever found a 32bit OS installed on a 64bit server? I have...
   Is the SQL edition appropriate i.e are we using it's features?


  • Physical Size
  • Swap file Configuration - Location, Min Size, Max Size, Recommended & Current Size *
* Ideally not in use at all on a live server.


For Each NIC -
  • Use
  • IP Address Subnet
  • Max Speed
  • Full Duplex? (Occasionally old servers prevail)

Other Settings
  • Power Saving - Yes, I've found this incorrectly configured on live servers!
  • Antivirus - Are SQL file types and executables excluded from scanning activity?
  • Are any Roles installed?
  • Are any Features installed?
  • Other Applications - Does SQL have to share the server?
  • Are any scheduled tasks present?
System Settings

  • No errors / misidentified hardware in device manager?
  • Is the server optimised for background services?

SQL Configuration


  • Instance Name
  • SQL Version
  • SQL Edition
  • SQL Service Pack
  • SQL Build
  • Collation

Service Account

Processor Cores

  • How many are there?
  • How many are configured for SQL?
  • Cost Threshold of Parallelism?
  • Max degree of Parallelism
  • Affinity
  • Affinity I/O

Memory Configuration

  • Physical Available to Server
  • SQL Allocation - Dynamic / Fixed
  • SQL Minimum Memory
  • SQL Maximum Memory
  • Which protocols are enabled?
  • Are they used / needed?
  • What is the binding order?

SQL High Availability 

Are these present ? Are they monitored?
  • Clustering
  • Mirroring
  • Replication
  • Log Shipping

Review Logs -

  • SQL Server Logs
  • SQL Agent Logs
  • Windows Event Logs

Drive Configuration , Look for -

  • Alignment (if pre Windows 2008)
  • Cluster Size
  • NTFS Compression
Drive Capacity
  • Disk Total
  • Disk Free
  • Data Allocated
  • Data Used 
  • Data Free
  • Logs Allocated
  • Logs Used
  • Logs Free

File Locations
  • Data
  • Logs
Data & Logs should be on different volumes for performance.

Database Configuration
  • Database Compatibility Mode - Does it match the server? Should it?
  • Data & Log Allocated
  • Data & Log Used
  • Data & Log Free
  • Data & Log Growth intervals
  • Autoshrink Enabled?
Database Maintenance

  • Last Successful Full Backup 
  • Last Successful Log Backup 
  • Backup Locations
Look in the backup path to ensure they are present.
Are they archived away from the server too?
Data, Logs & their respective backups should be on different volumes for safety.

Other Maintenance 

How often are these tasks performed?
  • Index Maintenance
  • Integrity Checks
  • Manage History tables
What period do these tables cover?
  • Backup History
  • Agent History
  • Maintenance Plan History
Don't allow them to get unnecessarily large.