Database Buddha: GIMPI Production Server Code (Part 2)

StoredProcedure [dbo].[dbasp_GIMPI_CaptureAndExport]

This Stored Procedure is the one that does all the work and will be called by the Agent Job when needed. This process cleans out any existing data and reloads the local holding tables with fresh data. Then it BCP's out the data to files and then sends them over to our central aggregation server. There was a pre-existing system in place here using the stored procedure dbasp_File_Transit which is being used to deliver the file to a central location. I am not able to dissect this currently and am only going to identify this a a "Black Box" process which can be replaced by any other method of copying the file to the report server. In the past I have used "Data Domain" SAN Replication for this. Because I was using a generic File Mover, I standardized the way that my servers create and import these files. When the file is created, it is named using a function that base64 encodes several pieces of information into a single filename that can then be decoded on the other side revealing where it came from and what to do with it. I am sure there are many other useful places to implement this logic and would love to hear how you have used the encode and decode functions.

This Stored Procedure has been over designed for how I currently use it but is very flexible, which will make my rewrite that does not use the unioned views much easier. The @target% and @retry_limit parameters are simply passed through to the "Black Box" file mover process and would probably be changed to match what ever method is utilized. While the other parameters can be used to run against a single database, schema, and/or table.

USE [dbaperf]

/****** Object: StoredProcedure [dbo].[dbasp_GIMPI_CaptureAndExport] Script Date: 06/09/2010 11:36:56 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbasp_GIMPI_CaptureAndExport]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROC [dbo].[dbasp_GIMPI_CaptureAndExport]

(

@UNCPath VarChar(6000) = NULL

,@target_env VarChar(50) = ''amer''

,@target_server sysname = ''SEAFRESQLDBA01''

,@target_share VarChar(2048) = ''SEAFRESQLDBA01_dbasql\IndexAnalysis''

,@retry_limit INT = 5

,@Fill_Factor int = 98

,@PopulateDMVsForAll bit = 1

,@database_name sysname = NULL

,@schema_name sysname = NULL

,@table_name sysname = NULL

)

/***************************************************************

** Stored Procedure dbasp_GIMPI_CaptureAndExport

** Written by Steve Ledridge, Getty Images

** June 2010

** Description: Generates two DMV''s used for the GIMPI Reports and sends the data

** To SEAFRESQLDBA01 to be aggregated.

** This proc accepts the following input parameters:

** @UNCPath = Whether or not online reindexing is used.

** @target_env = Passed to [dbasp_File_Transit].

** @target_server = Passed to [dbasp_File_Transit].

** @target_share = Passed to [dbasp_File_Transit].

** @retry_limit = Value User for FILLFACTOR on REBUILDS when Read Percentage > 60%

** @Fill_Factor = Value User for FILLFACTOR on REBUILDS when Read Precentage < 30%

** @PopulateDMVsForAll = Ignore any specific DB,SCHEMA,or TABLE VALUES (GET IT ALL)

** @database_name = FILTER - Only repopulate with tables in the specific Database.

** @schema_name = FILTER - Only repopulate with tables in the specific Schema.

** @table_name = FILTER - Only repopulate with tables with the specific Name.

***************************************************************/

set nocount on

-- ======================================================================================

-- Revision History

-- Date Author Desc

-- ========== ==================== =============================================

-- 2010-00-00 Steve Ledridge Created

-- 2010-06-07 Steve Ledridge Modified deletes to use truncates when they can.

-- Added missing delete for one of the DMV''s

-- ======================================================================================

/***

DECLARE @UNCPath VarChar(6000)

,@target_env VarChar(50)

,@target_server sysname

,@target_share VarChar(2048)

,@retry_limit INT

,@Fill_Factor int

,@PopulateDMVsForAll bit

,@database_name sysname

,@schema_name sysname

,@table_name sysname

SELECT @UNCPath = ''\\g1sqla\G1SQLA$A_dbasql\dba_reports''

,@target_env = ''amer''

,@target_server = ''SEAFRESQLDBA01''

,@target_share = ''SEAFRESQLDBA01_dbasql\IndexAnalysis''

,@retry_limit = 5

,@Fill_Factor = 98

,@PopulateDMVsForAll = 1

,@database_name = NULL

,@schema_name = NULL

,@table_name = NULL

--***/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @TSQL1 VarChar(max)

,@TSQL2 VarChar(max)

,@TSQL3 VarChar(max)

,@Object sysname

,@object_id int

,@database_id int

,@IndexScript nvarchar(max)

,@RC int

,@Script VarChar(6000)

,@Export_Source sysname

,@LocalPath VarChar(6000)

,@FileName VarChar(6000)

SELECT @UNCPath = COALESCE(@UNCPath,''\\'' + LEFT(@@SERVERNAME,(CHARINDEX(''\'',@@SERVERNAME+''\'')-1)) + ''\'' + REPLACE(@@SERVERNAME,''\'',''$'') + ''_dbasql\dba_reports'')

,@object_id = OBJECT_ID(@database_name+''.''+@schema_name+''.''+@table_name)

,@database_id = db_id(@database_name)

,@IndexScript = ''''

,@LocalPath = @UNCPath

If @PopulateDMVsForAll = 0 AND @database_name IS NULL

BEGIN

PRINT ''A DATABASE NAME MUST BE SPECIFIED IF @PopulateDMVsForAll=0''

RETURN -1

END

IF OBJECT_ID(''tempdb..#ForeignKeys'') IS NOT NULL

DROP TABLE #ForeignKeys

CREATE TABLE #ForeignKeys

(

database_id int

,foreign_key_name sysname

,object_id int

,fk_columns nvarchar(max)

,fk_columns_compare nvarchar(max)

);

if @@SERVERNAME IN (''SEADCASPSQLA\A'')

GOTO SkipCreateViews

DECLARE CreateAllDBViews CURSOR

FOR

SELECT ''sys'',''tables''

UNION ALL

SELECT ''sys'',''schemas''

UNION ALL

SELECT ''sys'',''sysindexes''

UNION ALL

SELECT ''sys'',''indexes''

UNION ALL

SELECT ''sys'',''dm_db_partition_stats''

UNION ALL

SELECT ''sys'',''allocation_units''

UNION ALL

SELECT ''sys'',''partitions''

UNION ALL

SELECT ''sys'',''columns''

UNION ALL

SELECT ''sys'',''index_columns''

UNION ALL

SELECT ''sys'',''foreign_keys''

UNION ALL

SELECT ''sys'',''foreign_key_columns''

OPEN CreateAllDBViews

FETCH NEXT FROM CreateAllDBViews INTO @TSQL2,@TSQL3

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

SET @TSQL1 = ''IF OBJECT_ID(''''[dbo].[vw_AllDB_''+@TSQL3+'']'''') IS NOT NULL'' +CHAR(13)+CHAR(10)

+ ''DROP VIEW [dbo].[vw_AllDB_''+@TSQL3+'']'' +CHAR(13)+CHAR(10)

SET @TSQL1 = ''USE [dbaperf];''+CHAR(13)+CHAR(10)+''EXEC ('''''' + REPLACE(@TSQL1,'''''''','''''''''''') + '''''')''

EXEC (@TSQL1)

SET @TSQL1 = ''CREATE VIEW [dbo].[vw_AllDB_''+@TSQL3+''] AS'' +CHAR(13)+CHAR(10)+''SELECT ''''master'''' AS database_name, DB_ID(''''master'''') AS database_id, * From [master].[''+@TSQL2+''].[''+@TSQL3+'']''+CHAR(13)+CHAR(10)

SELECT @TSQL1 = @TSQL1 +

''UNION ALL''+CHAR(13)+CHAR(10)+''SELECT ''''''+name+'''''', DB_ID(''''''+name+''''''), * From [''+name+''].[''+@TSQL2+''].[''+@TSQL3+'']''+CHAR(13)+CHAR(10)

FROM master.sys.databases

WHERE name != ''master''

SET @TSQL1 = ''USE [dbaperf];''+CHAR(13)+CHAR(10)+''EXEC ('''''' + REPLACE(@TSQL1,'''''''','''''''''''') + '''''')''

EXEC (@TSQL1)

END

FETCH NEXT FROM CreateAllDBViews INTO @TSQL2,@TSQL3

END

CLOSE CreateAllDBViews

DEALLOCATE CreateAllDBViews

SkipCreateViews:

BEGIN -- POPULATE DMVs or TEMP TABLES

-------------------------------------------------------

-- POPULATE dmv_MissingIndexSnapshot

-------------------------------------------------------

SELECT @database_name = QUOTENAME(@database_name)

,@schema_name = QUOTENAME(@schema_name)

,@table_name = QUOTENAME(@table_name)

if @PopulateDMVsForAll = 1

TRUNCATE TABLE [dbo].[dmv_MissingIndexSnapshot]

ELSE

DELETE dmv_MissingIndexSnapshot

WHERE (

QUOTENAME(database_name) = @database_name

OR @database_name IS NULL

OR @PopulateDMVsForAll = 1

)

AND (

QUOTENAME(table_name) = @table_name

OR @table_name IS NULL

OR @PopulateDMVsForAll = 1

)

;WITH XMLNAMESPACES (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'')

, PlanData AS

(

SELECT ecp.plan_handle

, MissingIndex.value (''(.//MissingIndex/@Database)[1]'',''sysname'') AS database_name

, MissingIndex.value (''(.//MissingIndex/@Schema)[1]'',''sysname'') AS schema_name

, MissingIndex.value (''(.//MissingIndex/@Table)[1]'',''sysname'') AS table_name

, MissingIndex.query (''.'') AS Statements

, MissingIndex.value (''(./@StatementId)[1]'', ''int'') AS StatementID

, MissingIndex.value (''(./@StatementText)[1]'', ''varchar(max)'') AS StatementText

, MissingIndex.value (''(./@StatementSubTreeCost)[1]'', ''float'') AS StatementSubTreeCost

, MissingIndex.value (''(.//MissingIndexGroup/@Impact)[1]'',''float'') AS Impact

, usecounts AS UseCounts

, eqp.[dbid]

, eqp.[objectid]

, ecp.objtype

FROM sys.dm_exec_cached_plans AS ecp

CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp

CROSS APPLY query_plan.nodes

(''for $stmt in .//Statements/*

where count($stmt/Condition/QueryPlan/MissingIndexes) > 0

or count($stmt/QueryPlan/MissingIndexes) > 0

return $stmt'') AS qp(MissingIndex)

WHERE (

MissingIndex.exist

(''.//MissingIndex[@Database = sql:variable("@database_name")]'') = 1

OR @database_name IS NULL

OR @PopulateDMVsForAll = 1

)

AND (

MissingIndex.exist

(''.//MissingIndex[@Table = sql:variable("@table_name")]'') = 1

OR @table_name IS NULL

OR @PopulateDMVsForAll = 1

)

, FormatedData AS

(

SELECT @@ServerName AS server_name

, REPLACE(REPLACE(

[database_name]

,''['',''''),'']'','''') AS database_name

, DB_ID(REPLACE(REPLACE(

[database_name]

,''['',''''),'']'','''')) AS database_id

, SCHEMA_ID(REPLACE(REPLACE(

[schema_name]

,''['',''''),'']'','''')) AS schema_id

, REPLACE(REPLACE(

[schema_name]

,''['',''''),'']'','''') AS schema_name

, OBJECT_ID(

[database_name]

+''.''+[schema_name]

+''.''+[table_name]

) AS object_id

, REPLACE(REPLACE(

[table_name]

,''['',''''),'']'','''') AS table_name

, [StatementSubTreeCost]

* ISNULL([Impact], 0)

* usecounts AS Improvement

, [Statements] AS CompleteQueryPlan

, OBJECT_NAME([objectid],[dbid]) AS Sproc_name

, [StatementId] AS StatementID

, [StatementText] AS StatementText

, [StatementSubTreeCost] AS StatementSubTreeCost

, NULL AS MissingIndex

, [Impact] AS IndexImpact

, [usecounts] AS UseCounts

, REPLACE(CAST(Mi.query

(''data( for $cg in .//ColumnGroup

where $cg/@Usage="EQUALITY" or $cg/@Usage="INEQUALITY"

return $cg/Column/@Name )'')

AS NVarchar(4000)),''] ['',''], ['') AS IndexColumns

, REPLACE(CAST(Mi.query

(''data( for $cg in .//ColumnGroup

where $cg/@Usage="INCLUDE"

return $cg/Column/@Name )'')

AS NVarchar(4000)),''] ['',''], ['') AS IncludeColumns

,REPLACE(REPLACE(REPLACE(CAST(Mi.query

(''data( for $cg in .//ColumnGroup

where $cg/@Usage="EQUALITY" or $cg/@Usage="INEQUALITY"

return $cg/Column/@ColumnId )'')

AS NVarchar(4000)),''['',''''),'']'',''''),'' '',''_'') AS IndexColumnIDs

,REPLACE(REPLACE(REPLACE(CAST(Mi.query

(''data( for $cg in .//ColumnGroup

where $cg/@Usage="INCLUDE"

return $cg/Column/@ColumnId )'')

AS NVarchar(4000)),''['',''''),'']'',''''),'' '',''_'') AS IncludeColumnIDs

From PlanData AS pd

CROSS APPLY Statements.nodes

(''.//MissingIndex'') AS St(Mi)

)

INSERT INTO dmv_MissingIndexSnapshot

SELECT server_name

, database_name

, database_id

, schema_id

, schema_name

, object_id

, table_name

, Improvement

, CompleteQueryPlan

, Sproc_name

, StatementID

, StatementText

, StatementSubTreeCost

, MissingIndex

, IndexImpact

, UseCounts

, IndexColumns

, '', '' + [IncludeColumns] AS IncludeColumns

, ''IX_''

+ REPLACE(REPLACE(

[table_name]

,''['',''''),'']'','''')

+ ''_''

+ [IndexColumnIDs]

+ CASE

WHEN [IncludeColumnIDs] = ''''

THEN ''''

ELSE ''_INC_'' + [IncludeColumnIDs]

END AS IndexName

,getdate()

FROM FormatedData

ORDER BY Improvement DESC

-------------------------------------------------------

-- POPULATE dmv_IndexBaseLine

-------------------------------------------------------

SELECT @database_name = REPLACE(REPLACE(

@database_name

,''['',''''),'']'','''')

,@schema_name = REPLACE(REPLACE(

@schema_name

,''['',''''),'']'','''')

,@table_name = REPLACE(REPLACE(

@table_name

,''['',''''),'']'','''')

if @PopulateDMVsForAll = 1

TRUNCATE TABLE [dbo].[dmv_IndexBaseLine]

ELSE

DELETE dmv_IndexBaseLine

WHERE (

database_name = @database_name

OR @database_name IS NULL

OR @PopulateDMVsForAll = 1

)

AND (

table_name = @table_name

OR @table_name IS NULL

OR @PopulateDMVsForAll = 1

)

;WITH AllocationUnits

AS (

SELECT p.database_id

,p.object_id

,p.index_id

,p.partition_number

,au.allocation_unit_id

FROM dbaperf.dbo.vw_AllDB_allocation_units AS au

JOIN dbaperf.dbo.vw_AllDB_partitions AS p

ON au.container_id = p.hobt_id

AND au.database_id = p.database_id

AND (au.type = 1 OR au.type = 3)

WHERE (p.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (p.object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

UNION ALL

SELECT p.database_id

,p.object_id

,p.index_id

,p.partition_number

,au.allocation_unit_id

FROM dbaperf.dbo.vw_AllDB_allocation_units AS au

JOIN dbaperf.dbo.vw_AllDB_partitions AS p

ON au.container_id = p.partition_id

AND au.database_id = p.database_id

AND au.type = 2

WHERE (p.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (p.object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

)

,MemoryBuffer

AS (

SELECT au.database_id

,au.object_id

,au.index_id

,au.partition_number

,COUNT(*)AS buffered_page_count

,CONVERT(decimal(12,2), CAST(COUNT(*) as bigint)*CAST(8 as float)/1024) as buffer_mb

FROM sys.dm_os_buffer_descriptors AS bd

JOIN AllocationUnits au

ON bd.allocation_unit_id = au.allocation_unit_id

AND bd.database_id = au.database_id

WHERE (au.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (au.object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

GROUP BY au.database_id, au.object_id, au.index_id, au.partition_number

)

INSERT INTO dmv_IndexBaseLine

(server_name, database_name, database_id, schema_id, schema_name, object_id, table_name, index_id, index_name, is_unique, type_desc, partition_number, reserved_page_count, size_in_mb, buffered_page_count, buffer_mb, pct_in_buffer, row_count, page_count, existing_ranking

, user_total_read, user_total_read_pct

, user_total_write, user_total_write_pct

, user_seeks, user_scans, user_lookups,user_updates

, row_lock_count, row_lock_wait_count, row_lock_wait_in_ms, row_block_pct, avg_row_lock_waits_ms

, page_lock_count, page_lock_wait_count, page_lock_wait_in_ms, page_block_pct, avg_page_lock_waits_ms

, splits, indexed_columns, included_columns, indexed_columns_compare, included_columns_compare)

SELECT @@SERVERNAME

,DB_Name(t.database_id)

,t.database_id

,s.schema_id

,s.name as schema_name

,t.object_id

,t.name as table_name

,i.index_id

,COALESCE(i.name, ''N/A'') as index_name

,i.is_unique

,CASE WHEN i.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + i.type_desc as type_desc

,ps.partition_number

,ps.reserved_page_count

,CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2)) as size_in_mb

,mb.buffered_page_count

,mb.buffer_mb

,CAST(100*buffer_mb/NULLIF(CAST(reserved_page_count * CAST(8 as float) / 1024 as decimal(12,2)),0) AS decimal(12,2)) as pct_in_buffer

,row_count

,used_page_count

,ROW_NUMBER()

OVER (PARTITION BY i.object_id ORDER BY i.is_primary_key desc, ius.user_seeks + ius.user_scans + ius.user_lookups desc) as existing_ranking

,ius.user_seeks + ius.user_scans + ius.user_lookups as user_total_read

,COALESCE(CAST(100 * (ius.user_seeks + ius.user_scans + ius.user_lookups)

/(NULLIF(SUM(ius.user_seeks + ius.user_scans + ius.user_lookups)

OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_read_pct

,ius.user_updates as user_total_write

,COALESCE(CAST(100 * (ius.user_updates)

/(NULLIF(SUM(ius.user_updates)

OVER(PARTITION BY i.object_id), 0) * 1.) as decimal(6,2)),0) as user_total_write_pct

,ius.user_seeks

,ius.user_scans

,ius.user_lookups

,ius.user_updates

,ios.row_lock_count

,ios.row_lock_wait_count

,ios.row_lock_wait_in_ms

,CAST(100.0 * ios.row_lock_wait_count/NULLIF(ios.row_lock_count, 0) AS decimal(12,2)) AS row_block_pct

,CAST(1. * ios.row_lock_wait_in_ms /NULLIF(ios.row_lock_wait_count, 0) AS decimal(12,2)) AS avg_row_lock_waits_ms

,ios.page_lock_count

,ios.page_lock_wait_count

,ios.page_lock_wait_in_ms

,CAST(100.0 * ios.page_lock_wait_count/NULLIF(ios.page_lock_count, 0) AS decimal(12,2)) AS page_block_pct

,CAST(1. * ios.page_lock_wait_in_ms /NULLIF(ios.page_lock_wait_count, 0) AS decimal(12,2)) AS avg_page_lock_waits_ms

,ios.leaf_allocation_count + ios.nonleaf_allocation_count AS [Splits]

,STUFF((SELECT '', '' + QUOTENAME(c.name)

FROM dbaperf.dbo.vw_AllDB_index_columns ic

JOIN dbaperf.dbo.vw_AllDB_columns c

ON ic.database_id = c.database_id

AND ic.object_id = c.object_id

AND ic.column_id = c.column_id

WHERE is_included_column = 0

AND i.database_id = ic.database_id

AND i.object_id = ic.object_id

AND i.index_id = ic.index_id

ORDER BY key_ordinal ASC

FOR XML PATH('''')), 1, 2, '''') AS indexed_columns

,STUFF((SELECT '', '' + QUOTENAME(c.name)

FROM dbaperf.dbo.vw_AllDB_index_columns ic

JOIN dbaperf.dbo.vw_AllDB_columns c

ON ic.database_id = c.database_id

AND ic.object_id = c.object_id

AND ic.column_id = c.column_id

WHERE i.database_id = ic.database_id

AND i.object_id = ic.object_id

AND i.index_id = ic.index_id

AND is_included_column = 1

ORDER BY key_ordinal ASC

FOR XML PATH('''')), 1, 2, '''') AS included_columns

,(SELECT QUOTENAME(ic.column_id,''('')

FROM dbaperf.dbo.vw_AllDB_index_columns ic

WHERE i.database_id = ic.database_id

AND i.object_id = ic.object_id

AND i.index_id = ic.index_id

AND is_included_column = 0

ORDER BY key_ordinal ASC

FOR XML PATH('''')) AS indexed_columns_compare

,COALESCE((SELECT QUOTENAME(ic.column_id, ''('')

FROM dbaperf.dbo.vw_AllDB_index_columns ic

WHERE i.database_id = ic.database_id

AND i.object_id = ic.object_id

AND i.index_id = ic.index_id

AND is_included_column = 1

ORDER BY key_ordinal ASC

FOR XML PATH('''')), SPACE(0)) AS included_columns_compare

FROM dbaperf.dbo.vw_AllDB_tables t

JOIN dbaperf.dbo.vw_AllDB_schemas s

ON t.database_id = s.database_id

AND t.schema_id = s.schema_id

JOIN dbaperf.dbo.vw_AllDB_indexes i

ON t.database_id = i.database_id

AND t.object_id = i.object_id

JOIN dbaperf.dbo.vw_AllDB_dm_db_partition_stats ps

ON i.database_id = ps.database_id

AND i.object_id = ps.object_id

AND i.index_id = ps.index_id

LEFT JOIN sys.dm_db_index_usage_stats ius

ON i.database_id = ius.database_id

AND i.object_id = ius.object_id

AND i.index_id = ius.index_id

LEFT JOIN sys.dm_db_index_operational_stats(@database_id, @object_id, NULL, NULL) ios

ON ps.database_id = ios.database_id

AND ps.object_id = ios.object_id

AND ps.index_id = ios.index_id

AND ps.partition_number = ios.partition_number

LEFT JOIN MemoryBuffer mb

ON ps.database_id = mb.database_id

AND ps.object_id = mb.object_id

AND ps.index_id = mb.index_id

AND ps.partition_number = mb.partition_number

WHERE (t.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (t.object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

INSERT INTO dmv_IndexBaseLine

(server_name, database_name, database_id, schema_id, schema_name, object_id, table_name, index_name

, type_desc, impact, existing_ranking, user_total_read, user_seeks, user_scans, user_lookups, indexed_columns

, indexed_column_count, included_columns, included_column_count)

SELECT @@Servername

,db_name(mid.database_id)

,mid.database_id

,s.schema_id

,s.name AS schema_name

,t.object_id

,t.name AS table_name

,''IX_''+t.name

+COALESCE((SELECT ''_''+CAST(column_id AS VarChar)

FROM dbaadmin.dbo.dbaudf_split(equality_columns,'','') T1

JOIN dbaperf.dbo.vw_AllDB_columns T2

ON LTRIM(RTRIM(REPLACE(REPLACE(T1.SplitValue,''['',''''),'']'',''''))) = T2.name

AND T2.database_id = t.database_id

AND T2.object_id = t.object_id

order by OccurenceId

FOR XML PATH('''')),'''')

+COALESCE((SELECT ''_''+CAST(column_id AS VarChar)

FROM dbaadmin.dbo.dbaudf_split(inequality_columns,'','') T1

JOIN dbaperf.dbo.vw_AllDB_columns T2

ON LTRIM(RTRIM(REPLACE(REPLACE(T1.SplitValue,''['',''''),'']'',''''))) = T2.name

AND T2.database_id = t.database_id

AND T2.object_id = t.object_id

order by OccurenceId

FOR XML PATH('''')),'''')

+CASE WHEN included_columns IS NULL THEN '''' ELSE ''_INC'' END

+COALESCE((SELECT ''_''+CAST(column_id AS VarChar)

FROM dbaadmin.dbo.dbaudf_split(included_columns,'','') T1

JOIN dbaperf.dbo.vw_AllDB_columns T2

ON LTRIM(RTRIM(REPLACE(REPLACE(T1.SplitValue,''['',''''),'']'',''''))) = T2.name

AND T2.database_id = t.database_id

AND T2.object_id = t.object_id

order by OccurenceId

FOR XML PATH('''')),'''')

,''--NONCLUSTERED--'' AS type_desc

,(migs.user_seeks + migs.user_scans) * migs.avg_user_impact as impact

,0 AS existing_ranking

,migs.user_seeks + migs.user_scans as user_total_read

,migs.user_seeks

,migs.user_scans

,0 as user_lookups

,COALESCE(equality_columns,'''')

+COALESCE(CASE WHEN equality_columns IS NULL THEN '''' ELSE '', '' END + inequality_columns,'''') as indexed_columns

,(LEN(COALESCE(equality_columns + '', '', SPACE(0)) + COALESCE(inequality_columns, SPACE(0))) - LEN(REPLACE(COALESCE(equality_columns + '', '', SPACE(0)) + COALESCE(inequality_columns, SPACE(0)),''['',''''))) indexed_column_count

,'', ''+ included_columns

,(LEN(included_columns) - LEN(REPLACE(included_columns,''['',''''))) included_column_count

FROM dbaperf.dbo.vw_AllDB_tables t

JOIN dbaperf.dbo.vw_AllDB_schemas s

ON t.database_id = s.database_id

AND t.schema_id = s.schema_id

JOIN sys.dm_db_missing_index_details mid

ON t.database_id = mid.database_id

AND t.object_id = mid.object_id

JOIN sys.dm_db_missing_index_groups mig

ON mid.index_handle = mig.index_handle

JOIN sys.dm_db_missing_index_group_stats migs

ON mig.index_group_handle = migs.group_handle

WHERE (t.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (t.object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

UPDATE T1

SET size_in_mb =

[dbaperf].[dbo].[dbaudf_GetLeafLevelIndexSpace]

(

T1.indexed_column_count

,T3.TotalIndexKeySize

,98

,T2.row_count)/1000.00/1000.00

[dbaperf].[dbo].[dbaudf_getIndexSpace] (

T1.indexed_column_count

,T3.TotalIndexKeySize

,T2.row_count)/1000.00/1000.00

,max_key_size = T3.TotalIndexKeySize

FROM dmv_IndexBaseLine T1

JOIN dmv_IndexBaseLine T2

ON T1.database_id = T2.database_id

AND T1.object_id = T2.Object_id

AND T2.type_desc IN (''CLUSTERED'', ''HEAP'', ''UNIQUE CLUSTERED'')

JOIN (

Select T1.row_id

,SUM(T3.max_length)AS TotalIndexKeySize

FROM dmv_IndexBaseLine T1

CROSS APPLY dbaadmin.dbo.dbaudf_split(indexed_columns,'','') T2

JOIN dbaperf.dbo.vw_AllDB_columns T3

ON T1.database_id = T3.database_id

AND T1.object_id = T3.object_id

AND ltrim(rtrim(T2.SplitValue)) = QUOTENAME(T3.name)

WHERE type_desc = ''--NONCLUSTERED--''

GROUP BY T1.row_id

) T3

ON T1.row_id = T3.row_id

where T2.row_count > 0

INSERT INTO #ForeignKeys

(database_id, foreign_key_name, object_id, fk_columns, fk_columns_compare)

SELECT fk.database_id, fk.name + ''|PARENT'' AS foreign_key_name

,fkc.parent_object_id AS object_id

,STUFF((SELECT '', '' + QUOTENAME(c.name)

FROM dbaperf.dbo.vw_AllDB_foreign_key_columns ifkc

JOIN dbaperf.dbo.vw_AllDB_columns c

ON ifkc.database_id = c.database_id

AND ifkc.parent_object_id = c.object_id

AND ifkc.parent_column_id = c.column_id

WHERE fk.database_id = ifkc.database_id

AND fk.object_id = ifkc.constraint_object_id

ORDER BY ifkc.constraint_column_id

FOR XML PATH('''')), 1, 2, '''') AS fk_columns

,( SELECT QUOTENAME(ifkc.parent_column_id,''('')

FROM dbaperf.dbo.vw_AllDB_foreign_key_columns ifkc

WHERE fk.database_id = ifkc.database_id

AND fk.object_id = ifkc.constraint_object_id

ORDER BY ifkc.constraint_column_id

FOR XML PATH('''')) AS fk_columns_compare

FROM dbaperf.dbo.vw_AllDB_foreign_keys fk

JOIN dbaperf.dbo.vw_AllDB_foreign_key_columns fkc

ON fk.database_id = fkc.database_id

AND fk.object_id = fkc.constraint_object_id

WHERE fkc.constraint_column_id = 1

AND (fkc.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (fkc.parent_object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

UNION ALL

SELECT fk.database_id, fk.name + ''|REFERENCED'' as foreign_key_name

,fkc.referenced_object_id AS object_id

,STUFF(( SELECT '', '' + QUOTENAME(c.name)

FROM dbaperf.dbo.vw_AllDB_foreign_key_columns ifkc

JOIN dbaperf.dbo.vw_AllDB_columns c

ON ifkc.database_id = c.database_id

AND ifkc.referenced_object_id = c.object_id

AND ifkc.referenced_column_id = c.column_id

WHERE fk.database_id = ifkc.database_id

AND fk.object_id = ifkc.constraint_object_id

ORDER BY ifkc.constraint_column_id

FOR XML PATH('''')), 1, 2, '''') AS fk_columns

,( SELECT QUOTENAME(ifkc.referenced_column_id,''('')

FROM dbaperf.dbo.vw_AllDB_foreign_key_columns ifkc

WHERE fk.database_id = ifkc.database_id

AND fk.object_id = ifkc.constraint_object_id

ORDER BY ifkc.constraint_column_id

FOR XML PATH('''')) AS fk_columns_compare

FROM dbaperf.dbo.vw_AllDB_foreign_keys fk

JOIN dbaperf.dbo.vw_AllDB_foreign_key_columns fkc

ON fk.database_id = fkc.database_id

AND fk.object_id = fkc.constraint_object_id

WHERE fkc.constraint_column_id = 1

AND (fkc.database_id = @database_id OR @database_id IS NULL OR @PopulateDMVsForAll = 1)

AND (fkc.referenced_object_id = @object_id OR @object_id IS NULL OR @PopulateDMVsForAll = 1)

UPDATE ibl

SET duplicate_indexes = STUFF(( SELECT '', '' + index_name AS [data()]

FROM dmv_IndexBaseLine iibl

WHERE ibl.database_id = iibl.database_id

AND ibl.object_id = iibl.object_id

AND ibl.index_id <> iibl.index_id

AND ibl.indexed_columns_compare = iibl.indexed_columns_compare

AND ibl.included_columns_compare = iibl.included_columns_compare

FOR XML PATH('''')), 1, 2, '''')

,overlapping_indexes = STUFF(( SELECT '', '' + index_name AS [data()]

FROM dmv_IndexBaseLine iibl

WHERE ibl.object_id = iibl.object_id

AND ibl.index_id <> iibl.index_id

AND (ibl.indexed_columns_compare LIKE iibl.indexed_columns_compare + ''%''

OR iibl.indexed_columns_compare LIKE ibl.indexed_columns_compare + ''%'')

AND ibl.indexed_columns_compare <> iibl.indexed_columns_compare

FOR XML PATH('''')), 1, 2, '''')

,related_foreign_keys = STUFF(( SELECT '', '' + foreign_key_name AS [data()]

FROM #ForeignKeys ifk

WHERE ifk.object_id = ibl.object_id

AND ibl.indexed_columns_compare LIKE ifk.fk_columns_compare + ''%''

FOR XML PATH('''')), 1, 2, '''')

,related_foreign_keys_xml = CAST(( SELECT foreign_key_name

FROM #ForeignKeys ForeignKeys

WHERE ForeignKeys.object_id = ibl.object_id

AND ibl.indexed_columns_compare LIKE ForeignKeys.fk_columns_compare + ''%''

FOR XML AUTO) as xml)

FROM dmv_IndexBaseLine ibl

INSERT INTO dmv_IndexBaseLine

(server_name, database_name, database_id, schema_id, schema_name, object_id, table_name, index_name, type_desc, existing_ranking, indexed_columns)

SELECT @@ServerName

,DB_Name(t.database_id)

,t.database_id

,s.schema_id

,s.name AS schema_name

,t.object_id

,t.name AS table_name

,fk.foreign_key_name AS index_name

,''--MISSING FOREIGN KEY--'' as type_desc

,9999

,fk.fk_columns

FROM dbaperf.dbo.vw_AllDB_tables t

JOIN dbaperf.dbo.vw_AllDB_schemas s

ON t.database_id = s.database_id

AND t.schema_id = s.schema_id

JOIN #ForeignKeys fk

ON t.database_id = fk.database_id

AND t.object_id = fk.object_id

LEFT JOIN dmv_IndexBaseLine ia

ON fk.database_id = ia.database_id

AND fk.object_id = ia.object_id

AND ia.indexed_columns_compare LIKE fk.fk_columns_compare + ''%''

WHERE ia.index_name IS NULL;

;WITH ReadAggregation

AS (

SELECT row_id

,CAST(100. * (user_seeks + user_scans + user_lookups)

/(NULLIF(SUM(user_seeks + user_scans + user_lookups)

OVER(PARTITION BY database_id, schema_name, table_name), 0) * 1.) as decimal(12,2)) AS estimated_user_total_pct

,SUM(buffer_mb) OVER(PARTITION BY database_id, schema_name, table_name) as table_buffer_mb

FROM dmv_IndexBaseLine

)

,WriteAggregation

AS (

SELECT row_id

,CAST((100.00 * user_updates)

/(NULLIF(SUM(user_updates)

OVER(PARTITION BY database_id, schema_name, table_name), 0) * 1.) as decimal(12,2)) AS estimated_user_total_pct

FROM dmv_IndexBaseLine

)

UPDATE ibl

SET estimated_user_total_read_pct = COALESCE(r.estimated_user_total_pct, 0.00)

,estimated_user_total_write_pct = COALESCE(w.estimated_user_total_pct, 0.00)

,table_buffer_mb = r.table_buffer_mb

,index_read_pct = (COALESCE(user_total_read,0.00) * 100.00) / CASE WHEN COALESCE(user_total_read,0.00) + COALESCE(user_total_write,0.00) = 0.00 THEN 1.00 ELSE COALESCE(user_total_read,0.00) + COALESCE(user_total_write,0.00) END

,index_write_pct = (COALESCE(user_total_write,0.00) * 100.00) / CASE WHEN COALESCE(user_total_read,0.00) + COALESCE(user_total_write,0.00) = 0.00 THEN 1.00 ELSE COALESCE(user_total_read,0.00) + COALESCE(user_total_write,0.00) END

FROM dmv_IndexBaseLine ibl

JOIN ReadAggregation r

ON ibl.row_id = r.row_id

JOIN WriteAggregation w

ON ibl.row_id = w.row_id

;WITH IndexAction

AS (

SELECT row_id

,CASE WHEN user_lookups > user_seeks AND type_desc IN (''CLUSTERED'', ''HEAP'', ''UNIQUE CLUSTERED'') THEN ''REALIGN''

WHEN type_desc = ''--MISSING FOREIGN KEY--'' THEN ''CREATE''

WHEN type_desc = ''XML'' THEN ''---''

WHEN is_unique = 1 THEN ''---''

WHEN type_desc = ''--NONCLUSTERED--'' AND ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY user_total_read desc) <= 10 AND estimated_user_total_read_pct > 1 THEN ''CREATE''

WHEN type_desc = ''--NONCLUSTERED--'' THEN ''BLEND''

WHEN ROW_NUMBER() OVER (PARTITION BY database_id, table_name ORDER BY user_total_read desc, existing_ranking) > 10 THEN ''DROP''

WHEN user_total_read = 0 THEN ''DROP''

ELSE ''---'' END AS index_action

FROM dmv_IndexBaseLine

)

UPDATE ibl

SET index_action = ia.index_action

FROM dmv_IndexBaseLine ibl

JOIN IndexAction ia

ON ibl.row_id = ia.row_id

UPDATE ibl

SET has_unique = 1

FROM dmv_IndexBaseLine ibl

JOIN (

SELECT DISTINCT

database_id

,object_id

FROM dbaperf.dbo.vw_AllDB_indexes i

WHERE i.is_unique = 1

) x

ON ibl.database_id = x.database_id

AND ibl.object_id = x.object_id

END

SELECT @Database_Name = COALESCE(@Database_Name,''NULL'')

,@Schema_Name = COALESCE(@Schema_Name,''NULL'')

,@Table_Name = COALESCE(@Table_Name,''NULL'')

-------------------------------------------------------

-- EXPORT DATA dbaperf.dbo.dmv_IndexBaseLine

-------------------------------------------------------

SET @Export_Source = ''dbaperf.dbo.dmv_IndexBaseLine''

SELECT @FileName = REPLACE([dbaadmin].[dbo].[dbaudf_base64_encode] (@@SERVERNAME+''|''+REPLACE(@Export_Source,''dbaperf.dbo.'','''')+''|''+@Database_Name+''|''+@Schema_Name+''|''+@Table_Name)+''.dat'',''='',''$'')

SET @SCRIPT = ''bcp ''+@Export_Source+'' out "''+@LocalPath+''\''+@FileName+''" -S ''+@@Servername+'' -T -N''

--Print @Script

Print ''Exporting Data from '' + @Export_Source

PRINT ''To File: '' + @FileName

EXEC xp_cmdshell @SCRIPT, no_output

Print ''Sending Data from '' + @Export_Source

EXEC [dbaadmin].[dbo].[dbasp_File_Transit]

@source_name = @FileName

,@source_path = @UNCPath

,@target_env = @target_env

,@target_server = @target_server

,@target_share = @target_share

,@retry_limit = @retry_limit

waitfor delay ''00:00:05''

-- DELETE FILE AFTER SENDING

SET @Script = ''DEL "''+ @UNCPath+''\''+@FileName+''"''

--Print @Script

Print ''Deleting File from '' + @Export_Source

exec master..xp_cmdshell @Script, no_output

-------------------------------------------------------

-- EXPORT DATA dbaperf.dbo.dmv_MissingIndexSnapshot

-------------------------------------------------------

SET @Export_Source = ''dbaperf.dbo.dmv_MissingIndexSnapshot''

SET @SCRIPT = ''bcp ''+@Export_Source+'' out "''+@LocalPath+''\''+@FileName+''" -S ''+@@Servername+'' -T -N''

--Print @Script

Print ''Exporting Data from '' + @Export_Source

PRINT ''To File: '' + @FileName

EXEC xp_cmdshell @SCRIPT, no_output

Print ''Sending Data from '' + @Export_Source

EXEC [dbaadmin].[dbo].[dbasp_File_Transit]

@source_name = @FileName

,@source_path = @UNCPath

,@target_env = @target_env

,@target_server = @target_server

,@target_share = @target_share

,@retry_limit = @retry_limit

waitfor delay ''00:00:05''

-- DELETE FILE AFTER SENDING

SET @Script = ''DEL "''+ @UNCPath+''\''+@FileName+''"''

--Print @Script

Print ''Deleting File from '' + @Export_Source

exec master..xp_cmdshell @Script, no_output

END

AgentJob [UTIL - PERF - GIMPI Collect and Export]

This job is only created and enabled on servers select to report on. If this job doesn't fire the sproc, then everything is just sitting there waiting to be used. The scheduling of this job can vary depending on how responsive your developers are and how quickly changes can be tested and deployed. It doesn't make any sense to run this hourly if the fastest turnaround you can do is weekly.

/**************************************************************

Generated SQL - SYSaddjobs

For Server: G1SQLA\A on Jun 9 2010 12:02:32:860PM

NOTE: The following SQL jobs were scripted using the input parm

@leave_enabled = 'y'.

**************************************************************/

use [msdb]

/*************************************************************************************************

Create new job: UTIL - PERF - GIMPI Collect and Export

**************************************************************************************************/

BEGIN TRANSACTION

DECLARE @JobID BINARY(16)

DECLARE @ReturnCode INT

DECLARE @charpos INT

DECLARE @miscprint nvarchar(500)

DECLARE @save_servername sysname

DECLARE @save_servername2 sysname

DECLARE @save_servername3 sysname

DECLARE @save_servername4b sysname

DECLARE @save_output_filename sysname

DECLARE @save_jname sysname

DECLARE @save_sname sysname

DECLARE @parm01 sysname

DECLARE @logpath sysname

Select @ReturnCode = 0

-- Set up the servername variables

Select @save_servername = @@servername

Select @save_servername2 = @@servername

Select @save_servername3 = @@servername

Select @save_servername4b = @@servername

Select @charpos = charindex('\', @save_servername)

IF @charpos <> 0

begin

Select @save_servername = substring(@@servername, 1, (CHARINDEX('\', @@servername)-1))

Select @save_servername2 = stuff(@save_servername2, @charpos, 1, '$')

Select @save_servername3 = stuff(@save_servername3, @charpos, 1, '(')

Select @save_servername3 = @save_servername3 + ')'

Select @save_servername4b = stuff(@save_servername4b, @charpos, 1, '_')

end

-- Set up the output file path variable

Select @parm01 = @save_servername2 + '_SQLjob_logs'

If exists(select 1 from master.sys.sysdatabases where name = 'dbaadmin')

begin

exec dbaadmin.dbo.dbasp_get_share_path @parm01, @logpath output

end

-- Verify the proper category exists

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

begin

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'Job', @type=N'Local', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

end

-- Delete the job with the same name (if it exists)

Select @JobID = job_id

from msdb.dbo.sysjobs

where (name = N'UTIL - PERF - GIMPI Collect and Export')

If (@JobID is not null)

begin

-- Check if the job is a multi-server job

IF (exists (Select * From msdb.dbo.sysjobservers

Where (job_id = @JobID) and (server_id <> 0)))

begin

-- This is a mult server job, so abort the script

Select @miscprint = 'Unable to delete job ''UTIL - PERF - GIMPI Collect and Export'' since there is already a multi-server job with this name.'

RAISERROR (@miscprint, 16, 1)

GOTO QuitWithRollback

end

Else

begin

-- Delete the [local] job

Select @miscprint = N'UTIL - PERF - GIMPI Collect and Export'

EXEC msdb.dbo.sp_delete_job @job_name = @miscprint

Select @JobID = null

end

-- Add the job

Select @save_jname = N'UTIL - PERF - GIMPI Collect and Export'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT

,@job_name = @save_jname

,@owner_login_name = N'sa'

,@description = N'Collect and Export Index Performance Data to support the GIMPI Reports'

,@category_name = N'[Uncategorized (Local)]'

,@enabled = 1

,@notify_level_email = 0

,@notify_level_page = 0

,@notify_level_netsend = 0

,@notify_level_eventlog = 0

,@delete_level= 0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Preparation for job step 1

-- Move the output file name and path to a variable

Select @save_output_filename = '\\g1sqla\G1SQLA$A_SQLjob_logs\UTIL_PERF_EXPORT_IndexAnalysisReport_Data.txt'

-- Move the command syntax for this job step to a variable

Declare @command_vrb1 NVARCHAR(MAX)

Select @command_vrb1 = ''

Select @command_vrb1 = @command_vrb1 + 'exec dbaperf.dbo.dbasp_GIMPI_CaptureAndExport'

-- Add job step 1

Select @save_sname = N'Export Data'

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID

,@step_id = 1

,@step_name = @save_sname

,@command = @command_vrb1

,@database_name = N'master'

,@server = N''

,@database_user_name = N''

,@subsystem = N'TSQL'

,@cmdexec_success_code = 0

,@flags = 6

,@retry_attempts = 0

,@retry_interval = 0

,@os_run_priority = 0

,@output_file_name = @save_output_filename

,@on_success_step_id = 0

,@on_success_action = 1

,@on_fail_step_id = 0

,@on_fail_action = 2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID

,@name = N'Daily at 1AM'

,@enabled = 1

,@freq_type = 4

,@active_start_date = 20100507

,@active_start_time = 10000

,@freq_interval = 1

,@freq_subday_type = 1

,@freq_subday_interval = 1

,@freq_relative_interval = 0

,@freq_recurrence_factor = 0

,@active_end_date = 99991231

,@active_end_time = 235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Update the job start step

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers

EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Update Production Job Status Info

IF exists(select * from master.sys.sysdatabases where name = 'DEPLinfo')

begin

IF exists(select * from DEPLinfo.sys.objects where name = 'ProdJobStatus')

begin

IF exists(select * from DEPLinfo.dbo.ProdJobStatus where JobName = 'UTIL - PERF - GIMPI Collect and Export')

begin

update DEPLinfo.dbo.ProdJobStatus set JobStatus = 1 where JobName = 'UTIL - PERF - GIMPI Collect and Export'

end

ELSE

begin

insert into DEPLinfo.dbo.ProdJobStatus (JobName, JobStatus) values ('UTIL - PERF - GIMPI Collect and Export', 1)

end

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

There is a lot here and I hope I didn't introduce any typos in my process of trying to make it readable for you. feel free to let me know if I screwed it up or if it could be better.

Database Buddha

Wednesday, August 11, 2010

GIMPI Production Server Code (Part 2)

No comments:

Post a Comment

Followers