Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for July, 2014

Script : Find Currently executing Queries, Blocking, Waits, Statement, Procedure, CPU

Posted by blakhani on July 17, 2014


This  is one of my all time favorite script which I use most of the time in troubleshooting performance issues on live systems. This helps in findings below:

  • What is the performance bottleneck?
  • Is there any blocking? If yes, who is the blocker?
  • What are the queries which are executing currently?
  • What is the name of the stored procedure running currently?
  • Which statement in stored procedure is getting executed right now?
  • Who is consuming CPU right now? What are the high CPU queries?
  • Who is doing lots of IO right now?

Column heading is self-explanatory. You need to adjust ORDER BY clause based on your need. In below query, I am showing top CPU consumers at the top.

SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in 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 is the partial output

image

Note that since it shows currently executing request, it won’t show the request which are not doing anything like sleeping connection or awaiting command.

Hope this helps

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

    Posted in Script | Tagged: , , , , , | 13 Comments »

    Tips and Tricks : Delete Complete History and Settings of Management Studio

    Posted by blakhani on July 15, 2014


    How many times you have browsed internet and cleared complete history of Internet Explorer (or any other browser)? I have done it many times and due to various reasons. Few days back one of my friend asked that how can same thing be achieved in SQL Server 2014 Management Studio? His goal was to delete all the saved setting of management studio for his login. This would include:

    • Server List and Login credentials saved on login screen.
    • History of reports launched – (Read Pinal’s blogs)
    • Settings change in Object Explorer Details like sorting and ordering of columns.
    • All setting which are saved under Tools > Options in menu bar.

    (This does NOT effect registered severs list)

    This all information is saved under file SQLStudio.bin.

    Below is the file in SQL 2012 Management Studio. (Go to start>run> and paste below path)

    %appdata%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

    Below is the file in SQL 2014 Management Studio.

    %appdata%\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

    image

    In earlier version of SQL Server (before SQL 2012), the path was different. Refer Jonathan’s blog

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Management Studio, SSMS | Tagged: , , , , , | Leave a Comment »

    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
  • Posted in Troubleshooting | Tagged: , , , , | 2 Comments »