Month: June 2016

Use the Query Store In SQL Server 2016 To Improve HammerDB Performance (T-SQL Tuesday #79)


Improving HammerDB Performance with the New 2014 Cardinality Estimator


TSQL2SDAY-150x150

T-SQL Tuesday #79 is being hosted by Michael J. Swart this month. His prompt was to check out what’s new with SQL Server 2016. I figured I would play around with the Query Store and our old friend HammerDB.

This post is a follow-up of sorts to my post HAMMERDB AND THE NEW SQL SERVER 2014 CARDINALITY ESTIMATOR. Read that first to get a basic overview of the problem we are trying to solve. Come back once you are finished.

Basic synopsis of that article if you don’t want to read it for yourself:

  • With the new cardinality estimator introduced in SQL Server 2014, performance of the SLEV stored procedure used by HammerDB seriously regresses.
  • Adding Trace Flag 9481 to the statement that regressed in the stored procedure brings the performance back to the same levels as SQL Server 2012.

The Workload on SQL 2016

First, we need to figure out if the HammerDB workload performs similarly to when we first upgraded to SQL 2014. I’m going to follow the same steps that I took when upgrading the database to 2014, except I will be changing the compatibility level to 130 (the new 2016 level) and at the end I will be enabling the query store on the database. (For my specific HammerDB build instructions, check out that original 2014 post).

/*
Using this blog post from Thomas LaRock as a guideline for upgrading to 2014:
thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
*/
USE [master]
GO

--Both data and log file on my C drive - not being fancy here
RESTORE DATABASE [tpcc]
FROM DISK = N'C:\2012INSTANCES\tpcc_blogtesting_10WH.bak'
WITH FILE = 1
,MOVE N'tpcc' TO N'C:\2016INSTANCES\DATA\tpcc.mdf'
,MOVE N'tpcc_log' TO N'C:\2016INSTANCES\LOGS\tpcc_log.ldf'
,NOUNLOAD
,STATS = 5
,REPLACE
GO

--Be sure to set the database compatibililty_level up to the newest 2016 version!
ALTER DATABASE [tpcc] SET COMPATIBILITY_LEVEL = 130;

USE [tpcc]
GO

DBCC CHECKDB WITH DATA_PURITY;

DBCC UPDATEUSAGE(tpcc);

USE [tpcc]
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

USE [tpcc]
GO
--Enable Query Store on tpcc database
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

After the database is restored and upgraded to 2016, we can run the TPC-C workload. Here are the results and the perfmon graph from the TPC-C workload running on my local 2016 instance:

HammerDB Results: 32,497 Transactions Per Minute;  7,171 New Orders Per Minute

CPU Average: 76%; Batch Requests / Sec average: ~ 527

2016_defaultPerfMon

Here are the results from the same workload on SQL 2012, 2014, and 2016 all in one table. You can see that we have the same issue on SQL 2016 that we had when first upgrading to SQL 2014.

Workload      TPM NOPM %CPU Avg BatchRequests/Sec Avg
2012 (baseline) 114,901 25,035 35% 1,750
2014 (Pre TF Fix) 31,542 6,894 74% 492
2016 32,497 7,171 76% 527

Sure enough, when we check the plan cache for the most expensive queries based on total_worker_time, we see the same query we would expect rise to the top. It is also using the same plan as it was on SQL 2014.

orig_plancache

orig_plan

Can we use the new Query Store to force the ‘good 2012 plan’ for the query without modifying the stored procedure?

Let’s say, for whatever reason, that we don’t want to modify the stored procedure and manually insert the trace flag option onto the query. Is there still a way to force the ‘good plan’? Well, let’s find out.

First, I’m going to pull up the Top Resource Consuming Queries window in the Query Store.

query_store_top_queries

You can see that our problem query is incredibly easy to find in the top left window based on total duration. Also notice that in the top right Plan summary window, there is currently only one available plan for the query (plan_id 49).

We need to figure out how we can get our ‘good plan’ using Trace Flag 9481 as an available plan that we can force using the Query Store.

The Query Store Fix

Here is the code we can use to manually execute the SLEV stored procedure using TF9481 at the session level. This will get us back to the ‘good plan’ and hopefully the query store will recognize the ‘good plan’ as an available plan that we can force. (IMPORTANT: Set Options will need to be identical to the options the existing query in cache used. You can find these pretty easily by looking at the XML of the original plan in cache.)

 USE [tpcc]
GO

--SET OPTIONS MUST BE IDENTICAL TO THE EXISTING PLAN IN CACHE!!
    --(otherwise our 'good plan' won't show as available for the existing query)

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON


--clear the plan of the bad query using the plan_handle
DBCC FREEPROCCACHE(0x05000500CE07091B409F80E73401000001000000000000000000000000000000000000000000000000000000)


--enable TF 9481 at the session level to revert to the legacy 2012 Cardinality Estimator
DBCC TRACEON(9481)

BEGIN TRAN
EXEC [dbo].[SLEV]
  @st_w_id = 8,
  @st_d_id = 9,
  @threshold = 10
ROLLBACK TRAN --we can even rollback the proc to prevent any actual data modification

GO 10 --execute 10 times just for fun

--disable the TF
DBCC TRACEOFF(9481) 

Now let’s check the Query Store Top Resource Consumers window again:
query_store_top_queries_fixedPlan

Sure enough, the ‘good plan’ is listed as part of the Plan Summary for our problem query (plan_id 74). Now we can easily force this plan using the GUI or t-sql:

--For query_id 49, force plan_id 74
EXEC sys.sp_query_store_force_plan @query_id = 49, @plan_id = 74;

Results

Just so that we can get a cool perfmon graph, I’m going to start the HammerDB workload before forcing the ‘good plan’. After about 30 seconds or so, I’ll force the ‘good plan’ using the query store and we can see what impact it has on the workload performance.

2016_perfMon_PlanForce

You can see the plan was forced right around the 3:55:20 mark. It quickly brought batches / second up to around 1800 and close to the SQL Server 2012 benchmark that was in the original post.

The query is back down to around 700 reads per execution…

query_stats_fixed

…and here are the stats after running a full workload with the Query Store fix in place. We still aren’t all the way back to the 2012 baseline levels, but we are much closer.

Workload TPM NOPM %CPU Avg BatchRequests/Sec Avg
Query Store Fix 98,177 21,324 32% 1,633
2012 (baseline) 114,901 25,035 35% 1,750

Conclusion

With new versions of SQL Server come new tools, and the Query Store is a very powerful tool that can be useful when you need to force a plan based on a trace flag but don’t want to necessarily hard code the trace flag into the query or stored procedure. I can’t wait to start using the Query Store on production databases.

Once again, always be sure to test workloads and applications before upgrading a production database to a newer version of SQL Server – especially when going from 2012 or older to 2014 and newer.

Advertisements

Easily Generate SQL Server Database Snapshot Create Statements

The other day, a developer came to me and requested a database backup be taken before we ran some upgrade scripts on the database provided by a vendor. This was only a test server so there wouldn’t be any traffic on the database. I thought about it for a second and figured that creating a snapshot of the database prior to the upgrade script being executed would probably be just as easy – and in the event something with the upgrade script errored out or went wrong, we could easily revert the database back to the snapshot which would be much quicker than an entire database restore.

I went to create the database snapshot and could not remember the syntax to save my life. I decided to make a simple script to help myself out in the future.

The entire script will be available down below. But I’ll show a quick example of how to get it setup. I will be using the StackOverflow database on which I have added two additional files/filegroups.

StackOverflow_files

The only two changes that NEED to be made in the script are the @Target_DB and @Snap_Desc variables. Here, I am setting @Target_DB to ‘StackOverflow’ and @Snap_Desc to ‘Pre_Database_Upgrade_’ plus a timestamp. By default, the snapshot name will be a combination of these two variables (this can be easily changed if you like – but I like descriptive names).


/*--------SET THE VARIABLES HERE-----------------*/
--target database of which you are going to take the snapshot
set @Target_DB = 'StackOverflow'

--description or purpose of the snapshot (to make the snapshot name somewhat descriptive)
set @Snap_Desc = 'Pre_Database_Upgrade_' + CONVERT(varchar,GETDATE(), 114)

--Combining @Target_DB and @Snap_Desc to create a snapshot name.
--This can be changed to whatever you wish.
set @Snap_Name = @Target_DB + '_' + @Snap_Desc + '_ss'

/*-----------------------------------------------*/

Here is the example output from the script using the input variables from above. It generates the command to create the snapshot and also provides the command to revert the database back to the snapshot (just in case).


--Snapshot Name: StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss
--Number of DB Files: 3

USE master;
CREATE DATABASE [StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss] ON
( NAME = [StackOverflow],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow.ss')
,( NAME = [StackOverflow_ETL],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow_ETL.ss')
,( NAME = [StackOverflow_ReadOnly],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow_ReadOnly.ss')
AS SNAPSHOT of [StackOverflow];

/*--------SNAPSHOT Revert Script------------/

USE master;
-- Reverting DB [StackOverflow] to [StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss]
RESTORE DATABASE [StackOverflow] from
DATABASE_SNAPSHOT = 'StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss';
GO

/-------------------------------------------*/

As of now, the script places the snapshot sparse files (.ss) in the same locations as the existing database .mdf and .ndf files. This can be manually tweaked pretty easily, but I may add in some options to specify a new location for the sparse files at some point in the future. Also, this script doesn’t create the actual snapshot – it simply outputs the command needed to create the snapshot. You will still have to manually execute the output yourself.

Anyway, here is the entire script or click here for the raw code:


/*---------------------------------------------------------------------

Author:		Justin Goodwin, https://sqljgood.wordpress.com/
							https://twitter.com/sql_jgood

Date:		June 2nd, 2016

NOTES:		For now, this places the snapshot files (.ss) in the 
			same location as the original database files. The 
			command can be tweaked once it is generated if the 
			location needs to be changed.

			
  You may alter this code for your own *non-commercial* purposes. 
  You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.

-----------------------------------------------------------------------*/

use master
go

set nocount on; --remove '(x row(s) affected)' garbage

declare @Target_DB varchar(200) 
declare @Snap_Desc varchar(100) 

declare @Snap_Name varchar(300)

declare @Snap_Script varchar(max)
declare @File_Count_Script varchar(max)
declare @File_Count int

declare @filename varchar(100)
declare @physical_name varchar(300)
declare @cursorcount int
declare @cursorscript varchar(max)


/*--------SET THE VARIABLES HERE-----------------*/
--target database of which you are going to take the snapshot
set @Target_DB = 'Database_Name'

--description or purpose of the snapshot (to make the snapshot name somewhat descriptive)
set @Snap_Desc = 'Pre_Database_Upgrade_' + CONVERT(varchar,GETDATE(), 114)

--Combining @Target_DB and @Snap_Desc to create a snapshot name. 
	--This can be changed to whatever you wish.
set @Snap_Name = @Target_DB + '_' + @Snap_Desc + '_ss'

/*-----------------------------------------------*/

--check if the @Target_DB exists on the current server
IF NOT EXISTS(select 1 from sys.databases where name = @Target_DB)
	Begin
	Print '@Target_DB ' + @Target_DB + ' does not exist on the server! Double Check the @Target_DB name.'
	GOTO FINISH
	End


print '--Snapshot Name: ' + @Snap_Name


--create 2 temp holding tables
	if OBJECT_ID(N'tempdb..#tempcount') is not null
	begin
		drop table #tempcount
	end
	
	create table #tempcount (count int)
	
	if OBJECT_ID(N'tempdb..#tempcursor') is not null
	begin
		drop table #tempcursor
	end
	
	create table #tempcursor (
		name sysname
		,physical_name nvarchar(260)
		)

--determining how many DB data files exist for @Target_DB
set @File_Count_Script = '
select COUNT(name)
from ' + QUOTENAME(@Target_DB) + '.sys.database_files
where type = 0 --Rows only
'
--by doing this insert..exec into a temp table, we can avoid having to be connected to the target_db
insert into #tempcount
exec (@File_Count_Script)

select @File_Count = count
from #tempcount

print '--Number of DB Files: ' + CAST(@File_Count as varchar(3)) + '

'

set @cursorcount = 1 --the iterative loop counter

--begin creation of the create snapshot script here...
set @Snap_Script = 'USE master; 
CREATE DATABASE ' + QUOTENAME(@Snap_Name) + ' ON 
 '

--if there is more than 1 database data file, we will need to iterate through each file....cursor time.
set @cursorscript = '
select name
	,physical_name
from ' + QUOTENAME(@Target_DB) + '.sys.database_files
where type = 0 --Rows only
'

--more insert..exec...
insert into #tempcursor
exec (@cursorscript)

declare file_name_cursor cursor
for
select name
	,physical_name
from #tempcursor

--start cursor
open file_name_cursor

fetch next
from file_name_cursor
into @filename
	,@physical_name

while @@fetch_status = 0
begin
	if (@cursorcount > 1) --we need a leading comma for each new file after the first file
	begin
		set @Snap_Script = @Snap_Script + '
,'
	end
	--add each DB data file to the snapshot command being built
	set @Snap_Script = @Snap_Script + '( NAME = ' + QUOTENAME(@filename) + ', 
		FILENAME = ''' + REPLACE((REPLACE(@physical_name, '.mdf', '.ss')), '.ndf', '.ss') + ''')' --replace .mdf or .ndf with .ss


	set @cursorcount = @cursorcount + 1 --add to the loop counter after each data file

	fetch next
	from file_name_cursor
	into @filename
		,@physical_name
end

close file_name_cursor

deallocate file_name_cursor

--add the final piece to the snapshot create statement and build the snapshot revert statement
set @Snap_Script = @Snap_Script + '
AS SNAPSHOT of ' + QUOTENAME(@Target_DB) + ';



/*--------SNAPSHOT Revert Script------------/

USE master;  
-- Reverting DB '+QUOTENAME(@Target_DB)+' to '+QUOTENAME(@Snap_Name)+'  
RESTORE DATABASE '+QUOTENAME(@Target_DB)+' from   
DATABASE_SNAPSHOT = '''+@Snap_Name+''';  
GO

/-------------------------------------------*/

'
--output the commands that we have built
print @Snap_Script

--cleanup temp tables
drop table #tempcount
drop table #tempcursor

FINISH: --if there is an error, skip to this label