Backup all DMV’s to a monitor database 2

Backup All DMV’s to a Monitor DatabaseEvery once in a while I have the need to access some DMV’s from remote locations. I use these for performance tuning with the use of the query plans, but also to get some statistical data. To gather all this information on a scheduled basis I have created the script below to backup all the information into a monitor database.

First I backup the DMV’s which I gathered from ‘sys.all_views’. Next I look into the ‘sys.all_columns’ if there is a ‘plan_handle’ or a ‘sql_handle’, so I can use the ‘sys.dm_exec_query_plan’ and ‘sys.dm_exec_sql_text’ DMF’s. These will give back a query plan or a sql text for these DMV’s, which can be used later on when you are gathering information about your environment.

Be carefull to run this script blind on a production environment. The load can become quite high if you have a lot of cached query plans and such.

You can download the SQL file at the bottom of this post.

use master
go
set nocount on
--drop database DynamicManagementViewsBackup
-----------------------------------------------------------------------------------------------
-- Create database if it does not exists
-----------------------------------------------------------------------------------------------
if not exists(select 1 from sys.databases where name = 'DynamicManagementViewsBackup')
begin
  create database DynamicManagementViewsBackup 
  alter database DynamicManagementViewsBackup set recovery simple
end
go
use DynamicManagementViewsBackup
go
-----------------------------------------------------------------------------------------------
-- Declare local variables and temptable
-----------------------------------------------------------------------------------------------
declare @SQLStatements table (
  Id int identity(1, 1) not null
, SQLStatement nvarchar(300)
, SelectStatementBackupDMV nvarchar(300)
)
declare @totrow int
      , @currow int     
      , @SQLStatement nvarchar(300) 
      , @SelectStatementBackupDMV nvarchar(300)

set @SQLStatement = ''
set @SelectStatementBackupDMV = '' 
set @currow = 1      
-----------------------------------------------------------------------------------------------
-- Insert queries to process - standard DMV's
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Insert queries to process - DMV's with plan_handle
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_PlanHandle_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME] a 
                  cross apply sys.dm_exec_query_plan(a.plan_handle) b'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_PlanHandle_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
        inner join sys.all_columns c
          on  c.object_id = v.object_id
          and c.name = 'plan_handle'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @totrow + @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Insert queries to process - DMV's with sql_handle
-----------------------------------------------------------------------------------------------
insert
into    @SQLStatements
select  replace(replace(replace(replace(replace(
          'select * 
           into   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_SqlText_[YY][MM][DD] 
           from   [SCHEMA_NAME].[DMV_NAME] a 
                  cross apply sys.dm_exec_sql_text(a.sql_handle) b'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
      , replace(replace(replace(replace(replace(
          'select * 
           from   DynamicManagementViewsBackup.dbo.[SCHEMA_NAME]_[DMV_NAME]_SqlText_[YY][MM][DD]'
        , '[DMV_NAME]', v.name)
        , '[SCHEMA_NAME]', s.name)
        , '[YY]', DATEPART(YEAR, getdate()))
        , '[MM]', DATEPART(MONTH, getdate()))
        , '[DD]', DATEPART(DAY, getdate()))
from    sys.all_views v
        inner join sys.schemas s
          on  s.schema_id = v.schema_id
          and s.name = 'sys'
        inner join sys.all_columns c
          on  c.object_id = v.object_id
          and c.name = 'sql_handle'
where   left(v.name, 2) = 'dm'
order by v.name
set @totrow = @totrow + @@ROWCOUNT
-----------------------------------------------------------------------------------------------
-- Loop through queries and execute them
-----------------------------------------------------------------------------------------------
while @totrow > 0 and @currow <= @totrow
begin
  ---------------------------------------------------------------------------------------------
  -- Get SQL Statement to execute
  ---------------------------------------------------------------------------------------------
  select  @SQLStatement = SQLStatement
        , @SelectStatementBackupDMV = SelectStatementBackupDMV
  from    @SQLStatements
  where   Id = @currow
  ---------------------------------------------------------------------------------------------
  -- Execute SQL Statement
  ---------------------------------------------------------------------------------------------
  print replicate('-', 150)
  print @SelectStatementBackupDMV

  exec sp_executesql @SQLStatement
  ---------------------------------------------------------------------------------------------
  -- Get next row identifier
  ---------------------------------------------------------------------------------------------
  set @currow = @currow + 1
end

 
Download the complete file right here : Backup All DMV’s to a Monitor Database
Happy tuning :)

2 thoughts on “Backup all DMV’s to a monitor database

  1. Reply Lorrin Ferdinand Nov 19, 2012 20:27

    HI,

    This is interesting and pertinent to a problem I’m currently facing. However, to complicate things, our procs are encrypted. Does your script work for encrypted objects?

    Also the statement “Be carefull to run this script blind on a production environment. The load can become quite high if you have a lot of cached query plans and such.”

    I assume this means that the backup process generates a heavy load on the environment and you are cautioning people to run this during a period of lighter load?

    thx

    • Reply Mark Kremers Nov 19, 2012 21:05

      Well, I am not known with DMO’s being encrypted. For the part where you get the sql_text from certain DMO’s you can try to use the dedicated administrator connection, which should listen on port 1434 if I am correct. I am not sure if the encrypted stored procedures and such are always available in readable format through this DAC, but you can give it a try.

      And for the ?Be carefull to run this script blind on a production environment. The load can become quite high if you have a lot of cached query plans and such.? part. What I try to say is that on a live production server you CAN have such big loads of data that you are trying to put into your monitor database that the overall performance would suffer from it. Try to look at DMO’s first and what is in it, so you can get a feeling of how much data you are copying. For example. When you look into the sys.dm_exec_cached_plans DMV, on a server where a lot of things are happening this one can get quite big in size. So when you have to copy all that info, it will take some time and performance.

      Hope it made you a little wiser :)

Leave a Reply

  

  

  

* Copy This Password *

* Type Or Paste Password Here *

Switch to our mobile site

%d bloggers like this: