Saturday, February 25, 2012

MSSQL Slowing Down until unusable.

Over time, MSSQL seems to get slower and slower until we cannot even log into the db list using Enterprise Manager.
We are using:
Win2k Pro SP3
2 @. 1ghz Pentium processors
2 gig RAM
143 GB Disk space 3 hot swap Raid 5

It seems in time the speed of the server slows until it almost becomes unusable. We run Optimization and Integrity checks without errors once a week. There are about 300 databases on the server, the largest being about 1.5 gig. Full backup every night.
Our web applications are used during the day (bulk insert is used alot by the web application) so I logged in to the server at about 3am to find the high memory use and Page Faults at over 1.5 million. This is while the server is getting no queries. I watched as the page faults just continually increased at about 100 to 200 per second.
We eventually have to move to the backup server, re-build the server and it will be good for a few months. Any suggestions or ideas?Originally posted by tpmcmaho
Over time, MSSQL seems to get slower and slower until we cannot even log into the db list using Enterprise Manager.
We are using:
Win2k Pro SP3
2 @. 1ghz Pentium processors
2 gig RAM
143 GB Disk space 3 hot swap Raid 5

It seems in time the speed of the server slows until it almost becomes unusable. We run Optimization and Integrity checks without errors once a week. There are about 300 databases on the server, the largest being about 1.5 gig. Full backup every night.
Our web applications are used during the day (bulk insert is used alot by the web application) so I logged in to the server at about 3am to find the high memory use and Page Faults at over 1.5 million. This is while the server is getting no queries. I watched as the page faults just continually increased at about 100 to 200 per second.
We eventually have to move to the backup server, re-build the server and it will be good for a few months. Any suggestions or ideas?

did you dump transaction log after Full Backup?|||Thanks for the reply...

No, I did not dump the transaction log files on the databases.

DB maint. is from the standard wizard:
-Reorganize data and index pages
-Optimize w/remove unused space from database files
-Check Integrity w/include indexes, attempt repair

Data and Trans. files are set to grow automatically by 10% unrestricted.
Options inlcude Auto Shrink.

Thank you!|||As far as autoshrink option is set to true it may hamper ur speed b'coz as soon as it gets shrunk again it starts increase to its default size which is required for the database for its manipulation.
In brief again it consumes ur resources .
This overhead u can avoid by autoshrink to manually when there is very less workload on sql server like (break).
2nd option could be problem with indexes which are clustured do u check showcntig reports scan density page density etc if not pl go through it it may give u some improvements in performance after running indexdefrag followed by index name.
there are some more options which i can tell u may be after getting -ve some reply from u .

No comments:

Post a Comment