I spend a large amount of time translating software requirements into schema and queries. These requirements are sometimes easy to implement but are often difficult. I want to talk about UI design choices that lead to data access patterns that are awkward to implement using SQL Server.

Sort By Column

Sort-By-Column is such a familiar pattern that we can take it for granted. Every time we interact with software that displays a table, we can expect the columns to be sortable like this:

Sort-By-Colunn is a great pattern when all the data can fit in the browser. But if the data set is billions of rows large this can get awkward even if the web page only requires one page of data. Consider this table of songs:

  CREATE TABLE Songs
  (
    Title NVARCHAR(300) NOT NULL,
    Album NVARCHAR(300) NOT NULL,
    Band NVARCHAR(300) NOT NULL,
    DurationInSeconds INT NOT NULL,
    CONSTRAINT PK_Songs PRIMARY KEY CLUSTERED (Title),
  );
  
  CREATE NONCLUSTERED INDEX IX_Songs_Album 
    ON dbo.Songs(Album) 
    INCLUDE (Band, DurationInSeconds);

  CREATE NONCLUSTERED INDEX IX_Songs_Band
    ON dbo.Songs(Band);

And consider these four queries sorted by each column:

  SELECT TOP (20) Title, Album, Band, DurationInSeconds FROM dbo.Songs ORDER BY Title;
  SELECT TOP (20) Title, Album, Band, DurationInSeconds FROM dbo.Songs ORDER BY Album;
  SELECT TOP (20) Title, Album, Band, DurationInSeconds FROM dbo.Songs ORDER BY Band;
  SELECT TOP (20) Title, Album, Band, DurationInSeconds FROM dbo.Songs ORDER BY DurationInSeconds;

Even for a query this simple, there are different query plans. The first two queries use covering indexes:

The third query needs to do a key lookup which is not ideal:

But the worst is the fourth query which needs to scan the whole table and do a sort in order to return the first 20 rows:

The point is that even though the only difference is the ORDER BY clause, those queries have to be analyzed separately. The basic unit of SQL tuning is the query. So if you show me UI requirements with ten sortable columns, I’ll show you ten queries to analyze.

When does this get awkward?

The Sort-By-Column feature is a great UI pattern, but it can get awkward if the data comes from a huge growing table with many, many columns. It may be tempting to create covering indexes on every column, but that has other tradeoffs. Columnstore indexes may help in some circumstances, but that introduces another level of awkwardness. There’s not always an easy alternative.

Paged Results

Using paged results is a good way to not overwhelm the user with too much information all at once. It’s also a good way to not overwhelm the database servers … usually.

Consider this design:

The data behind this example requires counting and processing the entire dataset in order to report the number of results. The query for this example might use syntax like this:

  ...
  ORDER BY LastModifiedTime
  OFFSET @N ROWS
  FETCH NEXT 25 ROWS ONLY;

It’s convenient syntax, and the query only produces 25 rows. But just because the result set is small, it doesn’t necessarily mean that it’s cheap. Just like we saw with the Sort-By-Column pattern, a TOP operator is only cheap if it doesn’t need to sort a lot of data first.

Asynchronous Page Requests

As a user navigates from one page of results to the next, the web requests involved can be separated by seconds or minutes. This leads to issues that look a lot like the pitfalls that are seen when using NOLOCK. For example:

  SELECT [Some Columns]
  FROM [Some Table]
  ORDER BY [Sort Value]
  OFFSET 0 ROWS
  FETCH NEXT 25 ROWS ONLY;

  -- wait a little bit
  SELECT [Some Columns]
  FROM [Some Table]
  ORDER BY [Sort Value]
  OFFSET 25 ROWS
  FETCH NEXT 25 ROWS ONLY;

When a row is added in between the two requests, the user might see the same row twice. And if a row is removed, the user might miss a row as they navigate the pages. This Paged-Results pattern is equivalent to “Give me rows 26-50”. When the real question should be “Give me the next 25 rows”. The difference is subtle.

Better Patterns

With Paged-Results, that “OFFSET @N ROWS” may take longer and longer as @N grows. Instead consider Load-More buttons or Infinite-Scrolling. With Load-More paging, there’s at least a chance to make efficient use of an index. The query would look something like:

  SELECT [Some Columns]
  FROM [Some Table]
  WHERE [Sort Value] > @Bookmark
  ORDER BY [Sort Value]
  FETCH NEXT 25 ROWS ONLY;

It still suffers from some of the pitfalls of asynchronous page requests, but because of the bookmark, the user will pick up where they left off.

Searching Text For Substring

Searching is everywhere on the internet. But what solution should be used on the back end? I want to warn against searching for a substring using SQL Server’s LIKE filter with wildcards like this:

  SELECT Title, Category
  FROM MyContent
  WHERE Title LIKE '%' + @SearchTerm + '%';

It can lead to awkward results like this:

“Aromatherapy” is probably not a good hit for the search term “math.” Meanwhile, the search results are missing articles that only mention Algebra or Trigonometry.

It can also be very difficult to pull off efficiently using SQL Server. There’s no straightforward index that supports this kind of search. Paul White gave one tricky solution with Trigram Wildcard String Search in SQL Server. There are also difficulties that can occur with collations and Unicode. It can become an expensive solution for a not-so-good user experience.

What To Use Instead

SQL Server’s Full-Text Search seems like it could help, but I’ve personally never used it. In practice, I’ve only seen success in solutions outside of SQL Server (e.g. Elasticsearch).

Conclusion

In my experience I’ve found that software designers are often very receptive to feedback that their designs are sometimes going to be awkward to implement. When they’re not, I’ve found it useful to highlight the pitfalls, the costs, and the time to delivery. That kind of feedback is necessary to help build maintainable, scalable solutions.

About the Author

Michael J Swart is a passionate database professional and blogger who focuses on database development and software architecture. He enjoys speaking about anything data related, contributing to community projects. Michael blogs as "Database Whisperer" at michaeljswart.com.

The post UI Design Patterns That Don't Scale appeared first on SQLPerformance.com.

In order to do this, you must either schedule or manually run a script (command line or Powershell, available for download on the DTU calculator website) during a period of a typical production workload.

If you're trying to analyze a large environment, or want to analyze data from specific points in time, this can become a chore. In a lot of cases, many DBAs have some flavor of monitoring tool that is already capturing performance data for them. In many cases, it is probably either already capturing the metrics needed, or can easily be configured to capture the data you need. Today, we're going to look at how to take advantage of SentryOne so we can provide the appropriate data to the DTU calculator.

To start, let's look at the information pulled by the command line utility and PowerShell script available on the DTU calculator website; there are 4 performance monitor counters that it captures:

• Processor – % Processor Time
• Logical Disk – Disk Reads/sec
• Logical Disk – Disk Writes/sec
• Database – Log Bytes Flushed/sec

The first step is determining if these metrics are already captured as part of data collection in SQL Sentry. For discovery, I suggest reading this blog post by Jason Hall, where he talks through how the data is laid out and how you can query it. I am not going to go through each step of this here, but encourage you to read and bookmark that entire blog series.

When I looked through the SentryOne database, I found that 3 of the 4 counters were already being captured by default. The only one that was missing was [Database – Log Bytes Flushed/sec], so I needed to be able to turn that on. There was another blog post by Justin Randall that explains how to do that.

In short, you can query the [PerformanceAnalysisCounter] table.


  SELECT  ID, 
    PerformanceAnalysisCounterCategoryID, 
    PerformanceAnalysisSampleIntervalID, 
    CounterResourceName, 
    CounterName
  FROM dbo.PerformanceAnalysisCounter
  WHERE CounterResourceName = N'LOG_BYTES_FLUSHED_PER_SEC';

You will notice that by default the [PerformanceAnalysisSampleIntervalID] is set to 0 – this means it is disabled. You will need to run the following command to enable this. Just pull the ID from the SELECT query you just ran and use it in this UPDATE:


  UPDATE dbo.PerformanceAnalysisCounter 
    SET PerformanceAnalysisSampleIntervalID = 1 
    WHERE ID = 166;

After running the update, you will need to restart the SentryOne monitoring service(s) relevant to this target, so that the new counter data can be collected.

