DBA- How To Find Out How Much Percentage Of Database Restoration Is Done?

DBA- How To Find Out How Much Percentage Of Database Restoration Is Done?

Sometime we run SQL Server agent jobs to restore databases. As restoration is in progress, the below query can be used to find out how much percentage of restoration is completed.

SELECT command,
       percent_complete,
       start_time,
       Txt.[text] AS Query 

FROM   sys.dm_exec_requests AS DER
       CROSS APPLY sys.Dm_exec_sql_text(DER.[sql_handle]) AS Txt

WHERE  command = 'RESTORE DATABASE'

Sql server script to find out blocking related information

Sql server script to find out blocking related information

  

Using  following query one can  find  out blocking related information such as

1)      which session is being blocked by which session ?

2)      User who is blocking ?

3)      Sql text for bloked and blocking session id’s

Scripts :-

      SELECT  CLIENT_NET_ADDRESS AS IP,
                  EC.SESSION_ID AS blocking_id ,
                  SESS.LOGIN_NAME AS [Who_is_blocking] ,
                  BLOCKINGSQL.TEXT AS [Blocking_statement] ,
                  WAITS.WAIT_TYPE AS [lock_type] ,
                  BLOCKED.SESSION_ID AS [victim_id] ,
                  BLOCKEDSQL.TEXT AS [what_is_blocked] ,
                  DB_NAME(BLOCKED.DATABASE_ID) AS [database]
      FROM SYS.DM_EXEC_CONNECTIONS AS EC
           
            INNER JOIN SYS.DM_EXEC_REQUESTS AS BLOCKED
                  ON EC.SESSION_ID = BLOCKED.BLOCKING_SESSION_ID
                   
            INNER JOIN SYS.DM_OS_WAITING_TASKS AS WAITS 
                  ON BLOCKED.SESSION_ID = WAITS.SESSION_ID
                 
            RIGHT OUTER JOIN SYS.DM_EXEC_SESSIONS SESS 
                  ON EC.SESSION_ID = SESS.SESSION_ID
                 
            CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS BLOCKINGSQL
           
            CROSS APPLY SYS.DM_EXEC_SQL_TEXT(BLOCKED.SQL_HANDLE) AS BLOCKEDSQL
      ORDER BY BLOCKING_ID

Retrive index related Information for all tables in a database

Retrive index related Information for all tables in a database

Use this scripts to find index related information for all table in a databases

 SELECT

                  DB_NAME(DB_ID()) AS DATABASENAME

            , SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMANAME

            , T.NAME AS TABLENAME

            , I.NAME AS INDEXNAME

            , I.TYPE_DESC AS INDEXTYPE

            , C.NAME AS COLUMNNAME

            , TYP.NAME AS DATATYPE

            , IC.IS_INCLUDED_COLUMN

            , C.IS_IDENTITY

            , I.IS_PRIMARY_KEY

            , I.IS_UNIQUE_CONSTRAINT

            , IC.IS_DESCENDING_KEY

            , I.IS_UNIQUE

            , I.FILL_FACTOR

      FROM SYS.TABLES T

            INNER JOIN SYS.INDEXES I ON T.OBJECT_ID =I.OBJECT_ID

            INNER JOIN SYS.INDEX_COLUMNS IC ON I.OBJECT_ID =IC.OBJECT_ID

                  AND I.INDEX_ID = IC.INDEX_ID

            INNER JOIN SYS.COLUMNS C ON IC.OBJECT_ID =C.OBJECT_ID

                  AND IC.COLUMN_ID = C.COLUMN_ID

            INNER JOIN SYS.TYPES TYP ON C.USER_TYPE_ID =TYP.USER_TYPE_ID

      WHERE 1=1

            AND T.IS_MS_SHIPPED = 0

            AND I.IS_HYPOTHETICAL = 0

ORDER BY DATABASENAME, SCHEMANAME, TABLENAME,INDEXNAME, IC.IS_INCLUDED_COLUMN, IC.KEY_ORDINAL

Graphical execution plan  - Introduction

               

When it comes Query optimization we don’t know from where to start. This series of articles will take you through or give you general idea about What is query optimization? I have tried to keep language as possible as simple. Without wasting your time more let’s check it out.

Let me give you general outline about Graphical execution plan

Graphical execution plan consists of total 79 operators, which are divided into 4  main categories.

·         Logical and physical operators

(query execution or (DML) statements.)

·         Cursor physical operators

(Transact-SQL cursor operations)

·         Parallelism physical operator

(Parallelism operations.)

·         Language element

(Assign, Declare, If, Select (Result), While)

The graphical operators are divided into 3 categories as shown above based on color code. In this article we will discuss only important operators used frequently

Before going further let’s understand What happens when we submit query to SQL SERVER ?

                The query submitted buy user gets processed in following steps

1)       Query parsing

2)      Generation of execution plan by query optimizer

3)      Query forwarded to storage engine which process actual query

Now we will see each step in details

1)                Query parsing

When you submit a query to SQL Server, it  gets checked for any syntax error ,if any present, sends error to user. When query is parsed , a parsed tree is generated which is nothing but logical steps to perform query submitted . Now this generatedParsed tree or Query tree is submitted to Query optimizer.

2)              Query optimizer

Using the query processor tree and the information about data present (like indexes, what types of joins can be used, statistics about data) it works out to find out What will be the optimal way to execute the query?  Finds it and generates an execution plan. It also considers CPU and IO cost.

                               

When Query is submitted to optimizer , it generates many Query plans and decide which query plan will be beneficial to use in terms of cost (CPU ,IO, EXECUTION TIME)

                               

3)      Query forwarded to storage engine which process actual query

Query optimizer then sends optimized Query plan generated by Query optimizer to storage engine to retrieve data from database or perform operation specified.

 

You can use following query to find out cached query plans in plan cache

SELECT      [CAP].[REFCOUNTS]

            ,[CAP].[USECOUNTS]

            ,[CAP].[OBJTYPE]

            ,[SQT].[DBID]

            ,[SQT].[OBJECTID]

            ,[SQT].[TEXT]

            ,[QP].[QUERY_PLAN]

FROM SYS.DM_EXEC_CACHED_PLANS CAP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CAP.PLAN_HANDLE) SQT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(CAP.PLAN_HANDLE) QP

ORDER BY [USECOUNTS]

Before going further lets discuss what is seek and scan

                Scan : Scan operation looks through all data pages or index pages to for data required by query

                Seek : Seek operation traveses through only subset of rows to  provide data required by query

Seek operation is more effiecent than Scan

Let’s learn each important common operator