May 4, 2010 at 4:23 am
I have a table with following columns
Column_nameType
=========== ====
Idbigint <Primary Key>
ContentIdbigint
UserIdbigint
FirstNamenvarchar
LastNamenvarchar
ClientIdint
EstimatedDurationint
TimelineDurationnvarchar
IsPublishedbit
Descriptionnvarchar
Titlenvarchar
Versionnvarchar
ThumbnailURLnvarchar
CreatedDatedatetime
Tagsnvarchar
DataXMLxml
Copyrightsnvarchar
LicenceInformationnvarchar
Summarynvarchar
TargetAudiencenvarchar
TargetIndustrynvarchar
CompleteAtPercentint
CompletionCriterianvarchar
StatusIdint
AccessiblityIdint
CopyOfPresentationMetaIdbigint
IsFeaturedbit
IsDeletedbit
UpdatedDatedatetime
UpdatedBybigint
PublishedDatedatetime
IsDisabledbit
IsSellablebit
TenantSubDomainURLnvarchar
Sizebigint
LivePresentationIdbigint
LongDescriptionnvarchar
IsLivePresentationbit
IsPublicInternallybit
IsFreebit
The Index Tunning Advisor suggested following four Indexes to be applied again a given workload.
The acutal question is whether we need to includ ID column in all non clustered indexes?
second question is do we need to separate each non-clustered index or keep multiple columns in one index
As follows:
/****** Object: Index [_dta_index_PresentationMetaInfo_27_991342596__K1_28_32_38] Script Date: 05/04/2010 15:19:39 ******/
CREATE NONCLUSTERED INDEX [_dta_index_PresentationMetaInfo_27_991342596__K1_28_32_38] ON [dbo].[PresentationMetaInfo]
(
[Id] ASC
)
INCLUDE ( [IsDeleted],
[IsDisabled],
[IsLivePresentation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [_dta_index_PresentationMetaInfo_27_991342596__K28_K25_K1] Script Date: 05/04/2010 15:19:39 ******/
CREATE NONCLUSTERED INDEX [_dta_index_PresentationMetaInfo_27_991342596__K28_K25_K1] ON [dbo].[PresentationMetaInfo]
(
[IsDeleted] ASC,
[AccessiblityId] ASC,
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [_dta_index_PresentationMetaInfo_27_991342596__K28_K6_K25_K9] Script Date: 05/04/2010 15:19:39 ******/
CREATE NONCLUSTERED INDEX [_dta_index_PresentationMetaInfo_27_991342596__K28_K6_K25_K9] ON [dbo].[PresentationMetaInfo]
(
[IsDeleted] ASC,
[TenantId] ASC,
[AccessiblityId] ASC,
[IsPublished] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [_dta_index_PresentationMetaInfo_27_991342596__K6_K9_K28_K1_25_27_33] Script Date: 05/04/2010 15:19:39 ******/
CREATE NONCLUSTERED INDEX [_dta_index_PresentationMetaInfo_27_991342596__K6_K9_K28_K1_25_27_33] ON [dbo].[PresentationMetaInfo]
(
[TenantId] ASC,
[IsPublished] ASC,
[IsDeleted] ASC,
[Id] ASC
)
INCLUDE ( [AccessiblityId],
[IsFeatured],
[IsSellable]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
My question is do i need to run then indexes or apply each column index separately. What you experts suggest?
Shamshad Ali.
May 4, 2010 at 4:39 am
[b
The acutal question is whether we need to includ ID column in all non clustered indexes?
second question is do we need to separate each non-clustered index or keep multiple columns in one index
These are unanswerable at the moment. It depends on the queries you are executing against the table , since you have not provided any details of those , any advice would be pure speculation.
May 4, 2010 at 6:15 am
I agree with Dave, specifics on your system are hard to say without a lot more detail. However, I would be very careful about indexes 2 & 3 in that list. They both have the same leading edge and have a common additional key column. I suspect with a little work you could eliminate one or the other of those indexes.
Remember, missing indexes and the index suggestions of the DTA are not carved in stone. They're suggestions based on estimates provided by your queries and the the statistics on your tables & indexes. They're not necessarily right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 12:20 am
based on the statistics, Sql server maintain suggestions itself, you can query from a DMV like...
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC;
I hope this helps, follow the index_advantage! 🙂
May 5, 2010 at 12:26 am
Another thing, you can also eliminate the rarely used index with the help of statistics.
Select ObjectName=object_name(s.object_id)
, ObjectId = s.object_id
, Indexname = i.name
, IndexId = i.index_id
, UserSeeks = user_seeks
, UserScans = user_scans
, UserLookups = user_lookups
, UserUpdates = user_updates
from sys.dm_db_index_usage_stats s
join sys.indexes i
on i.object_id= s.object_id
and i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable')= 1
order
by (user_seeks +
user_scans +
user_lookups +
user_updates
)asc
I hope it would help to decide which index to drop or not to.
May 5, 2010 at 2:01 am
Yes, you can use the missing index DMVs, however less logic goes into producing those recommendations than the recommendations produced by DTA. Neither should be trusted without verification, all suggestions must be evaluated and tested.
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
May 5, 2010 at 5:52 am
GilaMonster (5/5/2010)
Yes, you can use the missing index DMVs, however less logic goes into producing those recommendations than the recommendations produced by DTA. Neither should be trusted without verification, all suggestions must be evaluated and tested.
Absolutely, and another issue with the missing index DMV's is that it's impossible to tie them back to specific queries, so you can't readily test to see that they've solved an issue or not. Although there is a way around that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 6:00 am
Agree, implementation of proper indexes is a continous process, thats a big part of indexing strategy, you put some indexes, run some load test, get the results, compare with the previous ones and move on accordingly.
There is no such formula for that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply