Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘A – Z Series’ Category

A-Z of In-Memory OLTP: Online gaming casinos can handle more data per second

Posted by blakhani on September 2, 2014


I have been working with this new feature since its release and I am always amazed to see how it can change the world. Running an online casino is no joke. Not only do providers need to make sure that their customers are protected from data theft, but they also need to be certain that servers are always stable especially during peak hours. An overloaded server disconnects a player from the game, and providers can’t afford that to happen all the time. Competition is tight, so online casinos need to keep their customers happy.

Cryptologic, the world’s first casino brand to offer gaming services to the public in 1996 through their brand InterCasino, said that in the past, they used to only handle 14,000 people on one server. But today, according to the data by Statistica, online casino is now a 39.5 billion dollar industry that’s played by millions of people around the world. A stable server today should at least be able to handle a quarter of a million requests per second.

A case study by Microsoft revealed how a casino gaming site can scale up to 250,000 requests per second, and improve the gaming experience of players through an In-Memory Online Transaction Processing (OLTP) solution in Microsoft SQL Server 2014. According to the casino gaming site, it used to be able to handle only 30,000 batch requests per second. But with the upgraded system, not only can it handle 250,000 requests per second, but also provide their players a smooth gaming experience.

As I explained in first part of series SQL Server that uses a main memory optimization and no-locking/no-latching concurrency control in order to remove the jam that’s causing the scaling up. Using the OLTP in the feature has also improved the response time to 2-3 milliseconds, which is way faster than the old system that the online casino gaming site uses that needed a 50 millisecond delay.

Every second counts in a casino game – May it be poker or slot machine. By integrating an in-memory OLTP in Microsoft’s SQL server 2014, casino providers will be able to provide a faster and more stable gaming experience to their customers. Read more about Microsoft’s case study here

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

    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
  • 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 : Enhancement in Catalog Views

    Posted by blakhani on February 20, 2014


    During my interaction with customers, catalog views and DMV words are used interchangeably. In reality, there is a difference between the two. Till SQL Server 2000, there were system table like sysdatabases, sysobjects etc. SQL 2005 and above the new concept of catalog view was introduced which is replacement of system tables. It is the way to see the catalog metadata of SQL Server. The data showed in the catalog view is physically stored on the disk. On the other hand, DMV is a dynamic management view. It’s similar to sysprocesses which we have used in SQL 2000. As soon as query is fired, the data is read from memory structures and showed in row-column format.

    In this blog, I’ll try to show the changes to existing catalog view to support In-Memory OLTP (code name Hekaton)

    sys.tables

    There are few new columns introduced in sys.tables to get details about In-Memory Tables.

    Column Name Values
    durability  0 = SCHEMA_AND_DATA
    1 = SCHEMA_ONLY
    durability_desc  Description of value in durability Column
    is_memory_optimized  1 – Yes, 0 – No

     

    In my database, I have created two tables; One disk based and one in-memory optimized.

    SELECT name
        ,durability
        ,durability_desc
        ,is_memory_optimized
    FROM sys.tables

    image

     

    sys.table_types

    There is a new column is_memory_optimized is introduced. I would create a type using below T-SQL taken from Books online

    Use SQLSeverHelp_IMO
    go
    CREATE TYPE dbo.SalesOrderDetailsType AS TABLE
    (
           so_id int NOT NULL,
           lineitem_id int NOT NULL,
           product_id int NOT NULL,
           unitprice money NOT NULL,
    
           PRIMARY KEY NONCLUSTERED (so_id,lineitem_id)
    ) WITH (MEMORY_OPTIMIZED=ON)
    GO
    

    Now, look at catalog view.

    select  name, is_memory_optimized 
    from    sys.table_types
    

    image

    sys.indexes

    With the introduction of Hash Indexes, there is new value for type_desc column as shown below.

     SELECT object_name(object_id) TableName
        ,name 'IndexName'
        ,type_desc
    FROM sys.indexes
    WHERE object_id IN (
            SELECT object_id
            FROM sys.tables
            WHERE is_memory_optimized = 1
            )
    
    

    image

    sys.index_columns

    No Change in the columns but it’s important to note that since there is no sorting order in HASH Indexes, the column is_descending_key is shown as 0 which is ignorable.

    sys.data_spaces

    New values available in type column as below.

    Column Name Values
    type  FX = Memory-optimized tables filegroup
    type_desc  MEMORY_OPTIMIZED_DATA_FILEGROUP

     

    use SQLSeverHelp_IMO
    go
    select  * 
    from    sys.data_spaces
    

    image

     

    sys.sql_modules

    A new column uses_native_compilation has been added to identify if it’s a natively compiled procedure. Same is true for sys.all_sql_modules as well. We already have a proc in the database.

    Select object_name(object_id), 
        uses_native_compilation  
    from  sys.sql_modules 
    

    image

     

    In next post, we would explore DMVs related to In-Memory OLTP.

     

  • 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: , , , , , , , , , , , , | Leave a Comment »