Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

SQL Server Needs constant sp_UpdateStats to Run Fast

I'm running SQL Server Express 2005 with a 2.5 GB data file and Access 2003 programs are used as an interface to the data.  I've upsized to SQL Server over a year ago, and periodically, a couple of Access Forms (ones that run a particular stored procedure) would slow down, and running "sp_updatestats" on the database would always speed those forms back up.  Last week, however, all of the forms in all of the Access databases were really slow.  What usually takes 40 seconds to process was taking 1 minute and 40 seconds.  So for the first time in over a year I reindexed the database with the following script given to me by a friend ...

EXECUTE master.dbo.xp_sqlmaint N'-D Altek -Rpt "C:\sql\results\Sunday_Maint_DBCC.txt" -DelTxtRpt 4WEEKS -WriteHistory -CkDB -CkAl -CkTxtAl -CkCat'

EXECUTE master.dbo.xp_sqlmaint N'-D Altek -Rpt "C:\sql\results\Sunday_APP_Maint_Rebuild_Indexes.txt" -DelTxtRpt 4WEEKS -WriteHistory  -RebldIdx 100'

DBCC UPDATEUSAGE (Altek) with no_infomsgs

......

I ran this on Sunday night.  It seemed to have sped the programs up.  However, again, on Monday, I had to run sp_updatestats to speed up 2 of the Access forms when they slowed down and it worked.  Now today, like last week, all of the forms in all of the applications slowed down to a crawl again like last week.  I ran sp_updatestats and it seemed to speed the programs up, however, I need to find a different solution than to keep running sp_updatestats every time the programs slow down.  Also, we've been using the programs with SQL Server for over a year and never has the system slowed down as much as last week and today.  What can I do to prevent the programs from slowing down so much?
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial