By: Jared Westover | Updated: 2024-08-22 | Comments | Related: > Performance Tuning
Problem
Did you know that the Cost Threshold for Parallelism (CTFP) affects SQL Server's choice of a trivial execution plan? One area that can suffer from this setting is the optimizer's ability to suggest index recommendations. When SQL Server picks a trivial plan, it skips suggesting any missing indexes. So, if you've set a high CTFP and run simple queries, you might never get those handy index recommendations.
Solution
In this article, we'll explore how raising the CTFP affects missing index recommendations. First, we'll look at what the CTFP is and why you might want to increase it from the default setting. Next, I'll show you what happens to SQL Server's ability to suggest index recommendations. The goal of this article isn't to argue for keeping the default CTFP but to highlight one side effect of raising it.
Cost Threshold for Parallelism
Microsoft defines the Cost Threshold for Parallelism (CTFP) as an option that sets the point at which SQL Server creates and runs parallel query plans. When a plan runs parallel, multiple CPUs work on it to try and make it run faster. The number of CPUs used depends on your Max Degree of Parallelism (MAXDOP) server setting. Setting the MAXDOP to 1 forces SQL to run all your queries serially.
By default, SQL Server sets the CTFP to 5. You can check this setting in a couple of ways.
With SQL Server Management Studio (SSMS)
You configure both settings at the server level, not per database, although you can add a query hint to control MAXDOP. To change these settings, open SSMS, right-click on the Server node, and choose Properties from the options. Then, go to the Advanced page. You'll find both settings under the Parallelism section.
With T-SQL
The code below returns the CTFP and the MAXDOP settings.
-- mssqltips.com
SELECT name,
value,
value_in_use
FROM sys.configurations
WHERE name IN ( 'cost threshold for parallelism', 'max degree of parallelism' );
GO
Results:
As the screenshot above shows, I set the MAXDOP to 4 and CTFP to 35.
Trivial Plans
As the name implies, SQL Server generates trivial plans with low query cost and complexity. SQL Server doesn't think the cost of running the query justifies FULL optimization. The code below is an example of a query that would likely generate a trivial plan. Please note that this example assumes the plan doesn't cause parallelism.
SELECT Colum1,
Colum2
FROM Table1
WHERE Column3 = 'a';
What type of query doesn't qualify for a trivial plan? A few examples include:
- Joining multiple tables.
- Adding ORDER BY clauses without a supporting index.
- Using an inequality predicate (e.g., WHERE Id > 4).
- Aggregates (e.g., SUM, COUNT) with a GROUP BY.
Any of the examples above cause the optimizer to generate a full plan. Next, let's look at how the optimizer skips index recommendations with trivial plans.
Missing Index Recommendations
Every time I see a green missing index recommendation, it makes me smile. Even if I don't create the index, it's a sign that SQL Server is on my side and trying to help. However, even Microsoft's documentation says it doesn't create missing index suggestions for trivial query plans. SQL Server generates index recommendations during a sub-step of the optimization process. If SQL Server uses the trivial plan, it says, "all done," and moves on to the execution phase.
Demo Dataset
To see trivial plans in action, we need to create a dataset. The code below creates one database and one table with 3 million rows. As with any demo dataset, please don't create this in a production environment.
-- mssqltips.com
USE master;
IF DATABASEPROPERTYEX('MissingIndexDemo', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE MissingIndexDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MissingIndexDemo;
END;
GO
CREATE DATABASE MissingIndexDemo;
ALTER DATABASE MissingIndexDemo SET RECOVERY SIMPLE;
GO
USE MissingIndexDemo;
GO
CREATE TABLE dbo.BigTable
(
Column1 INT IDENTITY(1, 1) NOT NULL,
Column2 INT NOT NULL,
Column3 INT NOT NULL,
Column4 INT NULL,
Column5 VARCHAR(2000)
CONSTRAINT PK_BigTable_Column1
PRIMARY KEY CLUSTERED (Column1)
);
INSERT INTO dbo.BigTable
(
Column2,
Column3,
Column4,
Column5
)
SELECT (ABS(CHECKSUM(NEWID()) % (100000 - 1 + 1)) + 1) AS Column2,
(ABS(CHECKSUM(NEWID()) % (10000 - 1 + 1)) + 1) AS Column3,
(ABS(CHECKSUM(NEWID()) % (100 - 1 + 1)) + 1) AS Column4,
SUBSTRING(
REPLICATE('abcdefghijklmnopqrstuvwxyz', 2),
(ABS(CHECKSUM(NEWID())) % 26) + 1,
(ABS(CHECKSUM(NEWID()) % (1000 - 100 + 1)) + 100)
) AS Column5
FROM
(
SELECT TOP 3000000
ROW_NUMBER() OVER (ORDER BY s1.object_Id) AS Number
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
) numbers;
GO
Now, let's look at a simple query that often runs on our system. You might even say it's a trivial query, and with that, the puns end.
-- mssqltips.com
SELECT Column1,
Column5
FROM dbo.BigTable
WHERE Column4 = 1;
By clicking on the SELECT node, we can check the execution plan to see if SQL Server chose a trivial plan. Since the cost was below the threshold, the optimizer created a trivial plan and skipped the remaining steps.
My issue with this query is that an index would help my filter predicate on Column4. However, the optimizer doesn't return a hint because the query is simple and costs less than two movie tickets.
Rising Cost of Parallelism
For the last decade, there's been a consensus in the SQL Server community that a value of 5 for CTFP is far too low for most SQL Servers. Some people suggest setting the value between 25 and 50, and a few say 100 or higher. However, 5 is still the default value when you install SQL Server or provision an Azure SQL Database or Managed Instance.
If you remember from above, my CTFP is set to 35 because I'm a middle-of-the-range type of person. I've included a few SQL Server experts and their general advice for cost threshold settings:
- Monica Rathbun: Recommends a range of 35-50 during a SQLBits presentation.
- Erik Darling: Suggests starting at 50 but adjusting depending on the workload.
- Michael J Swart: Found that changing from 5 to 50 showed a massive improvement.
- Grant Fritchey: In the book SQL Server 2022 Query Performance Tuning, Grant recommends a scientific method for finding the value but says 35 is a good starting point.
- Kendra Little: Writes that 5 is too low and suggests that 50 is a safe default.
An important note: Most SQL pros suggest setting the value based on your workload and not blindly inputting one.
Now that we have decided to use a value greater than 5, how can we find trivial plans that might be missing an index recommendation?
Finding Trivial Plans
Since I set the cost threshold to a moderate value, how can we find the trivial plans? I've included two primary options below. Maybe you'll come up with more and post them in the comments section. Additionally, I used SqlQueryStress to generate the 2,500 executions in the screenshots.
Plan Cache
The first place we can look is in the Plan Cache. When you execute a query in SQL Server, a plan either exists or the optimizer generates and stores it in the Plan Cache. I've included a sample query below that shows the cost from our estimated plan and the count of executions. Also, I added a filter for executions over 100. Feel free to customize it to pull more information. For example, you can use the sql_text column for a WHERE clause.
-- mssqltips.com
;WITH xmlnamespaces
(
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT TOP (50)
DB_NAME(t.[dbid]) AS [database_name],
SUBSTRING( t.[text],
(qs.statement_start_offset + 2) / 2,
(CASE
WHEN qs.statement_end_offset = -1 THEN
LEN(CONVERT(NVARCHAR(MAX), t.[text])) * 2
ELSE
qs.statement_end_offset + 2
END - qs.statement_start_offset
) / 2
) as sql_text,
qs.execution_count,
qn.n.value('@EstimatedTotalSubtreeCost', 'FLOAT') AS total_subtree_cost
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//RelOp') AS qn(n)
WHERE t.dbid = DB_ID()
AND qp.query_plan.exist('//StmtSimple[@StatementOptmLevel="TRIVIAL"]') = 1
AND qs.execution_count > 100
ORDER BY qs.execution_count DESC;
GO
Results:
With this information, you get a good idea of how many trivial plans you have and how close they are to the cost threshold.
You could also use Brent Ozar's sp_BlitzCache. After installing the stored procedure, run it using the code below.
EXECUTE sp_BlitzCache @sortorder = 'executions', @Top ='5', @ExportToExcel = 1; GO
Results:
In the Warnings column, you'll see mentions of trivial plans. For more information on sp_BlitzCache, including what the options mean, check out the articles and videos at BrentOzar.com.
Query Store
With SQL Server 2016, Microsoft introduced Query Store, a built-in tool for capturing query performance metrics such as:
- Query execution counts.
- Estimated execution plans.
- Query wait stats, etc.
If you use Query Store, the column is_trivial_plan in the DMV sys.query_store_query shows whether the optimizer chose a trivial plan. I've included a sample query below.
-- mssqltips.com
;WITH xmlnamespaces
(
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT TOP (50)
t.query_sql_text,
rs.last_execution_time,
rs.count_executions,
qn.n.value('@EstimatedTotalSubtreeCost', 'FLOAT') AS total_subtree_cost,
p.plan_id,
p.query_plan,
rs.avg_cpu_time,
rs.avg_duration,
p.is_trivial_plan
FROM
(
SELECT CAST(p.query_plan AS XML) query_plan,
p.query_id,
p.plan_id,
p.is_trivial_plan
FROM sys.query_store_plan p
) p
INNER JOIN sys.query_store_query q
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
CROSS APPLY p.query_plan.nodes('//RelOp') AS qn(n)
WHERE p.is_trivial_plan = 1
AND rs.count_executions > 100
ORDER BY rs.count_executions DESC;
GO
Results:
Summary
Like my keyboard skills, missing index suggestions are by no means perfect, but they provide clues for improving SQL Server's performance. While reviewing Plan Cache or Query Store data, I focus on queries that execute hundreds or thousands of times per day. If a trivial query runs once or twice weekly, it's likely not worth investigating.
I'm curious to hear how others find missing indexes for trivial plans, especially when the Cost Threshold for Parallelism is set to a higher value.
Key Points
- When choosing a trivial plan, the optimizer skips missing index recommendations. The Cost Threshold for Parallelism plays an important role in SQL Server's choice of optimization level.
- The default recommendation of 5 for CTFP is arguably too low today. To find your ideal number, use the links provided in this article and experiment, but not in Production.
- To find trivial plans, look at Query Store and Plan Cache. Just remember, Plan Cache doesn't last forever.
Next Steps
- If you want to deep dive into trivial plans, Paul White created an in-depth overview in his "Simple Parameterization and Trivial Plans" series.
- Looking for a set of free scripts to explore Plan Cache? Check out Glenn Berry's SQL Server diagnostic scripts. There's one for almost anything you need, and Glenn updates them regularly.
- Want an introduction to Query Store? If so, check out the SQLBits video "Query Store Best Practices," where Erin Stellato provides an overview.
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-08-22
