I have a problem in SQL2005 so after several days of work without problems, suddenly takes a very slow (sqlserv.exe works much CPU in task manager) and checking the situation I realize that the database is growing and simultaneously a specific queue of messages is not empty anymore but only grows.
In the past I had a similar problem but the cause was not explicitly a ROUTE for each queue (note that the RECEIVE command has RETENTION OFF).
I think that the causes of the current problem could be two:
Hypothesis 1: Failure to empty the queue causes the increase of database size.
Hypothesis 2: the database is in normal expansion and this causes the slowdown with the consequence of permanent posts in the queue.
I state that I use several queues (33 for different events) and that the problem is always on the same queue (which is the most traffic).
Maybe I could work on some of these fronts:
a) Increase TIMEOUT (currently 500 for all queues) in the RECEIVE command.
b) Increase MAX_QUEUE_READERS (currently 2, the number of cores in PC, for all queues)
I could act in the creation of the database specifying appropriate options SIZE and FILEGROWTH.
a) It may make sense to immediately define SIZE as the size that is thought to have the database (eg. in a year)?
b) It may make sense to give a SIZE of the log file as half of that applied to the database? Or to avoid misunderstandings reserve the same size for both?
Note that my procedures do not use statement BEGIN TRANSACTION / COMMIT TRANSACTION.
c) For performance it is better to set the FILEGROWTH in MB or percent? (On internet I read mixed reviews and I'm doing some tests, I think it would make more sense to increase the percentage even though I often read to the contrary).
Thanks to those who can give me some help
1a: Increasing timeout won't do anything good or bad: this only influences how long the receive command will sit idle when waiting for a message to arrive before returning 0 rows.
1b: this depends on the actions that need to take place in your handler routine. If the task to perform is processor intensive it may be worth setting a higher number of queue readers, but don't set it too high. I've more than once found that setting it higher than the number of cpu's can be counter productive. But it depends on all sorts of factors what works best for your situation, so test it.
2: preallocating disk space is never a bad thing. It gives you control over how your disks are fragmented, how the db files are segmented and many more things. But most of the time concerns like these affect only processes that do mass loads into the database like for example a data warehouse ETL process. Most other processes will only sporadically be hindered by database growth. Have a look at the SQL server performance counters if you want to verify if this is the case for you.
The most likely case however for your performance issues is in what I described in my previous message: not properly ending conversations. As a result service broker is not able to do its cleaning up and it's internal tables fill up with all changes since the system was first started.
Posting Data Etiquette - Jeff ModenPosting Performance Based Questions - Gail ShawHidden RBAR - Jeff ModenCross Tabs and Pivots - Jeff ModenCatch-all queries - Gail Shaw
If you don't have time to do it right, when will you have time to do it over?