Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ron Johnson » Indexes, Indexes, Indexes
Add to briefcase
43 posts, Page 2 of 5
««12345»»»

Indexes, Indexes, Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
MadAdmin
Posted Tuesday, July 05, 2011 1:43 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 47, Visits: 280
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.
Post #1136788
MadAdmin
Posted Tuesday, July 05, 2011 1:49 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 47, Visits: 280
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.
Post #1136792
BW_Toro
Posted Tuesday, July 05, 2011 1:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 26, 2012 7:57 AM
Points: 12, 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
Post #1136798
Naked Ape
Posted Tuesday, July 05, 2011 2:28 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 04, 2013 5:54 PM
Points: 58, Visits: 634
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
Post #1136820
Brian O'Leary
Posted Tuesday, July 05, 2011 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 01, 2012 8:54 AM
Points: 31, Visits: 115
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
Post #1136832
MadAdmin
Posted Tuesday, July 05, 2011 4:15 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 47, Visits: 280
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'.
Post #1136892
Toby Harman
Posted Tuesday, July 05, 2011 5:01 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:21 AM
Points: 322, Visits: 475
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.
Post #1136930
Ninja's_RGR'us
Posted Tuesday, July 05, 2011 5:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:19 AM
Points: 21,357, Visits: 9,530
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 .
Post #1136931
scottm30
Posted Tuesday, July 05, 2011 7:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 3:26 PM
Points: 566, Visits: 341
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.
Post #1136967
Toby Harman
Posted Wednesday, July 06, 2011 12:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:21 AM
Points: 322, Visits: 475
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!
Post #1137036
« Prev Topic | Next Topic »

Add to briefcase
43 posts, Page 2 of 5
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.