Note that I set the [PerformanceAnalysisSampleIntervalID] to 1 so that the data is captured every 10 seconds, however, you could capture this data less often to minimize the size of collected data at the cost of less accuracy. See the [PerformanceAnalysisSampleInterval] table for a list of values that you can use.

Do not expect the data to start flowing into the tables immediately; this will take time to make its way through the system. You can check for population with the following query:


  SELECT TOP (100) *
    FROM dbo.PerformanceAnalysisDataDatabaseCounter
    WHERE PerformanceAnalysisCounterID = 166;

Once you confirm the data is showing up, you should have data for each of the metrics required by the DTU calculator, though you may want to wait top extract it until you have a representative sample from a full workload or business cycle.

If you read through Jason’s blog post, you will see that the data is stored in various rollup tables, and that each of these rollup tables have varying retention rates. Many of these are lower than what I would want if I am analyzing workloads over a period of time. While it may be possible to change these, it may not be the wisest. Because what I am showing you is unsupported, you may want to avoid tinkering too much with SentryOne settings since it could have a negative impact on performance, growth, or both.

In order to compensate for this, I created a script that allows me to extract the data I need for the various rollup tables and stores that data in its own location, so I could control my own retention and not interfere with SentryOne functionality.

TABLE: dbo.AzureDatabaseDTUData

I created a table called [AzureDatabaseDTUData] and stored it in the SentryOne database. The procedure I created will automatically generate this table if it does not exist, so there is no need to do this manually unless you want to customize where it is stored. You can store this in a separate database if you like, you would just need to edit the script in order to do so. The table looks like this:

CREATE TABLE dbo.AzureDatabaseDTUdata
(
      ID           bigint identity(1,1) not null,
      DeviceID     smallint not null,
      [TimeStamp]  datetime not null,
      CounterName  nvarchar(256) not null,
      [Value]      float not null,
      InstanceName nvarchar(256) not null,
      CONSTRAINT   PK_AzureDatabaseDTUdata PRIMARY KEY (ID)
);

Procedure: dbo.Custom_CollectDTUDataForDevice

This is the stored procedure that you can use to pull all of the DTU-specific data at one time (provided you have been collecting the log bytes counter for a sufficient amount of time), or schedule it to periodically add to the collected data until you are ready to submit the output to the DTU calculator. Like the table above, the procedure is created in the SentryOne database, but you could easily create it elsewhere, just add three- or four-part names to object references. The interface to the procedure is as follows:

CREATE PROCEDURE [dbo].[Custom_CollectDTUDataForDevice]
    @DeviceID     smallint = -1,
    @DaysToPurge  smallint = 14,

    -- These define the CounterIDs in case they ever change. 
    @ProcessorCounterID     smallint = 1858, -- Processor (Default)
    @DiskReadCounterID      smallint = 64,   -- Disk Read/Sec (DiskCounter)
    @DiskWritesCounterID    smallint = 67,   -- Disk Writes/Sec (Diskcounter)
    @LogBytesFlushCounterID smallint = 166,  -- Log Bytes Flushed/Sec (DatabaseCounter)
AS
...

Note: The entire procedure is a little long, so it is attached to this post (dbo.Custom_CollectDTUDataForDevice.sql_.zip).

There are a couple of parameters you can use. Each has a default value, so you do not have to specify them if you are fine with the default values.

• @DeviceID – This allows you to specify if you want to collect data for a specific SQL Server or everything. The default is -1, which means copy all watched SQL Servers. If you only want to export information for a specific instance, then find the DeviceID corresponding to the host in the [dbo].[Device] table, and pass that value. You can only pass one @DeviceID at a time, so if you want to pass through a set of servers, you can call the procedure multiple times, or you can modify the procedure to support a set of devices.
• @DaysToPurge – This represents the age at which you want to remove data. The default is 14 days, meaning that you will only pull data up to 14 days old, and any data older than 14 days in your custom table will be deleted.

The other four parameters are there for future-proofing, in case the SentryOne enums for counter IDs ever changes.

A couple of notes on the script:

1. When the data is pulled, it takes the max value from the truncated minute and exports that. This means there is one value per metric per minute, but it is the max value captured. This is important because of the way the data needs to be presented to the DTU calculator.
2. The first time you run the export, it may take a little longer. This is because it pulls all the data it can based on your parameter values. Each additional run, the only data extracted is whatever is new since the last run, so should be much faster.
3. You will need to schedule this procedure to run on a time schedule that stays ahead of the SentryOne purge process. What I have done is just created a SQL Agent Job to run nightly that collects all the new data since the night before.
4. Because the purge process in SentryOne can vary based on metric, you could end up with rows in your copy that do not contain all 4 counters for a time period. You may want to only start analyzing your data from the time you start your extraction process.
5. I used a block of code from existing SentryOne procedures to determine the rollup table for each counter. I could have hard-coded the current names of the tables, however, by using the SentryOne method, it should be forward compatible with any changes to the built-in rollup processes.

Once your data is being moved into a standalone table, you can use a PIVOT query to transform it into the form that the DTU calculator expects.

Procedure: dbo.Custom_ExportDataForDTUCalculator

I created another procedure to extract the data into CSV format. The code for this procedure is also attached (dbo.Custom_ExportDataForDTUCalculator.sql_.zip).

There are three parameters:

• @DeviceID – Smallint corresponding to one of the devices you are collecting and that you want to submit to the calculator.
• @BeginTime – Datetime representing the start time, in local time; for example, '2018-12-04 05:47:00.000'. The procedure will translate to UTC. If omitted, it will collect from the earliest value in the table.
• @EndTime – Datetime representing the end time, again in local time; for example, '2018-12-06 12:54:00.000'. If omitted, it will collect up to the latest value in the table.

An example execution, to get all the data collected for SQLInstanceA between December 4th at 5:47 AM and December 6th at 12:54 PM.

EXEC SentryOne.dbo.custom_ExportDataForDTUCalculator 
    @DeviceID    = 12, 
    @BeginTime   = '2018-12-04 05:47:00.000', 
    @EndTime     = '2018-12-06 12:54:00.000';

The data will need to be exported to a CSV file. Do not be concerned about the data itself; I made sure to output results so that there is no identifying information about your server in the csv file, just dates and metrics.

If you run the query in SSMS, you can right-click and export results; however, you have limited options here and you will have to manipulate the output to get the format expected by the DTU calculator. (Feel free to try and let me know if you find a way to do this.)

I recommend just using the export wizard baked into SSMS. Right-click on the database and go to Tasks -> Export Data. For your Data Source use “SQL Server Native Client” and point it at your SentryOne database (or wherever you have your copy of the data stored). For your destination, you will want to select “Flat File Destination.” Browse to a location, give the file a name, and save the file as a CSV.

Take care to leave the code page alone; some may return errors. I know that 1252 works fine. The rest of the values leave as default.

On the next screen, select the option Write a query to specify the data to transfer.

In the next window, copy the procedure call with your parameters set into it. Hit next.

When you get to the Configure Flat File Destination, I leave the options as default. Here is a screenshot in case yours are different:

Hit next and run immediately. A file will be created that you will use on the last step.

NOTE: You could create a SSIS package to use for this and then pass through your parameter values to the SSIS package if you are going to be doing this a lot. This would prevent you from having to go through the wizard each time.

Navigate to the location where you saved the file and verify it is there. When you open it, it should look something like this:

Open the DTU calculator web site, and scroll down to the portion that says, “Upload the CSV file and Calculate.” Enter the number of Cores that the server has, upload the CSV file, and click Calculate. You'll get a set of results like this (click any image to zoom):

Since you have the data being stored separately, you can analyze workloads from varying times, and you can do this without having to manually run\schedule the command utility\powershell script for any server you are already using SentryOne to monitor.

To briefly recap the steps, here is what needs to be done:

1. Enable the [Database – Log Bytes Flushed/sec] counter, and verify the data is being collected
2. Copy the data from the SentryOne tables into your own table (and schedule that where appropriate).
3. Export the data from new table in the right format for the DTU calculator
4. Upload the CSV to the DTU Calculator

For any server/instance you are considering migrating to the cloud, and that you are currently monitoring with SQL Sentry, this is a relatively pain-free way to estimate both what type of service tier you'll need and how much that will cost. You'll still need to monitor it once it's up there though; for that, check out SentryOne DB Sentry.

About the Author

Dustin Dorsey is currently the Managing Database Engineer for LifePoint Health in which he leads a team responsible for managing and engineering solutions in database technologies for 90 hospitals. He has been working with and supporting SQL Server predominantly in healthcare since 2008 in an administration, architecture, development, and BI capacity. He is passionate about finding ways to solve problems that plague the everyday DBA and loves sharing this with others. He can be found speaking at SQL community events, as well as blogging at DustinDorsey.com.

The post Sending SentryOne Data to the Azure SQL Database DTU Calculator appeared first on SQLPerformance.com.

PSDatabaseClone is a PowerShell module that has the ability to create images of databases ("clones") and distribute those clones to one or more hosts.

Why use this module?

Database administrators, and really anyone who has to deal with data provisioning, have users that want to work with our databases. This can be to develop new solutions, to extract the data for ETL processes, simulate performance issues, retrieve data for reporting purposes, and so on.

Provisioning the data to other locations can be a very tedious task and most of us spend a lot of time and resources performing that task.

Known solutions to provision the data is by restoring databases or developing some extensive scripting to solve this problem. These solutions are not always the most efficient way of provisioning data and can take a considerate amount of time.

Besides the time it costs to get the data to the other locations, it can also cost a lot of disk space.

What if there was a way to reduce the amount of time it takes to provision the data and, at the same time, reduce the amount of resources needed? Now there is a solution for that and it’s called PSDatabaseClone.

How to install the module

The installation of the module can be accomplished in several ways.

PowerShellGallery

The easiest way is by using the command Install-Module. The command looks for the module in the PowerShell Gallery. Execute the following code to install the module using the PowerShell Gallery:

Install-Module PSDatabaseClone

Using this method also makes it easier to update the module when a new version gets released. Just use the Update-Module command like below to update it to the latest version:

Update-Module PSDatabaseClone

Cloning the repository

Another way to install the module is by cloning the Git repository to your local machine. I would advise people who want to contribute to the project to use this method.

Execute the following command:

git clone https://github.com/sanderstad/PSDatabaseClone.git

This will download the entire repository to your local machine from which you can use the module or develop new features for it.

How does the module work?

Images

The goal of the module was to use as minimum external applications as possible, so everyone can use the module without having to install any extras. The module works by using native technology in Windows to create virtual hard disks (VHDs). A VHD is a file format that represents a hard disk and can contain volumes, partitions which in turn can contain files and folders. VHDs can be distributed to other hosts and mounted as an additional disk, much like plugging in an external USB drive. Once connected, the host can access the files and folders like any other disk on the system.

The module creates a VHD which will be our so-called image. The image will contain all the files and folders that are necessary for the database.

During the process of creating an image, the module will get a backup from the database you’ve specified and restore the database into a VHD.

After the restore, the VHD is closed and unmounted and now contains all the files needed to clone the database.

Figure 1: Process of creating an image

Clones

To create a clone, the module creates a “differencing disk” which contains the same files and folders as the parent disk, but only saves the delta between parent and child.

That functionality immediately saves disk space because only a fraction of the original database size is used for the clone.

Another advantage is that, because the clone saves the delta of the changes, the image itself is not affected by any of the changes. This means that other clones are also not affected.

The process of creating a clone is to create a differencing disk inside a created image (parent disk). The clone is mounted and the database inside the clone is attached to an instance.

From that moment on, the database is operational in the same way the original one would be. It contains the same objects, data, and behaviors. Because all the objects are in the database like the original database, query plans and query statistics will act the same.

This gives the user or developer the chance to create queries that will run the same on production as on their development system, reducing the risk of running inefficient queries.

Creating the images and clones

Create an image

The first step of the process is to create an image. To create an image you have the following prerequisites:

• Source instance
• Destination instance (can be the same as the source instance)
• Database
• Network path
• Backup

The command that creates the image is: New-PSDCImage.

It’s very important to use a network path to the images to make it possible for other hosts to find the image and use it to create a clone.

When the following command is executed:

New-PSDCImage -SourceInstance SQLDB1 -DestinationInstance SQLDB1 -ImageNetworkPath \\sqldb1\psdatabaseclone\images -Database DB1 -CreateFullBackup

You’ll get the following result:

This command was executed on 2018-07-24 around 9 PM, which explains the dates in the image

In this example the command looks at the instance SQLDB1 for a particular database and tries to create an image using the same instance. It will create a new full backup to get the latest data.

The result is an image located in the network share \\sqldb1\psdatabaseclone\images called DB1_20180724214146.vhdx.

The numbers in the name of the image are a timestamp at which time the image was created.

Creating a clone

The clone is dependent on the image and the easiest way to create a clone from a particular database is to let the module figure that out for you.

Executing the following command:

New-PSDCClone -SqlInstance SQLDB3 -Database DB1 -CloneName DB1_Clone2 -LatestImage

Will create a similar result like below:

The command just created a differential disk on another instance. The clone is based on the latest image for database DB1, and the clone will be named “DB1_Clone2.”

Because we did not supply a destination, the command will lookup the default data location of the SQL Server instance.

Within that directory it will create a subdirecory called “clone”. The clone will be placed within that folder.

Retrieving information

During the setup of the module, you assigned a location where all the clones can be found. This was either a database or a directory, and more specifically a share, where the configuration files are written.

To retrieve the information you can use the commands Get-PSDCImage and Get-PSDCClone. These commands will retrieve the information from the information store and return it back to the user.

Image information

To get the image information you have to use the command Get-PSDCCimage. This will give you a result similar as below:

You see that there can be multiple images from the same database which each could have different data.

Clone information

Similar as to retrieving image information, you can get clone information. Use the command Get-PSDCClone and you will get a similar result as below:

What’s next?!

The next thing with PSDatabaseClone is to make it possible to obfuscate the data in the images to make it suited for enterprise environments. We all know about the data breach incidents lately and we want to make sure our data, at least the data that is not on premise, is masked in such a way that nobody would know what to do with it.

How can you help?

PSDatabaseClone is an open source PowerShell module and everybody who’s interested can help to improve the module. If you’re familiar with developing PowerShell functions and/or modules you can help out developing new features, fix bugs (I’m sure there are bugs somewhere). If you’re not comfortable developing the code, don’t worry; there is plenty of stuff to do.

You can help test the different functions and try to break them. I, as one person, can only go so far and most of the time I test the happy flow of my programs. You probably test in a different way and get other results that I would never have thought of. This will help make the module better.

You can also help think of new features to add to the current solution. You may have a great idea for new functionality which I could not have thought of on my own.

If you have an idea for a new feature or found a bug, go to the github page and create a feature request or a bug report. It is greatly appreciated.

Information

Website: https://psdatabaseclone.org

Github: https://github.com/sanderstad/PSDatabaseClone

About the Author

Sander has been working with SQL Server since version 2000.
He has worked in a variety of industries, from agriculture, financial industries and healthcare.
He has been working with PowerShell since version 1 and is embracing automation where he can.

The post Cloning Databases with PSDatabaseClone appeared first on SQLPerformance.com.

Guest Author : Andy Mallon (@AMtwo)

I was recently doing a training session when a developer commented that it was OK to run an expensive query twice because on the second execution, SQL Server would use the "results cache" and be "practically free". It's not the first time I've heard someone refer to a "results cache" in SQL Server. This is one of those myths that is almost true, which makes it that much more believable. If you don't know better, you might think SQL Server has a "results cache" because the second execution of a query is often faster.

SQL Server does not have a "results cache" and the second execution is not "practically free."
SQL Server does have a "buffer cache" and the second execution is "faster, but not free."

The SQL Server buffer cache holds data pages in memory, in the exact form that they reside on disk. The second execution will not have to perform physical I/O operations to satisfy the query, because it can use the buffer cache. However, it does have to perform all other operations. Think of it like this: the second execution still executes the entire execution plan, including all the expensive operations. It is faster, but not "practically free."

Let's create a simple example to demonstrate that the second execution can still be slow and expensive.

First, I'll create a table and put some data in it. Imagine this is some big table from a real life system you work on.

CREATE TABLE dbo.SomeBigTable (
    ID INT IDENTITY(1,1),
    PretendRowData NCHAR(4000),
    CONSTRAINT PK_SomeBigTable PRIMARY KEY CLUSTERED (ID)
);

INSERT INTO dbo.SomeBigTable (PretendRowData)
SELECT REPLICATE(N'x',4000)
FROM sys.columns c1, sys.columns c2;

On my test database, this creates a 7.6 GB table. That'll be plenty big to test at scale! Let's start simple. To level the playing field, I'll clear out the cache:

-- Start with a clean cache
DBCC DROPCLEANBUFFERS

Then, I'll run a query three times that pulls back a single row. The first execution will be slow, then the next two will be fast right?

-- Run the query thrice; The second & third executions are free, right?
SELECT PretendRowData
FROM dbo.SomeBigTable
WHERE ID = 1000000;
GO 3

Whoa, all three were super fast. If I run these three executions in Plan Explorer, I see that the first execution consistently shows a duration of 1-2 milliseconds, and the next two executions show a duration is blank. Plan Explorer shows blank duration when the execution time is sub-millisecond. That looks like the myth is true after all! The results cache exists! Those executions were completely free!

Let's try again with a more expensive query. I'm going to force a table scan by applying a function to the ID column. This query gives the exact same result set as the first one, it just uses a different execution plan to get there. Again, we'll clear the cache first, then run the query three times.

-- Start with a clean cache, again
DBCC DROPCLEANBUFFERS
GO
-- Run the query thrice; The second & third executions are free, right?
SELECT PretendRowData
FROM dbo.SomeBigTable
WHERE CONVERT(varchar(10),ID) = 1000000;
GO 3

This query is quite a bit slower. Plan Explorer shows that the execution time on the first execution was 11 seconds, and the next two take around 9 seconds. Faster, but not free.

The query result is the same PretendRowData on all six executions of both flavors of this query. If there were a "results cache" then at least four of the executions should have come from that cache and been "practically free". The second flavor of the query performs a table scan, so it has to read all 7.6GB of data in order to return the single-row result. In fact, Plan Explorer shows that the significant CPU & (logical) I/O cost is equal for all three executions. The query is faster, because the table is in memory in the buffer cache, but it's hardly free.

If you have expensive queries where you want to use cached query results, there are some great technologies that are purpose built just for that. Two popular memory caching products are Redis and Memcached. These memory-optimized caches scale horizontally, making them well-suited for helping to offload expensive, frequent, read-only queries from your production database.

About the Author

Andy Mallon is a SentryOne Product Manager and Microsoft Data Platform MVP. Andy's background is as a DBA supporting high-volume, highly-available OLTP environments with demanding performance needs. Over his career he has managed databases in the healthcare, finance, e-commerce, and non-profit sectors. Andy is the founder of BostonSQL, past organizer of SQLSaturday Boston, and blogs at am2.co.

The post Performance Myths: The query result cache appeared first on SQLPerformance.com.

Guest Author : Bert Wagner (@bertwagner)

Join elimination is one of the many techniques that the SQL Server query optimizer uses to create efficient query plans. Specifically, join elimination occurs when SQL Server can establish equality by using query logic or trusted database constraints to eliminate unnecessary joins. See a full video version of this post on my YouTube channel.

Join Elimination In Action

The simplest way to explain join elimination is through a series of demos. For these examples I’ll be using the WideWorldImporters demo database.

To start things off, we’ll look at how join elimination works when a foreign key is present:

SELECT
  	il.*
  FROM
  	Sales.InvoiceLines il
  	INNER JOIN Sales.Invoices i
  		ON il.InvoiceID = i.InvoiceID;

In this example, we are returning data only from Sales.InvoiceLines where a matching InvoiceID is found in Sales.Invoices. While you might expect the execution plan to show a join operator on the Sales.InvoiceLines and Sales.Invoices tables, SQL Server never bothers looking at Sales.Invoices at all:

SQL Server avoids joining to the Sales.Invoices table because it trusts the referential integrity maintained by the foreign key constraint defined on InvoiceID between Sales.InvoiceLines and Sales.Invoices; if a row exists in Sales.InvoiceLines, a row with the matching value for InvoiceID must exist in Sales.Invoices. And since we are only returning data from the Sales.InvoiceLines table, SQL Server doesn’t need to read any pages from Sales.Invoices at all.

We can verify that SQL Server is using the foreign key constraint to eliminate the join by dropping the constraint and running our query again:

ALTER TABLE [Sales].[InvoiceLines]  
DROP CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices];

With no information about the relationship between our two tables, SQL Server is forced to perform a join, scanning an index on our Sales.Invoices table to find matching InvoiceIDs.

From an I/O standpoint, SQL Server must read an extra 124 pages from an index on the Sales.Invoices table, and that’s only because it is able to use a narrow (single column) index created by a different foreign key constraint. This scenario could play out much worse on larger tables or tables that are not indexed appropriately.

Limitations

While the previous example shows the basics of how join elimination works, we need to be aware of a few caveats.

First, let’s add back our foreign key constraint:

ALTER TABLE [Sales].[InvoiceLines]  
  WITH NOCHECK ADD  CONSTRAINT 
  [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices] FOREIGN KEY([InvoiceID])
REFERENCES [Sales].[Invoices] ([InvoiceID]);

If we run our sample query again, we’ll notice that we don’t get a plan that exhibits join elimination; instead we get a plan that scans both of our joined tables.

The reason this occurs is because, when we re-added our foreign key constraint, SQL Server doesn’t know if any data has been modified in the meantime. Any new or changed data may not adhere to this constraint, so SQL Server can’t trust the validity of our data:

SELECT
	f.name AS foreign_key_name
	,OBJECT_NAME(f.parent_object_id) AS table_name
	,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
	,OBJECT_NAME (f.referenced_object_id) AS referenced_object
	,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
	,f.is_not_trusted
FROM 
	sys.foreign_keys AS f
	INNER JOIN sys.foreign_key_columns AS fc
		ON f.object_id = fc.constraint_object_id
WHERE 
	f.parent_object_id = OBJECT_ID('Sales.InvoiceLines');

To re-establish SQL Server’s trust of this constraint, we must check its validity:

ALTER TABLE [Sales].[InvoiceLines] 
WITH CHECK CHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices];

On large tables, this operation may take some time, not to mention the overhead of SQL Server validating this data during every insert/update/delete modification going forward.

Another limitation is that SQL Server cannot eliminate joined tables when the query needs to return any data from those potential elimination candidates:

SELECT
	il.*,
	i.InvoiceDate
FROM
	Sales.InvoiceLines il
	INNER JOIN Sales.Invoices i
		ON il.InvoiceID = i.InvoiceID;

Join elimination doesn’t occur in the query above because we are requesting that data from Sales.Invoices is returned, forcing SQL Server to read data from that table.

Finally, it’s important to note that join elimination will not occur when the foreign key has multiple columns, or if the tables are in tempdb. The latter is one of several reasons you shouldn’t try to solve optimization issues by copying your tables into tempdb.

Additional Scenarios

Multiple Tables

Join elimination isn’t only limited to two-table inner joins and tables with foreign key constraints.

For example, we can create an additional table that references our Sales.Invoices.InvoiceID column:

CREATE TABLE Sales.InvoiceClickTracking
  (
  	InvoiceClickTrackingID bigint IDENTITY PRIMARY KEY,
  	InvoiceID int
  	-- other fields would go here 
  );  
GO

ALTER TABLE [Sales].[InvoiceClickTracking]  WITH CHECK 
    ADD  CONSTRAINT [FK_Sales_InvoiceClickTracking_InvoiceID_Sales_Invoices] 
    FOREIGN KEY([InvoiceID])
  REFERENCES [Sales].[Invoices] ([InvoiceID]);

Joining this table into our original sample query will also allow SQL Server to eliminate our Sales.Invoices table:

SELECT 
  	il.InvoiceID,
  	ict.InvoiceID
  FROM
  	Sales.InvoiceLines il
  	INNER JOIN Sales.Invoices i
  		ON il.InvoiceID = i.InvoiceID
  	INNER JOIN Sales.InvoiceClickTracking ict
  		ON i.InvoiceID = ict.InvoiceID;

SQL Server can eliminate the Sales.Invoices table because of the transitive association between these tables’ relationships.

Unique Constraints

Instead of a foreign key constraint, SQL Server will also perform join elimination if it can trust the data relationship with a unique constraint:

ALTER TABLE [Sales].[InvoiceClickTracking] 
  DROP CONSTRAINT [FK_Sales_InvoiceClickTracking_InvoiceID_Sales_Invoices];
  GO
  
ALTER TABLE Sales.InvoiceClickTracking
  ADD CONSTRAINT UQ_InvoiceID UNIQUE (InvoiceID);   
GO 

  SELECT 
  	i.InvoiceID
  FROM
  	Sales.InvoiceClickTracking ict
  	RIGHT JOIN Sales.Invoices i
  		ON ict.InvoiceID = i.InvoiceID;

Outer Joins

As long as SQL Server can infer relationship constraints, other types of joins can experience table elimination as well. For example:

SELECT
	il.InvoiceID
FROM
	Sales.InvoiceLines il
	LEFT JOIN Sales.Invoices i
		ON il.InvoiceID = i.InvoiceID

Since we still have our foreign key constraint enforcing that every InvoiceID in Sales.InvoiceLines must have a corresponding InvoiceID in Sales.Invoices, SQL Server has no problem returning everything from Sales.InvoiceLInes without the need to join to Sales.Invoices:

No Constraint Required

If SQL Server can guarantee that it won’t need data from a certain table, it can potentially eliminate a join.

No join elimination occurs in this query because SQL Server can’t identify if the relationship between Sales.Invoices and Sales.InvoiceLines is 1-to-1, 1-to-0, or 1-to-many. It is forced to read Sales.InvoiceLines to determine if any matching rows are found:

SELECT
	i.InvoiceID
FROM
	Sales.InvoiceLines il
	RIGHT JOIN Sales.Invoices i
		ON il.InvoiceID = i.InvoiceID;

However, if we specify that we want a DISTINCT set of i.InvoiceIDs, every unique value from Sales.Invoices returns from SQL Server regardless of what relationship those rows have with Sales.InvoiceLines.

-- Just to prove no foreign key is at play here

ALTER TABLE [Sales].[InvoiceLines] 
DROP CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices];
GO

-- Our distinct result set
SELECT DISTINCT
	i.InvoiceID
FROM
	Sales.InvoiceLines il
	RIGHT JOIN Sales.Invoices i
		ON il.InvoiceID = i.InvoiceID;

Views

One advantage of join elimination is that it can work with views, even if the underlying view query is not able to use join elimination:

-- Add back our FK

ALTER TABLE [Sales].[InvoiceLines]    
WITH CHECK ADD  CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices] 
FOREIGN KEY([InvoiceID])
REFERENCES [Sales].[Invoices] ([InvoiceID]);
GO

-- Create our view using a query that cannot use join elimination
CREATE VIEW Sales.vInvoicesAndInvoiceLines
AS
	SELECT
		i.InvoiceID,
		i.InvoiceDate,
		il.Quantity,
		il.TaxRate
	FROM
		Sales.InvoiceLines il
		INNER JOIN Sales.Invoices i
			ON il.InvoiceID = i.InvoiceID;
GO

-- Join elimination works because we do not select any 
-- columns from the underlying Sales.Invoices table

SELECT Quantity, TaxRate FROM Sales.vInvoicesAndInvoiceLines;

Conclusion

Join elimination is an optimization that SQL Server performs when it determines it can provide an accurate result set without needing to read data from all tables specified in the submitted query. This optimization can provide significant performance improvements by reducing the number of pages SQL Server has to read, however it often comes at the expense of needing to maintain certain database constraints. We can refactor queries to achieve the simpler execution plans that join elimination provides, however having the query optimizer automatically simplify our plans by removing unnecessary joins is a nice benefit.

Again, I invite you to watch the full video version of this post.

About the Author

Bert is a business intelligence developer from Cleveland, Ohio. He loves writing fast-running queries and enjoys helping others learn to be self-sufficient SQL problem solvers. Bert blogs about SQL Server at bertwagner.com and creates SQL Server YouTube videos at youtube.com/c/bertwagner.

The post Join Elimination: When SQL Server Removes Unnecessary Tables appeared first on SQLPerformance.com.

If you're familiar with supporting the database behind Microsoft Dynamics CRM, you probably know that it's not the fastest-performing database. Honestly, that shouldn't be a surprise–it's not designed to be a screaming-fast database. It's designed to be a flexible database. Most Customer Relationship Management (CRM) systems are designed to be flexible so that they can meet the needs of many businesses in many industries with vastly different business requirements. They put those requirements ahead of database performance. That's probably smart business, but I'm not a business person–I'm a database person. My experience with Dynamics CRM is when people come to me and say

Andy, the database is slow

One recent occurrence was with a report failing due to a 5-minute query timeout. With the proper indexes, we should be able to get a few hundred rows really fast. I got my hands on the query and some example parameters, dropped it into Plan Explorer, and ran it a few times in our Test environment (I'm doing all this in Test–that's going to be important later). I wanted to make sure I was running it with a warm cache, so that I could use "the best of the worst" for my benchmark. The query was a big nasty SELECT with a CTE, and a bunch of joins. Unfortunately, I can't provide the exact query, since it had some customer-specific business logic (Sorry!).

7 minutes, 37 seconds is as good as it gets.

Right off the bat, there's a lot of bad going on here. 1.5 million reads is a heck of a lot of I/O. 457 seconds to return 200 rows is slow. The Cardinality Estimator expected 2 rows, instead of 200. And there were a lot of writes–since this query is only a SELECT statement, this means we must be spilling to TempDb. Maybe I'll get lucky, and be able to create an index to eliminate a table scan and speed this thing up. What's the plan look like?

Looks like an apatosaurus, or maybe a giraffe.

There will be no quick hits

Let me pause for a moment to explain something about Dynamics CRM. It uses views. It uses nested views. It uses nested views to enforce row-level security. In Dynamics parlance, these row-level-security-enforcing nested views are called "filtered views." Every query from the application goes through these filtered views. The only "supported" way to perform data access is to use these filtered views.

Recall I said this query was referencing a bunch of tables? Well, it's referencing a bunch of filtered views. So the complicated query I was handed is actually several layers more complicated. At this point, I got a fresh cup of coffee, and switched to a bigger monitor.

A great way to solve problems is to start at the start. I zoomed in on the SELECT operator, and followed the arrows to see what was going on:

Even on my 34" ultra-wide monitor, I had to fiddle with the display settings for the plan to see this much. Plan Explorer can rotate plans 90 degrees to make "tall" plans fit on a wide monitor.

Look at all those table-valued function calls! Followed immediately by a really expensive hash match. My Spidey Sense started to tingle. What is fn_GetMaxPrivilegeDepthMask, and why is it being called 30 times? I bet this is a problem. When you see "Table-valued function" as an operator in a plan, that actually means it's a multi-statement table-valued function. If it were an inline table-valued function, it would get incorporated into the larger plan, and not be a black box. Multi-statement table-valued functions are evil. Don't use them. The Cardinality Estimator isn't able to make accurate estimates. The Query Optimizer isn't able to optimize them in the context of the larger query. From a performance perspective, they don't scale.

Even though this TVF is an out-of-the-box piece of code from Dynamics CRM, my Spidey Sense tells me that it's the problem. Forget this big nasty query with a big scary plan. Lets step into that function and see what's going on:

