Suddenly Slow Performance

Question

Post reply

Suddenly Slow Performance

danilo.arduini

SSC Journeyman

Points: 92
More actions
April 6, 2008 at 3:08 pm

#120914

Hi all,
it is the first time that i write on this forum and i want to greet all.
I have a problem in a production environment. I'm using SQL Server 2000.
From day to an other one a job lasted only five minutes now one hour and more lasts. When I try to launch a simple query SQL Server process keep all 1.7 GB of memory dedicated and it doesn't leave it until I reboot the service. The sql procedure is the same from six months. :crazy:
It is very strange, I have a very big table(over 150.000.000 rows). From this one i create a little temporary table that contains at max 1.000.000 of rows and i work on this one.
Why SQL Server process grow up until 1.7 Gb of ram and don't release ram this until at reboot?
The problem is from March 29, 2008.
Any help would be greatly appreciated. :crying:
Regards,
Danilo

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Only way to tell is to see the code 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The SQL Engine, by default won't release memory. You can specify the min and max memory settings (if necessary) and SQL will manage its memory within those boundries. The only time it will release memory is if the OS signals a low memory condition.

Sounds like a badly tuned query. Table definitions please, along with any indexes and the query. An idea of the applicable row counts in the tables and resulting output will also be useful

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jvamvas Say Hey Kid Points: 708 More actions · Answer 3

Have you tried a REINDEX ?

http://www.ITjobfeed.com

danilo.arduini SSC Journeyman Points: 92 More actions · Answer 4

Hi All,

thanks for yours fast responses and I'm sorry for the late of the update. The problem is solved by itself. I don't how it was happened, but this week the job lasted only five minutes as before.

Thanks a lot for your help and interest. I only think that there a was a problem on the machine.

Best Regards,

Danilo