Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

1 Answer 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?

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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