Skip to content
benjf.com
benjf.com

  • Home
  • About
  • Technology
    • Linux Mint (Cinnamon)
    • ✉ Email
    • Privacy
    • Programming
      • HTML
      • CSS
      • Javascript
        • jQuery
      • PHP
      • SQL
        • SQL Server
      • Powershell
      • MS Access
      • WordPress
  • Opinion
    • ✞ Faith
    • ☺ For Fun
    • ? Politics
    • ? Random Awesomeness
  • Health
benjf.com

SQL Server – Find Missing Indexes With Script In Output

benjf.com.blog@benjf.com, 2016-03-18

Source: http://sqlserverplanet.com/dmvs/missing-indexes-dmv

SELECT mid.statement
    ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
    ,OBJECT_NAME(mid.Object_id)
    ,'CREATE INDEX [idx_' + LEFT(PARSENAME(mid.statement, 1), 32) + '_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE
        WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
    ,migs.*
    ,mid.database_id
    ,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
SQL Server

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Coercion and Loving God
  • Fire is power and they don’t want you to have it
  • Biden Too Old?
  • Telegram is awful for content
  • Want proof that nobody believes the government and media?

Recent Comments

  1. Hairstyles on How To Convert PCM to MP3
  2. TSQL – Loop Through Result Set WITHOUT Cursors – {benjf.com/_/} on TSQL – Simple Loop Through Result Set Using Cursor
  3. TSQL – Simple Loop Through Result Set Using Cursor – {benjf.com/_/} on TSQL – Loop Through Result Set WITHOUT Cursors
  4. Script Storage – {benjf.com/_/} on Script Storage Update
  5. benjf.com » Well, The World Is Going To End Again on End of the World Prediction “Best Practices”

Archives

  • September 2023
  • July 2023
  • June 2023
  • May 2023
  • October 2022
  • September 2022
  • August 2022
  • May 2022
  • December 2021
  • November 2021
  • September 2021
  • July 2021
  • June 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • May 2018
  • April 2018
  • February 2018
  • December 2017
  • September 2017
  • June 2017
  • May 2017
  • February 2017
  • January 2017
  • December 2016
  • August 2016
  • July 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • October 2013
  • September 2013
  • August 2013
  • June 2013
  • May 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • October 2012
  • September 2012
  • July 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009

Categories

  • C#
  • CSS
  • EMail
  • Faith
  • For Fun
  • Health
  • HTML
  • Javascript
  • jQuery
  • Just Info
  • Movies
  • MS Access
  • Music
  • Opinion
  • PHP
  • Politics
  • Powershell
  • Privacy
  • Programming
  • Random Awesomeness
  • Self Hosting
  • SQL
  • SQL Server
  • Technology
  • Uncategorized
  • WordPress
©2023 benjf.com | WordPress Theme by SuperbThemes