October 15, 2007 at 8:11 am
Hello there,
I am currently trying to tune a 3rd party system running on SQL2005 and came across something that I was not too sure about so I thought; ask the best people to see what their opinion is.
I have a table that looks like this:
ErrorAssignID (Primarykey) Int,
ErrorId int,
OrderID int,
OrderPosID int,
ItemPosID int,
ErrorType int,
Update Timestamp,
Text varchar(4000),
Released tinyint,
MailingID int
I have identified a select statement that would be very happy to have an additional index.
Before creating the index I let SQL Server provide some suggestions by looking at the "Missing Index" list and especially for this table. The missing index list said to create three indexes:
Index 1 - MailingID
Index 2 - MailingID include (ErrorID)
Index 3 - ErrorID
This list suprised me. Why should I create 3 indexes when one would suffice?
My real question is: Which is the best approach?
Index 2 from the list above - create index INDEXNAME on TABLE (MailingID) include (ErrorID)
Or my initial planned index - create index INDEXNAME on TABLE (MailingID,ErrorID)
Are includes slower than non-include indexes? My guess is yes, but it's not really noticable. What are your opinions?
Thanks a lot
GermanDBA
Regards,
WilliamD
October 15, 2007 at 8:44 am
without seeing the queries it's difficult to be precise. I don't generally use includes in index creation. all suggested indexes are different it really depends upon the queries I'm afraid. your choice will not match all three suggestions, only 2.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 15, 2007 at 8:52 am
Hi Colin,
the query grabs the MailingID and the ErrorID, I was really only using that as an example to try and find out if an index with both fields is quicker/better than an index on the first field with an includes option.
Regards,
WilliamD
October 15, 2007 at 9:13 am
There is a difference between simply including a column and adding it to the index. You add a column to the index because it is part of sorts, searches, joins, whatever is needed to make the index perform better. You add a column to the include list because you want to avoid the added overhead of a key lookup to cluster to retrieve that column.
In your case, is there a search or join or something? Which fields satisify that search? If it's both, they should both be in the index. If it's only the first, not the second, but the second is returned by the query, add the second field to the include list.
But, most importantly, remember to test, test, test before you put it into production.
"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
October 16, 2007 at 12:35 am
Hi Grant,
it is a search, so I was right in thinking that the index should be made up of both fields. I will obviously test this before putting it into the live system.
This is something that the 3rd party software company hasn't done enough of, they are java developers and made a database model based on the java classes - it created some interesting results! Their idea of performance tuning is to throw more hardware at the problem, their design is perfect anyway 😉
Regards,
WilliamD
October 16, 2007 at 6:14 am
You've got perfect programmers too? Wow. Small world.
Seriously though, sounds like you're on the right track.
"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
October 16, 2007 at 6:42 am
there are three basic areas where you want to create indexes.
1. the where clause - creating an index which exactly matches the where clause will give a bookmark lookup - you generally want to get the most selective column in your where to be the first column of the index - but not always.
2. the join clause - columns in joins should be indexed - if the table has more than 1 column joined you may need one index with all the columns or a combination - sadly there's no real hard and fast rules on this one and you have to test ( think 8 and 12 table joins here )
3. the select clause - if the dataset is small compared to the entire table then an index may help as it moves the query off the table.
that's a very simplified view and you'd probably want to combine 1 and 2 at least. A covered index is 1+2+3. For a simple query it's pretty easy but for more complex queries with lots of joins it can take some working out.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 18, 2007 at 2:16 pm
GermanDBA, you mention that this is a '3rd party' system By this I assume it is a software package purchased from a vendor. Hopefully this purchase incudes some type of software support. While it is laudable and very often necessary to 'tune' vendor software I would recommend against it for a few reasons. First addition of extra objects to your vendor's schema (indexes in this case possibly) may void your software support agreement. Second if you add additional objects (indexes in this case possibly) this too mat void your software support agreement. Third, this will add an additional level of complexity to the process of upgrading the vendors schema and software.
However you do have a couple of options. The first I would do is make sure that any database optimization jobs/tasks supplied by the vendor have been implemented. If they have not provided any, then you need to create a maintenance plan or plans to update statistics and maintain your indexes, possible even rebuilding them. There are lots of discussions on this site pertaining to this. The second thing I would exercise is contacting the vendor and informing them that you are experiencing performance issues with their product. It is quite possible that her is a 'fix' available for your situation.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 19, 2007 at 12:32 am
Hi Rudy,
the software is a CRM system and we are the thrid customer for the software company. They are Java Developers with little to no SQL knowledge. We work together with them to improve the performance of the system.
We started a Datawarehouse project and have learned the Database Model of the CRM System, and have actively searched for performance gains on the system because our Support Agreement allows for it and the users demand it.
The company will program some extras for us and produce superb GUI solutions, but often forgets to think of the database concequences. We end up debugging slow/baldy performing sections of the software by analysing the sps/views that are used and suggest some things like indexes or script changes to speed everything up.
The way they work is securing my job all the time 😉
Regards,
WilliamD
October 19, 2007 at 12:54 pm
It sounds as if you are on the 'bleeding edge' ...
Not a fun place to be at times but very challenging and rewarding ...
Best of luck !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 22, 2007 at 12:59 am
Tell me about it..... we etach them stuff about SQL, even though we supposedly don't have the experience!!!
Ahh well, it's a great learning experience for us (and hopefully for them!)
Regards,
WilliamD
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply