Post reply

Simple Query Running long in PROD

  • May 9, 2016 at 4:45 am

    #326287

    Hi

    I am trying to run the simple select query to retrieve records from my PROD server. Till last Friday this query was running fine

    and there was no issue ,even this running fine in my DEV server without any issue.

    Select * from [MyTable] Order by Inserted_DateTime Desc ,Step Desc

    When I see the execution plan it gives me 'Warning : Operator uses tempDb to spill data during execution with spill Level 1"

    This message is displaying when I am hovering my mouse on Sort operator in Execution plan.

    There are only 1881 records.

    On DEV server : It is taking less than a Second to full 1881 records.

    On PROD server : It is taking 25 Seconds to full 1881 records.

    is there anything DBA needs to look into the PROD database server ? Since there nothing wrong with the DEV and QA Database

    Server.

    Please advise me on this.

    Thanks

  • May 9, 2016 at 4:46 am

    #1877423

    Try updating statistics on MyTable, with fullscan

    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

  • May 9, 2016 at 5:40 am

    #1877437

    Check to see if the tables have the same columns and indexes in prod and dev.

    If it is sorting on Prod but not on Dev, then I assume that the Dev box is clustered on the {order by} columns.

    Incidentally, rowcount might not give the whole picture since I have a 50000 row table (a small table in quotes) that is 4GB after page compression, because of incompressible LOBS on the table.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • May 9, 2016 at 6:33 am

    #1877453

    Your production database presumably has actual production work being done, meaning some locks are probably being taken on the table. So your query could simply be waiting for said locks to be released.

    Another possibility is that the table is a HEAP and it sees certain DML activity, in which case the size of the table in pages could be massive due to the way HEAPs are maintained in SQL Server.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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