Thursday, 28 May 2015

Investigating a (suddenly) slow running query...

Some Questions / lines of enquiry ...

What else was going on on the server at that time?

  • Is blocking occuring?
  • Is another process monopolising resources (memory, cpu, i/o)

What has changed?

  •  Any code changes
  •  Any configuration change
  •  Any change in data volume (influx of new data)

Is all maintenance happening? (it won't take long to check)

  • Are statistics up to date
  • How is index fragmentation
  • Are backups occurring (remember full backups control log fie growth)
Can the query be run manually?
- Look at Execution plan

Is it a stored procedure?
- Look at Execution plan for parameter differences (parameter sniffing)

Parameter Sniffing

A short reminder, again to get this fresh in my mind....

Parameter Sniffing is when a previously compiled execution plan is reused with different parameter values. This is by design and is generally good as we are saving time by not compiling a new plan. Occasionally though it can lead to suboptimal choices when the procedure is run again, with new parameter values.

If a procedure returns only 1 row with 1 parameter value and 1 million with a different parameter value then the optimal plans for retrieving those rows may be very different.
Here the retrieval of a million rows wold be done using the plan generated for 1 row.

2 ways around parameter sniffing are forcing a recompile (WITH RECOMPILE) in the procedure definition or using the OPTIMIZE FOR query hint.

Simple Talk : Parameter Sniffing
Brent Ozar : Parameter Sniffing
MSDN : Parameter Sniffing & Workarounds

Plan Cache Pollution

They say if you can't explain something easily, you don't understand it well enough. For that reason, I'm revisiting some things I take for granted.

The Plan Cache is where SQL Server stores exection plans.

If queries are not parameterized, the plan cache fills with lots of similar queries of hard coded values.

SELECT mycolumns FROM mytable WHERE myid = 1
SELECT mycolumns FROM mytable WHERE myid = 2
SELECT mycolumns FROM mytable WHERE myid = 3
SELECT mycolumns FROM mytable WHERE myid = 4

If queries are parameterized (or parameterization is forced on at a database level) then 1 entry will be in the cache instead.

SELECT mycolumns FROM mytable WHERE myid = n

Correctly parameterized queries will prevent wasting cpu (generating plans) & memory (storing them). Allowing the cache to fill with simiar plans in this way is Plan Cache Pollution.

You can examine the plan cache with the sys.dm_exec_cached_plans DMO.

Links -
Tamarick Hill : sys.dm_exec_cached_plans
Klaus Aschenbrenner : Plan Cache Pollution

Monday, 9 February 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, 29 January 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, 15 January 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.

Wednesday, 14 January 2015

Identifying Unused Databases

Today we looked at an old server we suspected was no longer used.
It actually had multiple SQL Instances, was poorly patched and was sitting on SQL 2005, but that isn't the point of this post. With zero documentation and a large company, we couldn't be sure this wasn't in use.

Whilst simply looking at the date & time stamps of the .MDF and .LDF files could help in some cases, maintenance jobs had been left running on this server so the smallest change in statistics meant the file dates were recent. Similarly, jobs populating the Reporting Services database were still being run, meaning data old data was being repeatedly loaded.

To evaluate whether it was worth keeping the databases online, I had some investigation to do.

Step 1 : Who is Connected?

The following TSQL summarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)

SELECT  loginame , nt_username, COUNT(*) AS Connections
FROM sys.sysprocesses
WHERE spid > 50 and spid != @@SPID
GROUP BY  loginame , nt_username

Step 2 : Is there any data there?

Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!

 DB_NAME(DB_ID()) AS [Database]
 , AS [Schema]
    , AS [Table]
    , AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id = p.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
WHERE != 'sys'
ORDER BY [Schema], [Table], [Index]

Step 3 : When were tables used?

Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.

SELECT i.[database_id],
    DB_NAME(i.[database_id]) AS [Database]
 , AS [SchemaName]
 , AS [TableName]
 , MAX(i.[last_user_lookup]) AS [last_user_lookup]
 , MAX(i.[last_user_scan]) AS [last_user_scan]
 , MAX(i.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.[database_id] = DB_ID()
  AND <> 'sys'
GROUP BY i.[database_id],,
ORDER BY i.[database_id],,