SQL Server Plan Comparison Tool

September 30, 2016

MSSQL Tiger Team – Plan Comparison Tool
I ran across the above link detailing the execution plan comparison tool included as part of SSMS(SQL Server Management Studio). This tool gives you a way to view two execution plans at the same time, along with displaying statistics from each plan.
Once you produce the first execution plan, you can right-click and select ‘Save Execution Plan As…’ and save the plan as a .sqlplan file. Once the second execution plan is generated, you can right click on the plan and select the ‘Compare Showplan’ option. The dialog opened will allow you to select the file with the first plan, and will open the comparison tool to display the two plans.

On the topic of execution plans, SQL Sentry announced recently that their Plan Explorer tool is now free, If you haven’t used this tool, it is a great improvement on viewing SQL Server execution plans.
SQL Sentry Plan Explorer

Inner Join vs Outer Join Performance

August 29, 2016

At work, a colleague and I discussed the performance of inner joins and against outer joins, particularly in the case where both types of joins would return the same number of rows. So if it turned out that you always had a match for each inner record, would you pay a penalty for having a left join? In most cases an outer join would return more records, since you would usually have unmatched records.
I posted a script on GitHub to create two tables that would be joined on RecordId.

create table dbo.Test1(
RecordId int not null identity(1,1) primary key clustered,
CreatedDate datetime not null default getdate()

create table dbo.Test2(
RecordId int null,
ItemId tinyint null,
CreatedDate datetime not null default getdate()

I created two million records in Test1, and then 5 Test2 records for each one in Test1. The Test2.RecordId column was left nullable, so we wouldn’t give the optimizer any hints as far as unmatched records.
So next we return all records in two queries, one with an inner join and one with an outer join.

select *
from dbo.Test1 as a
join dbo.Test2 as b
	on b.RecordId = a.RecordId;

select *
from dbo.Test1 as a
left join dbo.Test2 as b
	on b.RecordId = a.RecordId;

The two queries run in the same amount of time with virtually the same execution plan. Since Test1 has a clustered index, the key column is already sorted, so a Merge Join is used. So in this case, we’re running down the clustered index and finding a match for each record in the Test2 table, so it makes sense that the same amount of work would be done.
Dropping the clustered primary key on Test1 results in a different execution plan, where a Hash join is used instead of the Merge join, but the run time is still the same for both queries.
Deleting every 4th row from the detail table resulted in the inner join running slightly faster, but it was a 52-49 split in the execution plan.
I was a little surprised at first there wouldn’t be a difference in query performance between the two join types, but now it makes sense that the optimizer would go through the same plan for both types of join in this case. Inner vs Outer join does make a lot of difference logically in the records returned, but if both joins will return the same number of records then the plans turn out to be the same and the execution time is the same as well.

SQL Server 2016 – Memory Optimization Advisor

August 8, 2016

Right-clicking on a table in SSMS 2016 includes an option for the ‘Memory Optimization Advisor’. This starts a wizard to assist in migrating a table to an In-Memory table structure. If the base table has any issues that would prevent it from being migrated to an in-memory structure, the wizard will identify and help to resolve those issues. Here’s a link to some issues that would prevent the migration.
A big issue will be tables with foreign keys defined. The keys will have to be dropped and then re-created, they won’t be migrated.
If all checks pass, then the table can be migrated. There is an option to migrate the data as well.

I posted on In-Memory tables for SQL Server 2014, although several changes were made for 2016. 2016 supports foreign keys, check contraints, outer joins, among other features.

SQL Server 2016 – Sample Databases

August 6, 2016

Microsoft has updated their sample database, retiring the old Adventure Works. The new set is for Wide World Importers.
Backups for the new database is at:

There is a standard database, plus a database warehouse version, as well as backups for different versions (Standard, Enterprise, etc.).

SQL Server 2016 – Query Store

July 9, 2016

Just a quick introduction to the Query Store feature. Query Store is a feature new to SQL Server 2016 that will capture data on query performance. Once it is enabled, we’ll have access to dashboards to track query plans as well as statistics on query execution.
I’m using SQL Server 2016 RTM Developer Edition.

Set Up:
Query Store is enabled at the database level. Going to the database properties you’ll see a ‘Query Store’ page. Setting ‘Operation Mode (requested)’ from ‘Off’ to ‘Read Write’ will enable this functionality. You’re also able to set the max space taken up, among other settings.
Once enabled, you can go back to SSMS and you’ll see a ‘Query Store’ tree under the database.

Under the ‘Query Store’ tree in SSMS, we’ll see four entries. These are for available dashboards to return data on our query performance and query plans.
– Regressed Queries: Returns queries that have become worse performing over time. We can select from certain criteria and view query plans to determine issues.
– Overall Resource Consumption: A summary of query stats for Duration, Execution Count, CPU Time and Logical Read over a set period of time.
– Top Resource Consuming Queries: We can select our criteria, such as duration, CPU Time, Physical Reads, etc. and see the worst performing queries by that criteria.
– Tracked Queries: Takes a query ID as a parameter – Returns query plans, the time the query was executed along with the average duration.

Simple Talk

SQL Server 2016 – More New T-SQL Features

June 30, 2016

An earlier post went over some new T-SQL features for SQL Server 2016. Here are two more new string functions that could be useful. I’m using SQL Server 2016 RTM Developer Edition.

The String_Split function will take a delimited list and return each value in a one column table.

declare @List varchar(100) = 'Matt Ryan,Julio Jones,Vic Beasley,Desmond Trufant';
select value from string_split(@List, ',');

String_Split – MSDN

This function will take a string and escape special characters for a certain format. Currently, the only format supported is for JSON.

select STRING_ESCAPE('She said "OK"', 'json');

String_Escape – MSDN

SSMS – New Version Setup

May 31, 2016

June 1 is the release date for SQL Server 2016, so plenty of developers will be downloading and setting up new instances. One pain point is installing a new version of SSMS(SQL Server Management Studio) and getting it configured. I recently upgraded SSMS from 2012 to 2016 RC3 at work so that I could connect to 2014 instances. Here are the steps I went through to customize SSMS after it was installed.

1) Tools=>Options=>Designers=>Table and Database Designers – Uncheck ‘Prevent saving changes that require table re-creation’.
By default, the table designer prevents you from making schema changes to a table that will force the table to be rebuilt.

2) Search and Replace – By default sends results to the ‘Find Results 1’ window – On that window, click the down arrow and select ‘Auto Hide’ to keep the window from displaying next time.
Some people may appreciate this functionality, but this isn’t something I ever use. When searching a script, I want to see the

3) Templates: I use templates as a starting point for several different things, such as creating a new stored procedure or a table. I wrote about templates in an earlier post.
All that’s involved is copying the templates from the old installation to the 2016 directory:
“C:\Users\{User}\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\Templates\Sql\CS\*.sql”
Where {User} is the Windows user name, and CS is the directory I created for the custom templates. SSMS needs to be closed and re-opened to pick up these changes.

4) Registered servers: The registered servers can be exported to the new edition.
From SSMS 2012 – Right click on your group to export=>Tasks=>Export
Save export file – Select if you want to save user names and passwords with the file.
Saves a regsrvr file.
In new SSMS, right click on Local Server groups=>Tasks=>Import.

5) Toolbar: Select down arrow at end of toolbar then ‘Add or remove buttons’ to select Icons to remove or add to toolbar, to remove anything you won’t use to de-clutter the toolbar.

6) Function Names: I like my function names in lower case. To set up for Intellisense to fill in function names as lower case:
Tools=>Options=>Text Editor=>Transact-SQL=>IntelliSense : ‘Casing for built-in function names’ – Lower Case.

There was one feature I used that wasn’t included in this edition. I frequently export data to a CSV file, so SSMS had a setting to surround any text that contained the delimiter(a comma) with quotes. Hopefully this will be restored in a future update.
1) Tools=>Options=>Query Results=>SQL Server=>Results to grid – ‘Quote strings containing list separators when saving .csv results”