Author Archives: dba60k

SQLSaturday Holland. Moving to Extended Events

On the 26th of September I had a session on SQL Saturday Holland on Extended events. This is a session for beginners explaining architecture, GUI and also the SSMS 2008 Addin (on codeplex) for Extended events.

Link to presentation

Link to demo’s

Extended events manager

Downloads for the other SQLSaturday sessions can be found on:


Performance analysis: different ways to investigate the query plan cache

When monitoring your SQL instance for performance the most convenient way to start is by analyzing the query plan cache using DMV’s. The plan cache contains all query plans used on the instance since the last restart. A key benefit of using this method is that an explicit trace (using SQL Profiler or preferably Extended events) is not needed. Be aware however that the amount of query plan information is limited based on the uptime of the SQL instance.

In this article I will show different methods to investigate the query plan cache. This article will use the AdventureWorks2014 database as an example, but the queries are compatible with SQL 2005 and up.

Some technical background about the DMV’s

The following DMV’s should be used to get information from the plan cache.

  • sys.dm_exec_query_stats (contains cumulative information of the query plan cache)
  • sys.dm_exec_sql_text (DMF to retrieve SQL text based on the sql_handle)
  • sys.dm_exec_query_plan (DMF to retrieve the query plan based on the plan_handle)

The following columns from these DMV’s contain the most interesting information:

  • sys.dm_exec_query_stats
    • execution_count (number of queries executed with this plan)
    • total_logical_reads and last_logical_reads (amount of logical page reads)
    • total_worker_time and last_worker_time (amount of CPU time)
    • total_elapsed_time and last_elapsed_time (duration statistics)
  • sys.dm_exec_sql_text
    • text (SQL statement text. To be used in conjuction with statement_start_offset and, statement_end_offset)
    • statement_start_offset
    • statement_end_offset
  • sys.dm_exec_query_plan
    • query_plan (XML query plan

Cumulative statistics columns: total_* and last_*

Be aware that the dm_exec_query_stats DMV contains mainly cumulative statistics. For SQL Server a query plan which can be reused is most efficient. This means that a compilation of the query and storing the query plan in memory is not needed.  You will see in the execution_count column if a query plan is reused. If you notice the execution_count = 1 this could be an indication of the use of unparameterized adhoc queries which could lead to bloating the plan cache and consuming lots of memory. You could rewrite the queries and parameterize them or use the “Optimize for ad hoc workloads” option to mitigate this issue.

The columns starting with total just add up each time the query plan is reused. SQL will track the execution statistics each time a query is executed using the specific plan and just add’s the totals up. Conclusions on the values in these columns should be handled with care. A small query executed a lot of times will have typically have large total values, but this doesn’t mean it’s a heavy query. It’s more useful to compute an average by making a division like total_logical_reads/ execution_count AS avg_logical_reads.

The columns starting with last contain only information on the last query execution. These values can be compared to the averages to check if some queries suffer from “parameter sniffing” problems. In this case the query plan could be suboptimal for specific parameter values.

DMV Query example on AdventureWorks2014

The following query is just a sample of getting TOP 10 resource consuming query plans. You can modify this script to your own needs. It’s important to do a correct interpretation of the data. This means checking execution_count to see if the query executes frequently and checking if the last execution is representative to the average cumulative statistics of the plan. The ORDER BY is based on the last_logical_reads column, but it could be interesting to execute this query multiple times ordering on the total statistics as well.

Returns aggregate performance statistics for cached query plans in SQL Server
List top 10 most expensive queries

SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.last_logical_reads DESC -- logical reads
-- ORDER BY qs.last_logical_writes DESC -- logical writes
-- ORDER BY qs.last_worker_time DESC -- CPU time

Here is an example of an expensive query on AdventureWorks2014

SELECT [SalesOrderID]
FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]
where [UnitPrice] > 3000

This query returns 1947 rows out of 121.317 rows and could be considered selective. Due to a missing index the query plan however is less efficient.

IO statistics: Table ‘SalesOrderDetail’. Scan count 5, logical reads 1371, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


You can find the statistics of this plan by investigating the query stats. Based on the TOP 10 DMV query above I can easily identify this query based on the query text or I could check on the last_logical_reads of 1371.


The advantage of using the first DMV query is the presence of the qp.query_plan column.


You can actually click on the XML link to open the query plan which will show the same query plan with the missing index hint and Clustered Index scan. It’s actually possible to search in these XML fragments for these hints. An example is this article written by Dennes Torres on Simple-Talk.

Reports in Management Studio

SQL management studio provides some reports to get the same type of information. Just right click on the instance, reports and click on one of the desired Performance reports. In this case I choose to order by Average IO.




You can see they query is here too, but the plan is missing. It is possible do drill down  by clicking on the [+] icon in the Query Text column. However this just shows some of the performance statistics columns but not the real query plan.


The most powerful way to do some historical performance monitoring is to query the DMV’s using your customized ordering and filtering. The GUI functionality in SSMS is a quick way for revealing some of the information, but misses some important information.

Start thinking about data archiving early

Dealing with data growth is a big challenge for DBA’s and sysadmins. The main problems to deal with are  storage capacity (server storage is not cheap!), backups / index maintenance windows and query performance. Implementing data retention helps dealing with these issues and I often make a checklist:

  • What kind of data are we dealing with?
  • What is the expected data growth?
  • Which data can be deleted and which data must be kept?
  • What retention time to use? How many days, months, years must the data be available? (What are the requirements from a business perspective? Do we have to deal with government regulations for retaining data for a certain amount of time?)
  • Should we offload older data to another (cheaper) storage system?
  • Is it allowed to aggregate older data and lose some of the detailed information?
  • Should we use SQL Server Enterprise partitioning for easier data management?
  • Should we use Enterprise compression features?

A lot of these decisions do impact the business.  A customer might not see all his historical data. Big organisations like banks in the Netherlands for example only store the last 2 years of your banking account transaction history.

In the development process of an application these questions often remain  unanswered. Then problems start to arise in the production environment. Things get bad if the contracts fail to mention anything about retention. A customer might not agree to archive his data.

Developers often don’t have a focus on archiving  because  it’s not a critical issue in the development process. I used to work as a developer and made the same mistakes ignoring the archiving needs for certain data. That’s why DBA’s and sysadmin’s always need to be involved in the development process. Also business people need to provide as much information as possible about the expected number of customers and transactions so DBA’s can do forecasting and size storage and servers.

So to prevent problems in production it’s a good idea to start thinking about data archiving as early as possible.

SSRS 2012 mails missing when using data driven subscriptions and bulk mail

Data driven subscriptions (DDS) are a powerful mechanism which can be used to generate mailings. When migrating from SSRS 2008 to 2012 I noticed mails were missing with a DDS containing about 2.000 mails.

This problem occurred in SP1 and Microsoft’s advice was to download the hotfix pack.

After some serious testing the issue seems to be a SMTP server issue.

emailextension!WindowsService_0!1ce0!12/05/2013-15:39:03:: e ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. —> System.IO.IOException: Unable to write data to the transport connection: An established connection was aborted by the software in your host machine. —> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
— End of inner exception stack trace —
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Base64Stream.FlushInternal()
at System.Net.Base64Stream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.MimePart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mail.SmtpClient.Send(MailMessage message)
— End of inner exception stack trace —
at System.Net.Mail.SmtpClient.Send(MailMessage message)

The solution was to get rid of the limits “session size” and “messages per connection”


Why the issue didn’t occur in SSRS 2008 using the same SMTP server puzzled me. Maybe SSRS 2012 speeds up the processing using multiple threads and overloads the mail server. However I am glad that the update and the new SMTP settings solved my problem.

Which DTEXEC version is used when running multiple SSIS versions on 1 server?

Today I faced some problems running SSIS 2008 packages on a server where SSIS 2008 and 2012 was installed.
The SSIS 2008 binaries are placed in the 100\DTS\Binn folder and the 2012 binaries in the 110\DTS\Binn folder.

When clicking a DTSX file it is hard to see which DTEXEC version will be used.
When running dtexec.exe from the commandline you can check which version is actually used by default.

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Version 10.5 is the SSIS 2008-R2 version. 11.0 is SSIS 2012.

Solution for running the SSIS 2008 version by default
Remove  the PATH environment variable contaning the following string: \110\DTS\Binn\
Be sure the PATH variable contains the 2008 SSIS binary path : C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

Of course you can do this trick the other way around if you want to use SSIS 2012 by default.

Troubleshoot login errors with SQL Server login failed states

Whenever you receive a Login failed error from a SQL instance your faced with the problem  of context. In the SQL Server log you can find a more detailed description. You should see the client generating the error and the state. The table below helps to find the cause of the failed login attempt.

Error 18456, Severity 14: Login Failed States 
Login Failed states summary
State Description
1 Only state returned in SQL 2000. Server is in Single-User Mode (2005).
2 and 5 Invalid User Id
6 Attempt to use a windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid Password
10 Read Ref Link #2
11 and 12 Valid login but server access failure; Default Database access failure. Or Initial database connection failure in connection string.
13 SQL Server service paused
16 User doesn’t have permission to target database
18 Change password required
23 Server in process of shutting down, and user attempted to login.
27 Initial database could not be determined for session
38 Initial database could not be determined for session (SQL 2008)
40 Default database could not be accessed (SQL 2008)

Checking the progress of a backup or restore

With SQL Server’s powerful DMV’s it’s easy to monitor the progress of a SQL Backup or restore operation, but you can also use it to see the current running queries on your SQL instance. In this case you should delete the last WHERE command in the query. Note that the percent_complete column is not updated for all T-SQL commands.
Continue reading

Welcome to my SQL Server blog

On this blog I am planning to write especially SQL Server articles . I started the blog to share my knowledge on several SQL Server subjects. I hope you will find the articles useful and will provide me with feedback. We have a big SQL Server community around the world and sharing the knowledge will benefit all of us.