none
Database slow

    Question

  • Hi,

    A user complaining that database is responding very slowly. How will u troubleshoot the issue?

    Saturday, September 14, 2013 7:21 PM

Answers

  • use this doc

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, September 15, 2013 5:11 AM
    Moderator
  • Hi Surya,

    As Shanky post above, we can use SQL Server Profiler to troubleshooting the issue. And we can check SQL Server error log for more information about the timeout.
    There is a similar issue you can refer to it.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/24874b13-36a3-458c-bd44-957121d40420/troubleshooting-intermittent-sql-server-2000-slow-response-times

    Also, we can consider following issue to troubleshooting:
    • Proper indexing, not just the primary key but to satisfy different kinds of frequent/important operations.
    • Avoid over indexing to keep the modifications running smoothly.
    • From time to time, defragment the database
    • Utilize the database to take care about the logic when possible, meaning having triggers, procedures constraints.

    You can refer to the thread below:
    http://www.codeproject.com/Questions/441808/SQL-Server-slow-response

    Hope this helps. If you have any problem, please feel free to let me know.

    Regards,
    Alisa Tang

    Monday, September 23, 2013 11:10 AM
  • 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.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2013 1:04 PM
    Answerer
    • Have a look at what's running on your instance
    SELECT  der.session_id ,
            DB_NAME(der.database_id) AS database_name ,
            deqp.query_plan ,
            SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
    ( ( CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(dest.text)
    ELSE der.statement_end_offset
    END - der.statement_start_offset ) / 2 ) + 1)
                                            AS [statement executing] ,
            der.cpu_time,
    start_time,
          DATEDIFF(MINUTE,start_time,GETDATE()),
          --der.wait_time
          der.total_elapsed_time
          --der.reads
    FROM    sys.dm_exec_requests der
            INNER JOIN sys.dm_exec_sessions des
                           ON des.session_id = der.session_id
            CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
            OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
    WHERE   des.is_user_process = 1
            AND der.session_id <> @@spid
    ORDER BY der.cpu_time DESC ;
    -- ORDER BY der.granted_query_memory DESC ;
    -- ORDER BY der.wait_time DESC;
    -- ORDER BY der.total_elapsed_time DESC;
    -- ORDER BY der.reads DESC;
    
    
    
    

    • If you are having performance issues right now, use this script to point out what SQL Server is waiting on:
    select er.session_id,DB_NAME(er.database_id), er.command ,wt.resource_description,wt.wait_type,wt.wait_duration_ms
    from sys.dm_exec_requests er
    inner join sys.dm_os_waiting_tasks wt 
    on er.session_id = wt.session_id
    where er.session_id > 50
    

    To get an idea on where SQL Server is experiencing bottlenecks, use this:

     

    SELECT TOP 15
    wait_type ,
    wait_time_ms ,
    signal_wait_time_ms ,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
    AS percent_total_waits ,
    100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
    AS percent_total_signal_waits ,
    100.0 * ( wait_time_ms - signal_wait_time_ms )
    / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms > 0 -- remove zero wait_time
    AND wait_type NOT IN -- filter out additional irrelevant waits
    ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
    'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
    'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
    'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
    'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
    'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
    'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
    'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
    'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
    'RESOURCE_QUEUE' )
    ORDER BY wait_time_ms DESC
    
    Monday, September 23, 2013 1:04 PM

All replies

  • use this doc

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, September 15, 2013 5:11 AM
    Moderator
  • Hi Surya,

    As Shanky post above, we can use SQL Server Profiler to troubleshooting the issue. And we can check SQL Server error log for more information about the timeout.
    There is a similar issue you can refer to it.
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/24874b13-36a3-458c-bd44-957121d40420/troubleshooting-intermittent-sql-server-2000-slow-response-times

    Also, we can consider following issue to troubleshooting:
    • Proper indexing, not just the primary key but to satisfy different kinds of frequent/important operations.
    • Avoid over indexing to keep the modifications running smoothly.
    • From time to time, defragment the database
    • Utilize the database to take care about the logic when possible, meaning having triggers, procedures constraints.

    You can refer to the thread below:
    http://www.codeproject.com/Questions/441808/SQL-Server-slow-response

    Hope this helps. If you have any problem, please feel free to let me know.

    Regards,
    Alisa Tang

    Monday, September 23, 2013 11:10 AM
  • 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.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2013 1:04 PM
    Answerer
    • Have a look at what's running on your instance
    SELECT  der.session_id ,
            DB_NAME(der.database_id) AS database_name ,
            deqp.query_plan ,
            SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
    ( ( CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(dest.text)
    ELSE der.statement_end_offset
    END - der.statement_start_offset ) / 2 ) + 1)
                                            AS [statement executing] ,
            der.cpu_time,
    start_time,
          DATEDIFF(MINUTE,start_time,GETDATE()),
          --der.wait_time
          der.total_elapsed_time
          --der.reads
    FROM    sys.dm_exec_requests der
            INNER JOIN sys.dm_exec_sessions des
                           ON des.session_id = der.session_id
            CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
            OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
    WHERE   des.is_user_process = 1
            AND der.session_id <> @@spid
    ORDER BY der.cpu_time DESC ;
    -- ORDER BY der.granted_query_memory DESC ;
    -- ORDER BY der.wait_time DESC;
    -- ORDER BY der.total_elapsed_time DESC;
    -- ORDER BY der.reads DESC;
    
    
    
    

    • If you are having performance issues right now, use this script to point out what SQL Server is waiting on:
    select er.session_id,DB_NAME(er.database_id), er.command ,wt.resource_description,wt.wait_type,wt.wait_duration_ms
    from sys.dm_exec_requests er
    inner join sys.dm_os_waiting_tasks wt 
    on er.session_id = wt.session_id
    where er.session_id > 50
    

    To get an idea on where SQL Server is experiencing bottlenecks, use this:

     

    SELECT TOP 15
    wait_type ,
    wait_time_ms ,
    signal_wait_time_ms ,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
    AS percent_total_waits ,
    100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
    AS percent_total_signal_waits ,
    100.0 * ( wait_time_ms - signal_wait_time_ms )
    / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms > 0 -- remove zero wait_time
    AND wait_type NOT IN -- filter out additional irrelevant waits
    ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
    'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
    'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
    'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
    'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
    'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
    'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
    'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
    'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
    'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
    'RESOURCE_QUEUE' )
    ORDER BY wait_time_ms DESC
    
    Monday, September 23, 2013 1:04 PM
  • Hi,

    A user complaining that database is responding very slowly. How will u troubleshoot the issue?

    can helps you-

    Checklist for Analyzing Slow-Running Queries - TechNet - Microsoft
    http://technet.microsoft.com/en-us/library/ms177500%28v=sql.105%29.aspx


    A Performance Troubleshooting Methodology for SQL Server
    http://technet.microsoft.com/en-us/library/ms177500%28v=sql.105%29.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, September 23, 2013 1:22 PM