Home Forums SQL Server 2005 Administering No blocking, queries running slowly, what to check next? RE: No blocking, queries running slowly, what to check next?

  • I'd start with slow running queries;

    if you cannot change the app code, you might at least add indexes that will help.

    here's a query for the top 20 slowest queries

    SELECT TOP 20 object_schema_name(qt.object_id, qt.database_id) + N'.' + object_name(qt.object_id, qt.database_id) AS 'SPName', qt.query_text , qs.total_worker_time AS 'TotalWorkerTime',

    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

    qs.execution_count AS 'ExecutionCount',

    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'CallsPerSecond',

    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

    qs.max_logical_reads, qs.max_logical_writes,

    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'AgeInCache'

    FROM sys.dm_exec_query_stats AS qs

    cross apply msdb.MS_PerfDashboard.fn_QueryTextFromHandle(sql_handle, statement_start_offset, statement_end_offset) as qt

    WHERE qt.database_id = db_id()

    ORDER BY qs.total_worker_time DESC

    here's queries taking longer than 15 seconds:

    select

    fn.*,

    st.*

    from sys.dm_exec_query_stats st

    cross apply sys.dm_exec_sql_text(st.[sql_handle]) fn

    where st.max_elapsed_time >= (1000 * 15) --15 seconds

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!