Poor Performance Temporarily Cured by Re-booting SQL Server

Poor Performance Temporarily Cured by Re-booting SQL Server

Please can you help?

SQL Server 2000.

Accessed by VB.Net 2003.

Server set up by SQL consultant (who is no longer available).

We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small.However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).

The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes).In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes.Obviously though we wish to solve the problem and not just the symptom.

Additional information:

Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).

Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup.Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.

While the server running very slow, what symptoms are you seeing (at the server level)? Does the server seem like it is under CPU pressure?

What kind of hardware is it running on? Instead of simply rebooting the server, you should try running the command DBCC FREEPROCCACHE from Query Analyzer before you reboot it to see if that has an effect.

We need some more details to start to narrow down the problem.

|||

What are you using to create the extracts, for instance DTS, Reporting Services etc...

Also, do you have more than one instance of SQL Server on the machine?

Chris

|||

The extractions are created as CSV files using a VB.Net program. We only have one instance of SQL server running on the server but it contains more than one database. The system that has the problem is the most heavily used and it is only the users of this particular database who report a problem.

The problem recently got worse - in that it was happening more frequently - this may have been caused by one developer running a large number of test reports. I have examined many postings on the subject and although they did not all agree with each other, I have made several changes to the settings that the SQL consultant had set when the server was set-up.

I have: stopped the automatic shrinking of the databases, increased the size the databases grow by and increased the % of free space when the indexes are reorganized. All parts of the maintenance plan apart from transaction log back-ups already happened over-night when the database was not in use.

Making these changes does appear to have made an improvement but as the occurance of the problem was eratic it is too early to say for sure.

|||

How much RAM does your server have and how much physical memory does SQL Server use when the extracts are being run?

When you first start an instance of SQL Server then the memory consumed is usually minimal (unless explicitly set otherwise). Data read from disk is cached - SQL Server will continue to cache data and will, therefore, use more and more memory as time goes on. I'm just wondering if SQL Server is exceeding the amount of physical memoery available and, therefore, making use of Virtual Memory when running the queries, causing them to run slowly.

If this is the problem then there is a 'Max Server Memory' option that you can use to limit the maximum amount of memory used by SQL Server to an amount appropriate for your hardware.

Chris

|||How are things going with this problem? We have a similar problem that I think I have traced to the use of an inappropriate execution plan. You can use the profiler to capture traces and see if, say, reads go up. It will tell you what statement is being executed. If this is on a table that gets lots of updates, then re-use of an inappropriate execution plan might be at fault. Restarting SQL server is pretty drastic and does not go to the root cause. If you are running stored procedures, perhaps a recompile of a specific procedure may speed things up. That is what we do, although we have not yet found a way to fully prevent the slowdown.