Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Parallelism: Estimated Costs of All Queries

DZone's Guide to

Parallelism: Estimated Costs of All Queries

If you're tuning your cost threshold for parallel implementations, you need to find out how much your plans cost first, and that itself isn't easy—or cheap.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

One question constantly comes up: "What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to?"

What Do Your Plans Cost?

I have a question right back at you. What do your plans currently cost? Let’s say, for argument’s sake, that all your plans have an estimated cost value (and all plan costs are estimates, let’s please keep that in mind, even on actual plans) of 3 or less. Do you need to adjust the cost threshold in this case? Probably not. But the key is, how do you look at the costs for your plans? Unfortunately, there isn’t a property in a DMV that shows this value. Instead, we have to query the XML:

WITH XMLNAMESPACES (
                      DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
                   )
, TextPlans
AS (SELECT CAST(detqp.query_plan AS XML) AS QueryPlan,
           detqp.dbid
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_text_query_plan(
                                              deqs.plan_handle,
                                              deqs.statement_start_offset,
                                              deqs.statement_end_offset
                                           ) AS detqp
   ),
  QueryPlans
AS (SELECT RelOp.pln.value(N'@EstimatedTotalSubtreeCost', N'float') AS EstimatedCost,
           RelOp.pln.value(N'@NodeId', N'integer') AS NodeId,
           tp.dbid,
           tp.QueryPlan
    FROM TextPlans AS tp
    CROSS APPLY tp.queryplan.nodes(N'//RelOp')RelOp(pln)
   )
SELECT qp.EstimatedCost
FROM QueryPlans AS qp
WHERE qp.NodeId = 0;


You can add other fields if you need to, but this query will return all the costs from the execution plans currently in cache.

Query Store

Does Query Store make this any easier? Not really. There isn’t a property that just shows the cost of the plans, so once again, we’re forced to query the XML:

WITH XMLNAMESPACES (
                      DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
                   )
, QueryStore
AS (SELECT CAST(qsp.query_plan AS XML) AS QueryPlan
    FROM sys.query_store_plan AS qsp
   ),
  QueryPlans
AS (SELECT RelOp.pln.value(N'@EstimatedTotalSubtreeCost', N'float') AS EstimatedCost,
           RelOp.pln.value(N'@NodeId', N'integer') AS NodeId,
           qs.QueryPlan
    FROM QueryStore AS qs
    CROSS APPLY qs.queryplan.nodes(N'//RelOp')RelOp(pln)
   )
SELECT qp.EstimatedCost
FROM QueryPlans AS qp
WHERE qp.NodeId = 0;


Conclusion

With these queries, you can get the values for the costs on your plans. This information can be used to determine how high you make your Cost Threshold for Parallelism setting based on actual knowledge. Just be cautious, this query can be quite expensive. The next step is to take these results and derive an average and a standard deviation. That’s where we’re going with the next blog post, using R to determine Cost Threshold for Parallelism.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
query store ,database ,database performance ,parallelism ,tutorial

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}