Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2010-12-27 : 06:24:11
|
| Hi all, While analyzing a database with respect to performance optimization, I have come across few tables, which have 10 + rows (missing index) with different combination of columns and/or included columns for few tables. Improvement measures for the missing indexes are calculated using the following computation: "migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)"Please let me know how to handle this scenario e.g. to create all identified missing indexes or to handle it via some other way? Many thanks!MIK |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-12-27 : 07:02:13
|
For only 10 rows, there is no need to add an index. The least amount of data read by SQL Server anyway is one page (8k).There is a big chance all 10 records are read at once.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-27 : 07:49:42
|
| Depends on te system.It can make a diference having an index on a table it it is involved in a lookup loop even if it is all in memory.Also maybe an inefficient query on a table might be better than the overhead of an index.There's no way of saying whether an index should be added without looking at the system as a whole.Best is to look for issues in the running of the system - anything other than that will just be a guess.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2010-12-27 : 08:11:09
|
| @Peso, Thank you for your response.Perhaps I could not explained it well... its not table's data rows which returns 10. Actually I am trying to find out the missing indexes for a databases using the following query: 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), ,migs.* , mid.database_id yyy, 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 improvement_measure DESC The result of the above query returns multiple (10+) missing indexes for same table. e.g. there is a table name "Tab1" with 6 columns. the query suggests 15 different types of non-clustered/covering indexes if implemented would decrease the figure displaying under the Improvement measures ("migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)").Explanation via example: Table Name: Tab1Columns: Col1, Col2, Col3, Col4, Col5After executing the above script, SQL server identified that if I Create following missing indexes will reduce "improvement measure" figuresCREATE INDEX [missing_index_209_208_Tab1] ON [DBname].[dbo].[Tab1] ([Col1], [Col2])CREATE INDEX [missing_index_211_210_Tab1] ON [DBname].[dbo].[Tab1] ([Col3]) INCLUDE ([Col2])CREATE INDEX [missing_index_31_30_Tab1] ON [DBname].[dbo].[Tab1] ([Col2])CREATE INDEX [missing_index_241_240_Tab1] ON [DBname].[dbo].[Tab1] ([Col3],[Col5]) INCLUDE ([Col2])CREATE INDEX [missing_index_7466_7465_Tab1] ON [DBname].[dbo].[Tab1] ([Col5], [Col2], [Col6])As you can see that the advise from SQL server DMVs is to create so many indexes for just one table, but i think this may have side effects on the DML operations and could tend to reduce the performance (rather to increase). Please suggest what should I do in such scenario. Hope you may suggest a solution in light of this scenario! Many thanks!MIK |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2010-12-28 : 01:11:39
|
| @nigelrivett, Thanks for the response. Yes, I agree with you that its not good to create so many indexes. as far as the Stored procedures or queries are concerned i think all of them are working well enough to the level with out indexes. But some of them have missing indexes impact in 80+ for the tables having millions of records. one of the case is the above Tab1, for which the DMVs suggested 16 different missing indexes. Actually I think i should make one index which covers all the columns as specified by the missing index DMVs. However before I go for this on the production server I would like to have some advise from you guys, as if it would be a better approach or not. thanks!Cheers!MIK |
|
|
|
|
|
|
|
|
Missing indexes