create function [dbo].[fn_GetMaxPrivilegeDepthMask](@ObjectTypeCode int) 
returns @d table(PrivilegeDepthMask int)
-- It is by design that we return a table with only one row and column
as
begin
	declare @UserId uniqueidentifier
	select @UserId = dbo.fn_FindUserGuid()

	declare @t table(depth int)

	-- from user roles
	insert into @t(depth)	
	select
	--privilege depth mask = 1(basic) 2(local) 4(deep) and 8(global) 
	-- 16(inherited read) 32(inherited local) 64(inherited deep) and 128(inherited global)
	-- do an AND with 0x0F ( =15) to get basic/local/deep/global
		max(rp.PrivilegeDepthMask % 0x0F)
	   as PrivilegeDepthMask
	from 
		PrivilegeBase priv
		join RolePrivileges rp on (rp.PrivilegeId = priv.PrivilegeId)
		join Role r on (rp.RoleId = r.ParentRootRoleId)
		join SystemUserRoles ur on (r.RoleId = ur.RoleId and ur.SystemUserId = @UserId)
		join PrivilegeObjectTypeCodes potc on (potc.PrivilegeId = priv.PrivilegeId)
	where 
		potc.ObjectTypeCode = @ObjectTypeCode and 
		priv.AccessRight & 0x01 = 1

	-- from user's teams roles
	insert into @t(depth)	
	select
	--privilege depth mask = 1(basic) 2(local) 4(deep) and 8(global) 
	-- 16(inherited read) 32(inherited local) 64(inherited deep) and 128(inherited global)
	-- do an AND with 0x0F ( =15) to get basic/local/deep/global
		max(rp.PrivilegeDepthMask % 0x0F)
	   as PrivilegeDepthMask
	from 
		PrivilegeBase priv
        join RolePrivileges rp on (rp.PrivilegeId = priv.PrivilegeId)
        join Role r on (rp.RoleId = r.ParentRootRoleId)
        join TeamRoles tr on (r.RoleId = tr.RoleId)
        join SystemUserPrincipals sup on (sup.PrincipalId = tr.TeamId and sup.SystemUserId = @UserId)
        join PrivilegeObjectTypeCodes potc on (potc.PrivilegeId = priv.PrivilegeId)
	where 
		potc.ObjectTypeCode = @ObjectTypeCode and 
		priv.AccessRight & 0x01 = 1
	
	insert into @d select max(depth) from @t
	return	
end		
GO

This function follows a classic pattern in multi-statement TVFs:

• Declare a variable that is used as a constant
• Insert into a table variable
• Return that table variable

There's nothing fancy going on here. We could re-write these multiple statements as a single SELECT statement. If we can write it as a single SELECT statement, we can re-write this as an inline TVF.

Let's do it

If it isn't obvious, I'm about to re-write code provided by a software vendor. I've never met a software vendor that considers this to be "supported" behavior. If you change the out-of-the-box application code, you are on your own. Microsoft certainly considers this "unsupported" behavior for Dynamics. I'm going to do it anyway, since I'm using the test environment and I'm not playing around in production. Re-writing this function took just a couple minutes–so why not give it a try and see what happens? Here's what my version of the function looks like:

create function [dbo].[fn_GetMaxPrivilegeDepthMask](@ObjectTypeCode int) 
returns table
-- It is by design that we return a table with only one row and column
as
RETURN
	-- from user roles
	select PrivilegeDepthMask = max(PrivilegeDepthMask) 
	    from	(
	    select
            --privilege depth mask = 1(basic) 2(local) 4(deep) and 8(global) 
	    -- 16(inherited read) 32(inherited local) 64(inherited deep) and 128(inherited global)
	    -- do an AND with 0x0F ( =15) to get basic/local/deep/global
		    max(rp.PrivilegeDepthMask % 0x0F)
	       as PrivilegeDepthMask
	    from 
		    PrivilegeBase priv
		    join RolePrivileges rp on (rp.PrivilegeId = priv.PrivilegeId)
		    join Role r on (rp.RoleId = r.ParentRootRoleId)
		    join SystemUserRoles ur on (r.RoleId = ur.RoleId and ur.SystemUserId = dbo.fn_FindUserGuid())
		    join PrivilegeObjectTypeCodes potc on (potc.PrivilegeId = priv.PrivilegeId)
	    where 
		    potc.ObjectTypeCode = @ObjectTypeCode and 
		    priv.AccessRight & 0x01 = 1
        UNION ALL	
	    -- from user's teams roles
	    select
            --privilege depth mask = 1(basic) 2(local) 4(deep) and 8(global) 
	    -- 16(inherited read) 32(inherited local) 64(inherited deep) and 128(inherited global)
	    -- do an AND with 0x0F ( =15) to get basic/local/deep/global
		    max(rp.PrivilegeDepthMask % 0x0F)
	       as PrivilegeDepthMask
	    from 
		    PrivilegeBase priv
            join RolePrivileges rp on (rp.PrivilegeId = priv.PrivilegeId)
            join Role r on (rp.RoleId = r.ParentRootRoleId)
            join TeamRoles tr on (r.RoleId = tr.RoleId)
            join SystemUserPrincipals sup on (sup.PrincipalId = tr.TeamId and sup.SystemUserId = dbo.fn_FindUserGuid())
            join PrivilegeObjectTypeCodes potc on (potc.PrivilegeId = priv.PrivilegeId)
	    where 
		    potc.ObjectTypeCode = @ObjectTypeCode and 
		    priv.AccessRight & 0x01 = 1
        )x
GO

I went back to my original test query, dumped the cache, and re-ran it a few times. Here's the slowest run time, when using my version of the TVF:

That looks much better!

It's still not the most efficient query in the world, but it's fast enough–I don't need to make it any faster. Except… I had to modify Microsoft's code to make it happen. That's not ideal. Let's take a look at the full plan with the new TVF:

Goodbye apatosaurus, hello PEZ dispenser!

It's still a really gnarly plan, but if you look at the start, all those black box TVF calls are gone. The super-expensive hash match is gone. SQL Server gets right down to work without that big bottleneck of TVF calls (the work behind the TVF is now inline with the rest of the SELECT):

Big picture impact

Where is this TVF actually used? Nearly every single filtered view in Dynamics CRM uses this function call. There are 246 filtered views and 206 of them reference this function. It is a critical function as part of the Dynamics row-level security implementation. Virtually every single query from the application to the databases calls this function at least once–usually a few times. This is a two-sided coin: on one hand, fixing this function will likely act as a turbo boost for the entire application; on the other hand, there's no way for me to do regression tests for everything that touches this function.

Wait a second–if this function call is so core to our performance, and so core to Dynamics CRM, then it follows that everyone who uses Dynamics is hitting this performance bottleneck. We opened a case with Microsoft, and I called a few folks to get the ticket bumped along to the engineering team responsible for this code. With a little luck, this updated version of the function will make it into the box (and the cloud) in a future release of Dynamics CRM.

This isn't the only multi-statement TVF in Dynamics CRM–I made the same type of change to fn_UserSharedAttributesAccess for another performance issue. And there are more TVFs that I haven't touched because they haven't caused problems.

A lesson to everyone, even if you're not using Dynamics

Repeat after me: MULTI-STATEMENT TABLE VALUED FUNCTIONS ARE EVIL!

Re-factor your code to avoid using multi-statement TVFs. If you are trying to tune code, and you see a multi-statement TVF, look at it critically. You can't always change the code (or it may be a violation of your support contract if you do), but if you can change the code, do it. Tell your software vendor to stop using multi-statement TVFs. Make the world a better place by eliminating some of these nasty functions from your database.

About the Author

Andy Mallon is a SQL Server DBA and Microsoft Data Platform MVP that has managed databases in the healthcare, finance, e-commerce, and non-profit sectors. Since 2003, Andy has been supporting high-volume, highly-available OLTP environments with demanding performance needs. Andy is the founder of BostonSQL, co-organizer of SQLSaturday Boston, and blogs at am2.co.

The post Multi-Statement TVFs in Dynamics CRM appeared first on SQLPerformance.com.

We recently were surprised by a number of exceptions our application threw. Our application was failing when trying to Open a SqlConnection. The exceptions looked like this:

Connection Pools

Remember that .Net uses connection pools to help avoid the overhead of establishing a connection on every query. Connection pools are maintained for every connection string and by default the number of connections in the pool is capped at a hundred. One hundred connections are usually sufficient. We've never had a problem with this exception before and our servers weren’t any busier than usual so we were hesitant to increase the value of MaxPoolSize. We began to suspect database connection leaks.
 

Database Connection Leaks

Just like memory leaks, database connection leaks can occur if you don't dispose of your database connections in a timely manner. SqlConnections are IDisposable so it’s a best practice to use the using statement:

