July 9, 2012 at 12:10 pm
Hello Everyone,
Hope All is well.
I was tuning some of our EF queries. The query plans suggest adding some nonclustered indexes would improve the performance by 70%. But looking at the scripts what I noticed is the query plans suggest to add multiple non clustered indexes on the same table but with different included columns. Lets say if I have columns a,b,c,d,e,f,g then the indexes it suggest looks like this
CREATE NONCLUSTERED INDEX [IX_Tablename] ON [dbo].[Tablename]
(
[A] ASC,
ASC,
[C] ASC
)INCLUDE ( D,E) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Tablename] ON [dbo].[Tablename]
(
[A] ASC,
[E] ASC,
[F] ASC
)INCLUDE ( B,G) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Tablename] ON [dbo].[Tablename]
(
[D] ASC,
[E] ASC,
[F] ASC
)INCLUDE ( A,B,C) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Tablename] ON [dbo].[Tablename]
(
[A] ASC,
ASC,
[C] ASC,
[D] ASC,
[E] ASC
)INCLUDE ( F,G) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
Is it mandatory for me to create all these 4 different indexes or can i create 1 index that can take care of and replace all these four indexes.
As i said each index is imporiving the performance by 70%
Need ur help on this. Thanks for ur inputs.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 9, 2012 at 12:20 pm
Please post the DDL and PLAN of the query. Does your query have sarg for some of these columns and joins on other columns or maybe sarg on almost all of these columns?
But that will be clear if you can provide the query..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 9, 2012 at 12:47 pm
You cannot create one index to combine all 4, the key columns are different.
Given those indexes, and assuming that all are actually necessary to improve the query's performance to acceptable levels (missing indexes is suggestions, nothing more), I would create these:
(A,B, C, D, E) INCLUDE ( F,G)
(D, E, F) INCLUDE ( A,B,C)
(A, E, F) INCLUDE ( B,G)
The last would have to be tested see if it's necessary or if the first index will adequately support that query. Depends on the rows that matching on A returns vs matching on A, E and F for that query.
Unless the query is unacceptably slow without the 3rd index, I wouldn't create it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2012 at 12:59 pm
Thanks Gail and Gullimeel
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
July 9, 2012 at 2:54 pm
You likely should have (at least) column A as the clustered index on the table.
Would need more details and info to determine if another column(s) should be in the clus index.
With the wrong clus index, you'll be adding and adjusting nonclus indexes forever and still not getting the best response times.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 9, 2012 at 3:14 pm
Please substitute your db name below,
and your table name in BOTH places below where <your_table_name> appears, then run the queries and post the results.
USE <your_db_name>
SELECT
GETDATE() AS capture_date,
DB_NAME(mid.database_id) AS Db_Name,
OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
mid.*, igs.*
FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.dm_db_missing_index_group_stats igs WITH (NOLOCK) ON
igs.group_handle = mig.index_group_handle
WHERE
1 = 1
AND mid.database_id = DB_ID()
AND mid.object_id IN (OBJECT_ID('<your_table_name>'))
ORDER BY
Db_Name, Table_Name, equality_columns, inequality_columns
SELECT
DB_NAME() AS db_name, OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name, i.name AS index_name,
(SELECT DATEDIFF(DAY, create_date, GETDATE()) FROM sys.databases WHERE name = 'tempdb') AS sql_up_days,
dps.row_count,
ius.*
FROM sys.indexes i WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
dps.object_id = i.object_id AND
dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
ius.database_id = DB_ID() AND
ius.object_id = i.object_id AND
ius.index_id = i.index_id
WHERE
i.object_id > 100 AND
i.is_hypothetical = 0
AND OBJECT_NAME(i.object_id, DB_ID()) IN ('<your_table_name>')
ORDER BY
1, 2, 3
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply