SQL 2000 Server Slowness

  • Hi, I am relatively new to SQL. I have three databases running on SQL 2000 (8.00.2050 )server. This 3 databases (1 for payroll, 1 for accounts payable and 1 for a Customs system- all of them form different vendors), were working just fine for about 1 Year. About 1 month ago, one of the Databases (Customs), is having some kind of slowness. There is one process this database does, which is to verify some part numbers- This query took about 1 or 2 minutes, nowadays, it takes about 10 to 15 minutes!!!. We did some repairs, check indexes, shrinks, deleted the Log file (which BTW grows big, about 1GB) the MDF is about 2GB.

    Well, the only thing we found out to be effective was to Stop and Start the SQL Service with this commands on a batch file I took from a web site:

    @ECHO OFF

    NET STOP SQLSERVERAGENT

    NET STOP MSSQLSERVER

    NET START MSSQLSERVER

    NET START SQLSERVERAGENT

    When I run this, the query takes again 1 to 2 minutes., but next day, the same thing happens.

    I don't want to run this everytime I have problems, because, there are another users, using the "other" databases, and I guess that can cause troubles somehow.

    Question: What can be causing this behavior?, is there any "log" I can check ?, How should I approach this problem?

    Thanks.

  • Firstly, don't delete your log. You are asking for a suspect database doing that. It will grow when you do index rebuilds.

    It could be a number of things. If you run the process from query analyser how long does it take? Run the process from query analyser with the following settings on

    SET STATISTICS IO ON

    GO

    SET STATISTICS TIME ON

    GO

    and look for the queries that take the longest. If you find one or two that are taking the longest, run then with the execution plan on and see where the high costs are. See if perhaps an index needs changing or the code needs changing. If you want advice on that, post the queries here, along with the table and index definitions.

    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
  • Thanks Gail, I appreciate your help. As I mentioned, my sql expericence is very limited, what I am trying to accomplish here is found out if the problem resides on my server, or , if the problem resides on the Vendor Database.

    As I stated before, we have 3 db's on that server, but, just the Customs application, is the one who's behaving slow.

    I sent the ".mdf" file to the vendor, but he said, the DB has nothing wrong on it, but I am a little bit "concern" about he is not really giving the importance it demands. I cannot kick them out at the moment, because we run some custom-made interfases with his software, and, is not the moment.

    But, what I can't explain, is why the simple fact of reset the sql agent will fix this for some hours.

    - He also mentioned that I must upgrade to sql 2005, to see if that helps.

    or even considering buying a new server to host just this application.

    But how can I ensure this measures, will fix the problem in the first place?

  • light.harper (8/26/2008)


    But, what I can't explain, is why the simple fact of reset the sql agent will fix this for some hours.

    You're not resetting SQL agent. Those commands you listed earlier are restarting the entire SQL Server Service. That means all the cached data and plans are discarded and SQL's starting from scratch.

    I can't say what the issue could be without a lot more investigation. The thing is, a well-designed and well written database does not behave like that.

    - He also mentioned that I must upgrade to sql 2005, to see if that helps.

    or even considering buying a new server to host just this application.

    But how can I ensure this measures, will fix the problem in the first place?

    If it's a performance issue, neither of those will truly fix it. If it's bad code or bad indexes then at best doing one or both of those will slightly allieviate the symptoms, for a while,

    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
  • like Gail said its basically a restart.

    instead of doing that next time try

    checkpoint

    dbcc freeproccache

    dbcc dropcleanbuffers

    checkpoint

    it will have to recompile all the query plans so briefly there might be a performance hit. Let us know if that helps.

    Upgrading to sql2005 and upgrading hardware for just one long running query sounds crazy

  • thanks, I will try, and let you know

  • I would not completely rule out that it could be that your database(s) have outgrown your server hardware. SQL can be a bit of a memory hog if you allow it to be.

    This would seem to fall in line with the fact that right after you restart the SQL service the process runs fine, because right after you restart the service, you have more unallocated memory available. Additionally, as your databases have grown over time, the amount of memory the SQL Service would be consuming would also be growing.

    If you have your 3 databases on seperate instances, you could set the memory allocations for each individual instance.

    Memory is rather inexpensive, so it may be a bit easier and more effective to upgrade the hardware.

  • light.harper (8/28/2008)


    thanks, I will try, and let you know

    I made some testing yesterday:

    I ran a process (verify an invoice) on the Customs Application, before any command or SQL restart.

    1.- Invoice verification took 8 minutes

    2.- I Ran the

    checkpoint

    dbcc freeproccache

    dbcc dropcleanbuffers

    checkpoint

    Invoice verification: 8.15 minutes

    3.- I did the :

    NET STOP SQLSERVERAGENT

    NET STOP MSSQLSERVER

    NET START MSSQLSERVER

    NET START SQLSERVERAGENT

    Invoice verification: 1.30 minutes.

  • Very strange.

    Is the invoice verification using linked servers by any chance?

    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
  • What do you see for activity when you experience the slowness? Any blocking? (sp_who2 - look at the blkby column).

    If you can get the SPID for the process that is running the query that is running slow and then look at the status of that query in the results of sp_who2 as well.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • If you can get the SPID for the process that is running the query that is running slow and then look at the status of that query in the results of sp_who2 as well.

    And get the last wait type as well please.

    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
  • no linked Servers

  • It could simply be a case of "parameter sniffing"... Google it.

    Also, are "auto statisics" enabled or not?

    --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)

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

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