January 28, 2013 at 3:55 am
Good Morning Everyone,
Firstly I hope everybody is having a nice morning 🙂 I have a question that I hope someone can give me some advice on. Our website currently enables users to update information in our database and then they can view the information as a report for one year into the future. This all worked fine until last week when the reports could no longer be viewed and when I did some tests on the server I got a timeout error. I soon found that my query was taking to long and I updated the indexes so all is well... for now :unsure: Does anyone know what the best solution is to keep the query time to a minimum, is the best method to manually update the index on a scheduled basis? say once per month?
As always I really appreciate the help, and I've listed some of my specs below,
Kind Regards,
Craig
-------------
Windows Server 2008 R2 Standard Edition
SQL Server 2008 R2
January 28, 2013 at 4:04 am
Regular index maintenance is a must, you can use the free scripts provided by Ola for a comprehensive intelligent way to do index maintenance. There is a link to the scripts in my signature.
I would recommend doing this daily if you can, or weekly, to keep on top of fragmentation and keep things as prim and proper as you can. Also look at setting a fill factor on the indexes so help keep fragmentation down between the rebuilds.
Also when you find a report running slowly, try and trap the SQL being used along with the parameters, or if it is running try and pull the execution plan from the cache and analyse the access methods. There may be missing indexes, out of date statistics or indexes being fragmented to much between your scheduled rebuilds.
January 28, 2013 at 4:26 am
Hi Anthony,
Thanks for the reply, I'm going to read over Ola's maintenance scripts now, it looks like an interesting read. I hope you have a nice day 🙂
Regards,
Craig
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply