July 24, 2012 at 3:26 am
Hi,
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.
Hypothesis 1:
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)
Hypothesis 2:
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
August 14, 2012 at 7:44 am
Have a look at the state of the conversations in your system(s). Are they properly closed by a call to end conversation on both ends? A good starting point is sys.conversation_endpoints. Check the state column that they do not stay in any other states than 'CD'. Normally the conversations state should go from 'CO' into 'DI' or 'DO' and then to 'CD' and then disappear half an hour after that. Depending on the needs of your functionality you can have anywhere from a few to a lot of conversations in the "Conversing" state 'CO', but the total number of conversations should never go into the millions: SSSB can not properly deal with that sort of numbers. Do you have a lot of conversations in any state other than 'CD', or conversations that stay in 'CD' for more than half an hour you've probably got a broken handler implementation.
Also, I've found that contrary to my feeling it sometimes helps to reduce the number of queue readers to improve message processing throughput.
August 14, 2012 at 8:07 am
gdraghetti (7/24/2012)
Hi,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.
Hypothesis 1:
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)
Hypothesis 2:
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.
August 20, 2012 at 2:50 am
Thanks for the help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply