{"id":2338,"date":"2017-01-05T08:44:03","date_gmt":"2017-01-05T05:44:03","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=2338"},"modified":"2017-04-05T09:32:47","modified_gmt":"2017-04-05T06:32:47","slug":"how-to-find-required-missing-indexes-for-performance-tuning-in-sql-server-database","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/how-to-find-required-missing-indexes-for-performance-tuning-in-sql-server-database\/","title":{"rendered":"How to Find Required Missing Indexes For Performance Tuning in SQL Server Database"},"content":{"rendered":"

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. <\/p>\n

\"\"<\/p>\n

Find Missing Indexes Query<\/strong><\/p>\n

\n
\r\n\r\nSELECT TOP 25\r\ndm_mid.database_id AS DatabaseID,\r\ndm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,\r\ndm_migs.last_user_seek AS Last_User_Seek,\r\nOBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],\r\n'CREATE INDEX [INX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'\r\n+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') \r\n+ CASE\r\nWHEN dm_mid.equality_columns IS NOT NULL\r\nAND dm_mid.inequality_columns IS NOT NULL THEN '_'\r\nELSE ''\r\nEND\r\n+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')\r\n+ ']'\r\n+ ' ON ' + dm_mid.statement\r\n+ ' (' + ISNULL (dm_mid.equality_columns,'')\r\n+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns \r\nIS NOT NULL THEN ',' ELSE\r\n'' END\r\n+ ISNULL (dm_mid.inequality_columns, '')\r\n+ ')'\r\n+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement\r\nFROM sys.dm_db_missing_index_groups dm_mig\r\nINNER JOIN sys.dm_db_missing_index_group_stats dm_migs\r\nON dm_migs.group_handle = dm_mig.index_group_handle\r\nINNER JOIN sys.dm_db_missing_index_details dm_mid\r\nON dm_mig.index_handle = dm_mid.index_handle\r\nWHERE dm_mid.database_ID = DB_ID()\r\nORDER BY Avg_Estimated_Impact DESC\r\nGO\r\n\r\n<\/code><\/pre>\n<\/blockquote>\n
Query Result Example<\/strong><\/p>\n
\n
\"\"<\/p>\n<\/blockquote>\n
Action For Performance Increase<\/strong><\/p>\n
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.<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11,8],"tags":[96,118],"class_list":["post-2338","post","type-post","status-publish","format-standard","hentry","category-administration-mssql-server","category-sql-mssql-server","tag-mssql-server","tag-sql-server-performance-tuning"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-BI","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=2338"}],"version-history":[{"count":1,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2338\/revisions"}],"predecessor-version":[{"id":2342,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/2338\/revisions\/2342"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=2338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=2338"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=2338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}