Tuesday, April 13, 2010

Missing Index for SQL Server for slow performance

This query will script indexes create statements when you have slow performance with SQL server. After applying the indexes you should see some improvement. SQL Server keeps an internal list of indexes it believe that you should create to speed things up and this query will show them to you. This is not as thorough / accurate as using the Index Tuning Wizard and you do not know how large the indexes will be. The scripts with the highest improvement_measure should be tested first because you would see the most benefit. The tables that hold the recommended indexes are cleared every time you restart SQL server and are not populated until you use put a load on the database. If you do not see a query improve in speed after testing the index, it may still be improving but you did not notice. Check to see if the CPU / Memory/ Disk usage usage was reduced. I recommend adding this view to your database and selecting from it from time to time to see what shows up.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create view [dbo].[usp_Missing_Indexes] as

select * from
(
SELECT
'CREATE INDEX [experimental_index_' + CONVERT (varchar, missingGroups.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' 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,
stats.avg_total_user_cost * stats.avg_user_impact * (stats.user_seeks + stats.user_scans) as BenefitWeight,
stats.avg_total_user_cost * (stats.avg_user_impact / 100.0) * (stats.user_seeks + stats.user_scans) AS improvement_measure,
stats.unique_compiles,
stats.user_seeks + stats.user_scans as SeeksScans,
stats.last_user_seek,
stats.last_user_scan

FROM sys.dm_db_missing_index_groups missingGroups
INNER JOIN sys.dm_db_missing_index_group_stats stats ON stats.group_handle = missingGroups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON missingGroups.index_handle = mid.index_handle
) a
where improvement_measure > 100
--order by BenefitsWeight

5 Comments:

At 10:41 PM, Anonymous RS Gold said...

This is a great article, supplies the useful information for me.

 
At 3:30 AM, Anonymous QHYAPPLE said...

This is a very interesting blog and so i like to visit your blog again and again. Keep it up.it ls a good article and love your words , so charming and make people learn a lot , thanks !

http://teraheart.blog.fc2blog.net/blog-entry-1.html

http://littlehappy.qblog.it/2011/10/17/cant-wait-to-get-your-hands-on-the-most-anticipated-games/

 
At 12:36 AM, Blogger Tera Online Gold said...

Most of the particular Mmo currency Cheapest WOW Gold retail outlets will Purchasing Sidesplitter Aureate carry the percent from the RS your own previous(a) wristwatches, when your clench could possibly be huge, then you need to wait for an inadequate objet d'art (ordinarily more or less all day and in some cases numerous days) until such time as masses collect your current Gold WOW stock-taking for you Bribe Aion Kinah personally. You'll want to be affected person from time to time if they're offered-come out of the closet at that moment,Grease one's palms Tera Gilt it's very criterion internet Buying WOW Gold promotion purchased-emerge for you actually, simply because Old watches is successful victimization the majority of oceans.

 
At 10:51 PM, Blogger ambersbridal said...

A holy church, rings, a bouquet, a 3-floor wedding cake, lace wedding dress champagne, and the moving promise “ I do”, make up the happiest moment in one’s life. Then it is the wedding.
lace wedding gowns
A gorgeous wedding dress has been inside a girl’s dream since she was five years old. It is widely said that the bride is the most beautiful woman all over the world.
Bridal gowns vary in different parts of the world. For example, cheap wedding dresses plus sizein traditional Chinese culture, the color red is regarded for centuries as the symbol of good luck; while in the Occident, wedding dresses 2012 a full-length white wedding dress is always associated with romance and sanctity. But, with the globalization, wedding dresses tend to be alike in the whole world.

 
At 4:38 AM, Blogger ambersbridal said...

A holy church, rings, a bouquet, a 3-floor wedding cake, lace wedding dress
champagne, and the moving promise “ I do”, make up the happiest moment in one’s life. Then it is the wedding.
lace wedding gowns
A gorgeous wedding dress has been inside a girl’s dream since she was five years old. It is widely said that the bride is the most beautiful woman all over the world.
Bridal gowns vary in different parts of the world. For example, cheap wedding dresses plus sizein traditional Chinese culture, the color red is regarded for centuries as the symbol of good luck; while in the Occident, wedding dresses 2012 a full-length white wedding dress is always associated with romance and sanctity. But, with the globalization, wedding dresses tend to be alike in the whole world.

 

Post a Comment

<< Home