Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    10

    Red face Unanswered: Ways to determine intermittent slowdowns

    For some reason my SQL Server slows down throughout the day for no apparent reason..

    The database is 100 gig transaction replicated to 2 data warehouses.

    Ive done the usual stuff like monitoring processes killing off blocking transactions but it doesn't seem to do the trick.

    There are no jobs running at the point of slow-down so at this current time I am a little lost.

    Is there some way to determine why these slow-downs are happening ?

    The system becomes completely un-responsive during these periods !!
    HELP !!

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Does your server have a public IP using port 1433?

    There are some worms out there that look for servers listening on port 1433, then will try several thousand passwords using the sa account. The server slows to a crawl because it takes resources to deny all of these logins. I have seen this twice, and your symptoms sound similar.

    If you want to log failed login attempts, right-click your server in Enterprise Manager. Choose the security tab. Under audit level, choose failure. Restart server.

    Bill
    Last edited by cascred; 10-03-05 at 12:35.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    got any experience with profiler and performance monitor?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Apr 2004
    Posts
    10

    Yo

    Don't think its a worm...

    Ive tired using performance monitor but cannot make head nor tail of which counters I need to add..

    SQL Profiler doesn't throw up anything obvious.. There are up to 700 concurrent users on the system and its difficult to use tracing.

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You need to look at http://www.sql-server-performance.com and start reading all the articles on performance tuning. If you aren't using Performance Monitor and Profiler because you can't figure them out or it's too difficult, you can't tune the system effectively. Killing transactions that are blocking doesn't constitute tuning a system. How long is it taking those transactions to rollback? What were they doing? Why were they blocking in the first place?

    A good book to buy would be the performance tuning technical reference for SQL Server by Microsoft Press.

    On Profiler, run the actual profiler application on another server or PC. Trace anything over 10000 reads. Have another to trace anything over 10000 writes. Have another to trace CPU over 5000. Have another to trace long running over whatever. You can then tune these until you're getting results that are actually meaningful and will let you tune some of the worst areas of your system.

    Look at the tools PSS provides on the microsoft sites also. They have tools that can help a lot when tuning a system.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •