DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Parallelism: Estimated Costs of All Queries

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.

Grant Fritchey user avatar by
Grant Fritchey
·
Feb. 21, 17 · Tutorial
Like (2)
Save
Tweet
Share
4.52K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Type Variance in Java and Kotlin
  • Playwright vs. Cypress: The King Is Dead, Long Live the King?
  • Simulate Network Latency and Packet Drop In Linux
  • Cloud Native London Meetup: 3 Pitfalls Everyone Should Avoid With Cloud Data

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: