December 31, 2010 at 11:31 pm
Gidday,
We run a Reservation Software where bookings are the core of the business. The Reservation table has about 25 columns like bookedDate, PickupDate, DropOffDate, PickupLocation.....
And the users often search for reservations by specifing one of more fields. And I have been adding indexes to make these searches faster. We have now got to a stage where the index size is 3 times the data size on this table and insert and updates on this table is getting slower.
Any idea how to find a balance for this issue?
thanks & Regards,
Ravi
January 1, 2011 at 4:59 am
You might want to read this[/url] article to verify your data types are as efficient as possible.
Aother issue might be the indexing strategy itself. Maybe you can narrow it down by moving some of the columns to the INCLUDE section. It's also possible that some of your indexes are either redundant or not queried often enough.
What method did you use to define the indexes? (I surely hope the answer is not DTA...).
Do you regulary run a missing index as well as an unused index check?
Without table def including definition for all indexes as well as the results of sys.dm_db_index_usage_stats (including the index name based on sys.indexes) for this specific table it'll be hard to tell...
Edit: Link added.
January 3, 2011 at 6:47 am
Also if you're just adding single column indexes all over the place, you might be hurting yourself without knowing it. Are you adding the indexes based on the execution plans and do you know that, after adding the index, that performance improves, or are you making assumptions? The best way to solve this issue is through lots of gathered metrics so you know which queries are run most frequently, which will benefit from specific indexes and which won't and begin to narrow down what you're maintaining. Also, do you have a clustered index on the table? Is it the most frequently used access point to the data?
"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
January 3, 2011 at 6:50 am
LutzM (1/1/2011)
You might want to read this article to verify your data types are as efficient as possible.
Which article? 😉
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
January 3, 2011 at 6:52 am
Ravi, can you list the indexes on the table? Good chance you have some redundant ones.
My usual indexing strategy in that kind of situation is to tailor indexes for the most important/most common queries and then deal with others on a case-by-case basis, often SQL can manage with indexes that aren't ideal, which for less important queries is probably OK.
Can you also maybe post a couple of example queries? Have you verified that the queries are indeed using the indexes?
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
January 3, 2011 at 6:57 am
GilaMonster (1/3/2011)
LutzM (1/1/2011)
You might want to read this article to verify your data types are as efficient as possible.Which article? 😉
Ouch. :blush: Link added. Thanx Gail. (It's the Disk Is Cheap! editorial link btw...)
January 4, 2011 at 12:40 am
Hi Guys, Thanks for your responses... I have generalised the issue to make it more readable ...
The Reservation(actual name Requirement) Table def looks like this
ID [uniqueidentifier],
Kind [nvarchar] 255 Not Null, /* Kinds: Sale / Purchase */
ReservationNo [nvarchar] 16 Not Null,
ItineraryID [uniqueidentifier] Not Null, /* One Itinerary can have multiple Reservations */
BookedDate Datetime Not Null,
PickupDate Datetime Not Null,
DropOffDate DateTime Not Null,
PickUpLocationID [uniqueidentifier] Not Null, /* FK */
DropOffLocationID [uniqueidentifier] Not Null, /* FK */
RatePlanID [uniqueidentifier] Not Null, /* FK */
PromotionID [uniqueidentifier] Null, /* FK */
SalesPersonID [uniqueidentifier] Null, /* FK */
SellingCompanyID [uniqueidentifier] not null, /* FK */
AgentID [uniqueidentifier] not null, /* FK */
CustomerID [uniqueidentifier] not null, /* FK */
BookingValue Money not null,
ProductID [uniqueidentifier] not null,
CurrencyCode [nvarchar] 2 not null
..... and many more fields that are just value fields
Primary Clustered Index on [ID]
other Covering Indexs
-- Search for all reservations that belong to an Itinerary
1) Indexed Columns (
[Kind] ,
[ItineraryID]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.
--Search by reservation Number
2) Indexed Columns (
[ReservationNo]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.
-- Search by Customer Last Name &
-- Search by customer last name and Brand
3) Indexed Columns (
[Kind] ,
[CustomerID]
[SellingCompanyID]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.
-- Search by Booked Date or travel Dates or locations
4) Indexed Columns (
[Kind]
[BookedDate]
[PickupDate]
[DropOffDate]
[PickupLocationID]
[DropOffLocationID]
)
INCLUDE ( [Id],
[Version], [ItemValue], [ChildrenValue], [Flags], [ItemQuantityMinimum], [ItemQuantityMaximum], and almost all the fields in the table to avoid Lookups.
and this goes on...
One of the issues is we use Nhibernate to access the database... and I might be wrong but Nhibernate always access the whole object .... i.e select this_.ID, this_.ReservationNo..... (all columns) from Requirement where......
and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...
Questions
1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.
2) Does SQL server use the included columns in a convering index to Search? or is this only a solution for lookups?
Yes I do keep a close eye on dm_db_index_usage_stats to make sure there are no unused indexes.
Guys, thanks again for taking the time to look into this.
Regards,
Ravi
January 4, 2011 at 12:59 am
RaviinBne (1/4/2011)
and this goes on...
If you want help checking for duplicate (redundant) indexes, you'll need to post the whole lot, not a sampling
and due to this I am having to include all columns in the include column list of all indexes to avoid lookups...
Well that's why your index is so large. By doing that you're duplicating the entire table with each index. Including every column is almost never a good idea. Run some traces, see what queries are being run, index accordingly.
Covering indexes are all well and good, but the general though is that you can't cover all queries and you shouldn't try to cover all queries.
Questions
1) would it be better for me to just have the ID as the included column so that SQL server uses the covering index and clustered index to retrieve the data.
If the ID is the cluster, it's in the index anyway, regardless of whether or not you add it.
If your index is covering, SQL won't go to the cluster for additional columns. It's only when the index is not covering that a lookup is necessary.
Maybe the lookups are OK, maybe not. You'll have to test to see.
2) Does SQL server use the included columns in a convering index to Search?
No. Only key columns can be used in seeks and queries can only seek on an index if they're filtering on a left-based subset of the index key.
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
January 4, 2011 at 1:20 am
The key information in your reply is NHibernate and most probably a misuse of it.
Check your app while a trace is running and check the values needed for functionality vs. queried from database.
If there are columns queried that are not needed for a specific app functionality, it's time to call the vendor in and make them fix their code. If you have a poorly written app, index tuning will provide just minor improvement...
January 4, 2011 at 3:55 am
Notice how the leading edge of three of the indexes is the [Kind] column. That starts to look like a better candidate for your clustered index than the primary key (although, as a varchar(256) it's an amazingly bad choice for a clustered index). But that would be the start of trying to solve this dilema. You have the cluster (which Gail explained, never needs a lookup) on the PK, but, at least from what you've shown, the PK is not being used to determine access to the table. That at least suggests you should be looking elsewhere for the PK.
Also, having the same column, [Kind], as the leading edge of all your indexes, will cause the optimizer to not use some indexes that might be better suited to a given query and instead use an index that isn't as well suited. This is because the histogram, part of the statistics of the index, is only made on the first column of the index. It's not the only thing used to determine index usage, but it is a primary one.
Don't even get me started on just how bad nHibernate could be making your queries. Just a hint, unless your developers have specifically coded for it, nHibernate doesn't use the data type size when setting it's parameters, but uses the length of the value passed. So 'Dog' becomes varchar(3). 'Horse' is varchar(5). 'Elephant' is a varchar(8). When each of these is passed to SQL Server, a whole new execution plan is created instead of reusing an existing one, which would have happened if the datatype was set to varchar(125) as it should have been. Oh yes, you have lots of joy coming from nHibernate. I'd suggest looking up the N+1 problem and seeing if you can find evidence of that in your code because, again, it's extremely common unless your developers specifically coded around it.
"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
January 4, 2011 at 4:15 am
Grant Fritchey (1/4/2011)
the PK is not being used to determine access to the table. That at least suggests you should be looking elsewhere for the PK.
Not the PK, the clustered index. Though, from the example of what's in the Kind column, it's a very, very, very bad choice for a cluster.
Could be that some of the nonclustered indexes could benefit from reordering columns. Very hard to tell without doing a complete index analysis (which easily takes a month on a large DB)
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
January 4, 2011 at 4:19 am
GilaMonster (1/4/2011)
Grant Fritchey (1/4/2011)
the PK is not being used to determine access to the table. That at least suggests you should be looking elsewhere for the PK.Not the PK, the clustered index. Though, from the example of what's in the Kind column, it's a very, very, very bad choice for a cluster.
Could be that some of the nonclustered indexes could benefit from reordering columns. Very hard to tell without doing a complete index analysis (which easily takes a month on a large DB)
Oh crud, did I type 'PK'. Yes, of course I meant cluster. Thanks for correcting me on that. Yes, you're right [Kind] is a terrible choice, but based on all the info we have currently, it's possibly a better choice than the PK. Not saying it's at all a good choice, just a less bad choice.
"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
January 4, 2011 at 4:32 am
My concern is the comment that was next to the definition of kind:
/* Kinds: Sale / Purchase */
If it's 2 values (and nvarchar255 to boot), it's going to be near-useless as a clustered index. I agree the cluster may well need to move, just don't know to where...
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
January 4, 2011 at 4:53 am
GilaMonster (1/4/2011)
My concern is the comment that was next to the definition of kind:/* Kinds: Sale / Purchase */
If it's 2 values (and nvarchar255 to boot), it's going to be near-useless as a clustered index. I agree the cluster may well need to move, just don't know to where...
Oh wow! I hadn't noticed that. Again, you're right. This is a poor choice for the cluster... and a poor choice for the leading edge of any of the indexes if that's really the data contained in it.
I'd suggest it also begs the question of where the data type came from, but I already know. That's an artifact of nHibernate. Wonderful piece of software.
"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
January 4, 2011 at 8:33 am
1) GUIDs are a terrible design choice - fat and fragmenty (coined a new term there)!
2) no wonder your updates are getting slow - look at how many include columns you have in those indexes! You cannot have you cake and eat it too - either you accept lookups and stop including so many columns just to get a covered index or you suck up the blocking/locking/performance hit of maintaining so many fat indexes.
3) BTW, did you perchance use Database Tuning Advisor to derive those indexes??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply