SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats


In any new release of SQL Server, one of the features that I first explore is the DMV enhancements. In SQL Server 2016 CTP 2, 24 new columns are added to sys.dm_exec_query_stats (Transact-SQL) provide information about memory grants and parallel thread usage!

I have documented in the table below the equivalent XML nodes from the execution plan which are now available in the DMV output. This saves you a lot of time from tracking down each and every plan and then shredding the XML to get the relevant information! I had documented how to parse the XML plans in an older post of mine.

Column Name

Comments

Equivalent XML Execution Plan Node

total_dop

This information is available in the XML plan and now without having to parse XML, you will be able to get this information using direct SELECTs to a DMV.

                                <QueryPlan DegreeOfParallelism=”4″ MemoryGrant=”28968″ CachedPlanSize=”128″ CompileTime=”9″ CompileCPU=”9″ CompileMemory=”832″>

last_dop

min_dop

max_dop

total_grant_kb

Again you are saved from XML parsing and this information lets you get statistical averages of the memory grants per execution.

<MemoryGrantInfo SerialRequiredMemory=”7168″ SerialDesiredMemory=”7392″ RequiredMemory=”28744″ DesiredMemory=”28968″ RequestedMemory=”28968″ GrantWaitTime=”0″ GrantedMemory=”28968″ MaxUsedMemory=”4384″ />

last_grant_kb

min_grant_kb

max_grant_kb

total_used_grant_kb

Again this is something that you can fetch from the XML execution plan but it is available in the DMV now. This lets you check how much of the granted memory is being used during execution.

last_used_grant_kb

min_used_grant_kb

max_used_grant_kb

total_ideal_grant_kb

This is the a good field to look at to check if there was a difference in the ideal and the actual granted values. If this is an abnormal value, then this warrants investigation.

last_ideal_grant_kb

min_ideal_grant_kb

max_ideal_grant_kb

total_reserved_threads

This is quite useful when tracking down queries which have a high number of worker threads usage

            <ThreadStat Branches=”1″ UsedThreads=”4″>
              <ThreadReservation NodeId=”0″ ReservedThreads=”5″ />
            </ThreadStat>

last_reserved_threads

min_reserved_threads

max_reserved_threads

total_used_threads

last_used_threads

min_used_threads

max_used_threads

 

The DMV output with the new columns is shown in the screenshot below.

SQL Server 2016 Public Preview (CTP2) - sys.dm_exec_query_stats

There are a number of views which have been added for supporting the new Query Store feature which are:

  • sys.database_query_store_options 
  • sys.query_context_settings 
  • sys.query_store_plan
  • sys.query_store_query
  • sys.query_store_query_text
  • sys.query_store_runtime_stats  
  • sys.query_store_runtime_stats_interval

Additionally, two new views have been added for supporting the new row level security feature which are:

  • sys.security_predicates
  • sys.security_policies

More details about the above views in a later post!

Reference:

sys.dm_exec_query_stats (SQL Server 2016 CTP2)

Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Advertisements

5 thoughts on “SQL Server 2016 Public Preview (CTP2) – sys.dm_exec_query_stats

  1. Hello Amit,

    Thanks for sharing great stuff !

    Text is not visible in the table, looks like font color is set to white. Can you please fix it ?

    Br,
    Anil

    Like

  2. Pingback: SQL Server – Query Plans with DesiredMemory | Learning in the Open

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s