Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Indexes for Booking Search Expand / Collapse
Author
Message
Posted Friday, December 31, 2010 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 4, 2013 8:54 PM
Points: 2, Visits: 28
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
Post #1041506
Posted Saturday, January 1, 2011 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
You might want to read this 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.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1041521
Posted Monday, January 3, 2011 6:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:59 PM
Points: 15,735, Visits: 28,141
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1041822
Posted Monday, January 3, 2011 6:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1041824
Posted Monday, January 3, 2011 6:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1041827
Posted Monday, January 3, 2011 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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. Link added. Thanx Gail. (It's the Disk Is Cheap! editorial link btw...)




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1041830
Posted Tuesday, January 4, 2011 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 4, 2013 8:54 PM
Points: 2, Visits: 28
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
Post #1042204
Posted Tuesday, January 4, 2011 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1042219
Posted Tuesday, January 4, 2011 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
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...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1042224
Posted Tuesday, January 4, 2011 3:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:59 PM
Points: 15,735, Visits: 28,141
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1042274
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse