Query Performing slow when connections increase

Question

Post reply

Query Performing slow when connections increase

VastSQL

SSC-Dedicated

Points: 36097
More actions
September 23, 2011 at 5:22 am

#248586

Hi Experts,
One of the query fetching some 30k records and doing an update is taking some seconds in the morning and the same query taking hours to complete in the afternoon. No blocking observed,no other jobs running on that server. The user connections have increase but the concurrent connection value is 0.
Please help
TIA

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

First guess => http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column

If that doesn't help, post the actual execution plan. Ideally the one when it's good and the bad one too.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks for the Reply ninja.

Its teh same query executing morning and afternoon

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ratheesh.K.Nair (9/23/2011)
Thanks for the Reply ninja.
Its teh same query executing morning and afternoon

Please post both. The slow & fast version.

There's important info in those.

Netic Old Hand Points: 362 More actions · Answer 4

Are you aware of "huge" data modifications? If they exists, update statistics should be run 🙂

Regards,

Paulo Condeça.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Netic (9/23/2011)
Are you aware of "huge" data modifications? If they exists, update statistics should be run 🙂
Regards,
Paulo Condeça.

The plan would answer that question ;-).

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

But if thats the case tehn it should not run fast next day morning right??

It is like everyday morning the query executes in seconds and in afternoon its taking hours..Actually its an SSIS package we executed the scripts under the same in query windows and the result was same.

Netic Old Hand Points: 362 More actions · Answer 7

The size of the "touched" tables aren't the same during morning and night right ? 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Ratheesh.K.Nair (9/23/2011)
But if thats the case tehn it should not run fast next day morning right??
It is like everyday morning the query executes in seconds and in afternoon its taking hours..Actually its an SSIS package we executed the scripts under the same in query windows and the result was same.

Did you read the article I linked to? This sounds like a classic case of that.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Netic (9/23/2011)
The size of the "touched" tables aren't the same during morning and night right ? 🙂

Maybe not but right now I'm assuming constant growth with varying speed, but without dumping of the data. Maybe I'm wrong here.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 10

Netic (9/23/2011)
The size of the "touched" tables aren't the same during morning and night right ? 🙂

its the same table,same number or rows,same data..

Netic Old Hand Points: 362 More actions · Answer 11

Ninja's_RGR'us (9/23/2011)
Netic (9/23/2011)
The size of the "touched" tables aren't the same during morning and night right ? 🙂
Maybe not but right now I'm assuming constant growth with varying speed, but without dumping of the data. Maybe I'm wrong here.

😉

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us (9/23/2011)
Ratheesh.K.Nair (9/23/2011)
But if thats the case tehn it should not run fast next day morning right??
It is like everyday morning the query executes in seconds and in afternoon its taking hours..Actually its an SSIS package we executed the scripts under the same in query windows and the result was same.
Did you read the article I linked to? This sounds like a classic case of that.