SQL Server performing slow responce (SQL 2000,2005)


  • Recently application team raised case against us since last week SQL Server performing very slow....they confirmed no changes happened in last month.

    we checked at SQL level...

    1. No blocking.

    2. taken some query thru sys.dm_exec_sql_stats (order by total_worker_time desc) and sys.dm_exe_query_text dmvs and get the fragmentation details plus statistics but everything was OK.

    3. used DTA but did not get solution.

    4. added two more data files now total 4 data files (4 core processors) but still same slow issue.


    can some one send me query 1) that will find out the memory use by SQL Query and SQL jobs . 2) how do we check need to upgrade SQL Memory.

    1. what is impact of full\log backup in performance ?

    2. can we do point in recovery in Bulk logged recovery if we run BCP\Index operation ?


    15 เมษายน 2555 12:42



  • 1.Analyze waits at the instance level.
    2.Correlate waits with queues.
    3.Determine a course of action.
    4.Drill down to the database/file level.
    5.Drill down to the process level.
    6.Tune indexes/queries.

    What does the below return?

    WITH Waits AS
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          --filter out additional irrelevant waits

      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct,
      CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1
      JOIN Waits AS W2
        ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ORDER BY W1.rn;

    Best Regards, Uri Dimant SQL Server MVP

    15 เมษายน 2555 13:34
  • for one instance

    TRACEWRITE 1902075.52 44.73 44.73
    OLEDB 1578037.14 37.11 81.83
    ASYNC_NETWORK_IO 442082.64 10.40 92.23

    for another instance

    TRACEWRITE 1902099.67 44.73 44.73
    OLEDB 1578037.14 37.11 81.83
    ASYNC_NETWORK_IO 442082.64 10.40 92.23


    15 เมษายน 2555 15:26
  • Do you have a trace file (default or not) that perform excessive wites 

    SELECT * FROM sys.traces

    Regarding ASYNC_NETWORK_IO read my blog

    Best Regards, Uri Dimant SQL Server MVP

    • เสนอเป็นคำตอบโดย amber zhangModerator 16 เมษายน 2555 6:34
    • ทำเครื่องหมายเป็นคำตอบโดย amber zhangModerator 23 เมษายน 2555 8:51
    16 เมษายน 2555 5:32