SQL Server Transparent Data Encryption (TDE) Common Questions

Transparent Data Encryption is a way to encrypt your data in SQL Server. It affects the data and log files of the database. I recently gave a webcast on this topic, and got some great questions about TDE.

Lock that data up tight!

Lock that data up tight!

What versions of SQL Server is it available in?
It is in 2008+. (Let me ask again…why are you still using SQL Server 2005?)

Is TDE only available in Enterprise Edition?
Yes, this is an expensive-edition-only feature.

Can TDE encrypt at the table or column level?
No, it’s all or nothing.

Does TDE provide encryption of backups?
When you enable TDE on a database, the backups will, by nature of the feature, be encrypted. However, it’s not possible to encrypt only the backup files and not the data. Native backup-only encryption is available as of SQL Server 2014 (Enterprise, BI, and Standard editions).

When I turn TDE on, will all the data pages need to be read? Will this take some time?
Yes, and yes. Test it in your development environment first to determine how long it will take to perform this in production.

Does TDE work with…
Failover clustering? Yes – there is only one copy of shared data.
AlwaysOn Availability Groups? Yes, but you have to set it all up with T-SQL.
Database mirroring? Yes – the data will be encrypted on both instances.
Log shipping? Yes – the data is encrypted on both instances.
Replication? Technically…but it’s a pain. The certificate must be installed on all subscribers, and the data is not encrypted as it is distributed.

What other questions do you have?

The Mysteries of Missing Indexes [Video]

SQL Server is trying to help you – when you run a query, you see a missing index request. Before you run that CREATE INDEX script in production, consider a few things. How helpful will the index be? Is it similar to an existing index? Why is it recommending three indexes that are very similar? Jes will solve some common missing index mysteries in this 30-minute webcast.

Is Transparent Data Encryption Right for Your Data? [Video]

Transparent Data Encryption is a method to encrypt your SQL Server data on disk. Using it can present challenges, though. How does it work? How does it interact with other SQL Server features? What does it not encrypt? Join Jes as she explains the ins and outs of TDE.

Want to try it yourself (in a test environment!)? Download the script.

Comparing Estimated and Actual Execution Plans in SQL Server

Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.

SQL Server gives you a similar option. When you write a query, before committing to it, you can request an estimated execution plan.

The estimated execution plan is designed to show what SQL Server would most likely do if it were to execute the query. Using statistics, it estimates how many rows may be returned from each table. It chooses the operators it will use to retrieve the data – scans or seeks. It decides how to best join the tables together – nested loops, merge joins, hash joins. It is a reasonably accurate guide to what SQL Server will do.

You can view an estimated execution plan for an individual statement, a stored procedure, or a function. The plan can be text, XML, or graphical. To view text, use SET SHOWPLAN_TEXT ON. For an XML version, use the command SET SHOWPLAN_XML ON. To view a graphical plan in SSMS, click the Display Estimated Execution Plan button in SSMS.

Est Exec Plan

There are some cases in which SQL Server can’t create an estimated execution plan. If your query has parameters in it and values aren’t passed in, SQL Server can’t interpret those – it needs literal values. If the query references a temp table that is not declared, the plan also can’t be generated.

Now, let’s go back to our construction project. We’ve signed the contract and the contractor begins work. While the addition is being framed, you decide you want to add an extra room, or add windows, or make the ceilings higher. The contractor has to adjust for this in terms of time and cost. This will change how long the project takes, and how expensive it is.

Executing a query in SQL Server is no different. The actual execution plan is shown after a query is executed. The difference here is that SQL Server can tell you exactly how many reads were performed, how many rows were read, and what joins were performed.

If it’s a long-running query, it will take a while to get the execution plan. Parameters, if required, must be passed in.

The text plan is generated using SET STATISTICS PROFILE ON. The XML version of the actual plan can be viewed by using SET STATISTICS XML ON. A graphical version can be generated in SSMS by using the Include Actual Execution Plan button.

Actual Exec PLan

There are some cases in which things that show up in the estimated plan will not show in the actual plan. For example, when you call a scalar-value function, the estimated plan will show it – the actual plan will not. (This is why the impact of functions can be very misunderstood.)


The query optimizer is going to use statistics on the tables and indexes to decide how to perform the actual query execution. If statistics change for any reason between the time you estimate a plan and when the query is actually run, you can see differences.

Changes to table schema, indexes, or even the data can affect the statistics. If a new index is added, or rows are updated in the table, when the query optimizer executes the query, it could choose a different set of operators than it did during estimation. Sometimes the differences between the estimated and actual plans can be large!

How can you prevent this from being a problem? Make sure statistics are updated on your tables and indexes. Auto update stats will automatically refresh statistics if a specific number of rows in a table change – after a table reaches 500 rows, roughly 20% of the rows need to change. (Exact details about that are here The more rows that your table contains, the more changes that need to be made for them to automatically refresh – on large tables, you may need to set up more frequent stats updates.

You also want to be aware that using table variables on large result sets can be wildly inaccurate – they always estimate a low number of rows.


The estimated plan is stored in the plan cache. If you review the XML (doesn’t that sound like fun?!), you will see “ParameterCompiledValue” listed near the end. This is what value the query was run with when the plan was stored. Ensuing executions may use different values, which can lead to less-than-optimal performance if bad parameter sniffing happens.


An example of compiled values in the execution plan XML

An example of compiled values in the execution plan XML

Estimated execution plans can be very useful as you are writing and tuning queries, giving you an idea of how SQL Server will most likely perform query execution. However, if you need to know exactly what steps SQL Server will take, executing the query and reviewing the actual execution plan is the only way to be certain.

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.

Capturing Deadlocks in SQL Server [Video]

If you’re experiencing deadlocks in SQL Server, you want to know more about them. What queries are causing them? What tables and indexes are at the root of the problem? You can find this information by using tools such as trace flags, Profiler, and Extended Events. Jes shows you how in this 25-minute video:

Download the scripts here.

For more videos like this:

Capturing Deadlocks in SQL Server

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on her opponent, and Batman has his. This would be a superhero (and super) deadlock.

When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. Then, each task requests to lock the data the other task is already holding. Both tasks wait for the other to give up. Neither does. SQL Server could let this showdown continue indefinitely, but it won’t. It picks one task – usually, whichever will be the least expensive to roll back – as the victim, and that task is killed.

How do I know if it’s happening to me?

You might notice slowness in your application. Users might complain about slowness or error messages. And, you’ll see a message in the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.”

Your server has encountered a deadlock.

How can I capture more information about it?

You have several options: you can enable a trace flag to write more information to the log, you can capture deadlocks graphs using Profiler or Extended Events, and you can track the number of deadlocks occurring using Performance Monitor.

Use trace flags to write to the log

Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command

DBCC TRACEON (1204, 1222)

Here’s a small sample of what would appear in the log:

tf 1222

No one wants to read through that, manually parsing it to figure out what database it occurred in, what tasks were involved, and which was the victim. Wouldn’t it be great if this was represented graphically?

Capture a deadlock graph with Profiler or Extended Events

It is – you just have to know where to look to for a deadlock graph. In SQL Server 2005, 2008, and 2008R2, I prefer to use a Profiler trace; in SQL Server 2012 and 2014 I rely on Extended Events.


When you set up a Profiler session to gather deadlock information, select the “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events. Make sure you go to the Events Extraction Settings tab and select the option “Save Deadlock XML events separately”. This ensures that the .xdl files – the graphs – will be saved as separate files. When you start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event.

deadlock graph

The task with the blue X on it is the victim. By holding your mouse over the process oval, you can see what statement was being executed by that task. The Key Lock rectangles will help you find the object and index that the locking and blocking occurred on.

Extended Events

In the future, Profiler will be removed from SQL Server. Extended Events (EE) is taking its place. You have two options for getting deadlock information using EE. First, there is the system_health session that, by default, runs continuously. You can mine this for deadlocks that have happened in the past. You can also set up your own session to capture only deadlock information (and any other relevant events).

If using the system_health session, filter on “xml_deadlock_report”. If setting up your own session, capture the same event. If you are using the GUI available in 2012 and 2014, you can open the file and view the deadlock graph on the “Deadlock” tab.


If parsing the results using T-SQL and XQuery, you would use the .query method to extract the deadlock XML, like such:

SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph

This generates the XML, which you would then save as an .xdl, then close and re-open with SSMS to view the graph.

Track Performance Counters

Performance Monitor (PerfMon) is valuable for tracking many statistics. One of the metrics you can track for SQL Server is SQLServer:Locks – Number of Deadlocks/sec. This is the least informational of the methods mentioned here – it only gives you a count of how many are happening per second in your system. It can be helpful, however, to track this to see what times of the day produce the most deadlocks. You can then use the other methods mentioned here to research what is happening during that time to cause the deadlocks.

Don’t be a victim!

Take charge of your SQL Servers! Become familiar with detecting deadlocks, finding out what caused them, and fixing them! For more tools and advice, visit Locking and Blocking in SQL Server.

Treasure Hunt: SQL Server Error Log [Video]

What secrets lie within SQL Server’s error log? Many! This day-by-day record of your server’s actions can be a treasure trove of information. Here you’ll learn what to look for, how to effectively search it, and how to get alerted of potential problems.

Backups, Restores, Corruption, and You: How Strong are You?

You and your databases have a strong relationship. You back them up, and check them for corruption. They provide the business with information day after day.

Don't let your relationship come to this!

Don’t let your relationship come to this!

Then, one day, your relationship is rocked. The backups are too slow. The restores are too slow. There is…gasp…corruption. Are you prepared to handle this? Will your working relationship continue? Take this quiz to find out!

  • 0 points – I’ve never done this in real life before
  • 1 point – I’ve done it once or twice in a test environment 
  • 2 points – I’ve done it in production
  • 3 points – I’ve done it in production and I’ve documented it
Here are your questions!
  1. You run DBCC CHECKDB regularly. Your job fails one day, with an error that corruption was detected. You need to figure out what object has the problem – a clustered index? a nonclustered index? a metadata page? – and repair it.You can identify the object and corruption, and several ways to fix it.
  2. After this incident, the business owners of the database would like extra assurance that their data is safe. You’ve identified the steps you could take to validate backups and restores.
  3. You have a database that is very large. You have a window of 60 minutes to complete the backup each day, but it’s taking much longer. You implement a process to make backups faster.
  4. Due to its size, this database is also exceeding its RTO in a disaster recovery test. You re-architect the database and the restore process to make it faster.
  5. There are several related single-tenant databases for an application. You need to ensure that all related databases can be rolled back to immediately before a single transaction when upgrades occur.

0-2 points: Your relationship is doomed. You need to get my Backup & Recovery, Advanced training stat. As a matter of fact, you may want to start with my Backup & Recovery Step by Step training, to make sure you have the basics down.

3-6 points: Your relationship is rocky at best. Review my Backup & Recovery, Advanced training to make sure you’re prepared for all of the potential crises you could encounter.

7-10 points: Solid, but you still have room for improvement. Perhaps a few days in a test environment, with a little one-on-one attention and a review of all of the modules in my Backup & Recovery, Advanced training is just what you two need.

11+ points: You clearly have a great relationship. Take it to the next level by reviewing all of the scenarios outlined above and documenting those you haven’t tackled yet. Check out my video training for additional tips and ideas.

Spotting Trouble – and Help! – in Execution Plans [Video]

The execution plan is a step-by-step map of how SQL Server has returned the data from your query to you. Sometimes, there are detours and potholes in the road that make the journey more difficult. Sometimes, there’s an alternate, faster route. Watch this webcast to find out how to read the warning signs and identify the helpful information.

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.

What the Execution Plan Doesn’t Tell You [Video]

The execution plan is a gold mine of information about how your query performed. You can tell the steps that were taken, the number of rows that were processed, and even get index or statistics hint. But what isn’t the execution plan telling you? Jes will show things that you might not realize are problems – and how to fix them! 


Don’t Fear the Execution Plan [Video] 

Craig Freedman’s Parallel Query Execution Presentation 

Paul White’s Forcing a Parallel Query Execution Plan 

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.