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).

SQL Server 2016 Public Preview (CTP2) – Let’s see how it looks

The Public Preview of SQL Server 2016 is available for download on the Microsoft downloads site. The feature highlights are available in this blog post. I thought I will give blog about what’s different in the setup of the Database Engine.

SQL Server 2016 CTP2 Setup

The basic screens remain the same. The screen to select the edition is the same and the there is no change in the “License Terms”, “Global Rules”, “Product Update”, “Install Setup Files”, “Install Rules” and “Setup Role” pages.

The first major change that you will notice is in the “Feature Selection” page. This is where you will notice a change. Namely, the “PolyBase Query Service for External Data“. This installs two services:

1. SQL Server Polybase Engine Service which creates, coordinates and executes the parallel query plan against external data sources.

2. SQL Server Polybase Data Movement Service which manages communication and data transfer between SQL Server and the external data sources which will be used by the instance.

Note that these services are not instance aware and like Integration Services, there can only be one service per Windows host.

SQL Server 2016 CTP2 Setup

The next screen where you will notice a change is the Feature Rules page which checks if Oracle JRE 7 Update 51 is available or not. This is required for the PolyBase service since I had selected that in the previous screen.

SQL Server 2016 CTP2 Setup

If you don’t have the requisite Oracle JRE version running, then you will be prompted with the following message:
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

The forwarding link will take you to a webpage where you can download the latest JRE update. Once that is done, you will be able to continue with the installation. Another interesting part was the check for KB2919355 which is the Windows 8.1 Update which contains a cumulative set of security updates, critical updates and updates. So the setup has an OS related check as well.

The Server Configuration page allows you to choose the service accounts for the two PolyBase services.

SQL Server 2016 CTP2 Setup

The Database Engine Configuration page comes with a nifty tempdb file configuration which has a bit of learning to do but provides the opportunity to create additional tempdb files during the installation itself.

SQL Server 2016 CTP2 Setup

Once the setup is complete, you can connect to the database engine using Management Studio and you will see that your version number is 13.0.200. Also, you will notice that there are three user databases: DWConfiguration, DWDiagnostics and DWQueue which is related to the PolyBase service. More on that maybe in a future post.

So that was all about the Database Engine setup for the new SQL Server 2016 Public Preview release.

SQL Server 2016 CTP2 SSMS 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).