Cost Threshold for Parallelism

Cost Threshold for Parallelism is a server wide setting which specifies the lower bound over which plans will execute in parallel.

The default value on installing SQL Server is 5. This means any query with a cost deemed to be greater than 5 will execute in parallel. There are no units for this measure and none are required. Just know that SQL Server is a cost based engine and determines a cost for each operator in a query plan and an overall cost. We just need to know that 6 is greater than 5 and 50 is less than 199 etc.

You can view and specify this setting like so


EXEC sys.sp_configure @configname = 'cost threshold for parallelism',
 @configvalue = 1000;

RECONFIGURE;

SELECT *
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';

If we look at the execution plan for the following query


DBCC FREEPROCCACHE
DBCC SETCPUWEIGHT(1000)
GO

USE AdventureWorks2012;
SELECT * FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID
GO

DBCC SETCPUWEIGHT(1)
DBCC FREEPROCCACHE
go

We see that the overall cost is 538.914 (DBCC SETCPUWEIGHT is an undocumented command allowing me to artificially increase the cost here for demo purposes).

Cost for query

This means we will have a serial plan as our cost threshold is 1000 as set above. If I change the cost threshold to 538, then the new plan looks like this

Parallel plan

We know this is a parallel plan because of the double yellow arrows on the operators.

The big question remains however, what is the optimal value for cost threshold for parallelism? There is no definitive correct answer to this question. There is overhead for SQL Server to split a workload over multiple cores, then merge the output back together. That is why everything shouldn’t be parallel, for low cost queries it is detrimental for performance.

The range of valid values are 0 – 32,767 and trying the full range would probably result in a bell curve, too low and everything is parallel, too high and the majority of queries will remain serial and you will lose the potential performance benefit.

The answer is to test. Run a production like workload with different cost threshold values on a test server and monitor all metrics using a third party tool and compare to your baseline. If you were feeling very brave, you could change the value in live and monitor similarly. I have heard wiser people than me say they change the default to 25 or 50 as a first sensible change.

Some further points before wrapping up. When threads sharing the load in a parallel plan are waiting for the last to complete, the wait type is CXPACKET. Similarly when the repartition operator is waiting on all threads to complete and deliver their results, the wait type is the same. This is why CXPACKET waits are a perfectly normal symptom of parallelism and are not necessarily something to worry about when performance tuning.

Lastly, the whole post above depends on you having more than one CPU core of course. Also that the number of available CPU cores for use has not been throttled by setting max degree of parallelism to be 1 at either the instance or query level.

Index Computed Columns to Help Performance

A great way to improve performance without having to make any application changes is to add a computed column and index it.

This can be demonstrated with the help of an example, create a session log table and insert some data


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.SessionLog') )
 BEGIN;
 DROP TABLE dbo.SessionLog;
 END;

CREATE TABLE dbo.SessionLog
 (
 SessionID INT IDENTITY(1, 1)
 NOT NULL ,
 SessionStart DATETIME NOT NULL ,
 SessionEnd DATETIME NOT NULL ,
 CONSTRAINT CK_SessionLog_SessionStart_SessionEnd CHECK ( SessionEnd >= SessionStart )
 );

ALTER TABLE dbo.SessionLog ADD CONSTRAINT pkSessionLog PRIMARY KEY CLUSTERED (SessionID) WITH FILLFACTOR = 100;
 GO

INSERT INTO dbo.SessionLog
 ( SessionStart ,
 SessionEnd
 )
 SELECT DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 721,
 CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME)) ,
 DATEADD(MINUTE, 720 + ( ABS(CHECKSUM(NEWID())) % 721 ),
 CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME))
 FROM dbo.Numbers;

A query for sessions lasting under 60 minutes


SET STATISTICS IO ON; SELECT SessionID, SessionStart, SessionEnd
FROM dbo.SessionLog
WHERE DATEDIFF(MINUTE,SessionStart, SessionEnd) < 60;

results in the following query plan

Index computed column index scan.JPG

However, if we add a computed column and index it like so


ALTER TABLE dbo.SessionLog ADD Duration AS DATEDIFF(MINUTE,SessionStart, SessionEnd);
CREATE NONCLUSTERED INDEX IX_SessionLog_Duration ON dbo.SessionLog(Duration) INCLUDE(SessionStart, SessionEnd);

The scan is replaced by a seek

Index computed column index seek.JPG

And logical reads reduced from 237 to 2.

This is with no change to the query. Both query plans result from running the same query where the computed column Duration is not referenced explicitly


SET STATISTICS IO ON; SELECT SessionID, SessionStart, SessionEnd
FROM dbo.SessionLog
WHERE DATEDIFF(MINUTE,SessionStart, SessionEnd) < 60;

The query optimizer just knows that our where clause predicate matches the computed column definition and uses the index. This means we can potentially vastly improve performance with no application code changes required.

There are some gotchas you have to be careful with

  • SELECT * FROM dbo.SessionLog with now bring back the additional Duration column potentially breaking front end code. This is the reason you should never use SELECT * and always specify an explicit column list.
  • 6 set options must be set correctly when performing the following actions see the Indexes on Computed Columns MSDN article.
    • Creating the index on the computed column
    • Performing any DML (INSERT/UPDATE/DELETE) on the table.
    • If you want the index on the computed column to be used when performing selects.
  • NUMERIC_ROUNDABORT | OFF
  • ANSI_NULLS | ON
  • ANSI_PADDING | ON
  • ANSI_WARNINGS | ON
  • ARITHABORT | ON
  • CONCAT_NULL_YIELDS_NULL | ON
  • QUOTED_IDENTIFIER | ON

Here are some examples of errors

Create the index with SET QUOTED_IDENTIFIER OFF;

Msg 1934, Level 16, State 1, Line 40
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

Delete data with SET QUOTED_IDENTIFIER OFF;

Msg 1934, Level 16, State 1, Line 43
DELETE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

Perform the same select that can make use of the index but the SET ANSI_PADDING OFF, this causes the query plan to revert to using an index scan i.e. the index on the computed column is now ignored.

Index computed column index scan

The important point here is that you could break existing code by adding an index on a computed column if all connections don’t have the correct set options specified as required.

Missing Indexes

SQL Server can be fantastic on occasions and provide golden nuggets of information just waiting to be mined. One such great source of performance tuning gold dust is around missing indexes.

If I run a query such as

Missing index

You can see the missing index hint in green above the actual execution plan. Not only is this information shown here, but SQL Server is continually tracking these missing indexes. This is fantastic, you can now inherit a database you have no prior knowledge of, and still have an historical reference of missing indexes. Unfortunately this information does not persist over a server restart. Don’t be so lazy, nothing worthwhile is ever easy, you would have to persist this information yourself on a regular basis.

This can be queried like so


SELECT migs.unique_compiles ,
migs.unique_compiles ,
migs.user_seeks ,
migs.user_scans ,
migs.last_user_seek ,
migs.last_user_scan ,
migs.avg_total_user_cost ,
migs.avg_user_impact ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns ,
mid.[statement]
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mid.index_handle = mig.index_handle
ORDER BY mid.[statement];

This will display

  • Number of seeks and scans
  • Cost information – concentrate on the highest cost first
  • Equality, inequality and included columns
    • Equality typically used in a where clause equality predicate (x=y)
    • Inequality typically used in a where clause inequality predicate (x<>y)
    • Included columns are not used in the where clause, but are referenced elsewhere in the query. Typically these columns are in the select list.
  • The statement i.e. the object affected.

Some points to consider

  • Only non-clustered indexes are suggested and no advanced features such as filtered indexes.
  • Do not just blindly create all suggested indexes. There will be repetition and overlap between suggestions. You will have to use your judgement to decide which indexes to create.
  • Carefully consider the order of the columns you specify when creating the index. Which column is the most selective? Which column is used in the most queries? Which column is used in the most expensive queries?

SQL Server is constantly collecting information on missing indexes. Know how to extract this information and use it to your full advantage.

 

What is Happening on Your Server Currently

A great tool to determine what is occurring on your SQL Server right now is the fantastic free stored procedure sp_WhoIsActive.

This procedure can be executed as an alternative to sp_who2 to give far more insightful and useful information.

There are numerous different switches to control behaviour, view all options by executing: –

EXECUTE sp_WhoIsActive @help = 1;

My particular favourite is @get_plans to return the estimated plan from cache of all current connections. This allows you to immediately drill through to the plan if you think you can identify the culprit root cause of your performance issue.

Another great option is to log the results to a table for later analysis. This can be achieved by the following code: –


DECLARE @destination_table VARCHAR(24) = 'dbo.WhoIsActive_'
+ CONVERT(VARCHAR, GETDATE(), 112);
DECLARE @schema NVARCHAR(1000);

EXEC dbo.sp_WhoIsActive @format_output = 0, @return_schema = 1, @get_plans = 1,
@schema = @schema OUTPUT;

SET @schema = REPLACE(@schema, '&amp;amp;lt;table_name&amp;amp;gt;', @destination_table);

IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id=OBJECT_ID(@destination_table))
BEGIN;
EXECUTE(@schema);
END;

DECLARE @i INT = 0;

WHILE @i &amp;amp;lt; 100
BEGIN;
EXEC dbo.sp_WhoIsActive @format_output = 0, @get_plans = 1,
@destination_table = @destination_table;

SET @i = @i + 1;

WAITFOR DELAY '00:00:05';
END;
GO

Just be aware that table might grow quite large so this is probably not something you want to run continuously.

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

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 &amp; 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