Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,581,868 hits
  • Select GETDATE()

    March 2019
    M T W T F S S
    « Apr    
     123
    45678910
    11121314151617
    18192021222324
    25262728293031
  • Advertisements

Posts Tagged ‘query’

A-Z of In-Memory OLTP : Usage of sys.dm_exec_query_stats and sys.dm_exec_procedure_stats

Posted by blakhani on January 23, 2014


Have you ever played any game where crime has occurred in past and you need to look at evidences to find out who was criminal? Any post-mortem needs data collected during past. In SQL Server world DBAs are detectives where they sometime needs post-mortem about performance issue reported on server. To equip them SQL Server provides the statistics about the query and being is constantly gathered once they are executed. As long as plan is there is cache, we can use stats DMVs (sys.dm_exec_query_stats and sys.dm_exec_procedure_stats) to find the time taken by query and resources taken in the past. If you try to find query to find Top 10 CPU / IO consumers in the past, you will easily get query.

There is a little difference in this behavior when it comes to In-Memory OLTP. The gathering of execution stats collection is off by default. Here is the quick repro about what I mean. I have created two procedures to do same task.

One is interoperable.

Create Procedure [dbo].[SelectName_traditional]
@iID int

as Select vFName,vLName from dbo.MyFirstMemporyOptimizedTable
  where iID = @iID
GO

and another is natively compiled

CREATE PROCEDURE [dbo].[SelectName_Native] @iID INT
  WITH 
    NATIVE_COMPILATION,  SCHEMABINDING,  EXECUTE AS OWNER
AS 
BEGIN ATOMIC 
  WITH 
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')  Select vFName,vLName from dbo.MyFirstMemporyOptimizedTable
  where iID = @iID

END

 

Let’s execute both stored procedure 10 times using below code

exec SelectName_traditional 1
go 10
exec SelectName_Native 1
go 10

 

If I look at stats DMV, I would see information only for “SelectName_traditional” procedure as below. Here is the query for query_stats DMV

SELECT 
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS CPU_TIME
FROM SYS.DM_EXEC_QUERY_STATS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP
where QT.TEXT like '%MyFirstMemporyOptimizedTable%'

 

image

Here is the query for procedure_stats DMV

select object_id,
object_name(object_id) as 'object name',
cached_time,last_execution_time,
execution_count,total_worker_time,
last_worker_time,min_worker_time,
max_worker_time,total_elapsed_time,
last_elapsed_time,min_elapsed_time,
max_elapsed_time 
from sys.dm_exec_procedure_stats
where database_id=db_id('SQLSeverHelp_IMO') 

image

As we can see above that there is no entry for SelectName_Native.

Above confirms that, by default collection is off as its recommended to turn on stats collection only for a limited amount of time as needed for troubleshooting. This is done to minimize the performance impact on the production workload due to stats collection. We can turn on (and turn off as well) the collection of statistics by running below “control” procedures. One procedure is for collection at query level (within natively complied procedure) and other is at procedure level.

sys.sp_xtp_control_query_exec_stats: Here is the tooltip shown in SSMS.

image


sys.sp_xtp_control_proc_exec_stats:Here is the tooltip shown in SSMS.

image

As you would notice via tool tip that “query” stats can be turned on for a specific database id and object id, which gives us granular control of data collection. Once they are turned on, we can see the entry in statistics DMVs.

image

I have done DBCC FREEPROCCACHE and ran the same batch again to execute both procedure 10 times. Here is the result of earlier query for procedure stats DMV.

image

Now we can see two rows. Notice Zero values in various columns. Let’s do the same for query level.

image 

and look at query stats DMV now..

image

Now, we can see new row for natively complied procedure as well.

Few important notes, based on my learning on CTP2.

  1. the changes done via control procedures are reverted back to OFF after restart.
  2. DBCC FREEPROCCACHE doesn’t impact the entry shown for natively complied procedures in stats DMV as they are not in procedure cache.
  3. CPU, IO column value would be zero for natively compiled procedures.
  4. Turning off the control procedures doesn’t remove the value. Only way to remove them is either drop and recreate the procedure or restart.

Hope you have learned something new today.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , , , , , , | 5 Comments »

    A-Z of In-Memory OLTP : What’s new in Execution Plan

    Posted by blakhani on January 21, 2014


    I still remember the day when I was interviewed for my job with Microsoft. It was kind of a discussion about SQL Sever internals rather than interview. Of course, we had discussion about SQL Server Performance Tuning as well. Interviewer asked – “If there is a query taking very long time to execute, what would you do?” I tried few answers like blocking, resource bottleneck and also said that we can look at the execution plan. I was aware of few bad operators in execution plan and I explained those. Anyways, after 2 hours of discussion, the interviewer was satisfied that I know SQL Server features. I also got feedback that more than current knowledge, I have passion to learn, help someone and share my knowledge. End result – I was hired! After joining Microsoft SQL Support team, I am learning since day 1 and learning and sharing never stopped for me. Performance tuning has been my favorite and I would still love to see execution plans to learn more about query execution.

    In this blog of A-Z of In-Memory OLTP Series, we would talk about execution plan related changes with respect to In-Memory OLTP.

    Limitation on Natively Compiled Stored Procedure

    If we try to get execution plan of any natively complied stored procedure via SSMS using “Include Actual Execution Plan” and executing it; it won’t work. There is no plan in procedure cache for natively compiled stored procedures. Even if we try set statistics profile on, it won’t work.

    image

    Only way to get query plan for natively compiled procedure is to get estimated plan. That can be done using TSQL “set showplan_xml on” statement or via SSMS “Display Estimated Execution Plan”. This restriction is not applicable for TSQL statements touching in-memory tables and we can get actual plan as well.

    How to Identify which In-Memory Index is being used?

    We have earlier created table called MyFirstMemporyOptimizedTable with Primary Key (with hash index) and another Hash Index. There is another type of non-clustered index in-memory world called range index, which we would cover later in the series. We would create MySecondMemporyOptimizedTable with range index and I’ve highlighted below.

    CREATE TABLE [dbo].[MyFirstMemporyOptimizedTable]
    (
        [iID] [int] NOT NULL,
        [vFName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL,
        [vLName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL
    
    INDEX [imo_idx_vFname] NONCLUSTERED HASH 
    (
        [vFName]
    )WITH ( BUCKET_COUNT = 1048576),
    CONSTRAINT [imo_pk_iID] PRIMARY KEY NONCLUSTERED HASH 
    (
        [iID]
    )WITH ( BUCKET_COUNT = 1048576)
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
    
    GO
    
    CREATE TABLE [dbo].[MySecondMemporyOptimizedTable]
    (
        [iID] [int] NOT NULL,
        [vFName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL,
        [vLName] [varchar](20) COLLATE Latin1_General_100_BIN2 NOT NULL
    
    INDEX [imo2_idx_vFname] NONCLUSTERED  
    (
        [vFName]
    ),
    CONSTRAINT [imo2_pk_iID] PRIMARY KEY NONCLUSTERED HASH 
    (
        [iID]
    )WITH ( BUCKET_COUNT = 1048576)
    )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
    
    

    Hash Index

    Below query is going to use Hash index

    select count(*) 
    from MyFirstMemporyOptimizedTable
    where iID = 0

     

    And we would see Index Seen with “NonClusteredHash” keyword, which denoted hash index being used.

    image

    If we look at “storage” for the “Index Seek” operator, it would be “MemoryOptimized”

    image

    Range Index

    Here is a query for Range Index for second table

    select count(*) 
    from MySecondMemporyOptimizedTable
    where vFName= 'Balmukund'
    

     

    image

    Estimated I/O Cost is zero

    We can also notice that Estimated I/O Cost is zero. Of course it would be zero as table is residing in memory.

    image

    NoParallelForMemoryOptimizedTables is a reason for non-parallelized plan

    While looking at XML plan of the query, I noticed something new and I have drawn a box.

    image

    NonParallelPlanReason="NoParallelForMemoryOptimizedTables" This would mean that in-memory table operations would not go for parallelism.

    Hope you have learned something new today.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in A - Z Series, In Memory OLTP, In-Memory OLTP, SQL Server 2014 | Tagged: , , , , , , , , , , , , , , , | 2 Comments »