Post reply

picking out performance problems

  • September 2, 2011 at 5:04 am

    #243310

    Guys I have a few topics I would like to bring up in regards to database monitoring. First off I've heard a few people talk about long running task or uncommitted transactions on sql server which I'm sure can impact the performance of applications that are pulling from databases housed on your sql server. So I must ask how do you going about determining if a you have long running task or a lot of uncommitted transactions btw guys I'm running sql server 2008?

  • September 2, 2011 at 5:15 am

    #1376364

    Hi,

    We have DMV's available in SQL Server 2005 & 2008 for long running queries you can use the below :

    SELECT DISTINCT TOP 10

    t.TEXT QueryName,

    s.execution_count AS ExecutionCount,

    s.max_elapsed_time AS MaxElapsedTime,

    ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

    s.creation_time AS LogCreatedOn,

    ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

    FROM sys.dm_exec_query_stats s

    CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

    ORDER BY

    s.max_elapsed_time DESC

    GO

    For Open transactions you can use the below:

    DBCC Opentran()

    Reagrds

    Raju

  • September 2, 2011 at 6:09 am

    #1376390

    Adam Machaninc's who is active proc would be a good starting point

    I think this is the latest version

    http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx


    Clear Sky SQL
    My Blog[/url]

  • September 2, 2011 at 9:02 am

    #1376484

    And, you have to determine what defines "long running" on your system. On mine it might be when a query breaks 60 seconds. On someone elses it might be 60 minutes. It completely depends on the processes and data involved.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 3, 2011 at 1:05 pm

    #1376792

    Hi,

    We like to setup SQL trace files to run all the time and dump the results into a table. This allows us to do a couple things:

    1. Have an easy way to see what queries were having a performance issue the previous day (if it was reported late).

    2. Monitor for long running queries by comparing their trending over time.

    Determining if a query is taking a long time can definitely be relative (an import could take 5 minutes, while a report might take 5 seconds... but both are within norms). If you track the data over time and see that your import suddenly jumped up to 10 minutes today and was 5 minute on the last 50 runs, that should be a red flag for review. Here is a quick article that can get you started on SQL tracing (most importantly, extracting the data from the trace file which is at the bottom of the article):

    SQL Performance Trace

    Jason

    Webmaster at SQL Optimizations School

  • September 4, 2011 at 4:47 am

    #1376845

    loop

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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