Hi Peter, Databases that only have a relatively small amount of data and are unusually large are often caused by having transaction logs that have blown out in size. The usual way to reduce the size of the transaction log is simply to back it up properly (depending on the recovery model).
As for using index hints, I usually don't need them, because with a simple re-arrangement of the query, it will select the correct index anyway (in most cases). This usually involves reorganising the where clause / joins so the order of columns matches the order of the index. I recently blogged on ways that I was able to successfully improve query performance at one of my clients. May or may not help, I don't know, but it might give you some ideas: http://tonesdotnetblog.wordpress.com/2010/06/19/tips-for-optimizing-sql-server-2008-queries/ and I have an older article with different set of tips for 2005 which may be relevant too: http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005- queries/ T. On Mon, Jun 21st, 2010 at 9:07 PM, 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]] *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] > > <mailto:[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.* > > >
