• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

MSSQL 2000 performance dropping ('Timeout expired')

Hi.

I have a weird problem with MSSQL 2000. We moved to MSSQL from Sybase. Everything worked fine for about 3 weeks and then it started. The queries took longer and longer to execute until I started to receive 'Timeout expired'. I already reindexed the DB, checked for invalid columns (first I thought that something got mixed up during the conversion), but nothing helped. Then I restarded the entire server and that helped. But the problem returned in about 3 days. I found out, that after restarting the DB service, the problem disapears for 2-3 days, then it returns. I start receiving timeouts (set for 30s) on queries that should take only miliseconds to execute!

Any ideas on this one???
0
BlueAlien
Asked:
BlueAlien
  • 6
  • 3
  • 3
  • +1
3 Solutions
 
MikeWalshCommented:
have you looked for any blocking? SP_WHO2 do you see any processes being blocked? Have you looked at your performance counters like CPU usage/network utilization/IO/Memory/etc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2 issues possible:
* the statistics are not up-to-date on the tables/indexes, ie disabled for auto-refreshing
  -> enable auto-update statistics for the database
* the transaction log file of the database has grown huge
  -> either
          => implement regular full and transaction log backups (db in full recovery mode)
          => or change the db into simple recovery mode
  -> and shrink the database log file back to "normal" size
0
 
BlueAlienAuthor Commented:
To MikeWalsh:
I don't think anything is blocked, but I'll check. The CPU performance is about 50-60% on 4 CPUs during heavy duty queries. How to check for network utilization?

To angelIII:
The transaction log is shrinked, however I didn't checked the statics. Is it posible to enable the auto update statistic for the entire DB or I must do that for every table?

The thing is, that the program executes i.e. 3000 queries with no problem, than something happens and they get executed 3-4 times slower.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it posible to enable the auto update statistic for the entire DB
it's basically a db setting, but it can be overridden at table level
0
 
BlueAlienAuthor Commented:
I checked those statistics and they are ON (I used the sp_autostats command).
0
 
imran_fastCommented:
Try Using Sql Profiler to grab all the queries taking long time and then apply them in Index Tuning Wizard to look for index Recommendation.

For Profiler
In Enterprise Manager --> Tools --> Sql Profiler.
0
 
BlueAlienAuthor Commented:
I tunned up the queries with the profiler (a very handy tool), but that isn't the problem. Now the app runs faster, but it still slows down after a while.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the sql server max memory setting allow all the memory to be used?
if that is the case, reduce that setting to <PHYSICAL RAM> - 300/400MB (depends on how many other applications run on the server box)
0
 
imran_fastCommented:
Give the specification of your server. Can You?
0
 
BlueAlienAuthor Commented:
It's a:
2x 3.2 Xeon CPU
2GB RAM
2x 30 Ultra320 HDD

and it's only for the DB server
0
 
imran_fastCommented:
What is the size of you database?
and how many concurrent user using the system.
0
 
BlueAlienAuthor Commented:
The DB file has about 1GB, the log file was shrinked. There are about 6-7 concurrent users.
0
 
BlueAlienAuthor Commented:
Thanks for your effort.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now