GIMPI Production Server Code (Part 1)

Here is my deployment code that is run on each Server that will return data for the reports. I deploy the parts to every server but only create the job on servers that need to return data for the reports.

Lets start with the Local storage table and infrastructure needed. We keep all of our performance related tools in a database called dbaperf, but this can be changed to any name you chose. but I do recommend it be a dba only database and not used for your actual production environment. This process is still fairly new and has not been heavily tuned yet so you might find many places to improve.

Table [dbo].[dmv_MissingIndexSnapshot]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dmv_MissingIndexSnapshot]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[dmv_MissingIndexSnapshot](
[server_name] [nvarchar](128) NULL,
[database_name] [nvarchar](4000) NULL,
[database_id] [smallint] NULL,
[schema_id] [int] NULL,
[schema_name] [nvarchar](4000) NULL,
[object_id] [int] NULL,
[table_name] [nvarchar](4000) NULL,
[Improvement] [float] NULL,
[CompleteQueryPlan] [xml] NULL,
[Sproc_name] [nvarchar](128) NULL,
[StatementID] [float] NULL,
[StatementText] [varchar](4000) NULL,
[StatementSubTreeCost] [varchar](128) NULL,
[MissingIndex] [xml] NULL,
[IndexImpact] [float] NULL,
[usecounts] [int] NOT NULL,
[IndexColumns] [nvarchar](4000) NULL,
[IncludeColumns] [nvarchar](4000) NULL,
[IndexName] [nvarchar](4000) NULL,
[SnapShotDate] [datetime] NULL
) ON [PRIMARY]
END
GO




IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dmv_MissingIndexSnapshot]') AND name = N'_dta_index_dmv_MissingIndexSnapshot_13_1634104862__K3_8_9_10_12_13_14_15_16_17_18')
CREATE NONCLUSTERED INDEX [_dta_index_dmv_MissingIndexSnapshot_13_1634104862__K3_8_9_10_12_13_14_15_16_17_18] ON [dbo].[dmv_MissingIndexSnapshot] 
(
[database_id] ASC
)
INCLUDE ( [Improvement],
[CompleteQueryPlan],
[Sproc_name],
[StatementText],
[StatementSubTreeCost],
[MissingIndex],
[IndexImpact],
[usecounts],
[IndexColumns],
[IncludeColumns]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__dmv_Missi__SnapS__104C4D90]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[dmv_MissingIndexSnapshot] ADD  CONSTRAINT [DF__dmv_Missi__SnapS__104C4D90]  DEFAULT (getdate()) FOR [SnapShotDate]
END
GO


Table [dbo].[dmv_IndexBaseLine]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dmv_IndexBaseLine]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[dmv_IndexBaseLine](
[row_id] [int] IDENTITY(1,1) NOT NULL,
[server_name] [sysname] NOT NULL,
[database_name] [sysname] NOT NULL,
[database_id] [int] NULL,
[index_action] [varchar](50) NULL,
[schema_id] [int] NULL,
[schema_name] [sysname] NOT NULL,
[object_id] [int] NULL,
[table_name] [sysname] NOT NULL,
[index_id] [int] NULL,
[index_name] [nvarchar](256) NULL,
[is_unique] [bit] NULL,
[has_unique] [bit] NULL,
[type_desc] [nvarchar](255) NULL,
[partition_number] [int] NULL,
[reserved_page_count] [bigint] NULL,
[page_count] [bigint] NULL,
[max_key_size] [int] NULL,
[size_in_mb] [decimal](12, 2) NULL,
[buffered_page_count] [int] NULL,
[buffer_mb] [decimal](12, 2) NULL,
[pct_in_buffer] [decimal](12, 2) NULL,
[table_buffer_mb] [decimal](12, 2) NULL,
[row_count] [bigint] NULL,
[impact] [bigint] NULL,
[existing_ranking] [bigint] NULL,
[user_total_read] [bigint] NULL,
[user_total_read_pct] [decimal](6, 2) NULL,
[estimated_user_total_read_pct] [decimal](6, 2) NULL,
[user_total_write] [bigint] NULL,
[user_total_write_pct] [decimal](6, 2) NULL,
[estimated_user_total_write_pct] [decimal](6, 2) NULL,
[index_read_pct] [decimal](6, 2) NULL,
[index_write_pct] [decimal](6, 2) NULL,
[user_seeks] [bigint] NULL,
[user_scans] [bigint] NULL,
[user_lookups] [bigint] NULL,
[user_updates] [bigint] NULL,
[row_lock_count] [bigint] NULL,
[row_lock_wait_count] [bigint] NULL,
[row_lock_wait_in_ms] [bigint] NULL,
[row_block_pct] [decimal](6, 2) NULL,
[avg_row_lock_waits_ms] [bigint] NULL,
[page_lock_count] [bigint] NULL,
[page_lock_wait_count] [bigint] NULL,
[page_lock_wait_in_ms] [bigint] NULL,
[page_block_pct] [decimal](6, 2) NULL,
[avg_page_lock_waits_ms] [bigint] NULL,
[splits] [bigint] NULL,
[indexed_columns] [nvarchar](max) NULL,
[indexed_column_count] [int] NULL,
[included_columns] [nvarchar](max) NULL,
[included_column_count] [int] NULL,
[indexed_columns_compare] [nvarchar](max) NULL,
[included_columns_compare] [nvarchar](max) NULL,
[duplicate_indexes] [nvarchar](max) NULL,
[overlapping_indexes] [nvarchar](max) NULL,
[related_foreign_keys] [nvarchar](max) NULL,
[related_foreign_keys_xml] [xml] NULL,
[SnapShotDate] [datetime] NULL
) ON [PRIMARY]
END
GO


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dmv_IndexBaseLine]') AND name = N'_dta_index_dmv_IndexBaseLine_13_55671246__K3_K23D_K8_K25D_K27D_K4_2_5_7_9_11_12_13_14_17_18_19_21_22_24_28_29_30_31_32_33_34_')
CREATE NONCLUSTERED INDEX [_dta_index_dmv_IndexBaseLine_13_55671246__K3_K23D_K8_K25D_K27D_K4_2_5_7_9_11_12_13_14_17_18_19_21_22_24_28_29_30_31_32_33_34_] ON [dbo].[dmv_IndexBaseLine] 
(
[database_name] ASC,
[table_buffer_mb] DESC,
[object_id] ASC,
[impact] DESC,
[user_total_read] DESC,
[database_id] ASC
)
INCLUDE ( [server_name],
[index_action],
[schema_name],
[table_name],
[index_name],
[is_unique],
[has_unique],
[type_desc],
[page_count],
[max_key_size],
[size_in_mb],
[buffer_mb],
[pct_in_buffer],
[row_count],
[user_total_read_pct],
[estimated_user_total_read_pct],
[user_total_write],
[user_total_write_pct],
[estimated_user_total_write_pct],
[index_read_pct],
[index_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],
[indexed_column_count],
[included_columns],
[included_column_count],
[duplicate_indexes],
[overlapping_indexes],
[related_foreign_keys],
[related_foreign_keys_xml]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


if not exists (select * from sys.stats where name = N'_dta_stat_55671246_8_4_3_23_25_27' and object_id = object_id(N'[dbo].[dmv_IndexBaseLine]'))
CREATE STATISTICS [_dta_stat_55671246_8_4_3_23_25_27] ON [dbo].[dmv_IndexBaseLine]([object_id], [database_id], [database_name], [table_buffer_mb], [impact], [user_total_read])
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__dmv_Index__is_un__04459E07]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[dmv_IndexBaseLine] ADD  CONSTRAINT [DF__dmv_Index__is_un__04459E07]  DEFAULT ((0)) FOR [is_unique]
END
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__dmv_Index__has_u__0539C240]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[dmv_IndexBaseLine] ADD  CONSTRAINT [DF__dmv_Index__has_u__0539C240]  DEFAULT ((0)) FOR [has_unique]
END
GO


IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__dmv_Index__SnapS__114071C9]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[dmv_IndexBaseLine] ADD  CONSTRAINT [DF__dmv_Index__SnapS__114071C9]  DEFAULT (getdate()) FOR [SnapShotDate]
END
GO


Views [dbo].[vw_AllDB_{system table name}]
These views are created so that I can process all databases on the server at the same time and have proven to be problematic when any databases are not using the same default collation as the others. The next version of this will just run once per database and this will not be needed.


DECLARE @TSQL1 VarChar(max)
,@TSQL2 VarChar(max)
,@TSQL3 VarChar(max)


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,'''','''''') + ''')'
Print (@TSQL1)
END
FETCH NEXT FROM CreateAllDBViews INTO @TSQL2,@TSQL3
END


CLOSE CreateAllDBViews
DEALLOCATE CreateAllDBViews 




UserDefinedFunction [dbo].[dbaudf_CalculateHeapSize]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_CalculateHeapSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE function [dbo].[dbaudf_CalculateHeapSize]
(@database_id INT, @object_id INT)
Returns varchar(100)
AS
Begin
-- Calculate the space used taken at leaf level 
Declare @Num_Rows float
,@Num_Cols int
,@Fixed_data_size int
,@Num_var_Cols int
,@Max_var_size int
,@Null_Bitmap int
,@Variable_Data_Size int
,@Heap_size bigint
,@Row_Size int
,@Rows_per_page float
,@Num_Pages float

select @Num_Rows = [rows] 
from dbaperf.dbo.vw_AllDB_sysindexes 
where database_id = @database_id
and id=@object_id 
and indid=1 

select @Num_Cols = count(*) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id=@object_id

select @Fixed_data_size = sum(max_lenGth) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id 
and object_id=@object_id 
and system_type_id not in (165,167,231,34,35,99)

select @Num_var_Cols = count(*) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @Database_id
and object_id=@object_id
and system_type_id in (165,167,231,34,35,99)

select @Max_var_size = sum(max_lenGth) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id=@object_id
and system_type_id in (165,167,231,34,35,99)

set @Null_Bitmap= 2 + (@Num_Cols + 7)/8

If( @Num_var_Cols = 0)
BEGIN
set @Variable_Data_Size = 0
END
ELSE
begin
set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
END

set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

set @Rows_per_page= 8096/(@Row_Size + 2)

-- No. of pages needed to store rows
set @Num_Pages= ceiling(@Num_Rows/@Rows_per_page)

set @Heap_size = (8192 * @Num_Pages)/1024
-- Space used to store index info
return Ltrim(str(@Heap_size))-- + '' KB''
End

' 
END


GO
USE [dbaperf]
GO




UserDefinedFunction [dbo].[dbaudf_ClusteredIndexSize]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_ClusteredIndexSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))BEGIN
execute dbo.sp_executesql @statement = N'CREATE function [dbo].[dbaudf_ClusteredIndexSize]
(@database_id INT, @object_id INT)
Returns BigInt
AS
Begin
-- Calculate the space used taken at leaf level 
Declare @Num_Rows float
,@Num_Cols int
,@Fixed_data_size int
,@Num_var_Cols int
,@Max_var_size int
,@fill_factor int
,@uniquifier smallint
,@uniquefiersize smallint
,@Null_Bitmap int
,@Variable_Data_Size int
,@Total_Space varchar(100)
,@Row_Size int
,@Rows_per_page float
,@Free_rows_per_page float
,@level float 
,@Num_Pages float
,@Leaf_level_space int

SELECT @uniquifier=1
,@uniquefiersize=4
,@Num_Rows = [rows] 
from dbaperf.dbo.vw_AllDB_sysindexes 
where database_id = @database_id
AND id = @object_id
AND indid=1 

select @Num_Cols = count(*) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id=@object_id 

select @Fixed_data_size = sum(max_lenGth) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id = @object_id
and system_type_id not in (165,167,231,34,35,99)

select @Num_var_Cols = count(*) 
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id = @object_id 
and system_type_id in (165,167,231,34,35,99)

select @Max_var_size = sum(max_lenGth)
from dbaperf.dbo.vw_AllDB_columns 
where database_id = @database_id
AND object_id = @object_id 
and system_type_id in (165,167,231,34,35,99)

If ( (select is_unique from dbaperf.dbo.vw_AllDB_indexes where database_id = @database_id and object_id = @object_id and type=1) = 0 ) 
Begin
set @Num_Cols = @Num_Cols + @uniquifier
set @Num_var_Cols = @Num_var_Cols + @uniquifier
set @Max_var_size = @Max_var_size + @uniquefiersize
End 
set @Null_Bitmap= 2 + (@Num_Cols + 7)/8

set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size

set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info

set @Rows_per_page= 8096/(@Row_Size + 2)

select @fill_factor = fill_factor 
from dbaperf.dbo.vw_AllDB_indexes 
where database_id = @database_id 
AND object_id = @object_id 
and type =1

-- No. of reserved free rows per page
set @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100) / (@Row_Size + 2))

-- No. of pages needed to store rows
set @Num_Pages= ceiling((@Num_Rows/(@Rows_per_page - @Free_rows_per_page)))

set @Leaf_level_space = 8192 * @Num_Pages

-- Space used to store index info
Declare @Num_Key_cols int
,@Fixed_key_size int
,@Num_var_key_cols int
,@Max_var_key_size int
,@Index_Null_Bitmap int
,@Variable_Key_size int
,@Index_row_size int
,@Index_row_per_page float
,@levels int
,@Num_Index_pages int
,@Index_level_space int
,@Null_Cols int


select @Num_Key_cols = Keycnt 
from dbaperf.dbo.vw_AllDB_sysindexes 
where database_id = @database_id 
AND id = @object_id 
and indid=1

select @Fixed_key_size = sum(max_length) 
from dbaperf.dbo.vw_AllDB_index_columns a
JOIN dbaperf.dbo.vw_AllDB_indexes b
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id=b.index_id
JOIN dbaperf.dbo.vw_AllDB_columns c
ON c.database_id = b.database_id
AND c.object_id = b.object_id
AND c.column_id = a.column_id
where b.database_id = @database_id
and b.object_id = @object_id
and type=1 
and system_type_id not in (165,167,231,34,35,99)

select @Num_var_key_cols = count(c.name) 
from dbaperf.dbo.vw_AllDB_index_columns a
JOIN dbaperf.dbo.vw_AllDB_indexes b
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id=b.index_id
JOIN dbaperf.dbo.vw_AllDB_columns c
ON c.database_id = b.database_id
AND c.object_id = b.object_id
AND c.column_id = a.column_id
where b.database_id = @database_id
and b.object_id = @object_id
and type=1 
and system_type_id in (165,167,231,34,35,99)

select @Max_var_key_size = IsNull(sum(max_length),0) 
from dbaperf.dbo.vw_AllDB_index_columns a
JOIN dbaperf.dbo.vw_AllDB_indexes b
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id=b.index_id
JOIN dbaperf.dbo.vw_AllDB_columns c
ON c.database_id = b.database_id
AND c.object_id = b.object_id
AND c.column_id = a.column_id
where b.database_id = @database_id
and b.object_id = @object_id
and type=1 
and system_type_id in (165,167,231,34,35,99)

If ( (select is_unique from dbaperf.dbo.vw_AllDB_indexes where database_id = @database_id and object_id = @object_id and type=1) = 0 ) 
Begin
set @Num_Key_cols = @Num_Key_cols + @uniquifier
set @Num_var_key_cols = @Num_var_key_cols + @uniquifier
set @Max_var_key_size = @Max_var_key_size + @uniquefiersize
End 

select @Null_Cols = IsNull(count(c.name),0) 
from dbaperf.dbo.vw_AllDB_index_columns a
JOIN dbaperf.dbo.vw_AllDB_indexes b
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id=b.index_id
JOIN dbaperf.dbo.vw_AllDB_columns c
ON c.database_id = b.database_id
AND c.object_id = b.object_id
AND c.column_id = a.column_id
where b.database_id = @database_id
and b.object_id = @object_id 
and type=1 
and c.is_nullable=1

select @Index_level_space = dbo.dbaudf_getIndexSpace (
@Null_Cols
,@Num_var_key_cols
,@Max_var_key_size
,@Fixed_key_size
,@Num_Rows
)
set @Total_space = Ltrim(str((@Index_level_space+@Leaf_level_space)/(1024)))
return @Total_space
End
' 
END
GO


UserDefinedFunction [dbo].[dbaudf_GetIndexSpace]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_getIndexSpace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE Function [dbo].[dbaudf_getIndexSpace]
( @Num_Null_key_cols int,@Num_var_key_cols int,@Max_var_key_size int,@Fixed_key_size int,@Num_Rows float )
returns Bigint 
AS
BEGIN
Declare @Index_Null_Bitmap bigint,@Var_Key_Size bigint,@Index_row_Size bigint,@Index_Row_per_Page float
Declare @level float,@Num_Index_pages bigint,@Index_Space_Used bigint
If (@Num_Null_key_cols>0)
Begin
set @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols+7)/8)
End
Else
Begin
set @Index_Null_Bitmap=0
End

IF (@Num_var_key_cols>0)
BEGIN
set @Var_Key_Size = 2 + (@Num_var_key_cols*2) + @Max_var_key_size
END
ELSE
begin
set @Var_Key_Size=0
END

set @Index_row_Size=@Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap + 1+6
set @Index_Row_per_Page = 8096/(@Index_row_Size +2)
set @level = 1 + floor(abs((log10(@Num_Rows/@Index_row_per_page)/log10(@Index_row_per_page))))
set @Num_Index_pages=0
Declare @i int
if (@level>0)
Begin
set @i=1
while(@i<=@Level)
Begin
set @Num_Index_pages = @Num_Index_pages + power(@Index_row_per_page,@level - @i)
set @i= @i + 1
End
END

set @Index_Space_Used = (8192 * @Num_Index_pages)
Return @Index_Space_Used


End
' 
END
GO


UserDefinedFunction [dbo].[dbaudf_GetLeafLevelIndexSpace]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_GetLeafLevelIndexSpace]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE Function [dbo].[dbaudf_GetLeafLevelIndexSpace]
(@Num_Leaf_Cols int,@Num_Var_leaf_Cols int,@Max_var_leaf_size int,@Fixed_Leaf_Size int,@Fill_Factor int,@Num_Rows float)
Returns bigint
AS 
BEGIN


Declare @Leaf_Null_Bitmap int,@Variable_leaf_size int,@Leaf_Row_Size int,@Leaf_Rows_per_page int
Declare @Free_Rows_Per_Page int,@Num_Leaf_Pages float, @Leaf_Space_Used bigint

set @Leaf_Null_Bitmap= 2 + ((@Num_Leaf_Cols + 7)/8)

If (@Num_Var_leaf_Cols>0)
Begin
set @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2) + @Max_var_leaf_size
END
ELSE
Begin 
set @Variable_leaf_size = 0
END




set @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size + @Leaf_Null_Bitmap + 1+ 6


set @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2)


set @Free_Rows_Per_Page= 8096 * (((100 - @Fill_Factor) / 100) / (@Leaf_Row_Size + 2))

set @Num_Leaf_Pages = ceiling((@Num_Rows/(@Leaf_Rows_per_page - @Free_Rows_Per_Page)))

set @Leaf_Space_Used = 8192 * @Num_Leaf_Pages


return @Leaf_Space_Used
END
' 
END
GO




UserDefinedFunction [dbo].[dbaudf_NonClusteredIndexSize]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_GetNonClusteredIndexSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE function [dbo].[dbaudf_GetNonClusteredIndexSize] 
(@database_id INT, @object_id INT)
Returns bigint
AS
Begin


Declare @Num_Rows float
,@Num_Key_cols int
,@Fixed_key_size int
,@Num_var_key_cols int
,@Max_var_key_size int
,@is_clustered int
,@index_id int
,@is_unique bit
,@Num_Diff_cols int
,@Num_Null_key_cols int
,@Num_Index_pages int
,@Index_Space_Used int
,@Total_Index_space bigint
,@Num_Leaf_Cols int
,@Num_Included_Cols int
,@Leaf_Level_Space int
,@Fill_Factor int
-- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL
-- No of Rows in a table
set @Total_Index_space=0
set @Leaf_Level_Space=0
-- insert info intom temp table
select @Num_Rows = [rows] 
from dbaperf.dbo.vw_AllDB_sysindexes 
where database_id = @database_id
and id = @object_id
and indid=1 

Declare @Tmp_Info Table 
( 
Index_id int
,Num_key_cols int
,type int
,is_unique bit
,is_included smallint
,fill_factor int
,Num_Var_Key_cols int
,Fixed_Key_Size int
,Max_Var_Key_Size int
)


Declare @tmp_Index_info Table
(
sno int identity(1,1)
,index_id int
,Num_key_cols int
,type int
,is_unique bit
,Num_Var_Key_cols int
,Fixed_Key_Size int
,Max_Var_Key_Size int
,Num_Included_Col int
,fill_factor int
)


insert into @Tmp_Info

select b.Index_id
,count(c.name) Num_key_cols
,b.type
,b.is_unique
,is_included_column
,fill_factor
,IsNull((select count(c.name) from dbaperf.dbo.vw_AllDB_columns e where e.database_id = c.database_id AND e.object_id=c.object_id and a.column_id=e.column_id and c.system_type_id in (165,167,231,34,35,99)),0) As Num_Var_Key_cols
,ISNULL((select sum(max_length) from dbaperf.dbo.vw_AllDB_indexes d where d.database_id=c.database_id and d.object_id=c.object_id and d.index_id=b.index_id and c.system_type_id not in (165,167,231,34,35,99)),0) As Fixed_Key_Size
,ISNULL((select sum(max_length) from dbaperf.dbo.vw_AllDB_indexes d where d.database_id=c.database_id and d.object_id=c.object_id and d.index_id=b.index_id and c.system_type_id in (165,167,231,34,35,99)),0) As Max_Var_Key_Size
--into @Tmp_Info
from dbaperf.dbo.vw_AllDB_index_columns a
JOIN dbaperf.dbo.vw_AllDB_columns c
ON a.database_id = c.database_id
AND a.object_id = c.object_id
AND a.column_id=c.column_id
JOIN dbaperf.dbo.vw_AllDB_indexes b
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id=b.index_id

where b.database_id = @database_id
and b.object_id = @object_id
--and b.type>1


group by c.name
,b.index_id
,c.database_id
,c.object_id
,c.system_type_id
,a.column_id
,b.type
,b.is_unique
,is_included_column
,fill_factor
order by b.index_id



insert into @tmp_Index_info
select index_id As Index_id
,sum(num_key_cols) as num_key_cols
,type,is_unique
,sum(Num_var_key_cols) as Num_var_key_cols
,sum(Fixed_key_size) as Fixed_key_size
,sum(max_var_key_size) as max_var_key_size
,sum(is_included)
,fill_factor
--into @tmp_Index_info
from @Tmp_Info 
where type>1 
group by index_id
,type
,is_unique
,fill_factor

IF Exists(select 1 from @Tmp_Info where type=1)
Begin 
Set @is_clustered = 1

END
ELSE
BEGIN
Set @is_clustered = 0

END


Declare @row_Count int
set @row_Count=(select count(*) from @tmp_Index_info where type>1)
while (@row_Count>0)
begin

select @index_id=index_id
,@Num_Key_cols=num_key_cols
,@Fixed_key_size=fixed_key_size
,@Num_var_key_cols=Num_var_key_cols
,@Max_var_key_size=Max_var_key_size
,@is_unique=is_unique
,@Num_Included_Cols=Num_Included_Col
,@Fill_Factor=fill_factor
from @tmp_Index_info 
where sno=@row_Count


If (@is_clustered=0)
Begin
set @Num_Key_cols = @Num_Key_cols + 1
set @Num_Leaf_Cols= @Num_Key_cols + @Num_Included_Cols + 1
END
ELSE
BEGIN
select @Num_Diff_cols=count(column_id) 
from dbaperf.dbo.vw_AllDB_index_columns x
join dbaperf.dbo.vw_AllDB_indexes y
ON x.database_id = y.database_id
AND x.object_id=y.object_id
AND x.index_id=y.index_id
where x.database_id = @database_id
and x.object_id = @object_id
AND y.type=1
AND column_id not in 
(
select column_id 
from dbaperf.dbo.vw_AllDB_index_columns a
join dbaperf.dbo.vw_AllDB_indexes b 
ON a.database_id = b.database_id
AND a.object_id = b.object_id
AND a.index_id = b.index_id
where a.database_id = @database_id
and a.object_id = @object_id 
and a.index_id = @index_id
and type>1 
)

set @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols + @is_unique


set @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols + @Num_Diff_cols + @is_unique

END


select @Num_Null_key_cols=ISNULL(count(x.column_id),0) 
from dbaperf.dbo.vw_AllDB_index_columns x
join dbaperf.dbo.vw_AllDB_columns y 
ON x.database_id = y.database_id
AND x.object_id = y.object_id
AND x.column_id=y.column_id
where x.database_id = @database_id
and x.object_id = @object_id
and x.index_id=@index_id 
and y.is_nullable=1

declare @index_name varchar(100)

select @index_name = name 
from dbaperf.dbo.vw_AllDB_indexes 
where database_id = @database_id
AND object_id = @object_id
and index_id = @index_id

select @Index_Space_Used=dbo.dbaudf_getIndexSpace (
@Num_Null_key_cols
,@Num_var_key_cols
,@Max_var_key_size
,@Fixed_key_size
,@Num_Rows
)

select @Leaf_Level_Space=dbo.dbaudf_GetLeafLevelIndexSpace (
@Num_Leaf_Cols
,@Num_var_key_cols
,@Max_var_key_size
,@Fixed_key_size
,@Fill_Factor
,@Num_Rows
)

set @Total_Index_space= @Total_Index_space + @Index_Space_Used + @Leaf_Level_Space

set @row_Count=@row_count-1
END

return Ltrim(str((@Total_Index_space))/(1024))
END
' 
END
GO




UserDefinedFunction [dbo].[dbaudf_GetTableSize]


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_GetTableSize]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
Create Function [dbo].[dbaudf_GetTableSize]
(@database_id INT, @object_id INT)
returns varchar(25)
AS
Begin
Declare @TableSize varchar(25)
If Exists(select 1 from dbaperf.dbo.vw_AllDB_indexes where database_id = @database_id AND object_id = @object_id and type = 1)
Begin
select @TableSize = dbo.dbaudf_ClusteredIndexSize(@database_id,@object_id) 
+ dbo.dbaudf_GetNonClusteredIndexSize(@database_id,@object_id)
END
ELSE
BEGIN  
select @TableSize = dbo.dbaudf_CalculateHeapSize(@database_id,@object_id) 
+ dbo.dbaudf_GetNonClusteredIndexSize(@database_id,@object_id)
END
set @TableSize = Ltrim(str(@TableSize)) + '' KB''
return @TableSize
END
' 
END
GO

UserDefinedFunction [dbo].[dbaudf_base64_encode]

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_base64_encode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[dbaudf_base64_encode]

(

  @plain_text varchar(6000)

)

RETURNS 

          varchar(8000)

AS BEGIN

--local variables

DECLARE

  @output            varchar(8000),

  @input_length      integer,

  @block_start       integer,

  @partial_block_start  integer, -- position of last 0, 1 or 2 characters

  @partial_block_length integer,

  @block_val         integer,

  @map               char(64)

SET @map = ''ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/''

--initialise variables

SET @output   = ''''

--set length and count

SET @input_length      = LEN( @plain_text + ''#'' ) - 1

SET @partial_block_length = @input_length % 3

SET @partial_block_start = @input_length - @partial_block_length

SET @block_start       = 1

--for each block

WHILE @block_start < @partial_block_start  BEGIN

  SET @block_val = CAST(SUBSTRING(@plain_text, @block_start, 3) AS BINARY(3))

  --encode the 3 character block and add to the output

  SET @output = @output + SUBSTRING(@map, @block_val / 262144 + 1, 1)

                        + SUBSTRING(@map, (@block_val / 4096 & 63) + 1, 1)

                        + SUBSTRING(@map, (@block_val / 64 & 63  ) + 1, 1)

                        + SUBSTRING(@map, (@block_val & 63) + 1, 1)

  --increment the counter

  SET @block_start = @block_start + 3

END

IF @partial_block_length > 0

BEGIN

  SET @block_val = CAST(SUBSTRING(@plain_text, @block_start, @partial_block_length)

                      + REPLICATE(CHAR(0), 3 - @partial_block_length) AS BINARY(3))

  SET @output = @output

 + SUBSTRING(@map, @block_val / 262144 + 1, 1)

 + SUBSTRING(@map, (@block_val / 4096 & 63) + 1, 1)

 + CASE WHEN @partial_block_length < 2

    THEN REPLACE(SUBSTRING(@map, (@block_val / 64 & 63  ) + 1, 1), ''A'', ''='')

    ELSE SUBSTRING(@map, (@block_val / 64 & 63  ) + 1, 1) END

 + CASE WHEN @partial_block_length < 3

    THEN REPLACE(SUBSTRING(@map, (@block_val & 63) + 1, 1), ''A'', ''='')

    ELSE SUBSTRING(@map, (@block_val & 63) + 1, 1) END

END

--return the result

RETURN @output

END

' 

END

GO

UserDefinedFunction [dbo].[dbaudf_base64_decode]

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbaudf_base64_decode]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

BEGIN

execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[dbaudf_base64_decode]

(

  @encoded_text varchar(8000)

)

RETURNS 

          varchar(6000)

AS BEGIN

--local variables

DECLARE

  @output           varchar(8000),

  @block_start      int,

  @encoded_length   int,

  @decoded_length   int,

  @mapr             binary(122)

--IF @encoded_text COLLATE LATIN1_GENERAL_BIN

-- LIKE ''%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%''

--     COLLATE LATIN1_GENERAL_BIN

--  RETURN NULL

--IF LEN(@encoded_text) & 3 > 0

--  RETURN NULL

SET @output   = ''''

-- The nth byte of @mapr contains the base64 value

-- of the character with an ASCII value of n.

-- EG, 65th byte = 0x00 = 0 = value of ''A''

SET @mapr =

  0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF -- 1-33

+ 0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF -- 33-64

+ 0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF -- 65-96

+ 0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233 -- 97-122

--get the number of blocks to be decoded

SET @encoded_length = LEN(@encoded_text)

SET @decoded_length = @encoded_length / 4 * 3

--for each block

SET @block_start = 1

WHILE @block_start < @encoded_length BEGIN

  --decode the block and add to output

  --BINARY values between 1 and 4 bytes can be implicitly cast to INT

  SET @output = @output +  CAST(CAST(CAST(

   substring( @mapr, ascii( substring( @encoded_text, @block_start    , 1) ), 1) * 262144

 + substring( @mapr, ascii( substring( @encoded_text, @block_start + 1, 1) ), 1) * 4096

 + substring( @mapr, ascii( substring( @encoded_text, @block_start + 2, 1) ), 1) * 64

 + substring( @mapr, ascii( substring( @encoded_text, @block_start + 3, 1) ), 1) 

   AS INTEGER) AS BINARY(3)) AS VARCHAR(3))

  SET @block_start = @block_start + 4

END

IF RIGHT(@encoded_text, 2) = ''==''

 SET @decoded_length = @decoded_length - 2

ELSE IF RIGHT(@encoded_text, 1) = ''=''

 SET @decoded_length = @decoded_length - 1

--IF SUBSTRING(@output, @decoded_length, 1) = CHAR(0)

-- SET @decoded_length = @decoded_length - 1

--return the decoded string

RETURN LEFT(@output, @decoded_length)

END

' 

END

GO

Continued on Part 2...