SQL server performance slowing down

[jkrcjrkc]

The problem is, that performmance is slowing down. An example sql takes 10 seconds right after starting the sql-server and after 12 hours the same sql takes 3½ minutes and it remains stable on that level forever. If I stop and start the sql-server, performance goes up - 10 seconds for the sql again. I test with no other users on and I've tried both before an after an reorganization of the db with the same result. Indexes dosn't matter either - same result. Log- and db-files are on separated discs. The server runs COM+ and SQL-server. R/W to SQL-server is only through COM+

 

[mrdenny]

Some questions to better understand your situation.

How much memory do you have? How much reading and writing do you have going on? How many drives for data and how many for logs, what kind of config.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jkrcjrkc]

I have 1GB, In this process I only read and th eresulting set is about 1 Mb from tables with 50Mb. I have 1 drive for programs, dll's etc., 1 for data and 1 for log. Config?

 

[mrdenny]

Any raid config that you might have for the database and log files.

When the server starts taking 3 1/2 minutes to run your query, how much memory is in use?
What's your max server memory (MB) setting set to? (run sp_configure to find it)
What's your recovery interval (min) set to?
Is there any blocking going on when it takes a long time?
You might want to check your disk queuing in perfman, as well as your bytes written and bytes read while it's taking a long time to run.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[MadridEspana]

Did you check your tempdb and templog files?
I used to have these files size setup too small and when users would run queries or Crystal reports it would grow bigger than the original size and that expanding every time (because it only grows at certain size blocks) used to slow down the whole server. Once I increased their size to what their biggest size was usually, they don't grow bigger than the assigned size anymore and the server does not slow down anymore.

 

[TonyKhela]

I've come across something similar.

My issue was resolved by lowering the 'minimum Query Plan threshold for considering queries for parallel execution' to 1 (default is 5).

The setting is found in Ent Mgr, Server properties, Processor tab.

If you have multiple CPUs, then this may help.

Worth a try. Let us know if it works.

TK