Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server Needs constant sp_UpdateStats to Run Fast

Posted on 2009-05-13
2
1,102 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:Declan_Basile
2 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 260 total points
ID: 24378897
sp_updatestats updates statistics which are indexes in fact but they are static (not updated when data are updated), so you should create additional indexes based on info from these staistics and the query speed should remain moreless unchanged all the time.
0
 
LVL 8

Assisted Solution

by:ludo_friend
ludo_friend earned 240 total points
ID: 24381668
on a 2.5gb db, no query should really take that long. I'd say your issue here is to do with indexing more then statistics.  
if you have a full sql 2005 server available, I'd get a copy of the db put on the full sql server, a single client connected to it, use Database Engine Tuning Advisor to recommend changes which you can assess and apply if need be.  the database engine tuning advisor does NOT run on sql express.
I'd also check our your servers physical ram, how much sql is using and how much is left. I've found a bit of extra am goes a long way performance-wise.

hope the above helps
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question