Benjamin Nevarez Rotating Header Image

SQL Server on Linux Foreword by Kalen Delaney

I started working with SQL Server more than 30 years ago, when it was a Sybase product that ran on Unix (as well as on more than half a dozen other operating systems), and we did all our work on Unix machines. SQL Server didn’t run on Windows at that time, because there was no Windows operating system. But several years later, when Sybase partnered with Microsoft to port its database product onto PC-based operating systems, few people could foresee just how powerful and ubiquitous these PCs would become. PC is hardly about only personal computers these days.

I’ve seen a lot of changes with SQL Server over these three decades, and some of the most interesting ones for me are when the product seems to circle back and add features or internal behaviors that were originally part of the product but had once been discarded. Some of the original ideas were not that far off base after all. Having SQL Server return to its roots and become available on Linux (a Unix-based OS) in SQL Server 2017 almost seems like coming home.

Benjamin Nevarez has been working with Unix-based operating systems almost as long as I’ve been working with SQL Server. He was also excited to see SQL Server make an appearance on Linux. It didn’t take him long to decide to get his hands dirty and figure out how SQL Server professionals could get the maximum value out of the new OS. He wrote this book to make available to others all that he had learned.

I have known Ben for more than a dozen years, since he first started finding typos and other errors in my SQL Server 2005 books. We started a dialog, and I then asked him if he was interested in being a technical editor for some of my work. Through this technical collaboration, I have learned that when Ben sets out to learn something, he does it thoroughly. His attention to detail and passion for complete answers never cease to amaze me.

In this book, Ben tells you how to get started using SQL Server on Linux and how the database system actually works on the new platform. Chapters 1, 2, and 3 are particularly useful if you’re new to Linux but experienced with SQL Server. Although most SQL Server books wouldn’t go into operating system administration details, Chapter 3 does just that, to make the transition easier for people who have many years, if not decades, of experience with Windows. Of course, if you’re already proficient with Linux, but new to SQL Server, you can focus on the following chapters when Ben’s expertise with SQL Server shines through.

In Chapter 4, he tells you how SQL Server can be configured and blends SQL Server details with the Linux tools you need to access and control your SQL Server. Chapters 5 and 6 are very SQL Server focused. Chapter 5 provides some very detailed information about working with SQL Server queries, including how queries are optimized and processed, and how you can tune slow-running queries. Chapter 6 tells you all about some of the latest and greatest in SQL Server’s optimization techniques in the most recent versions of SQL Server. Finally, in Chapters 7 and 8, he provides coverage of two critical focus areas for a database administrator: managing availability and recoverability, and setting up security. These are critical topics for any DBA, and because they involve the relationship between the database engine and the operating system, it’s best to learn about them from someone who is an expert in both areas.

Although both Linux and SQL Server are huge topics and there is no way one book can provide everything you need to know about both technologies, Ben has done an awesome job of giving you exactly what you need to know, not only to get SQL Server running on the Linux operating system, but to have it performing well, while keeping your data safe and secure.

—Kalen Delaney

Poulsbo, Washington, March 2018

Why I decided to write a book about SQL Server on Linux

I started my IT career working with Unix applications and databases back in the early ’90s, and my first job ever was as a data processing manager for a small IT shop. Back then, I was running Unix System V Release 4 on an NCR system. With such big and expensive minicomputer systems, I was always wondering if I could have a Unix system on less expensive hardware, such as a PC, to learn and test without disrupting our shared test systems.

Then I read an article in a personal computing magazine about something called Linux. Nobody knew what Linux was back in those days. Very few people—mostly at universities—had even access to the Internet back then. So I downloaded Linux on four or five floppy disks, installed it on a PC, and started playing with it. It was a distribution called Slackware. It was amazing that I could finally experiment and test everything I wanted on my own personal Unix system.

I continued to work with Linux and all the popular Unix commercial implementations, including IBM AIX, HP-UX, Sun Solaris, and others, throughout the ’90s. For several years, people still didn’t know what Linux was. It was not an immediate success. But by the end of the ’90s I decided to specialize in SQL Server, and by doing that I left the Unix world behind.

So it looked like I was not going to touch a Unix system ever again. But one day in March 2016, Microsoft surprised the technology community by announcing that SQL Server would be available on Linux. When I first heard the news, I thought it would be cool to write a book about it. Because I was just finishing a book about SQL Server 2016, I decided to wait to see how the technology evolved and to take a break from writing. One day, as I was running while training for a marathon, I started thinking about the project again and decided it could be a great idea to write a book about SQL Server on Linux. Just after finishing my run, I went to my laptop and sent an e-mail to my contact at McGraw-Hill Education, who eventually connected me with Lisa McClain. Within a few days, I was now working on this new book project.

Let me tell you how I structured this book.

Chapter 1 shows you how to get SQL Server running on Linux as quickly as possible, so you can start using the technology, even though I haven’t covered all the details yet. The chapter covers how to install SQL Server on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu and how to configure an image of SQL Server on a Docker container. More details of the setup and configuration are included in Chapter 4.

Chapter 2 covers some SQL Server history with different operating systems and explains some of the details about how SQL Server on Linux works. This includes describing the interaction between SQL Server and the operating system, decisions regarding its architecture, and information about its software implementation, among other related topics. It also covers details about the SQL Operating System (SQLOS), the Drawbridge technology, and the SQL Platform Abstraction Layer (SQLPAL).

I include an entire chapter dedicated to Linux for the SQL Server professional. Chapter 3 covers all the basic Linux commands you need to get started, including managing files and directories and their permissions, along with a few more advanced topics, including system monitoring.

Chapter 4 covers SQL Server setup and configuration in a Linux environment, and it is divided into three main topics: using the mssql-conf utility to configure SQL Server, which is required in Linux environments; using Linux-specific kernel settings and operating system configurations; and using some traditional SQL Server configurations for both Windows and Linux installations.

After spending time learning how to set up and configure SQL Server, you’ll move to Chapter 5, which discusses how to use SQL Server to perform database operations. This chapter, in particular, covers query tuning and optimization topics, which are applicable both to Windows and Linux installations—and, in fact, to all the currently supported versions of the product.

Chapter 6 continues with query processing and covers the new features available in SQL Server 2017, such as adaptive query processing and automatic tuning.

Chapter 7 is about high-availability and disaster-recovery solutions for SQL Server on Linux and focuses on Always On availability groups. Availability groups on both Windows and Linux can be used in high-availability and disaster-recovery configurations and for migrations and upgrades, or even to scale out readable copies of one or more databases. The chapter also covers Pacemaker, a clustering solution available on Linux distributions.

Finally, I close the book with Chapter 8, which is about security. This chapter reviews security from a general point of view and includes details about some of the new security features in SQL Server, including Transparent Data Encryption, Always Encrypted, Row-Level Security, and Dynamic Data Masking.

High Performance SQL Server

High Performance SQL Server

I am very excited to announce that my new book, High Performance SQL Server, is now available on Amazon and, in this post, I include the Introduction of the book, which covers what this book is about and describes the content of each of its nine chapters.

I’ve been writing and presenting about query tuning and optimization for years. I even wrote a book on the topic called SQL Server 2014 Query Tuning and Optimization. Query tuning and optimization is extremely important to improve the performance of your databases and applications.

Equally important is having a well-designed and configured system in the first place. SQL Server default configuration can work fine for some applications, but mission critical and high-performance applications demand a thoughtful design and configuration. Well-written and tuned queries will not shine if a system is not properly configured. For example, queries will not use processor resources in an optimal way if a maximum degree of parallelism setting is not configured correctly. Database performance will suffer if a database is using the default file auto-growth settings or if the storage is not properly configured. A misconfigured  tempdb  may show contention on many busy systems. Even the query optimizer will struggle with a bad database design or badly written queries. These are just some common configuration problems out there in real production systems.

In addition, even when a well-designed application goes to production, performance tuning does not end there. Monitoring and troubleshooting are an extremely important part of an application and database life cycle since performance problems eventually will arise. Workloads may change, hopefully for the better (for example, an application having to deal with an unexpected increase on business transactions). Sometimes those changes will require a redesign, changes, and perhaps new configurations.

So this is, in fact, an iterative process, consisting of design and configuration, followed by implementation, monitoring, and troubleshooting, which again may lead to new designs or configurations, monitoring, and so on. In addition, collecting performance data, creating a baseline, and performing trend analysis is an important part of a production implementation, not only to troubleshoot problems but also to anticipate issues or understand future growth and additional requirements. It is essential to estimate and trend those changes proactively instead of waking up to a system suddenly having trouble in handling changing workloads or, even worse, to face a downtime that could have been avoided. There are several tools to help with this, and the great news is that SQL Server 2016 offers a very promising one called the Query Store.

I spent a good part of my daily job working on all these items so I decided to write a book about them. I wanted to cover everything you need to know about performance in SQL Server that does not require you to know about query tuning, work with execution plans, or “fight” the query optimizer. There are so many areas to cover and more are being added as new features and technologies appear on SQL Server such as In-Memory OLTP, columnstore indexes, and the aforementioned Query Store.

This book covers all currently supported versions of SQL Server with a major focus on SQL Server 2016. Although this is a performance book from the practical point of view, SQL Server internals are very important too. The best way to troubleshoot something is to know how it works and why things happen. Consequently, I focus on internals when required.

Finally, this book complements my query tuning and optimization book. If you are a database developer or a SQL Server professional who cares about query performance, you could benefit from both books. If you are a database administrator, a database architect, or even a system administrator, and you want to improve the performance of your system without query tuning, you can read only this book.

As mentioned earlier, understanding SQL Server internals is important to better optimize a system and troubleshoot database problems, so this book starts explaining how the SQL Server database engine works and covers everything happening in the system from the moment a connection is made to a database until a query is executed and the results are returned to the client. Chapter 1 includes such topics such as the Tabular Data Stream (TDS) and network protocols used by SQL Server, SQLOS, and the work performed by the SQL Server relational engine, focusing on query processing and the most common query operators.

Waits happen in an SQL Server instance all the time. Chapter 2 introduces the waits performance methodology, which can be used to troubleshoot performance problems, especially when other methods are not able to pinpoint a performance issue.

Chapter 3 covers the Query Store, a very promising query performance feature introduced with SQL Server 2016. The Query Store can help you to collect query and plan information along with their runtime statistics, which you can use to easily identify query-performance-related problems and even force an existing execution plan. The chapter closes by mentioning some related new features such as the Live Query Statistics and the SQL Server Management Studio plan comparison tool.

Chapter 4 explains a number of instance level configuration settings that can greatly impact the performance of your SQL Server implementation. As an interesting fact, it shows how some trace flags originally introduced to solve a particular problem are now implemented as SQL Server configuration defaults.

Chapter 5 covers  tempdb  configuration, which is especially important as such a database is shared between all the user and system databases on a SQL Server instance. Focus on the chapter is given to  tempdb  latch contention of allocation pages and  tempdb  disk spilling, a performance issue that occurs when not enough memory is available for some query processor operations.

In-memory technologies are introduced in Chapter 6, including In-Memory OLTP, which in SQL Server 2016 sees its second release, and columnstore indexes, now on its third mayor implementation. Both features suffered severe limitations with their original release so this chapter covers how these technologies work and what their current improvements are. The chapter ends with Operational Analytics, which combines both technologies to allow analytical queries to be executed in real-time in an OLTP system. In-memory technologies promise to be the future in relational database technologies.

Chapter 7 shows how proactively collecting and persisting performance information could be extremely beneficial to understand how a specific system works, to create a baseline, and to understand when performance is deviating from a desirable or expected behavior. The chapter also covers the most critical performance counters, dynamic management objects, and events, along with some of the tools used to display and collect such data.

Indexing, a required topic for database performance, is covered in Chapter 8. It explains how indexes work and why they are important on both OLTP and Data Warehouse environments. The chapter provides emphasis on using SQL Server tools to help create indexes such as the missing indexes feature and the more sophisticated Database Engine Tuning Advisor.

Finally, SQL Server storage is explained in Chapter 9. Disk has traditionally been the slowest part of a database system, but newer technologies such as flash-based storage offer great performance improvements and are becoming a de facto enterprise standard as their cost continues to decline. The chapter also indicates that storage optimization is not only about using the fastest disk possible but also minimizing its usage by implementing the methods covered in several chapters of the book, such as proper indexing or some query tuning techniques.

SQL Server 2014 Incremental Statistics

A major problem with updating statistics in large tables in SQL Server is that the entire table always has to be scanned, for example when using the WITH FULLSCAN option, even if only recent data has changed. This is also true when using partitioning: even if only the newest partition had changed since the last time statistics were updated, updating statistics again required to scan the entire table including all the partitions that didn’t change. Incremental statistics, a new SQL Server 2014 feature, can help with this problem.

Using incremental statistics you can update only the partition or partitions that you need and the information on these partitions will be merged with the existing information to create the final statistics object. Another advantage of incremental statistics is that the percentage of data changes required to trigger an automatic update of statistics now works at the partition level which basically means that now only 20% of rows changed (changes on the leading statistics column) per partition are required. Unfortunately the histogram is still limited to 200 steps for the entire statistics object in this version of SQL Server.

Let us look at an example of how can you update statistics at a partition level to explore its behavior as of SQL Server 2014. First we need to create a partitioned table using the AdventureWorks2012 database:

   '20071001', '20071101', '20071201', '20080101', 
   '20080201', '20080301', '20080401', '20080501', 
   '20080601', '20080701', '20080801'


CREATE TABLE dbo.TransactionHistory 
  TransactionID        INT      NOT NULL, -- not bothering with IDENTITY here
  ProductID            INT      NOT NULL,
  ReferenceOrderID     INT      NOT NULL,
  ReferenceOrderLineID INT      NOT NULL DEFAULT (0),
  TransactionType      NCHAR(1) NOT NULL,
  Quantity             INT      NOT NULL,
  ActualCost           MONEY    NOT NULL,
  CONSTRAINT CK_TransactionType 
    CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))
ON TransactionsPS1 (TransactionDate);

Note: For details about partitioning and the CREATE PARTITION FUNCTION / SCHEME statements please refer to Partitioned Tables and Indexes in Books Online.

We currently have data to populate 12 partitions. Let us start by first populating only 11.

INSERT INTO dbo.TransactionHistory
SELECT * FROM Production.TransactionHistory
WHERE TransactionDate < '2008-08-01';

If required, you can use the following statement to inspect the contents of the partitions:

SELECT * FROM sys.partitions
  WHERE object_id = OBJECT_ID('dbo.TransactionHistory');

Let us create an incremental statistics object using the CREATE STATISTICS statement with the new INCREMENTAL clause set to ON (OFF is the default):

CREATE STATISTICS incrstats ON dbo.TransactionHistory(TransactionDate) 

You can also create incremental statistics while creating an index using the new STATISTICS_INCREMENTAL clause of the CREATE INDEX statement.

You can inspect the created statistics object using DBCC:

DBCC SHOW_STATISTICS('dbo.TransactionHistory', incrstats);

Among other things, you will notice that the histogram has 200 steps (only the last 3 shown here):

198 2008-07-25 00:00:00.000 187 100 2
199 2008-07-27 00:00:00.000 103 101 1
200 2008-07-31 00:00:00.000 281 131 3

Initial DBCC results

So we already have the maximum of steps in a statistics object. What would happen if you add data to a new partition? Let us add data to partition 12:

INSERT INTO dbo.TransactionHistory 
SELECT * FROM Production.TransactionHistory 
WHERE TransactionDate >= '2008-08-01';

Now, we update the statistics object using the following statement:

UPDATE STATISTICS dbo.TransactionHistory(incrstats) 

Note the new syntax specifying the partition, where you can specify multiple partitions, separated by comma. The UPDATE STATISTICS statement reads the specified partitions and then merges their results with the existing statistic object to build the global statistics. Note the RESAMPLE clause; this is required as partition statistics need to have the same sample rates to be merged to build the global statistics. Although only the specified partition was scanned, you can see that SQL Server has rearranged the histogram. The last three steps now show data for the added partition. You can also compare the original with the new histogram for other minor differences:

197 2008-07-31 00:00:00.000 150 131 2
198 2008-08-12 00:00:00.000 300 36 9
199 2008-08-22 00:00:00.000 229 43 7
200 2008-09-03 00:00:00.000 363 37 11

DBCC results after the incremental update

If for any reason you want to disable the incremental statistics you can use the following statement to go back to the original behavior (or optionally just drop the statistics object and create a new one).

UPDATE STATISTICS dbo.TransactionHistory(incrstats) 

After disabling the incremental statistics trying to update a partition as shown previously will return the following error message:

Msg 9111, Level 16, State 1

UPDATE STATISTICS ON PARTITIONS syntax is not supported for non-incremental statistics.

Finally, you can also enable incremental statistics for your automatic statistics at the database level, if needed. This requires the INCREMENTAL = ON clause in the ALTER DATABASE statement and obviously also requires AUTO_CREATE_STATISTICS set to ON.

Note: Article originally posted on

The SQL Server Query Store

Have you ever found a plan regression after a SQL Server upgrade and wanted to know what the previous execution plan was? Have you ever had a query performance problem due to the fact that a query unexpectedly got a new execution plan? At the last PASS Summit, Conor Cunningham uncovered a new SQL Server feature, which can be helpful in solving performance problems related to these and other changes in execution plans.

This feature, called the Query Store, can help you with performance problems related to plan changes and will be available soon on SQL Azure and later on the next version of SQL Server. Although it is expected to be available on the Enterprise Edition of SQL Server, it is not yet known if it will be available on Standard or any other editions. To understand the benefits of the Query Store, let me talk briefly about the query troubleshooting process.

Why is a Query Slow?

Once you have detected that a performance problem is because a query is slow, the next step is to find out why. Obviously not every problem is related to plan changes. There could be multiple reasons why a query that has been performing well is suddenly slow. Sometimes this could be related to blocking or a problem with other system resources. Something else may have changed but the challenge may be to find out what. Many times we don’t have a baseline about system resource usage, query execution statistics or performance history. And usually we have no idea what the old plan was. It may be the case that some change, for example, data, schema or query parameters, made the query processor produce a new plan.

Plan Changes

At the session, Conor used the Picasso Database Query Optimizer Visualizer tool, although didn’t mention it by name, to show why the plans in the same query changed, and explained the fact that different plans could be selected for the same query based on the selectivity of their predicates. He even mentioned that the query optimizer team uses this tool, which was developed by the Indian Institute of Science. An example of the visualization:

Picasso Database Query Optimizer Visualizer

Picasso Database Query Optimizer Visualizer

Each color in the diagram is a different plan, and each plan is selected based on the selectivity of the predicates. An important fact is when a boundary is crossed in the graph and a different plan is selected, most of the times the cost and performance of both plans should be similar, as the selectivity or estimated number of rows only changed slightly. This could happen for example when a new row is added to a table which qualifies for the used predicate. However, in some cases, mostly due to limitations in the query optimizer cost model in which it is not able to model something correctly, the new plan can have a large performance difference compared to the previous one, creating a problem for your application. By the way, the plans shown on the diagram are the final plan selected by the query optimizer, don’t confuse this with the many alternatives the optimizer has to consider to select only one.

An important fact, in my opinion, which Conor didn’t cover directly, was the change of plans due to regressions after changes on cumulative updates (CUs), service packs, or version upgrades. A major concern that comes to mind with changes inside the query optimizer is plan regressions. The fear of plan regressions has been considered the biggest obstacle to query optimizer improvements. Regressions are problems introduced after a fix has been applied to the query optimizer, and sometimes referred as the classic “two or more wrongs make a right.” This can happen when, for example, two bad estimations, one overestimating a value and the second one underestimating it, cancel each other out, luckily giving a good estimate. Correcting only one of these values may now lead to a bad estimation which may negatively impact the choice of plan selection, causing a regression.

What Does the Query Store Do?

Conor mentioned the Query Store performs and can help with the following:

  1. Store the history of query plans in the system;
  2. Capture the performance of each query plan over time;
  3. Identify queries that have “gotten slower recently”;
  4. Allow you to force plans quickly; and,
  5. Make sure this works across server restarts, upgrades, and query recompiles.

So this feature not only stores the plans and related query performance information, but can also help you to easily force an old query plan, which in many cases can solve a performance problem.

How to Use the Query Store

You need to enable the Query Store by using the ALTER DATABASE CURRENT SET QUERY_STORE = ON; statement. I tried it in my current SQL Azure subscription, but the statement returned an error as it seems that the feature is not available yet. I contacted Conor and he told me that the feature will be available soon.

Once the Query Store is enabled, it will start collecting the plans and query performance data and you can analyze that data by looking at the Query Store tables. I can currently see those tables on SQL Azure but, since I was not able to enable the Query Store, the catalogs returned no data.

You can analyze the information collected either proactively to understand the query performance changes in your application, or retroactively in case you have a performance problem. Once you identify the problem you can use traditional query tuning techniques to try to fix the problem, or you can use the sp_query_store_force_plan stored procedure to force a previous plan. The plan has to be captured in the Query Store to be forced, which obviously means it is a valid plan (at least when it was collected; more on that later) and it was generated by the query optimizer before. To force a plan you need the plan_id, available in the sys.query_store_plan catalog. Once you look at the different metrics stored, which are very similar to what is stored for example in sys.dm_exec_query_stats, you can make the decision to optimize for a specific metric, like CPU, I/O, etc. Then you can simply use a statement like this:

EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 1;

This is telling SQL Server to force plan 1 on query 1. Technically you could do the same thing using a plan guide, but it would be more complicated and you would have to manually collect and find the required plan in the first place.

How Does the Query Store Work?

Actually forcing a plan uses plan guides in the background. Conor mentioned that “when you compile a query, we implicitly add a USE PLAN hint with the fragment of the XML plan associated with that statement.” So you no longer need to use a plan guide anymore. Also keep in mind that, same as using a plan guide, it is not guaranteed to have exactly the forced plan but at least something similar to it. For a reminder of how plan guides work take a look at this article. In addition, you should be aware that there are some cases where forcing a plan does not work, a typical example being when the schema has changed, i.e. if a stored plan uses an index but the index no longer exists. In this case SQL Server can not force the plan, will perform a normal optimization and it will record the fact that the forcing the plan operation failed in the sys.query_store_plan catalog.


Every time SQL Server compiles or executes a query, a message is sent to the Query Store. This is shown next.


Query Store Workflow Overview

The compile and execution information is kept in memory first and then saved to disk, depending on the Query Store configuration (the data is aggregated according to the INTERVAL_LENGTH_MINUTES parameter, which defaults to one hour, and flushed to disk according to the DATA_FLUSH_INTERVAL_SECONDS parameter). The data can also be flushed to disk if there is memory pressure on the system. In any case you will be able to access all of the data, both in memory and disk, when you run the sys.query_store_runtime_stats catalog.


The collected data is persisted on disk and stored in the user database where the Query Store is enabled (and settings are stored in sys.database_query_store_options. The Query Store catalogs are:

sys.query_store_query_text Query text information
sys.query_store_query Query text plus the used plan affecting SET options
sys.query_store_plan Execution plans, including history
sys.query_store_runtime_stats Query runtime statistics
sys.query_store_runtime_stats_interval Start and end time for intervals
sys.query_context_settings Query context settings information

Query Store views

Runtime statistics capture a whole slew of metrics, including the average, last, min, max, and standard deviation. Here is the full set of columns for sys.query_store_runtime_stats:

runtime_stats_id plan_id runtime_stats_interval_id
execution_type execution_type_desc first_execution_time last_execution_time count_executions
avg_duration last_duration min_duration max_duration stdev_duration
avg_cpu_time last_cpu_time min_cpu_time max_cpu_time stdev_cpu_time
avg_logical_io_reads last_logical_io_reads min_logical_io_reads max_logical_io_reads stdev_logical_io_reads
avg_logical_io_writes last_logical_io_writes min_logical_io_writes max_logical_io_writes stdev_logical_io_writes
avg_physical_io_reads last_physical_io_reads min_physical_io_reads max_physical_io_reads stdev_physical_io_reads
avg_clr_time last_clr_time min_clr_time max_clr_time stdev_clr_time
avg_dop last_dop min_dop max_dop stdev_dop
avg_query_max_used_memory last_query_max_used_memory min_query_max_used_memory max_query_max_used_memory stdev_query_max_used_memory
avg_rowcount last_rowcount min_rowcount max_rowcount stdev_rowcount

Columns in sys.query_store_runtime_stats

This data is only captured when query execution ends. The Query Store also considers the query’s SET options, which can impact the choice of an execution plan, as they affect things like the results of evaluating constant expressions during the optimization process. I cover this topic in a previous post.


This will definitely be a great feature and something I’d like to try as soon as possible (by the way, Conor’s demo shows “SQL Server 15 CTP1” but those bits are not publicly available). The Query Store can be useful for upgrades which could be a CU, service pack, or SQL Server version, as you can analyze the information collected by the Query Store before and after to see if any query has regressed. (And if the feature is available in lower editions, you could even do this in a SKU upgrade scenario.) Knowing this can help you to take some specific action depending on the problem, and one of those solutions could be to force the previous plan as explained before.

SQL Server 2014 Query Tuning & Optimization Book Code

Today I was finally able to finish extracting the code of my new book Microsoft SQL Server 2014 Query Tuning & Optimization after a few readers requested it recently but I had been busy with some traveling in the last few weeks. The book contains a large number of SQL queries, all of which are based on the AdventureWorks2012 database, except Chapter 9 which uses the AdventureWorksDW2012 database. All code has been tested on SQL Server 2014. Note that these sample databases are not included by default in your SQL Server installation, but can be downloaded from the CodePlex website.

To download the code click here Query Tuning and Optimization Code.

PASS Summit and Other Speaking Engagements

It is almost that time of the year again when we are all headed to attend the largest SQL Server conference in the world, the PASS Summit, hosted again in Seattle. In my particular case I will also be attending some other SQL Server events where I will be given probably more than a dozen presentations. But like a rock band getting ready for a world tour 🙂 I’ll start warming up with a local gig at our SQL Server user group. I’ll be presenting Query Processing in In-Memory OLTP (Hekaton) this Thursday, September 18th at the Los Angeles SQL Server Professionals Group. Their meetings are hosted at the UCLA campus and you can visit their page for more details.

Two days later I’ll be attending our SQLSaturday in San Diego where I am schedule to present three sessions: the Hekaton session previously mentioned, Understanding Parameter Sniffing and Dive into the Query Optimizer – Undocumented Insight. As usual this event includes great speakers from all over the country and this time we will have Itzik Ben-Gan, Grant Fritchey, Randy Knight, TJay Belt, Mickey Stuewe, Brandon Leach, and Andrew Karcher among others. You can see the schedule and register at the SQLSaturday website.

So far I only have one event scheduled for October which is a local user group meeting at the San Dimas Software & Database Professionals group where again I will be presenting my Hekaton session. The session is scheduled for October 8th and you can find the meetup page here.

Then the first week of November I’ll be attending both the MVP Summit and the PASS Summit in Seattle, WA. But my flight will make a short stop first in Portland, OR to attend the Oregon SQLSaturday which is scheduled for November 1st. My only session for the event, Query Processing in In-Memory OLTP (Hekaton), is scheduled at 4:30pm. After the Oregon SQLSaturday I will be attending my first MVP Summit from November 2nd to November 6th. I am excited to be attending this event for the first time. The PASS Summit starts on November 4th and I’ll be presenting Query Processing in In-Memory OLTP on Friday, November 7th at 8:00am, rooms 611-612. This would be my 12th PASS Summit and my 7th year as presenter.

Before closing 2014, I’ve been invited to Istanbul, Turkey to participate on their SQLSaturday which is scheduled for the first weekend of December. I am planning to deliver a pre-con on Friday 5th covering “SQL Server Query Tuning & Optimization” topics and will be presenting my Hekaton session at the SQLSaturday the following day. I’ll provide or tweet more details as they become available.

I look forward to seeing lots of SQL Server professionals and meeting new friends at these SQL Server events.

Finally, after writing a new book this year it looks like I took a good break from writing. I just started working on some articles again and will be posting them soon both on this blog and on

Speaking at SQLBits and the PASS Summit


I am really excited that this weekend I will be flying to the UK to participate in my first European SQL Server conference, SQLBits XII, where I am scheduled to present 2 sessions. SQLBits XII, the largest SQL Server conference in Europe, will be held at The International Centre in Telford, UK from July 17th to July 19th. My first session, Dive into the Query Optimizer – Undocumented Insight, is scheduled for Friday 18th at 12:15 PM, room Ironbridge 3 and the second one, Understanding Parameter Sniffing for Saturday 19th at 8:15 AM, room Wenlock 1. These are sessions I have presented previously at the PASS Summit.

In addition, I got selected to present a session at the PASS Summit 2014, which will be held again in Seattle, WA this November. The PASS Summit is the largest SQL Server and BI conference in the world and this would be my 12th year attending, and my 7th year as a presenter. My session, Query Processing in In-Memory OLTP, will cover query processing and optimization with the new Hekaton database engine, a topic which I also cover in a chapter of my new book.

Just a few days after the sessions for the PASS Summit 2014 were announced, I also got the nice surprise that I was awarded as a SQL Server MVP. It is a great honor for me to be part of this awesome team.

Finally, my new book SQL Server 2014 Query Tuning & Optimization has been completed and will be soon published by McGraw-Hill Education.

I look forward to meeting lots of SQL Server professionals on these conferences. See you all in Telford and Seattle.

New Book: SQL Server 2014 Query Tuning & Optimization



It looks like my blog has been very quiet for a while once again. Yes, I’ve been busy working on some other SQL Server projects since last year. One of those projects was working on Kalen Delaney’s new book SQL Server 2012 Internals where I co-authored two chapters, “Special Databases” and “Query Execution”, which were originally written for previous versions of the book by Kalen and Craig Freedman, respectively. The book was finally released last November.

As soon as I finished my part on Kalen’s book, I immediately started working on my new book, SQL Server 2014 Query Tuning & Optimization, which has been available for pre-order at Amazon for a while now. This book covers everything you need to know about query tuning and optimization for SQL Server to help you design high-performing database applications. As a SQL Server 2014 book it obviously covers new features like Hekaton, the new cardinality estimator, and incremental statistics, among others, but most of the content can be used in previous versions of SQL Server as well. I am currently working on the last items of the book with the great team at McGraw-Hill Education and the help of the amazing Dave Ballantyne (@davebally) as technical reviewer and the book should be available soon.

Also, at the last PASS Summit in Charlotte I was invited by SQL Sentry to blog at their site, which has great content by some of the most amazing bloggers in the SQL Server community. Although I haven’t had much time to blog there as I would wanted, I have started with a few SQL Server 2014 articles that you can read here.

Finally, I have submitted sessions for SQLBits (first time ever submitted) and to the PASS Summit (which I have attended for 11 years so far), and just at the moment I am writing this I got the great news that I got two sessions selected for SQLBits so I am really looking forward to finally attend this conference in the UK this July. I am also planning to attend some other SQL Server events in the US, including our always amazing local SQLSaturdays in Orange County this April 26th, Sacramento on July 12th and San Diego, which is usually scheduled for September. Hope to see you at any of these SQL Server events.

Speaking at the PASS Summit 2013


In just a few days I will be headed to Charlotte, NC to attend my eleventh PASS Summit. Yes, I’ve been attending this, the largest SQL Server and BI conference in the world, every year since 2003. I am also honored to be speaking at the conference again making this my sixth year as a presenter. My session, Defeating the Limitations of the Query Optimizer, which I just presented for the first time at the San Diego SQLSaturday #249, is scheduled at the end of the conference, Friday 4:15 PM at the Ballroom B.

Of course I am also planning to attend many sessions. I haven’t finished my schedule yet but of what I have seen so far I definitely want to attend Mark Russinovich’s Windows Azure Deep Dive, Bob Ward’s Inside SQL Server 2012 Memory: The Sequel, Conor Cunningham’s OLTP Sharding Techniques for Massive Scale, Kevin Liu’s SQL Server 2014 In-Memory OLTP: DBA Deep Dive and Kevin Farlee’s SQL Server 2014 In-Memory OLTP: DB Developer Deep Dive.

In addition to the PASS Summit I will also be attending SQL in the City on Monday and SQLSaturday Charlotte BI Edition on Saturday. I will also be speaking at this last event where I will present my session Dive into the Query Optimizer – Undocumented Insight. You can still register for these two free events.

I look forward to meeting lots of SQL Server professionals, including those whom I only know via twitter. See you all in Charlotte!