April 19, 2012 at 6:24 pm
GilaMonster (4/19/2012)
SQLKnowItAll (4/19/2012)
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered.That's not necessarily true. Depends very much on the specifics, the query, the full index definition, etc.
At a client a few years ago, over half of the indexes they had (ones that I'd created) had a bit column as the leading column of the index key.
I guess that's what I meant. In "certain" cases it does not help. Its worth looking into if you are investigating all indexes.
Jared
CE - Microsoft
April 19, 2012 at 6:41 pm
SQLKnowItAll (4/19/2012)
GilaMonster (4/19/2012)
SQLKnowItAll (4/19/2012)
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered.That's not necessarily true. Depends very much on the specifics, the query, the full index definition, etc.
At a client a few years ago, over half of the indexes they had (ones that I'd created) had a bit column as the leading column of the index key.
I guess that's what I meant. In "certain" cases it does not help. Its worth looking into if you are investigating all indexes.
If it's not useful, it'll be picked up by the 'low read, high write' check
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
April 19, 2012 at 6:57 pm
GilaMonster (4/19/2012)
SQLKnowItAll (4/19/2012)
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered.That's not necessarily true. Depends very much on the specifics, the query, the full index definition, etc.
At a client a few years ago, over half of the indexes they had (ones that I'd created) had a bit column as the leading column of the index key.
Did that with an index on a table at a previous employer. The field indicated active or archive on the records. Most queries were against active records and those made up less than 10% of the records in the table. Pretty sure that percentage as dropped some over time as more records become archived. Growth on the table was stable with about the same number of records going to archive as new records came in and we didn't purge any of the archived data.
April 19, 2012 at 9:02 pm
Check the read and write %age on the tables. If the table is queried ones in a year, then there is no pointing in having indexes for such tables. Bank on it and try to create the missing indexes with the help of sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns.
April 19, 2012 at 9:06 pm
preetham gowda (4/19/2012)
Check the read and write %age on the tables. If the table is queried ones in a year, then there is no pointing in having indexes for such tables. Bank on it and try to create the missing indexes with the help of sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns.
Not necessarily. Sometimes those indexes are more important than you may think, and the index is actually needed.
April 20, 2012 at 3:40 am
Lynn Pettis (4/19/2012)
preetham gowda (4/19/2012)
Check the read and write %age on the tables. If the table is queried ones in a year, then there is no pointing in having indexes for such tables. Bank on it and try to create the missing indexes with the help of sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns.Not necessarily. Sometimes those indexes are more important than you may think, and the index is actually needed.
And don't create everything that missing indexes says, otherwise you'll have more duplicate and overly-wide indexes than you know what to do with.
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
April 20, 2012 at 3:59 am
SkyBox (4/19/2012)
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.
Thats true for when you create an index in the AOT, but you can also create a custom module which executes when the AOT is started which you can give a create index statements which will actually create included indexes, just the AOT wont be able to see them as they havn't been created in the AOT. This is how we manage indexes for our AX reporting environment which is in a logshipping environment as we cant create indexes on the secondary.
Appologies if you already know this, but if you are making any changes to the schema for AX it has to be done in the AOT not in SQL itself due to how it stores the data dictionary.
April 20, 2012 at 5:37 am
anthony.green (4/20/2012)
SkyBox (4/19/2012)
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.Thats true for when you create an index in the AOT, but you can also create a custom module which executes when the AOT is started which you can give a create index statements which will actually create included indexes, just the AOT wont be able to see them as they havn't been created in the AOT.
So the AOT synchronizes with the db via the module? I was considering altering indexes in sql after creation in the AOT, but knew the AOT would not sync properly with the mods.
Would be very interested in additional info you can provide on creating this custom module and the up keep involved. Your using this module for a reporting env. - is it not a good fit for your prod ERP system?
April 20, 2012 at 5:54 am
SkyBox (4/20/2012)
anthony.green (4/20/2012)
SkyBox (4/19/2012)
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.Thats true for when you create an index in the AOT, but you can also create a custom module which executes when the AOT is started which you can give a create index statements which will actually create included indexes, just the AOT wont be able to see them as they havn't been created in the AOT.
So the AOT synchronizes with the db via the module? I was considering altering indexes in sql after creation in the AOT, but knew the AOT would not sync properly with the mods.
Would be very interested in additional info you can provide on creating this custom module and the up keep involved. Your using this module for a reporting env. - is it not a good fit for your prod ERP system?
No, the module just issues a bunch of create index statements, the AOT will never know anything about these indexes as they have been created outside of the AOT. As the AOT cannot see these indexes it cannot apply them to the ERP environment as such due to them not being in the data dictionary.
I will have to dig out the URL for it, not looked at this in around 12 months.
April 20, 2012 at 5:57 am
GilaMonster (4/19/2012)
SQLKnowItAll (4/19/2012)
GilaMonster (4/19/2012)
SQLKnowItAll (4/19/2012)
To add a bit that people often forget is the uniqueness of the column(s) indexed. If the data is skewed the index may not be very helpful unless filtered.That's not necessarily true. Depends very much on the specifics, the query, the full index definition, etc.
At a client a few years ago, over half of the indexes they had (ones that I'd created) had a bit column as the leading column of the index key.
I guess that's what I meant. In "certain" cases it does not help. Its worth looking into if you are investigating all indexes.
If it's not useful, it'll be picked up by the 'low read, high write' check
@Gail Now I am confused, I thought that we cannot rely on that for determining the usefulness of an index? Or, are you saying that once determining the usefulness of the index by looking at the design and the queries that may be using it, we can then look at the read/write to see if the optimizer is actually using it?
Jared
CE - Microsoft
April 20, 2012 at 6:11 am
anthony.green (4/20/2012)
SkyBox (4/20/2012)
anthony.green (4/20/2012)
SkyBox (4/19/2012)
One of our systems (AX 2009) doesn't allow "included columns" on indexes, so with that in mind, I want be sure that those indexes aren't covering too many columns.Thats true for when you create an index in the AOT, but you can also create a custom module which executes when the AOT is started which you can give a create index statements which will actually create included indexes, just the AOT wont be able to see them as they havn't been created in the AOT.
So the AOT synchronizes with the db via the module? I was considering altering indexes in sql after creation in the AOT, but knew the AOT would not sync properly with the mods.
Would be very interested in additional info you can provide on creating this custom module and the up keep involved. Your using this module for a reporting env. - is it not a good fit for your prod ERP system?
No, the module just issues a bunch of create index statements, the AOT will never know anything about these indexes as they have been created outside of the AOT. As the AOT cannot see these indexes it cannot apply them to the ERP environment as such due to them not being in the data dictionary.
I will have to dig out the URL for it, not looked at this in around 12 months.
Cant quite seem to find the webpage, but I know all it took was to write the module and then call it from a batch job in AX after a AOT sync has been issued. I'll keep digging.
April 20, 2012 at 6:13 am
SQLKnowItAll (4/20/2012)
@Gail Now I am confused, I thought that we cannot rely on that for determining the usefulness of an index?
You can, I never said otherwise (see blog post I referenced earlier). You do have to be extremely careful that you have a large enough sample.
What I'm saying about unselective indexes is that you cannot simply state that an unselective index isn't useful. If it's not useful then a careful and thorough analysis of the index usage stats will show that it's not useful, just like with any other index. You can then investigate and see if anything needs it (anything that doesn't run on a regular basis), see if that 'anything' can make do with one of the other indexes on the table adequately and then decide whether it gets dropped or not.
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
April 23, 2012 at 8:45 am
Thanks all - for sharing the very useful links and knowledge. I always like to check in here to be sure I have a full understanding, before I make drastic changes.
Never worked with any DBA's (or senior), so I have no one to bounce ideas off of and learn from. This site rocks!
January 24, 2024 at 4:49 am
This helps show possible overlaps. I hope it helps.
WITH IndexInfo AS (
SELECT
SCHEMA_NAME(t.[schema_id]) AS [SchemaName],
i.object_id AS ObjectID,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
(
SELECT STUFF((
SELECT ', ' + c.name
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) AS KeyColumns,
(
SELECT STUFF((
SELECT ', ' + c.name
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) AS IncludeColumns
FROM sys.indexes AS i
LEFT OUTER JOIN sys.tables AS t WITH (NOLOCK)
ON t.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND i.[type_desc] = 'NONCLUSTERED' -- Filter for nonclustered indexes
AND i.is_unique = 0 -- Filter for indexes that are not unique
AND i.is_hypothetical = 0 -- Exclude statistics indexes
),
GroupedIndexes AS (
SELECT
SchemaName,
ObjectID,
TableName,
(
SELECT STUFF((
SELECT ', ' + IndexName
FROM IndexInfo AS ii
WHERE ii.ObjectID = gi.ObjectID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) AS IndexList,
MAX(KeyColumns) AS KeyColumns,
MAX(IncludeColumns) AS IncludeColumns
FROM IndexInfo AS gi
WHERE EXISTS (
SELECT 1
FROM sys.index_columns AS ic1
INNER JOIN sys.index_columns AS ic2
ON ic1.object_id = ic2.object_id
AND (
(ic1.column_id <> ic2.column_id AND ic1.is_included_column = 0 AND ic2.is_included_column = 0) OR
(ic1.column_id <> ic2.column_id AND ic1.is_included_column = 0 AND ic2.is_included_column = 1) OR
(ic1.column_id <> ic2.column_id AND ic1.is_included_column = 1 AND ic2.is_included_column = 1)
)
WHERE ic1.object_id = gi.ObjectID
AND ic1.index_id != ic2.index_id
)
GROUP BY SchemaName, ObjectID, TableName
),
FilteredIndexes AS (
SELECT
SchemaName,
ObjectID,
TableName,
IndexList,
KeyColumns,
(
SELECT STUFF((
SELECT ', ' + TRIM(value)
FROM (
SELECT
LTRIM(RTRIM(SUBSTRING(',' + IncludeColumns + ',', Number + 1, CHARINDEX(',', ',' + IncludeColumns + ',', Number + 1) - Number - 1))) AS value
FROM master..spt_values
WHERE
Type = 'P'
AND Number <= LEN(',' + IncludeColumns + ',') - 1
AND SUBSTRING(',' + IncludeColumns + ',', Number, 1) = ','
) AS split
WHERE CHARINDEX(TRIM(value), KeyColumns) = 0
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) AS FilteredIncludeColumns
FROM GroupedIndexes
WHERE CHARINDEX(',', IndexList) > 0 -- Filter for results with more than one index in IndexList
),
GroupedIndexesWithGroupId AS (
SELECT
*,
DENSE_RANK() OVER (ORDER BY SchemaName, TableName) AS CorrelationID
FROM FilteredIndexes
)
SELECT
gi.CorrelationID,
gi.SchemaName,
gi.TableName,
gi.IndexList,
gi.KeyColumns,
gi.FilteredIncludeColumns AS IncludeColumns
FROM GroupedIndexesWithGroupId gi
ORDER BY gi.CorrelationID, gi.TableName;
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply