I would have to say that despite your feelings about the size of the production database, the dba does in fact (I suspect) own it and for what ever reasons has allowed it to grow to the size that it is. Since your code now has to support this then I would feel this is a requirement that you have to negotiate over and I may have to end up supporting. As with all profiling I think you need to get hard and fast numbers as to what is slow and when, and then determine would be a solution. If the solution is the use of particular indexes over others then so be it, but a blanket is it bad or good doesn't make sense to me.
Surely the dba can provide the evidence needed. You too should be providing *evidence *as to why the db shouldn't have that much data in it - you may well be right. Ultimately it may well be down to who has the biggest big brother on their side. preet On 21 June 2010 23:07, Peter Maddin <[email protected]> wrote: > I have no idea what the DBA has done to determine what the issues are. > He is usually not that informative. > Its just do this. > > I believe the database has grown too large. > The system has been working fine until the database grew to past 15 GB. > There is no reason for it to be any near this size. > I said the database needs to be purged of unnecessary records and shrunk > however I am a mere developer. > > > Regards Peter > > On 21/06/2010 6:39 PM, Michael Nemtsev wrote: > > How did you determine that the issue with the indexes? Did you use any > SQL performance monitoring tools that point you to the indexes or it’s just > a guess? > > Hits are usually the last resort when you can’t rewrite the query to have > the right execution plan, or when you query is quite complex and can’t > pickup the right indexes, so you point your query to the right stuff. > > > > *Michael Nemtsev* > > Microsoft MVP > > B: http://msmvps.com/blogs/laflour > > S: http://www.sharepoint-sandbox.com > > > > *From:* [email protected] [ > mailto:[email protected] <[email protected]>] *On > Behalf Of *Maddin, Peter > *Sent:* Monday, 21 June 2010 7:57 PM > *To:* [email protected] > *Subject:* Embedding SQL index hints into SQL commands for SQL SERVER. > > > > I have a DBA that is suggesting that to improve performance I need to embed > optimizer hints in the SQL commands that I use against the database so it > will use specific indexes. > > > > Is this a good idea? > > > > I believe performance problems are related to the size of the database as > my test database (2 GB) has no problems at all and the production database > has grown large and fat with lots of unnecessary records that should have > been purged years ago. > > > > I would have thought that embedding optimizer hints into a SQL statement in > my code increases the level of coupling between my code and the database > which is not a good thing. > > Also it’s the role of the optimizer to determine what indexes (provided > they exist) to use. > > > > Has anyone else done this sort of thing? > > Am I just being stupid (or more so than usual)? > > > > *Regards Peter Maddin* > *Applications Development Officer* > *Path**West Laboratory Medicine WA* > *Phone : +618 9473 3944* > *Fax : +618 9473 3982* > *E-Mail : [email protected]* > *The contents of this e-mail transmission outside of the WAGHS network are > intended solely for the named recipient's), may be confidential, and may be > privileged or otherwise protected from disclosure in the public interest. > The use, reproduction, disclosure or distribution of the contents of this > e-mail transmission by any person other than the named recipient(s) is > prohibited. If you are not a named recipient please notify the sender > immediately.* > > > > -- regards, Preet, Overlooking the Ocean, Auckland
