• kevaburg - Tuesday, December 26, 2017 6:33 AM

    Is it possible you are returning a very (unneccessarily) large result set that is being stored in TempDB as part of a single transaction before being filtered and sorted?  Is your transaction log similarly bloated?

    If that is the case try breaking down the transaction into smaller more manageble transactions.

    Also, if other applications are affected as a result of file contention (as I assume it might be) try adding more files to TempDB.  You will need to look in the logfiles to see if this is happening. 

    If you decide to add more files, have a look at this first:
    https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

    As mentioned in the link, trace flag 1118 could help mitigate some problems if you haven't already turned it on and aren't already on SQL Server 2016.

    Is it possible for you to post the stored procedure code so we can have a look at it?

    For SQL Server 2016 and up, you have no real choice... Trace Flag 1118 is on whether you want it to be or not.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)