using (SqlConnection conn = new SqlConnection(connectionString)) 
{
  conn.Open();
  // etc...
}

As soon as you're done with the SqlConnection, it’s disposed and the actual connection immediately returns to the connection pool so it can be used by someone else. Otherwise the connection remains in use until the process ends or garbage collection cleans it up.

Finding Your Connection Leaks

So, if your application experiences connection timeouts because of a database connection leak, the stack traces may not help you. Just like an out-of-memory exception due to a memory leak the stack trace has information about the victim, but not the root cause. So where can you go to find the leak?
 
Even though database connection leaks are a client problem, you can find help from the database server. On the database server, look at connections per process per database to get a rough estimate of the size of each pool:

select count(*) as sessions,
         s.host_name,
         s.host_process_id,
         s.program_name,
         db_name(s.database_id) as database_name
   from sys.dm_exec_sessions s
   where is_user_process = 1
   group by host_name, host_process_id, program_name, database_id
   order by count(*) desc;

Program name, host name, process id and database name are usually good enough to identify connections coming from the same connection pool.

This leads me to ask a few more questions about pools with many connections. Given a pool, are there sessions that have been sleeping for a while and, if so, how long have they been sleeping and what was the last SQL statement they executed?

declare @host_process_id int = 1508;
  declare @host_name sysname = N'SERV4102';
  declare @database_name sysname = N'My_Database';

  select datediff(minute, s.last_request_end_time, getdate()) as minutes_asleep,
         s.session_id,
         db_name(s.database_id) as database_name,
         s.host_name,
         s.host_process_id,
         t.text as last_sql,
         s.program_name
    from sys.dm_exec_connections c
    join sys.dm_exec_sessions s
         on c.session_id = s.session_id
   cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) t
   where s.is_user_process = 1
         and s.status = 'sleeping'
         and db_name(s.database_id) = @database_name
         and s.host_process_id = @host_process_id
         and s.host_name = @host_name
         and datediff(second, s.last_request_end_time, getdate()) > 60
   order by s.last_request_end_time;

The text can now be used to search your application's code base to find where you may have a database connection leak.

These queries are useful for troubleshooting a database connection leak and they can also be used to create a monitor or health check.

Dispose your disposables, use those usings, seal those leaks!

About the Author

Michael J Swart is a passionate database professional and blogger who focuses on database development and software architecture. He enjoys speaking about anything data related, contributing to community projects. Michael blogs as "Database Whisperer" at michaeljswart.com.

The post Find Database Connection Leaks in Your Application appeared first on SQLPerformance.com.

Guest Author : Derik Hammer (@SQLHammer)

 
The differences between TRUNCATE TABLE and DELETE are often misunderstood. I seek to disprove the myth that TRUNCATE TABLE cannot be rolled back:

Reading the manual

The Books Online article on TRUNCATE TABLE is fairly descriptive:

The fact that TRUNCATE TABLE uses fewer transaction log resources implies that it does write to the transaction log to some degree. Let's find out how much and investigate its ability to be rolled back.

Prove it

In an earlier post, Paul Randal goes through this in meticulous detail, but I thought it would be useful to provide very simple repros to disprove both elements of this myth.

Can TRUNCATE TABLE be rolled back?

Proving that TRUNCATE TABLE can be rolled back is easy enough. I will simply put TRUNCATE TABLE in a transaction and roll it back.

USE demo;
BEGIN TRANSACTION;
  SELECT COUNT(*) [StartingTableRowCount] FROM [dbo].[Test];
  TRUNCATE TABLE [dbo].[Test];
  SELECT COUNT(*) [TableRowCountAfterTruncate] FROM [dbo].[Test];
ROLLBACK TRANSACTION;
SELECT COUNT(*) [TableRowCountAfterRollback] FROM [dbo].[Test];

There are 100,000 rows in the table and it returns to 100,000 rows after rollback:

Does TRUNCATE TABLE write to the log?

By executing a CHECKPOINT, we get a clean starting point. Then we can check the log records before and after the TRUNCATE TABLE.

USE demo;
CHECKPOINT;

  SELECT COUNT(*) [StartingLogRowCount]
  FROM sys.fn_dblog (NULL, NULL);

  TRUNCATE TABLE [dbo].[Test];

  SELECT COUNT(*) [LogRowCountAfterTruncate]
  FROM sys.fn_dblog (NULL, NULL);

Our TRUNCATE TABLE command generated 237 log records (at least initially). This is what enables us to perform a rollback, and how SQL Server registers the change to begin with.

What about DELETEs?

If both DELETE and TRUNCATE TABLE write to the log and can be rolled back, what makes them different?

As mentioned in the BOL reference above, TRUNCATE TABLE takes fewer system and transaction log resources. We already observed that 237 log records were written for the TRUNCATE TABLE command. Now, let us look at the DELETE.

USE demo;
CHECKPOINT;

  SELECT COUNT(*) [StartingLogRowCount]
  FROM sys.fn_dblog (NULL, NULL);

  DELETE FROM [dbo].[Test];

  SELECT COUNT(*) [LogRowCountAfterDelete]
  FROM sys.fn_dblog (NULL, NULL);

With over 440,000 log records written for the DELETE, the TRUNCATE command is clearly much more efficient.

Wrap-up

TRUNCATE TABLE is a logged command and can be rolled back, with a huge performance advantage over an equivalent DELETE. DELETE becomes important when you want to delete fewer rows than exist in the table (since TRUNCATE TABLE does not accept a WHERE clause). For some ideas about making DELETEs more efficient, see Aaron Bertrand's post, "Break large delete operations into chunks."

About the Author

Derik is a data professional and freshly-minted Microsoft Data Platform MVP focusing on SQL Server. His passion focuses around high availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned long-term database administration, consulting, and entrepreneurial ventures working in the financial and healthcare industries. He is currently a Senior Database Administrator in charge of the Database Operations team at Subway Franchise World Headquarters. When he is not on the clock, or blogging at SQLHammer.com, Derik devotes his time to the #sqlfamily as the chapter leader for the FairfieldPASS SQL Server user group in Stamford, CT.

The post Performance Myths : Truncate Can't Be Rolled Back appeared first on SQLPerformance.com.

 
Sometimes hardware performance issues, like Disk I/O latency, boil down to non-optimized workload rather than underperforming hardware. Many Database Admins, me included, want to immediately blame the storage for the slowness. Before you go and spend a ton of money on new hardware, you should always examine your workload for unnecessary I/O.

Things to Examine

Unused Indexes

We all know the power of an index. Having the proper indexes can make light years of a difference in query speed. However, how many of us continually maintain our indexes above and beyond index rebuild and reorgs? It’s important to regularly run an index script to evaluate which indexes are actually being used. I personally use Glenn Berry’s diagnostic queries to do this.

You’ll be surprised to find that some of your indexes haven’t been read at all. These indexes are a strain on resources, especially on a highly transactional table. When looking at the results, pay attention to those indexes that have a high number of writes combined with a low number of reads. In this example, you can see I am wasting writes. The non-clustered index has been written to 11 million times, but only read twice.

I start by disabling the indexes that fall into this category, and then drop them after I have confirmed no issues have arisen. Doing this exercise routinely can greatly reduce unnecessary I/O writes to your system, but keep in mind usage statistics on your indexes are only as good as the last reboot, so make sure you have been collecting data for a full business cycle before writing off an index as "useless."

Missing Indexes

Missing Indexes are one of the easiest things to fix; after all, when you run an execution plan, it will tell you if any indexes were not found but that would have been useful. But wait, I hope you’re not just arbitrarily adding indexes based on this suggestion. Doing this can create duplicate indexes, and indexes that may have minimal use, and therefore waste I/O. Again, back to Glenn’s scripts, he gives us a great tool to evaluate the usefulness of an index by providing user seeks, user impact, and number of rows. Pay attention to those with high reads along with low cost and impact. This is a great place to start, and will help you reduce read I/O.

Implicit Conversions

Implicit conversions often happen when a query is comparing two or more columns with different data types. In the below example, the system is having to perform extra I/O in order to compare a varchar(max) column to an nvarchar(4000) column, which leads to an implicit conversion, and ultimately a scan instead of a seek. By fixing the tables to have matching data types, or simply converting this value before evaluation, you can greatly reduce I/O and improve cardinality (the estimated rows the optimizer should expect).

dbo.table1 t1 JOIN dbo.table2 t2 
  ON t1.ObjectName = t2.TableName

Jonathan Kehayias goes into a lot more detail in this great post: " How expensive are column-side Implicit Conversions?"

Functions

One of the most avoidable, easy-to-fix things I’ve run across that saves on I/O expense is removing functions from where clauses. A perfect example is a date comparison, as shown below.

CONVERT(Date,FromDate) >= CONVERT(Date, dbo.f_realdate(MyField))
AND 
(CONVERT(Date,ToDate) <= CONVERT(Date, dbo.f_realdate(MyField))

Whether it is on a JOIN statement or in a WHERE clause this causes each column to be converted before it is evaluated. By simply converting these columns before evaluation into a temp table you can eliminate a ton of unnecessary I/O.

Or, even better, don't perform any conversions at all (for this specific case, Aaron Bertrand talks here about avoiding functions in the where clause, and note that this can still be bad even though convert to date is sargable).

ETL

Take the time to examine how your data is being loaded. Are you truncating and reloading tables? Can you implement Replication, a read only AG Replica, or log shipping instead? Are all the tables being written to actually being read? How are you loading the data? Is it through stored procedures or SSIS? Examining things like this can reduce I/O dramatically.

In my environment, I found that we were truncating 48 tables daily with over 120 million rows each morning. On top of that we were loading 9.6 million rows hourly. You can imagine how much unnecessary I/O that created. In my case, implementing transactional replication was my solution of choice. Once implemented we had far fewer user complaints of slowdowns during our load times, which had initially been attributed to the slow storage.

Order By & Group By

Ask yourself, does that data have to be returned in order? Do we really need to group in the procedure, or can we handle that in a report or application? Order By and Group By operations can cause reads to spill over to disk, which causes additional disk I/O. If these actions are warranted, ensure you have supporting indexes and fresh statistics on the columns being sorted or grouped. This will help the optimizer during plan creation. Since we sometimes use Order By and Group By in temp tables. make sure you have Auto Create Statistics On for TEMPDB as well as your user databases. The more up to date the statistics are, the better cardinality the optimizer can get, resulting in better plans, less spill over, and less I/O.

Now Group By definitely has its place when it comes to aggregating data instead of returning a ton of rows. But the key here is to reduce I/O, the addition of the aggregation adds to the I/O.

Summary

These are just the tip-of-the-iceberg kinds of things to do, but a great place to start to reduce I/O. Before you go blaming hardware on your latency issues take a look at what you can do to minimize disk pressure.

About the Author

Monica Rathbun is currently a Consultant at Denny Cherry & Associates Consulting, and a Microsoft Data Platform MVP. She has been a Lone DBA for 15 years, working with all aspects of SQL Server and Oracle. She travels speaking at SQLSaturdays helping other Lone DBAs with techniques on how one can do the jobs of many. Monica is the Leader of the Hampton Roads SQL Server User Group and is a Mid-Atlantic Pass Regional Mentor. You can always find Monica on Twitter (@SQLEspresso) handing out helpful tips and tricks to her followers. When she’s not busy with work, you will find her playing taxi driver for her two daughters back and forth to dance classes.

The post It’s not you, it’s me (I/O troubleshooting) appeared first on SQLPerformance.com.

Guest Author : Derik Hammer (@SQLHammer)

 
Recently Aaron Bertrand blogged about Harmful, Pervasive SQL Server Performance Myths. As an extension of this blog series, I am going to disprove this common myth:

Reading the manual

Going straight to the source, I looked at the Books Online article on tables which includes table variables. Even though the article references benefits of using table variables, the fact that they are 100% in-memory is conspicuously missing.

A missing affirmative does not imply a negative, however. Since In-Memory OLTP tables were released, there is now a lot more documentation in BOL for in-memory processing. That is where I found this article on making temp table and table variables faster by using memory optimization.

The entire article revolves around how to make your temporary objects use the in-memory OLTP feature, and this is where I found the affirmative I was looking for.

Table variables are not in-memory constructs. In order to use the in-memory technology you have to explicitly define a TYPE which is memory optimized and use that TYPE to define your table variable.

Prove it

Documentation is one thing but seeing it with my own eyes is quite another. I know that temporary tables create objects in tempdb and will write data to disk. First I will show you what that looks like for the temporary tables and then I will use the same method to validate the hypothesis that table variables act the same way.

Log record analysis

This query will run a CHECKPOINT to give me a clean starting point and then show the number of log records and the transaction names which exist in the log.

USE tempdb;
GO

CHECKPOINT;
GO

SELECT COUNT(*) [Count] 
  FROM sys.fn_dblog (NULL, NULL);

SELECT [Transaction Name]
  FROM sys.fn_dblog (NULL, NULL)
  WHERE [Transaction Name] IS NOT NULL;

Running the T-SQL repeatedly resulted in a consistent three record count on SQL Server 2016 SP1.

This creates a temporary table and displays the object record, proving that this is a real object in tempdb.

USE tempdb;
GO

DROP TABLE IF EXISTS #tmp;
GO

CREATE TABLE #tmp (id int NULL);

SELECT name
  FROM sys.objects o
  WHERE is_ms_shipped = 0;

Now I will show the log records again. I will not re-run the CHECKPOINT command.

Twenty one log records were written, proving that these are on-disk writes, and our CREATE TABLE is clearly included in these log records.

To compare these results to table variables I will reset the experiment by running CHECKPOINT and then executing the below T-SQL, creating a table variable.

USE tempdb;
GO

DECLARE @var TABLE (id int NULL);

SELECT name
  FROM sys.objects o
  WHERE is_ms_shipped = 0;

Once again we have a new object record. This time, however, the name is more random than with temporary tables.

There are eighty two new log records and transaction names proving that my variable is being written to the log, and therefore, to disk.

Actually in-memory

Now it is time for me to make the log records disappear.

I created an in-memory OLTP filegroup and then created a memory optimized table type.

USE Test;
GO

CREATE TYPE dbo.inMemoryTableType  
  AS TABLE 
  ( id INT NULL INDEX ix1 )
  WITH (MEMORY_OPTIMIZED = ON);  
GO

I executed the CHECKPOINT again and then created the memory optimized table.

USE Test;
GO

DECLARE @var dbo.inMemoryTableType;

INSERT INTO @var (id) VALUES (1)

SELECT * from @var;  
GO

After reviewing the log, I did not see any log activity. This method is in fact 100% in-memory.

Take away

Table variables use tempdb similar to how temporary tables use tempdb. Table variables are not in-memory constructs but can become them if you use memory optimized user defined table types. Often I find temporary tables to be a much better choice than table variables. The main reason for this is because table variables do not have statistics and, depending upon SQL Server version and settings, the row estimates work out to be 1 row or 100 rows. In both cases these are guesses and become detrimental pieces of misinformation in your query optimization process.

Note that some of these feature differences may change over time – for example, in recent versions of SQL Server, you can create additional indexes on a table variable using inline index syntax. The following table has three indexes; the primary key (clustered by default), and two non-clustered indexes:

DECLARE @t TABLE
(
  a int PRIMARY KEY,
  b int,
  INDEX x (b, a DESC),
  INDEX y (b DESC, a)
);

There is a great answer on DBA Stack Exchange where Martin Smith exhaustively details the differences between table variables and #temp tables:

• What's the difference between a temp table and table variable in SQL Server?

About the Author

Derik is a data professional and freshly-minted Microsoft Data Platform MVP focusing on SQL Server. His passion focuses around high availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned long-term database administration, consulting, and entrepreneurial ventures working in the financial and healthcare industries. He is currently a Senior Database Administrator in charge of the Database Operations team at Subway Franchise World Headquarters. When he is not on the clock, or blogging at SQLHammer.com, Derik devotes his time to the #sqlfamily as the chapter leader for the FairfieldPASS SQL Server user group in Stamford, CT.

The post Performance Myths : Table variables are always in-memory appeared first on SQLPerformance.com.