Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexes, Indexes, Indexes


Indexes, Indexes, Indexes

Author
Message
MadAdmin
MadAdmin
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1915
For me it was quick to find the root cause.

I found many non clustered indexes included dateTime as part of a covering index. 6 indexes on the main mothershiptable of our app.
Problem is that dateTime is not an int.
Chances of getting the exact time is very bad so doesnt work with "=".
They work best when clustered on datetime , i.e. ">=; <" etc. (if clustered on a column that is ever increasing and never changing.)

So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)
the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".
It then avoids the index at all costs cause it would be, well, costly.

The next question should be, who actually allowed an index to be created including datetime on a nonclustered index.

I then ran a query finding all nonclustered indexes which include datetime and surprise surprise, they had millions of updates and no userscan, no index seek, no index scan, rid lookup(heap).
This is fine if it is the PK that never gets queried on, and links to other tables using FK's.

But I am a mere tester so my opinion is worth less than squat. Even with proof.

Catch-all queries done right
Gail Shaw's Performance Blog
MadAdmin
MadAdmin
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1915
Did you know it is possible to create duplicate indexes on one table, both non clustered?
One will never be used, but will cost in inserts and updates.

What reason would there be to not immediately delete the unused index?
If it is not used after 6 months, and statistics are up to date, then only due to irrational fear would they not be deleted.

Catch-all queries done right
Gail Shaw's Performance Blog
BW_Toro
BW_Toro
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 523
Steven993,

Are you sure the query cited by peter-757102 is fine? The object_id values are not unique across databases. When I run the query below I get numerous cases of the same object_id and index_id in different databases. I think checking the database_id is necessary to avoid associating index usage stats with the wrong object.

  SELECT object_id
, index_id
, MIN(DB_NAME(database_id) + '.' + OBJECT_NAME(object_id,database_id))
, MAX(DB_NAME(database_id) + '.' + OBJECT_NAME(object_id,database_id))
, COUNT(*)
FROM sys.dm_db_index_usage_stats
GROUP BY object_id
, index_id
HAVING COUNT(*) > 1
ORDER BY object_id
, index_id


Naked Ape
Naked Ape
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 703
Interesting reading. Was that a deliberate reference to Alices Restaurant, Massacre (twenty-eight color graphs with circles and arrows and a paragraph on the bottom of each one)?

Chris
Brian O'Leary
Brian O'Leary
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 116
doranmackay (7/5/2011)
For me it was quick to find the root cause.

So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)
the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".
It then avoids the index at all costs cause it would be, well, costly.



Thats not true. If your nonclustered index is not covering you will still need to do a bookmark or rid lookup (thats the point of covering) regardless of the data type. The descision to perform a range scan by the optimizer is to do with cardinality rather than the use of a datetime data type.

For example, run this script against the AdventureWorks database and you will see an index seek based on the date filter, however if you change the date from '20040703' to '20040702' the optimzer will choose a clustered index scan.

DBCC FREEPROCCACHE
Go
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[WorkOrder]') AND name = N'IX_StartDate')
DROP INDEX [IX_StartDate] ON [Production].[WorkOrder]
GO
CREATE NONCLUSTERED INDEX [IX_StartDate] ON [Production].[WorkOrder]
(
   [StartDate] ASC
)
GO
SELECT
*
FROM Production.WorkOrder
WHERE StartDate >= '20040703'
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[WorkOrder]') AND name = N'IX_StartDate')
DROP INDEX [IX_StartDate] ON [Production].[WorkOrder]

MCITP SQL Server 2005/2008 DBA/DBD
MadAdmin
MadAdmin
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1915
Brian O'Leary (7/5/2011)
doranmackay (7/5/2011)
For me it was quick to find the root cause.

So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)
the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".
It then avoids the index at all costs cause it would be, well, costly.



Thats not true. If your nonclustered index is not covering you will still need to do a bookmark or rid lookup (thats the point of covering) regardless of the data type. The descision to perform a range scan by the optimizer is to do with cardinality rather than the use of a datetime data type.

For example, run this script against the AdventureWorks database and you will see an index seek based on the date filter, however if you change the date from '20040703' to '20040702' the optimzer will choose a clustered index scan.


Correct, I should have included that the specific indexes existed on a highly transactional database.
DateTime has low cardinality when only using the date portion,and based on the size of the result set estimated in adventureworks, I would expect successful usage of datetime index since the results from smaller tables is few.
Do the same test, where you have 1000 unique records being created per hour, and modified, with a couple of joins happening and due to the row count of the tables, a date filter will result in too much records. In those situations, inserts and updates cost because the likelihood of benefiting from a datetime index is zero unless you can specify the datetime down to the millisecond and use where x = '20040703.123456'.

Catch-all queries done right
Gail Shaw's Performance Blog
Toby Harman
Toby Harman
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 668
Good article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.

As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Toby Harman (7/5/2011)
Good article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.

As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.



Just to humor them you could disable them so that you can "easily" reenable them in the future... they probably don't know the difference anyways w00t.
scottm30
scottm30
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 383
Toby Harman (7/5/2011)
Good article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.

As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.


The moment they are dropped, users will believe the system is running slower. Furthermore any application problems will be initially blamed on the missing indexes.
Toby Harman
Toby Harman
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 668
scottm30 (7/5/2011)

The moment they are dropped, users will believe the system is running slower. Furthermore any application problems will be initially blamed on the missing indexes.


So don't tell them when you drop them. Do the usual CYA stuff and tell your boss and explain to him exactly why you want to be sneaky!

Remember Schrödinger's cat and the Observer-expectancy effect! If they don't know they are being observed then they'll behave as normal. They'll still be complaining, but no more than normal.

Either that or drop the indexes, and the say that you have re-added them after the complaints come but leave them disabled.

There's more than one way to fool a user!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search