June 14, 2010 at 5:10 am
Hello,
I have a quick question, I do not think its a simple yes or no answer, but general opinion is what I am after.
Lets say you put in an application that creates a SQL database and indexes, I was wondering whether having run a trace, changing/removing/adding indexes would prevent an application from running correctly. The reason I ask this is because I thought that an application would run what ever query was coded into it (depending on what the end user was doing on the app), and it would still be up to the SQL engine how it retrieved that data, not the application itself. I'm talking about an off the peg/out of the box style of app, not one built or developed in-house. I wonder if off the peg applications create indexes that are not as optimal as they could be, and generally can they be changed.
Sorry if I'm not making total sense. I'd be interested in anybodys experience on such things.
Regards,
D.
June 14, 2010 at 7:14 am
It really depends on the application. Yes, modifying indexes or even adding indexes, depending on the index, could negatively affect an application. Just as you would with your own apps, you simply need to test, test, test, to ensure that adding or modifying indexes don't negatively affect the app. Most apps, rightly or wrongly, will consider you to have violated your agreements if you modify their structure. Usually adding indexes is OK, but you can still run into problems with tech support if you modify what they have in place, no matter how bad. It's something to consider.
"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
June 14, 2010 at 4:21 pm
Duran (6/14/2010)
... I wonder if off the peg applications create indexes that are not as optimal as they could be, and generally can they be changed.
Yes, off the peg applications often create non-optimal indexes. These apps use similar performance checks to SQL and then decide if an index is needed and adds it. But the algorithms aren't perfect, so I've seen databases where a 2GB table has 10GB of indexes.
In general if you fiddle with the database you are breaching your EUL. Also the app may just create the index again.
Also some apps then add the index as an INDEX HINT if they are using dynamic SQL, so if you delete the index you may get a warning that the index is missing. Dropping the index shouldn't break the app, but you don't know what controls the developers have built into the system.
Your best bet it so try contact the vendor and see if they will accept your tuning the db yourself. Offering to send them recommendations may help.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
June 15, 2010 at 2:11 am
Hey,
Thank you for replying to my thread, basically I got the perfect answers.
Thanks guys.
Regards,
D.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply