Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Suggestions on a query plan RE: Suggestions on a query plan

  • December 21, 2017 at 12:43 pm

    #1973150

    vsamantha35 - Thursday, December 21, 2017 12:38 PM

    The problem is, running the query individually for 1 user it is performing well. however, when the same sp/sql stmt is execute with 60 concurrent connections then the average response time is more than 3 secs and there SLA for the query to return the results back should be 1 or less than 1 secs. That's the concern.

    Two choices.
    One, don't sort in SQL Server and have the application that receives the data sort it there if needed.
    Two, create a filtered nonclustered index on the table like this:

    CREATE NONCLUSTERED INDEX idx_Sample ON [LubeAnalyst].[Sample] (
     [x].[DateDrawn] DESC
    ,[x].[EquipmentLife] DESC
    ,[x].[LubricantLife] DESC
    ,[x].[DateRegistered] DESC
    ,[x].[SampleID]
    )
    WHERE [SampleStatusID] = 1;