Server

  • sudhakara

    SSCrazy Eights

    Points: 9425

    What are the things need to be checked when the server is slow and how .

    Thanks in Advance .

  • Gail Shaw

    SSC Guru

    Points: 1004454

    What do you mean by slow? Queries taking longer than the should? Queries timing out? All operations (including remote desktop) slower than normal? CPU maxed out?

    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
  • sudhakara

    SSCrazy Eights

    Points: 9425

    example normally my query wont take so much of time but due to some issue in server its taking so much of time In such a case what i need to check ...

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717326

    You can check sp_who2 to look for blocking, check the CPU as another query might be taking up resources. If the server was recently restarted, the first query can be slow as the cache is primed.

  • sudhakara

    SSCrazy Eights

    Points: 9425

    when check the error log i had seen the msg

    "A significant part of sql server process memory has been paged out. This may result in a performance degradation.

    Duration: 0 seconds. Working set (KB): 10864, committed (KB): 91356, memory utilization: 11%." what was the rason for this error message.....

  • Gail Shaw

    SSC Guru

    Points: 1004454

    It means that the OS has forced a lot of SQL's memory into the swap file. As the message says, it's near guranteed to result in poor performance.

    How much memory is on the server?

    What's sQL's max memory setting?

    What else in running on the server?

    Standard or enterprise version of SQL?

    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
  • rajiniforu

    Ten Centuries

    Points: 1147

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    sudhakara (8/31/2008)


    when check the error log i had seen the msg

    "A significant part of sql server process memory has been paged out. This may result in a performance degradation.

    Duration: 0 seconds. Working set (KB): 10864, committed (KB): 91356, memory utilization: 11%." what was the rason for this error message.....

    Looks like you are using AWE, locked at 10.9 GB. Is this correct?

    It's possible that large table scans are flushing the memory.

    Check for missing indexes:

    SELECT

    index_advantage

    , user_seeks

    , last_user_seek

    , avg_total_user_cost

    , avg_user_impact

    , equality_columns

    , included_columns

    , [statement]

    FROM

    ( SELECT

    user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage

    , migs.* FROM sys.dm_db_missing_index_group_stats migs

    ) AS migs_adv

    inner join

    sys.dm_db_missing_index_groups AS mig

    ON

    migs_adv.group_handle = mig.index_group_handle

    inner join

    sys.dm_db_missing_index_details AS mid

    ON

    mig.index_handle = mid.index_handle

    WHERE

    migs_adv.index_advantage > 10000

    ORDER BY

    migs_adv.index_advantage desc

    Look for most costly queries by IO:

    --http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

    SELECT TOP 10

    [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

    ,[Total IO] = (total_logical_reads + total_logical_writes)

    ,[Execution count] = qs.execution_count

    ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

    (CASE WHEN qs.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

    ,[Parent Query] = qt.text

    ,DatabaseName = DB_NAME(qt.dbid)

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

    ORDER BY [Average IO] DESC;

    As a last resort, run SQL Profiler on your system, choose the tuning template, add the Reads column and filter out SQL statements with under 1000 reads. Run the trace for some time, and collect the data into a table. Focus on queries with the largest no. of Reads. These are likely the queries bringing down your system.

    The other possibility is that you do need more RAM for your system. Here are some questions:

    (1) How large is your database(s)?

    (2) Are you on 32-bit or 64-bit?

    (3) What is the RAM on your machine?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • sudhakara

    SSCrazy Eights

    Points: 9425

    How much memory is on the server? 16 GB

    What's sQL's max memory setting? 10 GB

    What else in running on the server? Nothing

    Standard or enterprise version of SQL? Standard

  • sudhakara

    SSCrazy Eights

    Points: 9425

    How much memory is on the server? 16 GB

    What's sQL's max memory setting? 10 GB

    What else in running on the server? Nothing

    Standard or enterprise version of SQL? Standard

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    sudhakara (9/3/2008)


    How much memory is on the server? 16 GB

    What's sQL's max memory setting? 10 GB

    What else in running on the server? Nothing

    Standard or enterprise version of SQL? Standard

    Raise the max memory to 13 GB. You have 6 GB dedicated to OS right now, that's a waste.

    You will need to check for costly queries, large table scans, missing indexes. Try some of the above recommendations to troubleshoot those.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 11 posts - 1 through 11 (of 11 total)

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