Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,668,941 hits
  • Select GETDATE()

    September 2019
    M T W T F S S
    « Apr    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    30  
  • Advertisements

Archive for the ‘Hekaton Series’ Category

A-Z of In-Memory OLTP : Performance Tip for Placement of Data and Delta Files (Checkpoint Files)

Posted by blakhani on April 29, 2014


While preparing my session about In-Memory OLTP for internal audience, I learned something new about placement of checkpoint files. In case you don’t know about checkpoint files, I would like you to read about checkpoint (part 1 and part 2).

Data files and Delta files are created in round robin fashion. Let’s assume that we have two drives W and X and we want to distribute the IO load of In-Memory table. In general, for disk based table, what we used to do? Create the file on each drive? Yes, that would work for disk bases table but there is a little behavior which we should know before doing such thing for in-memory tables.

For demo purpose, I am going to use two folder on same drive to mimic two drives.

USE master
GO
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W'
    ,filename = 'c:\dataHK\ObjectsDemo_W'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X'
    ,filename = 'c:\dataHK\ObjectsDemo_X'
    ) TO filegroup ObjectsDemo_mod
GO

So, I have two folders “ObjectsDemo_W” and “ObjectsDemo_X” to represent W and X drives. Once we create a in-memory table, we should see checkpoint file pairs (CFP) created. 
USE ObjectsDemo
GO

CREATE TABLE dbo.t1 (
    c1 INT NOT NULL PRIMARY KEY NONCLUSTERED
    ,c2 INT
    )
    WITH (memory_optimized = ON)
GO

Let’s have a look at folders now.

image

One folder (Drive W) contains only data files (pre-created at 16 MB each) and another folder (Drive X) contains only delta file (pre-created at 1 MB each). That would not put uniform load on the both folders. The files are placed in this fashion because data and delta files are created in container into round-robin fashion

Here is the tip: Since we have even number of drives, we should create two folders on each drive and place files in 4 containers instead of 2 containers. We need to remember that first two containers should be from same drive.

use master
go
IF EXISTS (
        SELECT *
        FROM sys.databases
        WHERE NAME = 'ObjectsDemo'
        )
    ALTER DATABASE ObjectsDemo SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE ObjectsDemo
GO

CREATE DATABASE ObjectsDemo
GO

ALTER DATABASE ObjectsDemo 
ADD filegroup ObjectsDemo_mod CONTAINS memory_optimized_data
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W1'
    ,filename = 'c:\dataHK\ObjectsDemo_W1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_W2'
    ,filename = 'c:\dataHK\ObjectsDemo_W2'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X1'
    ,filename = 'c:\dataHK\ObjectsDemo_X1'
    ) TO filegroup ObjectsDemo_mod
GO

-- Add Files 
ALTER DATABASE ObjectsDemo 
ADD FILE (
    NAME = 'ObjectsDemo_mod_X2'
    ,filename = 'c:\dataHK\ObjectsDemo_X2'
    ) TO filegroup ObjectsDemo_mod
GO


W1 and W2 are two container for mimicking two folder on W and same for X as well. Now, if we create a table, due to round robin, we should see below.

image

Now we have DATA file distributed and should have better usage of both driver. What you should do if you have ODD number of drives? Well, nothing because of round robin, data and delta files would be distributed automatically. 

Bottom line: If you have plans to create file stream containers on odd number of drives, create them using trick mentioned above. If you have even number of containers then no special consideration.

Hope you have learned something new today.

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

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

    A-Z of In-Memory OLTP : Native Compilation Advisor in SSMS

    Posted by blakhani on February 4, 2014


    In our last part of A-Z Series, we learned about Memory Optimization Advisor (wizard in SSMS) which can be used to migrate disk based tables to in-memory tables. This post is dedicated to another wizard which is for stored procedure. It is important to remember that, unlike Memory Optimization Advisor, this wizard is NOT to convert regular stored procedures into natively complied stored procedure. The idea of the “Native Compilation Advisor” is to scan the code of stored procedure and give advise about what to change in the stored procedure.

    Where is the wizard?

    The “Native Compilation Advisor” wizard can be launched by right clicking the stored procedure which we wish to convert to natively complied. In below screenshot, we can notice the option difference when we right click on regular stored procedure vs. natively complied stored procedure.

    image

    Image#1 – Comparison of right click.

    What is the purpose?

    As explained earlier, this Advisor would NOT convert regular stored procedure to natively complied but provide just the advice and pointers. Let me explain them via good boy and bad boy. We would use same database which we have been using earlier. Here is the blog to get script for database and tables.

    Good Boy!

    Since we already have database SQLSeverHelp_IMO and tables (DiskBasedTable and MyFirstMemporyOptimizedTable), Let’s create regular stored procedure as below.

    -- Create regular stored procedure in database and referencing IMO table
    Use SQLSeverHelp_IMO 
    GO
    CREATE PROCEDURE [dbo].[InsertName] @iID INT, @vFName VARCHAR(20), @vLName VARCHAR(20)
    AS 
    BEGIN  
    
      INSERT INTO dbo.MyFirstMemporyOptimizedTable
      VALUES (@iID, @vFName, @vLName);
    
    END
    GO

    Now, let’s launch the wizard by right clicking on InsertName stored procedure in SQL Server Management Studio (shown in image 1). As soon as we click on Native Compilation Advisor, we get welcome screen.

    image

    Image#2 – Welcome Screen.

    As we can read on the welcome screen – “The Native Compilation Advisor will assist you to discover and evaluate Transact-SQL elements in your stored procedure that are not supported in Native Compilation”. See, I told you! Okay, Let’s hit next.

    Below screen appears and you would notice that Next button is disabled.

    image

    Image#3 – Stored Procedure Validation – no errors

    This is because there is no T-SQL element which is not supported by Natively complied stored procedures. Cancel the wizard to exit.

    Bad Boy!

    Now, I am going to create stored procedure with some (actually many) elements which are not supported by natively complied stored procedure. Please note that this is for CTP2 and things might change in RTM.

    -- Create regular stored procedure in database   
    -- with many elements not supported by natively complied stored procedures  
    USE sqlseverhelp_imo 
    
    go 
    
    IF Object_id('usp_InsertName_Errors') IS NOT NULL 
      DROP PROCEDURE usp_insertname_errors 
    
    go 
    
    CREATE PROCEDURE [dbo].[Usp_insertname_errors] 
    @iID    INT, 
    @vFName VARCHAR(20), 
    @vLName VARCHAR(20) 
    AS 
      BEGIN 
          -- four part naming   
          SELECT * 
          FROM   hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 
    
          -- three part naming 
          SELECT * 
          FROM   sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 
    
          -- multivalued insert 
          INSERT INTO dbo.myfirstmemporyoptimizedtable 
          VALUES      (@iID, 
                       @vFName, 
                       @vLName), 
                      (@iID + 1, 
                       @vFName, 
                       @vLName) 
    
          -- Subquery 
          SELECT vfname 
          FROM   diskbasedtable 
          WHERE  iid IN (SELECT is_published 
                         FROM   sys.objects) 
    
          -- goto 
          GOTO hi 
    
          -- cursor 
          DECLARE c CURSOR FOR 
            SELECT iid, 
                   vfname 
            FROM   dbo.diskbasedtable 
    
          HI: 
    
          -- cte 
          WITH cte (i, j) 
               AS (SELECT iid, 
                          vfname 
                   FROM   dbo.diskbasedtable) 
          SELECT * 
          FROM   cte 
      END 
    
    go   
    

    if we run wizard for above stored procedure, we would have next button enabled. This is one of those next button which is not going to make your happy. Agree?

    image

    Image#4 – Stored Procedure Validation – with errors

    Fine, so there are problems. Hitting next would tell us more.

    image

    Image#5 – Final Results!

    As highlighted in image # 5 , we also have the option to “generate report” for these unsupported elements. Here is the report which I saved in html format. It gives us code, line number and they are grouped under T-SQL Element (like *, Four-part names etc.)

    Native Compilation Advisor evaluation results for [SQLSeverHelp_IMO].[usp_InsertName_Errors]

    Report Date/Time:2/3/2014 12:16 PM

    Transact-SQL Element Transact-SQL Code Line Number
    * SELECT * FROM hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 3
      SELECT * FROM sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 7
      WITH cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) SELECT * FROM cte 37
    Four-part names hk.sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 4
    Three-part names sqlseverhelp_imo.dbo.myfirstmemporyoptimizedtable 8
    Multi-row table-valued constructor VALUES (@iID, @vFName, @vLName), (@iID + 1, @vFName, @vLName) 12
    One-part names diskbasedtable 21
      cte 42
    IN iid IN (SELECT is_published FROM sys.objects) 22
    Subquery (SELECT is_published FROM sys.objects) 22
    GOTO GOTO hi 26
    Cursors CURSOR FOR SELECT iid, vfname FROM dbo.diskbasedtable 30
    WITH clause WITH cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) 37
      cte (i, j) AS (SELECT iid, vfname FROM dbo.diskbasedtable) 37

     

    Here is the link which is there in image # 5 http://go.microsoft.com/fwlink?LinkID=311587 talks about common patterns and their workarounds. (link might get change after release of the product)

    Conclusion

    Native Compilation wizard can be used to get advice from SQL Server about the changes which are needed to covert a regular stored procedure to natively compiled stored procedure. There are some common pattern of the problems where workaround can be used by following the link provided by the advisor. Conversation of stored procedure is still manual process and not as clean as Table Advisor (Memory Optimization Advisor). Using both wizards, we can get advice to take action prior to a migration. At the end I would say that this is the only wizard I have seen which doesn’t have finish button.

    Have you installed SQL Server 2012 CTP2 yet? If you haven’t yet, be sure to check out the SQL Server 2014 CTP2 so that you can follow this series and get your hands dirty.

    Stay Tuned!

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

    A-Z of In-Memory OLTP : Monitoring Memory Usage

    Posted by blakhani on January 16, 2014


    Monitoring is a human nature to be safe and avoid and unexpected failure. Whenever we drive a car at high speed, its human nature to have a look at speedometer. Whenever you go to visit doctor, he would do some monitoring of certain parameters and then ask relevant question to troubleshoot the problem which you tell him. (Oh God! Troubleshooting has become part of my examples, even for doctor!)

    In SQL Server, how do you normally monitor space used by a table? Generally we use sp_spaceused and provide table name which gives good amount of details. But interestingly enough, the store procedure would NOT work for in-memory tables as shown below.

    select count(*) as 'count' 
    from [dbo].[MyFirstMemporyOptimizedTable]
    go
    sp_spaceused 'MyFirstMemporyOptimizedTable'
    go
    

     

    image

    Our table MyFirstMemporyOptimizedTable has good amount of rows and can’t be using zero space as reported by sp_spaceused. In order to get the memory used by in-memory tables, we need to use another DMV called dm_db_xtp_table_memory_stats. Now, how do I write a query? I generally look for the UI interface or standard report which gives me the data and then use profiler to find the nicely formatted query.

    I was able to find a standard report for memory usage of in-memory tables called “Memory Usage By Memory Optimized Objects” as shown below.

    image

    I launched the report and captured profiler to get the query. Here is the query which can get us the data for all IMO tables in the database. (captured by profiler, not written by me)

     SELECT t.object_id
        ,t.NAME
        ,ISNULL((
                SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_table_kb) / 1024.00)
                ), 0.00) AS table_used_memory_in_mb
        ,ISNULL((
                SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_table_kb - TMS.memory_used_by_table_kb) / 1024.00)
                ), 0.00) AS table_unused_memory_in_mb
        ,ISNULL((
                SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_used_by_indexes_kb) / 1024.00)
                ), 0.00) AS index_used_memory_in_mb
        ,ISNULL((
                SELECT CONVERT(DECIMAL(18, 2), (TMS.memory_allocated_for_indexes_kb - TMS.memory_used_by_indexes_kb) / 1024.00)
                ), 0.00) AS index_unused_memory_in_mb
    FROM sys.tables t
    INNER JOIN sys.dm_db_xtp_table_memory_stats TMS ON (t.object_id = TMS.object_id)

    Here is the output:

    image

    If we look at report, we would get exactly same output but in more “presentable” format as below.

    image

    If we look at the bottom area of report, it’s the output of earlier query.

    If you find other ways to monitor the memory usage, write it in comment section and share with the world.

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