0

Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server. I'm hoping that column represents the same concept of cost that is used by the optimizer to determine if the query should be parallelized, but I'm not sure. Is that the case?

Edit: below is the script I'm using:

SELECT  text
      , objtype
      , refcounts
      , usecounts
      , size_in_bytes
      , disk_ios_count
      , context_switches_count
      , original_cost
      , current_cost
FROM    sys.dm_exec_cached_plans p
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
        JOIN sys.dm_os_memory_cache_entries e 
          ON p.memory_object_address = e.memory_object_address
WHERE   cacheobjtype = 'Compiled Plan'
        AND type IN ( 'CACHESTORE_SQLCP', 'CACHESTORE_OBJCP' )
ORDER BY objtype DESC
      , usecounts DESC;
1

From the documentation for sys.dm_os_memory_cache_entries, this represents the combined CPU + I/O cost that the optimizer uses both for the value of the plan (and those metrics) and also to determine how quickly to evict those plans from the cache. Though, for your use case, I suspect current_cost may be more relevant. Be curious to see what comes next - how exactly are you going to use this information?

  • Basically, I'm trying to determine a good rule-of-thumb for setting my cost threshold on the server by looking at the plan cache and getting some insight into the distribution of plans. If the cost represented by that DMV is analogous to the cost threshold, that gives me a starting point. – Stuart Ainsworth Oct 23 '13 at 21:53
  • so after a lot of review, I decided that it was NOT the same as the threshold cost. Decided to go another route. – Stuart Ainsworth Oct 24 '13 at 2:46
  • @Stuart Yeah, for some reason I didn't think it was quite the same as threshold cost. – Aaron Bertrand Oct 24 '13 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.