SQL Server Performance

Suggestions about performance?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by adworldjoe, Apr 1, 2007.

  1. adworldjoe New Member

    I'm running SQL server 2005, and my perfmon stats look like:

    PhysicalDisk:
    % Disk Read Time: 0.510
    % Disk Write Time: 159.496
    % Idle Time: 35.169
    Avg. Disk Queue Length: 1.600

    Process: sqlservr
    % Processor Time: 18.750
    Working Set: 1201704960

    Processor:
    % PRocessor Time: 5.471

    SQLServer: Buffer Manager
    Page life expectancy: 32460
    Page reads/sec: 0.998
    Page writes/sec: 15.969

    SQLServer: Databases Total
    Active Transactions: 2
    Transactions/sec: 589.861

    SQLServer General Statistics
    Logins/sec: 296.428
    Transactions: 2
    User Connections: 211

    SQLServer: Locks
    Average Wait Time (ms): 17.273
    Lock Requests/Sec: 28836.331

    SQLServer: Memory Manager:
    Connection Memory(Kb): 3526
    SQL Cache Memory(kb): 12472
    Total server Memory (kb): 1104896

    SQLServer: Plan Cache
    Cache Hit Radio: 78.988

    SQLServer: SQL Statistics
    Bach Requests/Sec: 588.863

    System:
    Context Switches/sec: 5508.366
    Processor Queue Length: 0

    Any suggestions on what I can do to optimize the setup?
  2. Luis Martin Moderator

    I don't see any issue in those counters.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. adworldjoe New Member

    Thanks for the help.

    Everything seems to be running smoothly on the server; but randomly the server locks jump up significantly and then the server starts rejecting logins and I have to restart the sql server; or reboot.

    Any clue on what can be causing that?

    Thanks,
    Joe
  4. Luis Martin Moderator

    Run Profiler to find long queries (duration, cpu) and try to optimize those queries.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. joechang New Member

    you are probably throttled on logins/sec,
    start by enable connection pooling on you app server
    afterward, you could probably benefit from consolidating calls to SQL, depending on what you are trying to accomplish with this app

    next, get the main database logs on to a dedicated disk

    see my site, www.qdpma.com/instructions for which perf counters are relevent, and which are not

Share This Page