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 

How to find memory available free using SQL Server 2008?

Whenever we get performance issues, we look how much memory is left available for that we have to login Windows box and have to look on task manager.Using SQL Server DMV also we can find. Pls run below query to find out the memory details.

SELECT total_physical_memory_kb/1024 as TotalMemoryMB, available_physical_memory_kb/1024 AvailableMemoryMB,
(total_physical_memory_kb - available_physical_memory_kb)/ 1024  as UsedMemoryMB
FROM sys.dm_os_sys_memory

If you want to find out, how much memory is using each database in SQL Server 2008 running below query you can find out.


SELECT  CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS DatabaseName, cast((count(*) * 8.0)/1024.0 as decimal(10,1)) as UsedMemoryMB 
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY database_id DESC

Clear the Buffers in Database


DECLARE @intDBID INT; SET @intDBID = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE name = 'AdventureWorks'); -- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID); 
dbcc dropcleanbuffers 
dbcc freeproccache

Configure Logshipping for SQL SERVERVER 2008

--LOG SHIPPING USING T-SQL

select @@SERVERNAME

--backup and restore the database from primiry server to secondary server initially

backup database bbaa to disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

restore filelistonly from disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

--restore the database

restore database finalTest from disk='\\10.50.11.149\Backup\bb\bbbb.bak' with move 'bbbb' to 'L:\aaaabbbb\bbaa.mdf', move 'bbbb_log' to 'L:\aaaabbbb\bbaa.ldf',norecovery

go

--primary --> bbaa

--secondary --> AB

--1) adding primary database 

use master

exec sp_add_log_shipping_primary_database @database='bbaa',

@backup_directory='\\10.50.11.149\Backup\bbSource',

@backup_share='\\10.50.11.149\Backup\bbSource',

@backup_job_name='bbBackup_job',

@backup_compression=1,

@overwrite = 1

go

-- 2) adding job schedule for backup job

use msdb

exec sp_add_jobschedule

@job_name='bbBackup_job',

@name='backupSchedule',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=1

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='bbBackup_job',

@schedule_name='backupSchedule'

go

-- 4) enable/disable

use msdb

exec sp_update_job @job_name='bbBackup_job',

@description='backing up...',

@enabled=1

go

--5) add job alert

use master

go

exec sp_add_log_shipping_alert_job;

--6) adding information about secondary database to primary database

use master

exec sp_add_log_shipping_primary_secondary @primary_database='bbaa',

@secondary_server='BETASQL2K8',

@secondary_database='finalTest'

go

----- primary over ----

--start secondary

--1) adding details about primary server in secondary server

use master

exec sp_add_log_shipping_secondary_primary @primary_server='BETASQL2K8',

@primary_database='bbaa',

@backup_source_directory='\\10.50.11.149\Backup\bbSource',

@backup_destination_directory='\\10.50.11.149\Backup\bbDestination',

@copy_job_name='copyJob1',

@restore_job_name='restoreJob1',

@overwrite = 1

go

--2) adding job schedule for copy job

use msdb

exec sp_add_jobschedule

@job_name='copyJob1',

@name='copylog',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=2

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='copyJob1',

@schedule_name='copylog'

go

--4) adding job schedule for restore job

use msdb

exec sp_add_jobschedule

@job_name='restoreJob1',

@name='restorejob',

@enabled = 1,

@freq_type = 4,

@freq_interval = 1 ,

@freq_subday_type = 0x4,

@freq_subday_interval = 5

go

-- 5) attaching schedule

use msdb

exec sp_attach_schedule @job_name='restoreJob1',

@schedule_name='restorejob'

go

--6) adding secondary database

use master

exec sp_add_log_shipping_secondary_database @secondary_database='finalTest',

@primary_server='BETASQL2K8',

@primary_database='bbaa',

@overwrite = 1,

@restore_mode=1,

@disconnect_users=1

go

--7) update copy and restore jobs

use msdb

exec sp_update_job @job_name='copyJob1',

@description='copying...',

@enabled=1

go

use msdb

exec sp_update_job @job_name='restoreJob1',

@description='restoring...',

@enabled=1

go

--startind job (backup job)

use msdb

exec sp_start_job @job_name='bbBackup_job'

go

SELECT @@version

Configuring Replication From Sql Server 2008.

--enabling replicatio for a database

use [AVehicles]

select * from cycles

exec sp_replicationdboption @dbname='AVehicles',

@optname='publish',

@value='true'

go

--adding a publication

use [AVehicles]

exec sp_addpublication @publication='final_pub',

@status='active',

@allow_pull='true',

@allow_push='true',

@independent_agent='true',

@immediate_sync='true',

@allow_sync_tran='true'

go

--adding article

use [AVehicles]

exec sp_addarticle @publication='final_pub',

@article='ar3',

@source_object='cycles',

@pre_creation_cmd='drop',

@force_invalidate_snapshot=1

go

--adding subscription

use [AVehicles]

exec sp_addsubscription @publication='final_pub',

@subscriber='BETASQL1',

@destination_db='Database_Pub',

@subscription_type='push'

go

--adding publication snapshot

use [AVehicles]

exec sp_addpublication_snapshot @publication='final_pub'

go

--starting snapshot

use [AVehicles]

exec sp_startpublication_snapshot @publication='final_pub'

go

--adding rows to articles

select @@SERVERNAME

sp_helpsubscription