Baselining

Introduction

Before starting any performance tuning you need to baseline your SQL Server performance. This is for the following reasons: –

  • You can only gauge the effectiveness of any performance tuning techniques if you can quantifiably demonstrate the performance improvement. Therefore you need to compare performance metrics pre and post the change.
  • Performance tuning needs to begin somewhere with the biggest pain point in your system. With a baseline of performance metrics you can see where the issues lies, CPU, memory etc.
  • When you are alerted to a performance issues “it’s going slow”! You are able to compare the current state to your baseline to see which metric differs from normal to assist you in identifying the root cause of the problem.

How to Baseline

After reading numerous productivity and self-help books, the best method to improve your productivity is to eliminate those tasks from your life which are possible to do so.

Seriously, don’t roll your own baseline tools via Perfmon, extended events, DMV queries or some other tool then attempt to graph trends yourself using Excel or Power BI. Simply buy a third party product to monitor performance over time if at all possible. You really don’t want to be bug-fixing your monitoring solution when robust products are already available. This leaves you free to concentrate your efforts on what is important.

What to Baseline

At a high level, the metrics to baseline fall into the following categories (some examples of each are included): –

  • Memory
    • Total Server Memory
    • Buffer Cache Hit Ratio
  • CPU
    • % Processor Time
  • I/O
    • Avg. Disk Sec/Write
  • Disk
    • Space
  • SQL Server metrics
    • Page life expectancy
    • Batch Requests/Sec
    • Full Scans/sec

Automate synonym creation

Let the code write the code.

We frequently want to access tables within another database on the same server. I often want to do this within a BI solution when the staging area is a separate database on the same server as the dimensional model database.

Never access these tables via 3-part names. If things change, say for example the staging database was moved to another server or to within the same database as the facts/dimensions you need to change your entire codebase where these tables are referenced.

My preferred solution here is to add synonyms as an abstraction layer. This is a very elegant solution, allowing you to move the base tables anywhere you like and for things to carry on working as before with a synonym change. An alternative here is to use views but synonyms are preferred for a number of reasons.

  1. Views become stale and have to be updated if the underlying table changes, synonyms do not.
  2. You can use hints on synonyms as if you were referencing the table itself, this is not supported with views.

I have automated the creation of such synonyms to produce a consistent repeatable structure for hundreds or even thousands of tables. Connect to the database containing the base tables to reference (the staging database in the example above) and run this script, this will produce statements to check for existence, drop, create synonym and also create an extended property to document who created the synonym and when. Take the output and run against your database that should reference the base tables (the dimensional model database in the example above).


/*
Name:

(C) Andy Jones

mailto:andrew@aejsoftware.co.uk

Description: -

Creates if exists / drop / create synonym statements for each table in a database.
Creates an extended property for each synonym with who created and when.

Change History: -
1.0 12/11/2015 Created.

License: free to download and use providing this header is kept in place. Author consent is required for
any sale or re-distibution.
*/
SET NOCOUNT ON;

WITH Command ( CommandID, Command )
AS ( SELECT 1 ,
'IF EXISTS (SELECT 1 FROM sys.synonyms WHERE [object_id] = OBJECT_ID(''_Replace_QualifiedName''))'
UNION ALL
SELECT 2 ,
CHAR(9) + 'BEGIN;'
UNION ALL
SELECT 3 ,
CHAR(9) + 'DROP SYNONYM _Replace_QualifiedName;'
UNION ALL
SELECT 4 ,
CHAR(9) + 'END;'
UNION ALL
SELECT 5 ,
CHAR(9) + ''
UNION ALL
SELECT 6 ,
'CREATE SYNONYM _Replace_QualifiedName FOR '
+ DB_NAME() + '._Replace_QualifiedName;'
UNION ALL
SELECT 9 ,
CHAR(9) + ''
UNION ALL
SELECT 10 ,
'EXEC sp_addextendedproperty @name = N''MS_Description'',@value = N'''
+ SYSTEM_USER + ' '
+ CONVERT(CHAR(10), CURRENT_TIMESTAMP, 103)
+ ': synonym pointing at ' + DB_NAME()
+ '._Replace_QualifiedName'',@level0type = ''SCHEMA'',@level0name = N''dbo'',@level1type = ''SYNONYM'',@level1name = N''_Replace_Name'', @level2type = NULL,@level2name = NULL;'
UNION ALL
SELECT 11 ,
CHAR(9) + ''
),
Obj ( QualifiedName, Name )
AS ( SELECT OBJECT_SCHEMA_NAME(o.object_id) + N'.' + o.name ,
o.name
FROM sys.objects AS o
WHERE o.type_desc IN ( N'USER_TABLE', N'VIEW' )
AND OBJECT_SCHEMA_NAME(o.[object_id]) <> N'tSQLt'
AND o.name NOT IN (N'sysdiagrams')
)
SELECT Command = REPLACE(REPLACE(c.Command, '_Replace_QualifiedName',
o.QualifiedName), '_Replace_Name',
o.Name)
FROM Command AS c
CROSS JOIN Obj AS o
ORDER BY o.QualifiedName ,
c.CommandID;

 

Parameter sniffing

A previous blog post regarding querying the plan cache, referred to a concept called parameter sniffing, here I will examine this further. As an example, I will create the following table and stored procedure: –

USE DBA;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM sys.tables WHERE [object_id] = OBJECT_ID(N'dbo.Sale'))
BEGIN;
DROP TABLE dbo.Sale;
END;

CREATE TABLE dbo.Sale (
SaleID INT IDENTITY(1,1) NOT NULL,
Dt DATETIME NOT NULL CONSTRAINT df_Sale_Dt DEFAULT (CURRENT_TIMESTAMP),
CountryID INT NOT NULL
);

INSERT INTO dbo.Sale (CountryID)
SELECT 1
FROM master.dbo.spt_values AS a
CROSS JOIN master.dbo.spt_values AS b;

INSERT INTO dbo.Sale (CountryID) VALUES (2);

ALTER TABLE dbo.Sale ADD CONSTRAINT pkSale PRIMARY KEY CLUSTERED (SaleID);
CREATE NONCLUSTERED INDEX ix_Sale_CountryID ON dbo.Sale(CountryID);
GO

USE DBA;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;

IF EXISTS (SELECT 1 FROM sys.procedures WHERE [object_id] = OBJECT_ID(N'dbo.LatestOrder'))
BEGIN;
DROP PROCEDURE dbo.LatestOrder;
END;

GO

CREATE PROCEDURE dbo.LatestOrder (@CountryID INT) AS

SELECT MaxDt = MAX(Dt)
FROM dbo.Sale
WHERE CountryID = @CountryID;

GO

The data inserted was for 6,325,225 sales in CountryID=1 but only 1 sale in CountryID=2. The stored procedure returns the maximum order date for the CountryID. If I then execute the dbo.LatestOrder stored procedure three times like so: –


SET STATISTICS IO ON;
EXECUTE sp_recompile N'dbo.LatestOrder';
EXECUTE dbo.LatestOrder @CountryID = 2;
EXECUTE dbo.LatestOrder @CountryID = 1;
EXECUTE sp_recompile N'dbo.LatestOrder';
EXECUTE dbo.LatestOrder @CountryID = 1;

We see the following three plans: –

LatestOrder query plans

And logical reads of: –

Table ‘Sale’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Sale’. Scan count 1, logical reads 18986644, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Sale’. Scan count 5, logical reads 19743, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A plan is compiled for the first execution of the stored procedure that correctly estimates that one row will result from the initial seek. That plan is then placed into cache and is subsequently used for the second execution. The second execution again estimates one row will be returned from the index seek where actually 6,325,225 rows are returned. This is parameter sniffing – the parameters used for the first execution are those used to create the cached execution plan and are used in estimates for subsequent executions regardless of the actual parameter values in those subsequent executions. We can see in the third example the optimal query plan for @CountryID = 1 after all previous plans for this procedure are removed from cache. This results in 19,743 logical reads or 0.1% of the 18,986,644 logical reads from the second cached plan.

This example also demonstrates query costs in actual execution plans are in fact estimated. These % cost estimates should never be used as a basis of query tuning as they are always estimated, even in actual plans. They simply corroborate the erroneous estimates that caused the performance issue in the first place.

There are different fixes that could be applied here. We could perform some index tuning to add an included column: –

CREATE NONCLUSTERED INDEX ix_Sale_CountryID ON dbo.Sale(CountryID) INCLUDE (Dt) WITH DROP_EXISTING;

That will not remove parameter sniffing, but will reduce the differential of logical reads between queries two and three. Further work would probably be required.

We could also add the WITH RECOMPILE statement to the stored procedure definition, add WITH RECOMPILE to an individual statement within a stored procedure or lastly to the invocation of the stored procedure. This will cause the execution plan to be compiled for each invocation. This may be suitable for longer running stored procedures where the plan compilation cost is negligible but certainly will not be appropriate in all scenarios and should be used with caution.

Another option would be to add sub-procedures within the main procedure and logically flow execution to the appropriate sub-procedure depending on some initial user-defined logic. Those sub-procedures will each then have a plan in cache with their own set of sniffed parameters with an appropriate plan for each. This extra development overhead might be suitable in certain circumstances where parameter sniffing is a real issue causing performance pain.

Effect of set options of the query plan

Changing the set options for a query, can result in different query plans saved in cache for what is otherwise the same query. Best demonstrated with an example: –


EXEC sp_recompile N'dbo.uspGetBillOfMaterials';

--remove any previous plans from cache.

SET ANSI_PADDING ON;

EXECUTE dbo.uspGetBillOfMaterials
@StartProductID = 893,
@CheckDate = '2015-11-05T00:00:00';

SET ANSI_PADDING OFF;

EXECUTE dbo.uspGetBillOfMaterials
@StartProductID = 893,
@CheckDate = '2015-11-05T00:00:00';

Querying the plan cache for this particular stored procedure, now shows two different plans each with a usecounts value of 1. This can lead to two potential issues: –

  1. The plan cache becomes bloated with these types of plans. Leaving less room within the cache for other plans.
  2. Troubleshooting performance issues can lead to erroneous conclusions. Users could be experiencing performance issues with a particular query from a front-end application. If a DBA were to execute the same query from within SSMS with differing set options, a new plan could be compiled that could perform very differently.

Both issues above can be mitigated by having consistent set options. Set options were previously demonstrated in my post regarding bitwise operations. In the example above, we can see via sys.dm_exec_plan_attributes for the two plan handles that the set options values are 4346 and 4347 respectively. If we then run: –

WITH cteSetOptions (SetOption,Value) AS (
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144)

SELECT [Set Option] = SetOption + ' option is set'
FROM cteSetOptions
WHERE Value & 4346 = Value

We see the results for the first query: –

ANSI_NULL_DFLT_ON option is set
ANSI_NULLS option is set
ANSI_WARNINGS option is set
ARITH_ABORT option is set
CONCAT_NULL_YIELDS_NULL option is set
Parallel Plan option is set
QUOTED_IDENTIFIER option is set

and the second: –

ANSI_NULL_DFLT_ON option is set
ANSI_NULLS option is set
ANSI_PADDING option is set
ANSI_WARNINGS option is set
ARITH_ABORT option is set
CONCAT_NULL_YIELDS_NULL option is set
Parallel Plan option is set
QUOTED_IDENTIFIER option is set

Count of rows in a table

Here are some methods to return the row count from a table: –

SELECT row_count = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
SELECT row_count = SUM(rows)
FROM sys.partitions
WHERE [object_id] = OBJECT_ID(N'Sales.SalesOrderHeader') AND
index_id < 2; --0 = Heap; 1 = Clustered index.
EXECUTE sys.sp_spaceused 'Sales.SalesOrderHeader';
EXECUTE dbo.sp_DataProfile 'Sales.SalesOrderHeader',0;
SELECT COUNT(*) FROM Sales.SalesOrderHeader;

The first three rely on the dynamic management views sys.dm_db_partition_stats and sys.partitions, where the latter two will actually scan the table or index to count the rows.

sp_DataProfile is a great free (see EULA for terms) tool, to give various statistics for a table and it’s columns, one of which is row count.

A direct count against the table will give an accurate row count but will hold locks potentially blocking other connections. For most scenarios, sp_spaceused or a DMV query is accurate enough. It is typically not accurate if other connections are modifying data and if that is the case, a direct table count might give you an accurate count that will soon be stale anyway.

Optimise for Ad-Hoc workload

When querying your plan cache, you may see a large number of plans with a usecounts value of 1.

An option here may be to set the “optimize for ad hoc workloads” configuration option. When set, this option means only a stub plan is placed into cache the first time a plan is compiled with subsequent executions removing the stub and inserting the full plan. This prevents the plan becoming bloated with plans that are never re-used and leaves room for valuable plans that are used many times.

You can see if the value is set on a server via: –


SELECT value_in_use FROM sys.configurations WHERE name = N'optimize for ad hoc workloads';

A value of 1 means this option is set.

You update this setting using sp_configure and the reconfigure option via


EXECUTE sp_configure 'show advanced options',1;
RECONFIGURE;
EXECUTE sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE;