MSSQL 2005 performance issue

  • Greetings,

    A few weeks back we implemented a new back end for our production environment. We used to have 2 RW and 3 RO database servers - but bought new hardware with more power which means one big server.

    Database and logs are placed on quick SSD Disks - using HBA's < -- So Its not a disk problem

    We host websites:

    What we experience is that inserts and reads / requests are getting slower / worse and worse in a few hours. When we restart the server everything is fine. At this point we restart the database 3 times a day!

    Activity Monitor show 781 processes - no one is blocking

    CPU usage is not that high

    Memory is not that high

    Something is stalling?

    We updated the database a few days ago with the following (did nothing)

    Cumulative update package 9 for SQL Server 2005 Service Pack 2

    http://support.microsoft.com/kb/953752/en-us

    We have had a consultant to try to figure out what could cause it.

    He deactivated:

    Parallel Query

    OUR SPECS:

    SQL Server 9.0.3282

    Microsoft SQL Server Management Studio9.00.3042.00

    Microsoft Analysis Services Client Tools 2005.090.3042.00

    Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

    Microsoft MSXML 2.6 3.0 6.0

    Microsoft Internet Explorer 6.0.3790.3959

    Microsoft .NET Framework 2.0.50727.1433

    Operating System 5.2.3790

    SQL Server 9.0.3282

    ProductMicrosoft SQL Server Standard Edition (64-bit)

    OS Microsoft Windows NT 5.2 (3790)

    Platform NT AMD64

    Version9.00.3282.00

    Memory32765 (MB)

    Processors 16

    Is Clusteret False

    Any help would be greatly appreciated 🙂

    Is there a way to contact Microsoft and get help? I mean we are using MSSQL 2005 STD Edition.

  • Do you have any SQL Server Agent jobs that are failing? I've found that, depending on what they do, they can leave open connections that hold on to resources. Try stopping the SQL Server Agent and restarting it without bouncing the server. If the performance improves, that's the problem.

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

  • Have you limited the server's max memory? If not it would be a very good idea to do that. Set the max memory to around 29GB.

    Also have a look at this blog post and see if perhaps you're encountered the issue it describes:

    http://sqlinthewild.co.za/index.php/2007/10/21/memory-and-sql-2005-sp2/

    Please monitor the following for a few hours (from one bounce of the server to another is good)

    physical disks:avg sec\read

    physical disks:avg sec\write

    physical disks:%idle time

    processor: %user time

    processor: %privileged time

    memory: available MB

    SQL Server buffer manager: checkpoint pages/sec

    SQL Server buffer manager: lazywrites/sec

    SQL Server buffer manager: buffer cache hit ratio

    SQL Server buffer manager: page life expectency

    SQL Server memory manager: Total server memory

    SQL Server memory manager: target server memory.

    Also, can you run the following query about every hour. The values are cumulative since the server starts, so it's the change that's important more than the values

    select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime

    from sys.dm_os_wait_stats

    where waiting_tasks_count>0 and wait_type not like 'Sleep%'

    order by wait_time_ms desc

    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
  • Hi Jeff Moden

    Well havent thought about that - I will give it a try next time.

    Regarding the memory - the limit is 29G at this moment. The MSSQL never uses more than 18G at any time.

    GilaMonster

    Ill try that tommorow.

    Greatly appreciated guys!

  • Perhaps a silly question, but have you rebooted? i've seen that clear up some bad query plans.

    Do you have any reference execution plans to examine the slow queries and see where things might be going wrong or changing?

  • Hi Steve Jones,

    Yes we did try reboot without any luck.

    I tried to analyse where the time went using Nexus - but is seems everything looks good. Ill upload a few screenshot so you guys can look tomorrow.

  • If you're able to upload the permon stuff, please not as a screenshot. There's not enough info in a screen shot. Zip and upload whatever you saved (if possible)

    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
  • Hello again,

    I have uploaded some performance data to a ftp:

    @REM To register the collector as a service, open a command prompt, change to this

    @REM directory, and run:

    @REM

    @REM SQLDIAG /R /I "%cd%\SQLDiagPerfStats_Trace.XML" /O "%cd%\SQLDiagOutput" /P

    @REM

    @REM You can then start collection by running "SQLDIAG START" from Start->Run, and

    @REM stop collection by running "SQLDIAG STOP".

    START SQLDIAG /I "%cd%\SQLDiagPerfStats_Trace.XML" /O "%cd%\SQLDiagOutput" /P

    FTP Hostname: skjelmose.lir.dk

    User: mssql

    passwd: mssql

    Hope its usable - otherwise please direct me to the proper way to obtain debug information.

  • GilaMonster

    select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime

    from sys.dm_os_wait_stats

    where waiting_tasks_count>0 and wait_type not like 'Sleep%'

    order by wait_time_ms desc

    This is my output:

  • iwantroot (9/29/2008)


    GilaMonster

    select top 20 *, cast(wait_time_ms as float)/waiting_tasks_count as Avg_WaitTime

    from sys.dm_os_wait_stats

    where waiting_tasks_count>0 and wait_type not like 'Sleep%'

    order by wait_time_ms desc

    This is my output:

    Was that while the server was very slow?

    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
  • Yes it was - but we did not hit the point where the speed was terrible low. Perhaps because of server agent restart earlier this morning.

  • I have another one from this morning

  • Ouch. Your IO performance looks terrible. Average of 44 sec for an async IO completion with a max of 22 minutes is way, way too high.

    I can't download the perfmon data. It's just too big. What did the physical disk counters look like? Min, max and avg?

    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
  • GilaMonster (9/30/2008)


    Ouch. Your IO performance looks terrible. Average of 44 sec for an async IO completion with a max of 22 minutes is way, way too high.

    I can't download the perfmon data. It's just too big. What did the physical disk counters look like? Min, max and avg?

    Hi Gila,

    Ive uploaded a new perfmon (only 10 Mb i size) im currently trying to import it in nexus. It would be great if you could take a look sometime.

    Greatly appreciated.

    Daniel

  • IwantRoot,

    Did you ever discover the culprit for this issue?

    I am having a similar issue with a 2005 sp3 box.

    The performance starts to hang on a fairly common screen query.

    I have found the stalling sql.

    The data is being hit by a 3rd party application that I cannot change the SQL. The query is no thing of beauty but has an operating cost < 7 (execution plan).

    Killing the query returns the database performance to nominal. Then when the user returns to the query in question it will not complete. MS wait in activity monitor 242###...

    After sqlserver restart the performance is again acceptable.

    thanks

    Daryl

Viewing 15 posts - 1 through 14 (of 14 total)

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