Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow performance Expand / Collapse
Author
Message
Posted Tuesday, July 24, 2012 3:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:23 AM
Points: 37, Visits: 330
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
Post #1334333
Posted Tuesday, August 14, 2012 7:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1344718
Posted Tuesday, August 14, 2012 8:07 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:49 AM
Points: 411, Visits: 1,403
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.




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1344732
Posted Monday, August 20, 2012 2:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:23 AM
Points: 37, Visits: 330
Thanks for the help!
Post #1347029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse