Post reply

Query running slow

  • March 29, 2013 at 2:47 am

    #272282

    Hi,

    Sql queries were running slow and these are triggering from

    They are supposed to run for 5-10 sec, but now they are taking 2 min.

    I can see SOS_scheduler in last wait column of sysprocesses.

    We have found fragmentation and rebuild the indexes. but after some time, the users were reporting the problem again.

    Can anyone advise how to proceed?

  • March 29, 2013 at 3:09 am

    #1601945

    Can you post the actual execution plan of the query together with the query itself?

    There are numerous reasons for a query "suddenly" taking longer than expected:

    Examples:

    dramatic growth of rows in affected tables

    "catch-all" query with a query parameter leading to table scans instead of index seeks

    locking / blocking due to a DML script within a transaction where the commit is postponed for some reason

    modified query no longer using covering indexes

    ... (the list goes on)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • March 29, 2013 at 3:23 am

    #1601947

    The query is a SSRS generated query. And it differs from each time.

    It's not using any DML statements either.

    It includes select and multiple joins (Left Join). I've checked the actual execution plan, it shows index seek/index scans in the plan. I think the performance is down when table scans were there.

  • March 29, 2013 at 6:39 am

    #1601986

    Without seeing the queries or the execution plan, everything that follows is just a vague guess.

    If you're seeing scans, it means either that you don't have the right indexes in place or that your T-SQL code is doing something such as functions on columns in the WHERE clause or JOIN criteria. Check that.

    Just so you know, performance problems are very seldom fixed by defragging indexes. The primary causes of performance issues is bad code. So that's why we tend to focus there. Secondary causes could be incorrect statistics, bad or missing indexes, tertiary causes are system configuration issues.

    "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

Viewing 4 posts - 1 through 3 (of 3 total)

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