Slow Reports - Index Update

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Slow Reports - Index Update

Slow Reports - Index Update

Rate Topic

Display Mode

Topic Options

Author

Message

craig.dixon

Posted Monday, January 28, 2013 3:55 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24, Visits: 91

Good Morning Everyone,

Firstly I hope everybody is having a nice morning Smile

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

Post #1412314

anthony.green

Posted Monday, January 28, 2013 4:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412323

craig.dixon

Posted Monday, January 28, 2013 4:26 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:00 AM
Points: 24, Visits: 91

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 Smile

Regards,
Craig

Post #1412338

« Prev Topic | Next Topic »

Permissions