Check your database for missing indexes (proposal) + generated script to insert into SF


Userlevel 2
Badge +6

Goal

Indexes are very important for performance reasons. It is truly important to have the right indexes available on your tables. The SF automatically creates indexes based on your primary key and foreign keys. If you always select or join on that table the same way (using the PK or FK indexes) there’s no problem. But when doing more advanced coding you often are filtering the data on a different way. This is where new indexes could really help boost your performance.

SQL server constantly checks and proposes indexes that could possibly improve performance. Using this data you can create a statement to insert this proposed index into the SF.

NOTE

This script is a little more advanced, as you need to know what you are doing!

It's certainly NOT meant as a script to add all indexes it proposes. Think well before adding an extra index to a table.

 

Solution

--TESTED ON 2018.2
--Step 1:
----Fill your project variables
declare @project_id varchar(max) = ''
,@project_vrs_id varchar(max) = ''

--Server + SF database
declare @sf_db varchar(8000) = '[server].[sf]'

--Stap 2:
----Use this script on the end product database where you'd like to get the missing index proposal
--Stap 3:
----Determine which indexed you'd like to add. Think well about doing this, because the rule 'the more indexes, the beter' does not always apply!
----Bepaal welke indexen je wilt toevoegen en kopieer de string uit de kolom [INSERT_INDEX] en [INSERT_INDEX_COLUMNS]
--Stap 4:
----You can try out the index using the script in "ORIGINAL_CREATE_STATEMENT" to test it's performance gain/loss
----If you'd like to add the index to your SF: Open the script in INSERT_INDEX and INSERT_INDEX_COLUMNS to insert the index in to the SF

-- Missing Index Script
SELECT TOP 25 /*dm_mid.database_id AS DatabaseID
,dm_mig.index_handle
,*/dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) AS Avg_Estimated_Impact
,dm_migs.last_user_seek AS Last_User_Seek
,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS Tabel
,isnull(dm_mid.equality_columns,dm_mid.inequality_columns) AS on_column
,dm_mid.included_columns AS incluce_column
,'INSERT INTO ' + @sf_db + '.dbo.indx
(project_id
,project_vrs_id
,indx_id
,tab_id
,primary_key
,foreign_key
,type_of_indx
,unique_indx
,full_text_catalog_id
,full_text_key_indx_id
,storage_id
,alias_indx_id
,indx_desc
--,no_of_col
,generated
--,insert_user
--,insert_date_time
--,update_user
--,update_date_time
)
SELECT
' + '''' + @project_id + '''' + ' as project_id
,' + '''' + @PROJECT_VRS_ID + '''' + ' as project_vrs_id
,' + '''' + 'X_'+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + '''' + ' as indx_id
,' + '''' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '''' + ' as tab_id
,0' + ' as primary_key
,0' + ' as foreign_key
,1' + ' as type_of_indx
,0' + ' as unique_indx
,null' + ' as full_text_catalog_id
,null' + ' as full_text_key_indx_id
,null' + ' as storage_id
,null' + ' as alias_indx_id
,'+'''' + 'This index has been created by a script.' + '''' + ' as indx_desc
,0
where not exists(select 1
from '+ @sf_db + '.dbo.indx a
where a.project_id = ' + '''' + @project_id + '''' + '
and a.project_vrs_id = ' + '''' + @project_vrs_id + '''' + '
and a.indx_id = ' + '''' + 'X_'+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + '''' + ')' as INSERT_INDEX
,replace(replace(replace(replace(replace(
STUFF(
(select 'INSERT INTO ' + @sf_db + '.dbo.indx_col'
+'(project_id'
+',project_vrs_id'
+',indx_id'
+',tab_id'
+',col_id'
+',sort_order'
+',included'
+',order_no'
+',abs_order_no'
+')'
+'SELECT '
+ '''' + @project_id + '''' + ' as project_id'
+',' + '''' + @PROJECT_VRS_ID + '''' + ' as project_vrs_id'
+',' + '''' + 'X_'+ OBJECT_NAME(dm_mid1.OBJECT_ID,dm_mid1.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid1.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid1.equality_columns IS NOT NULL AND dm_mid1.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid1.inequality_columns,''),', ','_'),'[',''),']','') + '''' + ' as indx_id'
+',' + '''' + OBJECT_NAME(dm_mid1.OBJECT_ID,dm_mid1.database_id) + '''' + ' as tab_id'
+',' + '''' + dm_col1.column_name + '''' + ' as col_id'
+',' + cast(0 as varchar(max)) + ' as sort_order'
+',' + cast(case dm_col1.sort
when 3 then 1
else 0
end as varchar(max)) +' as included'
+',' + cast(ROW_NUMBER() over (partition by dm_mid1.index_handle order by dm_col1.sort,dm_col1.column_id) * 10 as varchar(max)) + ' as order_no'
+',' + cast(ROW_NUMBER() over (partition by dm_mid1.index_handle order by dm_col1.sort,dm_col1.column_id) as varchar(max)) + ' as abs_order_no '
+'where not exists(select 1 from '+ @sf_db + '.dbo.indx a ' + '/\N' +
'join ' + @sf_db + '.dbo.indx_col b' + '/\N' +
'on b.project_id = ' + '''' + @project_id + '''' + '/\N' +
'and b.project_vrs_id = ' + '''' + @project_vrs_id + '''' + '/\N' +
'and b.indx_id = a.indx_id ' + '/\N' +
'where a.project_id = ' + '''' + @project_id + '''' + '/\N' +
'and a.project_vrs_id = ' + '''' + @project_vrs_id + '''' + '/\N' +
'and a.indx_id = ' + '''' + 'X_'+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') + '''' + '/\N' +
'and b.col_id = ' + '''' + dm_col1.column_name + ''''
+ ')'
FROM sys.dm_db_missing_index_groups dm_mig1
JOIN sys.dm_db_missing_index_group_stats dm_migs1
ON dm_migs1.group_handle = dm_mig1.index_group_handle
JOIN sys.dm_db_missing_index_details dm_mid1
ON dm_mig1.index_handle = dm_mid1.index_handle
outer apply(select column_id
,column_name
,case column_usage
when 'EQUALITY' then 1
when 'INEQUALITY' then 2
when 'INCLUDE' then 3
else 4
end as sort
from sys.dm_db_missing_index_columns(dm_mig.index_handle)
)dm_col1
WHERE dm_mid1.database_ID = DB_ID()
and dm_mid1.index_handle = dm_mig.index_handle
FOR XML PATH('')
),1,0,'')
--replace
,','
,char(13)+','
)
--replace
,'INSERT INTO'
,CHAR(13) + CHAR(13) + 'INSERT INTO'
)
--replace
,'SELECT '
,CHAR(13) + 'SELECT '
)
--replace
,'WHERE NOT EXISTS'
,CHAR(13) + 'WHERE NOT EXISTS'
)
--replace
,'/\N'
,CHAR(13)
) as INSERT_INDEX_COLUMNS

,'CREATE INDEX [X_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Original_Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC

 


0 replies

Be the first to reply!

Reply