Jan 05 2017

How to Find Required Missing Indexes For Performance Tuning in SQL Server Database

Category: Administration,SQLFatih Acar @ 08:44

Indexes play vital role for performance tuning on database systems. When you adjust correct indexes on tables, queries can run faster. Otherwise, a proper index can improve the performance and a bad index can hamper the performance. You can find missing indexes with below query. But, result of the query do not mean that you have to create indexes which are all results of query. You have to think which index will be helpful. If you create a lot index on the same table, your database size will increase and DML operation speed will be able to decrease.

Find Missing Indexes Query



SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [INX_' + 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 Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER 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
GO

Query Result Example

Action For Performance Increase

You can use create scripts which are query result to create indexes. Avg_Estimated_Impact column give index average impact and according to this value you can think which index is important. You do not forget that if you create a lot index on the same table, this increase size of database and decrease DML operation performance. You should create index which has only high Avg_Estimated_Impact value.

664 total views, 2 views today

Tags: MsSQL Server, SQL Server Performance Tuning


Dec 19 2012

Create Schedule Job For Execute Procedure in SQL Server 2008

Category: Administration,SQLFatih Acar @ 20:22

If you want to run procedure or any query as scheduling, you can use job in SQL Server Database. For this process, you can use below operations.

Right Click On SQL Server Agent > New > Job

Scheduled_Job_1

Scheduled_Job_2
Continue reading “Create Schedule Job For Execute Procedure in SQL Server 2008”

38,154 total views, 4 views today

Tags: Database Administration, MsSQL Server