Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,584,220 hits
  • Select GETDATE()

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

Posts Tagged ‘performance’

Troubleshooting : Slow Delete Database from Management Studio

Posted by blakhani on July 10, 2014


Not very long ago, I had a database on my SQL 2012 Instance which was getting log shipped happily at frequency of 1 minute. Long long ago I have done such configuration for a demo purpose and forgot. Today I had “Memory Recall” when space was getting filled up with log backups. Since the demo was complete, I decided to drop the database. So I broke log shipping and tried dropping the database. What you do as a DBA to drop a database? Right Click > Delete .. huh?

image

When I clicked on “OK” button it was taking a long time. GUI to seem like its hanging. As usual, troubleshooting started! Ran my standard troubleshooting query to find out what is going on.

 SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'Blk by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Sec'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elaps Sec'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

Here was the result (I have removed few columns to avoid clutter)

session_id status cpu_time logical_reads writes Elaps Sec statement_text command_text
60 runnable 247734 42732577 29196 545.223 DELETE msdb.dbo.backupmediaset

FROM msdb.dbo.backupmediaset bms

WHERE bms.media_set_id IN (SELECT media_set_id

     FROM @media_set_id)

    AND ((SELECT COUNT(*)

  FROM msdb.dbo.backupset

  WHERE media_set_

[msdb].[dbo].[sp_delete_database_backuphistory]

 

Why would delete database do that? Well, it’s done by a small little checkbox which we never noticed.

image

That little checkbox executed this command (along with drop database). If we use “Script” button, this is the outcome

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'AdventureWorks2014'
GO
USE [master]
GO
DROP DATABASE [AdventureWorks2014]
GO

Now we know why it’s taking time but can this be made faster? Well, I check msdb database and there are few indexes which have been added in SQL Server 2014 which would help in this situation. Here is the quick comparison.

Select @@version
go
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     ColumnName = col.name
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND t.is_ms_shipped = 1
     AND t.name in ( 'backupfile', 'backupfilegroup', 'backupmediafamily', 'backupmediaset', 'backupset', 'restorefile', 'restorefilegroup', 
'restorehistory' ) ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id

image

image

If you are facing the same problem which I described on SQL 2008 or SQL 2012, you may want to try creating new indexes as advised on other blogs (search for “msdb performance tuning” in bing/google) but my only suggestion is that it might be unsupported.

If you clean msdb backup history regularly, you might not face the issue though. There is maintenance plan to do that. Try it out!

Hope this helps.

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

    Posted in Troubleshooting | Tagged: , , , , | 4 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 »