May 4, 2010 at 4:23 am
I have a table with following columns
Column_name Type
=========== ====
Id bigint <Primary Key>
ContentId bigint
UserId bigint
FirstName nvarchar
LastName nvarchar
ClientId int
EstimatedDuration int
TimelineDuration nvarchar
IsPublished bit
Description nvarchar
Title nvarchar
Version nvarchar
ThumbnailURL nvarchar
CreatedDate datetime
Tags nvarchar
DataXML xml
Copyrights nvarchar
LicenceInformation nvarchar
Summary nvarchar
TargetAudience nvarchar
TargetIndustry nvarchar
CompleteAtPercent int
CompletionCriteria nvarchar
StatusId int
AccessiblityId int
CopyOfPresentationMetaId bigint
IsFeatured bit
IsDeleted bit
UpdatedDate datetime
UpdatedBy bigint
PublishedDate datetime
IsDisabled bit
IsSellable bit
TenantSubDomainURL nvarchar
Size bigint
LivePresentationId bigint
LongDescription nvarchar
IsLivePresentation bit
IsPublicInternally bit
IsFree bit
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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