Monthly Archives: June 2017

Stretch Databases – SQL Server 2016

— by Ginger Keys

Blog_20170629_1

Microsoft has added another cool feature in SQL Server 2016 – Stretch databases – that provides a cost effective way to store historical or ‘cold’ data.  This feature is ideal for transactional databases with large amounts of cold data, typically stored in a small number of tables. Stretching is better than the traditional method of archiving your old data to a remote location because stretched data can still be queried and accessed through applications. The data is always online and there is no need to change anything in your application in order to query it. The end user doesn’t see any difference other than possibly a small amount of latency when querying the stretched data

Features and Advantages:

Some features and benefits of stretching your data to Microsoft Azure:

  • Useful for ‘cold’ or historical data.
  • Your data is still online and available for query.
  • No changes to applications or queries required.
  • Stretching your data to Azure is encrypted and safe – and Always Encrypted, Row Level Security, and other SQL security features are still available
  • Useful for reducing storage space needed on local disk
  • Useful for reducing size of backups and the time required to perform backups.
  • Stretching applies to individual tables – you can stretch either the entire table or selected rows from the table.
  • Select and Insert is supported for stretched data; while Update and Delete is not supported.

 

Limitations:

There are some limitation to stretch databases, and not all databases or tables will be eligible for stretching. Some features and datatypes in a table will prohibit stretching a table in your database. These include

  • Tables with FILESTREAM data
  • Tables replicated or using Change Tracking or Change Data Capture
  • Memory optimized tables
  • Text, ntext, image, timestamp datatypes
  • Sql_variant, geometry or geography, CLR, hierarchyid datatypes
  • COLUMN_SET or computed column types
  • Default or check constraints, or foreign key constraints that reference the table
  • Full text, XML, spatial indexes, or index views that reference the table

Certain limitations also exist for tables that have been stretched:

  • Uniqueness is not enforced with UNIQUE or PRIMARY KEY constraints in the stretched table in Azure
  • You can’t Update or Delete rows in a stretch enabled table or view
  • You can’t Insert rows into a stretched table on a linked server
  • You can’t create an index for a view that includes stretch enabled tables
  • Filters on indexes do not propagate to the remote table.

Steps to set up Stretch Database

  1. Get an Azure account with Microsoft – https://azure.microsoft.com/en-us/free/
    1. Create Azure server
    2. Establish user credentials
    3. Configure firewall rules
  2. Run DMA (Data Migration Assistant) to see if your database is eligible
    1. Download Stretch Database Advisor (DMA) – https://www.microsoft.com/en-us/download/details.aspx?id=53595
  3. Enable Instance
  4. Enable Database
  5. Monitor state of stretch progress

I’m going to assume you have already completed the first two steps and will not go over these. So beginning with step 3, to implement a stretch database you must enable the feature on both the instance and database.

Enable Instance

To enable Stretch Database on your instance run the following statement:

–enable instance for stretch

EXEC sp_configure ‘remote data archive’, ‘1’

GO

RECONFIGURE

GO

Enable Database

From SQL Server Management Studio (SSMS) right click on the database which holds the table(s) you want to stretch to Azure and select Tasks > Stretch > Enable.

Blog_20170629_2

This will open the Enable Database for Stretch Wizard. First step will be to select the table(s) you want to stretch to Azure. You can select the entire table contents, or…

Blog_20170629_3

…if you click on the ‘Entire Table’ link, you can select specific rows to stretch as shown below.

Blog_20170629_4

The next step is to Configure Azure. You will be prompted to sign in to your Azure account:

Blog_20170629_5

After you have signed in to Azure, select the subscription to use (if you have more than one), select the correct region, select the server you have created (or create a new one), and provide the login credentials. Currently Azure only supports SQL Server Authentication for stretch databases.

Blog_20170629_6

In order to stretch a database table to Azure, the database must have a database master key (DMK). Specify (and save) the password for the DMK by creating the credential in the Wizard as follows on the Secure Credentials page.

Blog_20170629_7

If you already have a DMK for your database you will enter the password on this same screen as shown.

Blog_20170629_8

On the Select IP Address page, you can select the ‘Use source SQL Server Public IP’ button or specify the range of subnet IP addresses to use.

Blog_20170629_9

After specifying the IP addresses, click next through the Summary and Results pages.

Blog_20170629_10

Blog_20170629_11

Thank you Microsoft, we will enjoy it!

Monitor your Stretch Progress

In order to see the migration status of your data to the Azure cloud server, run this TSQL statement to show the number of rows migrated:

select * from sys.dm_db_rda_migration_status

order by start_time_utc desc

You can also monitor your stretched database by right clicking on your database > Tasks > Stretch > Monitor as shown

Blog_20170629_12

This will display a report with your Source server info, the Azure server info and information about the tables that were stretched:

Blog_20170629_13

You will also notice in Object Explorer, the stretched database has a different icon beside it

Blog_20170629_14

You will also notice under the External Resources of your database, the Stretched server will be listed in the External Data Sources folder.

Blog_20170629_15

When connecting to your Azure server, you can now see the stretched database which get assigned a name and table name automatically.

Blog_20170629_16

Now What?

Let’s see what happens now when we query data from the Orders table we just stretched.

From your local server instance, select rows from your table as shown. You will notice that the execution plan shows a ‘Remote Query’ operation because it is pulling data from the remote Azure server.

Blog_20170629_17

I only stretched certain rows of data to the Azure cloud, not the entire table. When I run a select statement that only pulls data from my local database and not the stretched Azure data, you will notice the execution plan changes. No Remote Query or concatenation operation is involved because it is only pulling data from the local table.

Blog_20170629_18

Disable Stretch Database

Now if for some reason you need to un-stretch your database you can disable Stretch Database for your entire database or just for an individual table. Disabling stretch database does not delete the table or data from Azure. You have to manually drop it using the Azure management portal, and the remote table in Azure will continue to incur costs until you delete it.

To disable Stretch Database for your database right click on your database in Object Explorer on your local SSMS, select Tasks > Stretch > Disable as shown

Blog_20170629_19

To disable Stretch Database for a table, right click on the table name and select Stretch > Disable > and either Bring data back from Azure, or Leave data in Azure. Bringing data back from Azure will incur data transfer costs.

Blog_20170629_20

Conclusion

Migrating cold or historical data to Microsoft’s Azure can be a cost effective way to retain inactive data, while still having access to it when needed. For information on Azure Stretch Database pricing go here https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/ .   This article covered very basic information on how to set up a Stretch Database, but there are several more aspects such as performance implications and backup/restore processes that we did not cover. Hopefully this gives a starting point on which you can begin to test and explore this new feature.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

 

 

 

 

Installing and Connecting to SQL 2017 on Ubuntu (Linux)

–By Lori Brown

SQL Server 2017 is available as a CTP for testing (https://www.microsoft.com/en-us/sql-server/sql-server-2017) and is able to be installed on a system running Linux. Because I have done all of my professional work on Windows systems, the leap to a Linux system was a bit of a stretch. I learned that while Linux is the OS kernel, Red Hat, SUSE, Ubuntu and Docker Engine are all flavors of Linux. A rough analogy might be that something like Ubuntu is sort of like a version of Windows. Regardless of how you think of it, it is a truly interesting experience to get SQL 2017 installed.

The first thing to do is get your friendly System Admin to stand up a Linux server. We used Ubuntu 16.04 but there are other flavors of Linux that can also be installed as listed below. Your Admin should provide you with a login and password that is an administrator so that you can get SQL running.

Blog_20170621_1

Since everything I have ever done on a computer has had a GUI, I decided to install Gnome to try to shorten my learning curve on Ubuntu. (https://ubuntugnome.org/download/ ) Truthfully, navigating in Gnome was not super intuitive to me but I was able to bumble around and get things done. When following the install instructions from Microsoft, there are places where commands are used to get things done that are labeled “bash”. It took me a bit to figure out that bash is a Unix shell or command prompt that is used to execute commands. I found a bash guide if anyone is interested. (http://www.tldp.org/LDP/Bash-Beginners-Guide/html/Bash-Beginners-Guide.html) Apparently you can use bash on Windows….I learn something new every day. (https://blogs.windows.com/buildingapps/2016/03/30/run-bash-on-ubuntu-on-windows/#igsmYPLj6ovEWf7Y.97)

In Gnome, I had to search for terminal to get the command prompt (bash) window. To get SQL installed, I followed the instructions from Microsoft and have taken screenshots of what it looks like after running commands. (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu)

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

Blog_20170621_2

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

Blog_20170621_3

sudo apt-get update

Blog_20170621_4

sudo apt-get install -y mssql-server

Blog_20170621_5

During the setup completion step, you should be ready to provide the SA password when prompted for the SQL Server system administrator password.

sudo /opt/mssql/bin/mssql-conf setup

Blog_20170621_6

systemctl status mssql-server

Blog_20170621_7

Now install SQLCMD and BCP tools. (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu)  I only installed SQLCMD but the BCP commands are similar.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

Blog_20170621_8Blog_20170621_9

sudo apt-get update

Blog_20170621_10

sudo apt-get install mssql-tools unixodbc-dev

You have to answer licensing questions here so be ready.

Blog_20170621_11

Blog_20170621_12

I also installed the SQL Server Agent. The commands are very similar to previous commands so I did not take any extra screen shots of those. https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-sql-agent

Once the tools are installed, you must use SQLCMD to query. You cannot use SSMS on Ubuntu.

Blog_20170621_13

Since I am a creature of the Microsoft world and really want to use SSMS to do things, I need to be able to connect to SQL on Ubuntu using SSMS. So that you can connect remotely, I made sure to open SQL ports in Ubuntu. First you have to enable the firewall:

Blog_20170621_14

Then open ports 1433 & 1434:

Blog_20170621_15

To connect and query from a Windows machine….install SQL 2017 SSMS. (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms)

Connect to SQL as normal. I tried many times to get connected using my Ubuntu hostname but did not have luck. I was able to connect using the IP address however.

Blog_20170621_16

The Ubuntu hostname appears in the terminal connection. Use ifconfig to get the IP address. (http://www.aboutlinux.info/2006/11/ifconfig-dissected-and-demystified.html)

Blog_20170621_17

Once connected you can do most things as usual in SSMS.

Blog_20170621_18

Blog_20170621_19

Blog_20170621_20

Blog_20170621_21

As of this writing, I found several places where Microsoft employees warned that some things will likely break and that they are working on bugs. So, if you run into anything that you can’t find a way around, let them know. So far Microsoft seems to be very open to information and suggestions which makes me very hopeful that SQL 2017 will be released soon and will be much faster than previous versions.

For a taste of some of the new things in SQL 2017 see: https://blog.sqlrx.com/2017/05/19/sql-2017-is-on-the-way/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

What You Need to Know Before Hosting Your SQL Server in the Cloud

–By Lisa Dominy

cloud-headaches_crop

Whether you currently host your SQL Server in the Cloud or are considering moving it to the cloud, here are a few guidelines that can lead you to select the right hosting provider for your specific requirements.

  1. Set the stage for a tech discussion. Familiarize yourself with your current technology profile so you can have an efficient technical conversation with hosting providers. If you already know your needs in these areas…

CPU-Processors

RAM-memory

Disk Space-Storage

Applications

Database Software Requirements

…you are all set. If not, be sure you are confident that the hosting provider can ask the right questions to provide optimum performance for your company.

  1. Judge whether they will take the time to find out about your business. Find a hosting provider that will take the time to ask the right questions to get to know your business. They should work with you to create a relationship of success. The right hosting provider should be able to provide the right technology and services that meet your business objectives.
  2. Confirm that they provide quick response time. Make sure the hosting provider will provide prompt response from knowledgeable support staff when needed. What is the process they use if an issue comes up? This is a very important step that is sometimes overlooked, and never a good lesson to learn during the move.
  3. Look for hard evidence that they solve problems and issues promptly. It is very important for your hosting provider to solve problems quickly. If a server is down or running slow, this could cause a lot of damage in the long run, as well as lost revenue.
  4. Ask if they have access to SQL experts like ISI’s SQLRx team. Make sure your hosting provider has access to SQL experts. This is very important to the success of your implementation. Why?
  • You’ll need performance information specific to a SQL workload that indicates that hardware may need to be expanded in a specific way.
  • Without a partnership with a SQL expert like SQLRx, it may be difficult and costly to provide enough hardware to support some companies’ workloads. There have been times when our SQLRx team found that the hosting company had provided great hardware but SQL needed tuning or configuration changes.
  • Applications and database software can cause server conflicts no matter how well architected they appear. This is where the right partnership is critical.

Are you ready to go to the cloud? The right hosting company must have access to the right experts and deep experience in the Cloud. This is why we’ve built a strong, trusted relationship with VAZATA. They are the hardware/hosting expert and we are their trusted SQL partner. VAZATA is a very responsive and knowledgeable hosting provider.

Here is an actual client example:

Despite having a very important SQL Server backend, an information services company did not have a SQL Database Administrator, and they had multiple application servers in their environment heavily utilizing that SQL backend. Plus, many of the applications they were running were outdated, but could benefit from better hardware. Based on their needs, there was no cost justification to bring in a full-time SQL Database Administrator. This company leveraged ISI’s SQLRx to provide that service.  VAZATA enhanced their experience when they moved all of the company’s virtual servers into the VAZATA private cloud environment, and the customer immediately started to see better performance. Equally important, based on recommendations from ISI’s SQLRx team, they adjusted some of the infrastructure resources (CPU, RAM, and Storage) to obtain the best performance possible in the most cost-effective way for the amount of users accessing the system.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com or www.vazata.com We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Get a List of Tables That Are Compressed

Find out if any of your tables are compressed in your database using the query below. Compressed tables can save space and make queries run faster.

— Compressed Tables
select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0

If you don’t have any tables compressed but think you might want to compress some, you can check your data compression savings by running the stored procedure sp_estimate_data_compression_savings for your targeted table.

USE WideWorldImporters;
GO
EXEC sp_estimate_data_compression_savings ‘Sales’, ‘Invoices’, NULL, NULL, ‘ROW’ ;
GO

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql

Query Tuning and Missing Index Recommendations

–By Jeffry Schwartz

Overview

Most analysts are familiar with missing index recommendations provided by SQL Server when query plans are displayed within SSMS or reported by various missing index DMVs. Several questions arise concerning these recommendations:

  1. What determines whether a column becomes a key or included in the new index?
  2. Where does the ordering of the included columns come from? Does column ordering in queries affect the recommendations, e.g., will query orderings of A, B, C and C, B, A in the selection column list result in the one or two recommendations?
  3. How sophisticated are the missing index recommendations, i.e., does SQL Server compare recommendations and perform any kind of recommendation consolidation or optimization?

This article provides a reproducible example using six different queries that deliberately caused SQL Server to generate missing index recommendations and provide answers to these questions. The example is used to study missing index recommendations in detail, including how they relate to the underlying table and how query columns affect these recommendations. The article also illustrates how a single consolidated index can address the performance needs of all six queries.

Test Table Creation & Load

To determine missing index recommendation behavior, a generic table was constructed and filled with 20 million records. Each record contained an identity column, an ID column, a text column, and 47 metric columns whose values ranged between 1 and 10,000,000. The large number of table columns was used to insure SQL Server would choose an index option when appropriate. Six queries that incorporated various column combinations were executed (some of which differed only in column ordering). To minimize duplication of column values and skewing of query plans, the ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000 formula was used to generate values that were as random as possible. Two indices were created: a clustered index that used the identity column as its only key and a second nonclustered index that used DupID as its only column. The scripts for the creation, loading, and initial indexing of the table are shown below.

— ##############################################################

— Create test table

— ##############################################################

drop table FewDuplicates;

CREATE TABLE FewDuplicates (

IDCol bigint identity (20000000,1),

DupID bigint,

MyText varchar(10),

Metric01 bigint, Metric02 bigint, Metric03 bigint, Metric04 bigint, Metric05 bigint,

Metric06 bigint, Metric07 bigint, Metric08 bigint, Metric09 bigint, Metric10 bigint,

Metric11 bigint, Metric12 bigint, Metric13 bigint, Metric14 bigint, Metric15 bigint,

Metric16 bigint, Metric17 bigint, Metric18 bigint, Metric19 bigint, Metric20 bigint,

Metric21 bigint, Metric22 bigint, Metric23 bigint, Metric24 bigint, Metric25 bigint,

Metric26 bigint, Metric27 bigint, Metric28 bigint, Metric29 bigint, Metric30 bigint,

Metric31 bigint, Metric32 bigint, Metric33 bigint, Metric34 bigint, Metric35 bigint,

Metric36 bigint, Metric37 bigint, Metric38 bigint, Metric39 bigint, Metric40 bigint,

Metric41 bigint, Metric42 bigint, Metric43 bigint, Metric44 bigint, Metric45 bigint,

Metric46 bigint, Metric47 bigint

)

 

— ##############################################################

— Load original table

— ##############################################################

declare @DupID bigint = 1

declare @NumRecs bigint = 20000000

 

truncate table FewDuplicates

set nocount on

while (@DupID <= @NumRecs)

begin

insert into [dbo].[FewDuplicates] (

[DupID], [MyText],

[Metric01], [Metric02], [Metric03], [Metric04], [Metric05], [Metric06], [Metric07],

[Metric08], [Metric09], [Metric10], [Metric11], [Metric12], [Metric13], [Metric14],

[Metric15], [Metric16], [Metric17], [Metric18], [Metric19], [Metric20], [Metric21],

[Metric22], [Metric23], [Metric24], [Metric25], [Metric26], [Metric27], [Metric28],

[Metric29], [Metric30], [Metric31], [Metric32], [Metric33], [Metric34], [Metric35],

[Metric36], [Metric37], [Metric38], [Metric39], [Metric40], [Metric41], [Metric42],

[Metric43], [Metric44], [Metric45], [Metric46], [Metric47]

)

VALUES (

@DupID,‘my text’,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000,

ABS(cast(CHECKSUM(NewId()) as bigint)) % 10000000

)

set @DupID += 1

end — group option loop

set nocount off

 

— ##############################################################

— Create indices on the test table

— ##############################################################

CREATE UNIQUE CLUSTERED INDEX [ci_RecID] ON [dbo].[FewDuplicates]

(

[IDCol] ASC

)

WITH (fillfactor = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

 

CREATE NONCLUSTERED INDEX [ix_DupID] ON [dbo].[FewDuplicates]

(

DupID ASC

)

WITH (fillfactor = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

 

Queries & Execution Plans with Missing Index Recommendations

The six queries all performed a range scan based upon DupID and Metric01. Clearly, the ix_DupID index could be used for the first portion of the where clause, but none of the existing indices could assist in the resolution of the second portion of the where clause. Note: All queries used identical where clauses to insure that the only query differences involved the columns that were requested in the select clause. These variations employed different combinations of the first six metric columns included a variety of column orderings. All of the queries returned the same 809 rows. Note: Due to the random nature of the data, the reader will not obtain identical results, but they will be functionally similar. Each query was run separately after the following commands had been executed:

dbcc dropcleanbuffers with no_infomsgs

dbcc freeproccache with no_infomsgs

dbcc freesystemcache(‘TokenAndPermUserStore’) with no_infomsgs

These commands clear all the pertinent caches to insure reproducibility and prevent memory-resident portions of the database from skewing the results. Each query required approximately 90 seconds to execute as shown in Table 1. Each query required approximately one million logical and physical reads to complete. The SSMS option for returning the actual execution plan was set prior to execution, and the six queries and execution plans are shown in the individual query sections below. The first five queries resulted in different recommendations, but the sixth query’s plan and recommended missing index were identical to that of the fifth because the only difference between Queries #5 and #6 is the ordering of the columns (part of the answer to question #2). The differences among all the queries are summarized in Table 2 below.

Blog_20170601_T1

Table 1: Summary of Initial Query Executions & Timings

Blog_20170601_T2

Table 2: Summary of Query Selection Columns

Query #1

select [DupID], [MyText], [Metric01]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_1

Query #2

select [DupID], [MyText], [Metric02]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_2

Query #3

select [DupID], [MyText], [Metric03], [Metric06], [Metric04]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_3

Query #4

select [DupID], [MyText], [Metric04], [Metric06], [Metric02], [Metric01]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_4

Query #5

select [DupID], [MyText], [Metric01], [Metric03], [Metric05], [Metric02]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_5

Query #6

select [DupID], [MyText], [Metric05], [Metric02], [Metric01], [Metric03]

from FewDuplicates

where DupID between 100000 and 500000 and Metric01 between 10000 and 30000

Blog_20170601_6

Missing Index Recommendations Displayed via DMVs

The following query was used to provide some of the information enumerated in Table 3 below:

— ##############################################################

— Missing Index DMV Query

https://blogs.msdn.microsoft.com/bartd/2007/07/19/are-you-using-sqls-missing-index-dmvs/

— ##############################################################

SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,

‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)

+ ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’

+ ‘ ON ‘ + mid.statement

+ ‘ (‘ + ISNULL (mid.equality_columns,)

+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE END

+ ISNULL (mid.inequality_columns,)

+ ‘)’

+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ) AS create_index_statement,

migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

The following table lists the missing index recommendations as displayed by the DMVs. It also summarizes all of the missing index recommendations to make it easier to determine what a consolidated index might require. It is important to note that the missing index recommendations for Query #5 and #6 are the same, just as they were in the query plan listings above. Although discussion of the calculation and interpretation of the Improvement Measure column is beyond the scope of this article, it should be noted that the total improvement measure was approximately twice that of any other index recommendation and further investigation reveals that this index would satisfy both Query #5 and #6.

Blog_20170601_T3

Table 3: Summary of All Missing Index Recommendations

Composite Index Recommendation for All Six Queries

Either Table 2 or Table 3 could be used to determine a composite index. In-depth investigation reveals that since DupID and Metric01 are specified in the where clauses as inequalities, these should be key columns in any index, and review of Table 3 highlights this for all index recommendations. Since MyText is used in all of the queries and Metric01 is to be used as a key column, the only remaining columns are Metric02, Metric03, Metric04, Metric05, and Metric06. An index that employs these columns as included columns can “cover” all the queries shown in this article. For further information about covering indices, please refer to the following article: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns. The composite (and covering) index is shown below:

CREATE NONCLUSTERED INDEX ix_ResolveMultipleQueriesNeeds ON [dbo].[FewDuplicates] (

[DupID],

[Metric01]

)

INCLUDE (

[MyText],

[Metric02],

[Metric03],

[Metric04],

[Metric05],

[Metric06]

)

WITH (fillfactor = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]

Query Plans for Queries #1 – #6 with Composite Index Added

Once the combined index was implemented, each query was run as before: caches cleared and SSMS actual execution plan option on. No query ran longer than one second even when the caches had been purged. Although the queries were run individually for the purposes of obtaining accurate timings, they were also run together for the purpose of showing the similarities of the execution plans with the new index in place. Table 4 lists the execution results and clearly, performance improved greatly for all queries. For example, durations decreased from approximately 90 seconds to about one second and reads dropped from approximately one million to four thousand. Examination of the query plans shows that all six queries used the new index to obtain the observed extremely fast performance.

Blog_20170601_T4

Table 4: Summary of Query Executions & Timings After Composite Index Added

Blog_20170601_7

Summary

Most readers probably already knew the answer to the first question: key columns are used to filter query results. The simplest example of this involves a where clause. Columns referenced by equalities are placed first, followed by columns that are used with inequalities. Some of the missing index DMVs actually differentiate between the types of relationships involved. The example queries show that table column ordering dictates included column order thereby answering question #2. Since table column ordering is used to specify included column ordering, SQL Server WILL NOT duplicate missing index recommendations when the columns are identical regardless of their ordering in the queries. The answer to the last question is that SQL Server does not appear to perform any consolidations or optimizations other than those cited in the answer to question #2. This knowledge is invaluable, particularly when evaluating missing index recommendations on production systems because unless the queries that cause SQL Server to generate the missing index recommendations have been executed recently, there is little information to associate the actual queries with the recommendations. Understanding those factors that affect SQL Server’s recommendations can simplify causing query identification and give an analyst more certainty that the correct associations have been made.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!