February 17, 2003 at 8:05 am
Does anyone have any theories about Optimisations & Integrity checks ?
Should these always be done before a backup?
We have a problem. Over the weekend, we see a dramatic drop in available memory (which is not recovered from). This continues to drop and by the time Monday morning comes we have very little server memory available.
Over the weekend we do the weekly Integrity Checks/ Optimisations, & weekly reports (as well as the daily backups).
So if anyone has any theories on our memory loss or whether optimisations & Integrity checks should be done before a backup, I would love to hear from you.
Thanks
February 17, 2003 at 9:02 am
I typically do the optimizations after a backup. Better to have a backup and have to reindex than have something go really wrong during reindex and have no backup. I dont sweat the integrity check, pretty rare day for it to find anything. Memory loss how? It's normal for SQL to acquire all the RAM allocated, makes for better performance.
Andy
February 17, 2003 at 9:12 am
I would expect to see gradual memory loss during the day but to jump from 331Mb free at 23:59 to 144 Mb free at 00:00 (we have perfmon running all the time at 1 minute intervals) - is a bit dramatic !
Users are reporting server running slow and memory is just one of the things we are investigating.
Its always worse on a Monday too..
February 17, 2003 at 10:15 am
Interesting. Is something starting at 00:00? Either SQL or non SQL related?
I run the checks once a week. Rarely see errors, so gotten a little lax in worrying about them.
Steve Jones
February 17, 2003 at 10:23 am
Yes - one of the integrity checks
February 20, 2003 at 9:22 am
The optimization and integrity checks will cause your database to slow down. The jump in the memory utilization you are talking about is normal from what I have seen. Check and see how long your optimization jobs are running. If there is a problem in the database it may still be running on Monday morning. I have seen them run for hours. Also you should not have any users in the database when running optimization it can cause erroneous error in your reports.
February 20, 2003 at 9:23 am
The optimization and integrity checks will cause your database to slow down. The jump in the memory utilization you are talking about is normal from what I have seen. Check and see how long your optimization jobs are running. If there is a problem in the database it may still be running on Monday morning. I have seen them run for hours. Also you should not have any users in the database when running optimization it can cause erroneous error in your reports.
February 20, 2003 at 9:25 am
The Optimisation takes around 3 mins, and the integrity check less than 10 seconds.
What I am worried about is the continuing drop in memory. SQL is not recovering
February 20, 2003 at 9:39 am
The memory I would not worry about unless it is more than you have allocted to SQL. What version of SQL are you running? I have had a simialer problem in weekend drop of performance do to stats. I had to turn off the auto stats and rebuild them with a script. Later I was able to turn auto stats back on. It sounds like your database is small so I would say just check and make sure autostats are turned on. I have never seen SQL release memory once it has taken it, but most of my databases are large and they quickly take all the memory I give them.
February 20, 2003 at 9:57 am
We are running SQL 2000 sp3. Memory must be recovered some how, as when I come in on a Tuesday morning the memory is back to 300Mb + available.
February 27, 2003 at 8:35 am
I have run into this same issue on both 7.0 and 2KEE. After a bit of research we discovered SQL Server was re-organizing its allocated memory so that it could free up contiguous space for other work. MS told us that this is normal behavior for systems that have memory stressed. It generally takes less than three sec. to re-org but if a user was unfortunate enough to issue a query at the time they would receive an ODBC 701 error. The solution.....as always....More memory. There are a number of other memory metrics that can help nail this down. Let me know if you need more.
February 27, 2003 at 9:09 am
thanks for that.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply