Post reply

Sql server random slow query

  • May 13, 2015 at 3:56 pm

    #318216

    Hi to all,

    In my company we are dealing with a very strange problem in the last couple of week in a SQL 2012 server. And maybe you could give us an hit.

    We have database that supports an ASP.NET application. We have about 500 users, and we use IIS 7 with Web Garden. Usually everything is ok, but 3-5 times a day there is one, and just one, query that takes some minutes to be executed, and usually it takes about (500 ms).

    This represents about 0,1% of the times the same query is called.

    Using profiler, I see that usually the query takes 10k to 20k of reads, but when the problem occurs we get 1.5 Million reads.

    The stored procedure is dynamic, we send the query as a string. If I copy the query and execute it in MSSMS it is always fast.

    We update indexes and statics every week.

    We tried already the

    •WITH RECOMPILE option,

    •copy the parameters to local variables because of parameter sniffing

    •free cache.

    •WITH REPAST

    •WITH NOLOCK

    Nothing seems do work and the problem seems to be random.

    Did you ever come across with something like this? We didn’t detect locks that explain this, because the same query is called almost at the same time by other users with normal responses. CPU and Memory also seems to be ok.

    Any kind of help would be appreciated.

  • May 13, 2015 at 4:56 pm

    #1797716

    mdsantos (5/13/2015)

    Hi to all,

    In my company we are dealing with a very strange problem in the last couple of week in a SQL 2012 server. And maybe you could give us an hit.

    We have database that supports an ASP.NET application. We have about 500 users, and we use IIS 7 with Web Garden. Usually everything is ok, but 3-5 times a day there is one, and just one, query that takes some minutes to be executed, and usually it takes about (500 ms).

    This represents about 0,1% of the times the same query is called.

    Using profiler, I see that usually the query takes 10k to 20k of reads, but when the problem occurs we get 1.5 Million reads.

    The stored procedure is dynamic, we send the query as a string. If I copy the query and execute it in MSSMS it is always fast.

    We update indexes and statics every week.

    We tried already the

    •WITH RECOMPILE option,

    •copy the parameters to local variables because of parameter sniffing

    •free cache.

    •WITH REPAST

    •WITH NOLOCK

    Nothing seems do work and the problem seems to be random.

    Did you ever come across with something like this? We didn’t detect locks that explain this, because the same query is called almost at the same time by other users with normal responses. CPU and Memory also seems to be ok.

    Any kind of help would be appreciated.

    All of your attempts except WITH RECOMPILE are useless for this problem, as you have documented that reads go out to lunch on the bad executions. Without further information I am 70% sure you are using index seek/nested loop plan for something that for some reason hits a bajillion rows. 30% certain it is switching to bad scan/hash plan for certain parameters/conditions.

    Are you sure the query plan is stable? Either way, provide any query plans for the executions. Also provide inputs for good and bad executions. Simple tracing to disk can provide these.

    Also, I don't know what you mean about the sproc and dynamic sql. Please provide exact examples of whatever this construct is.

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

  • May 13, 2015 at 4:59 pm

    #1797717

    RECOMPILE and Copying the parameter as a local variable will likely eliminate that possibility of parameter sniffing.

    If you normally get 10-15K reads then it goes up to 1.5M reads, that sounds like a bad query plan. Perhaps the 1.5M reads is a query plan that is doing a huge scan instead of a quick seek against an index, the wrong join algorithm, a huge sort when it normally does not need to, perhaps an index that is going offline, stuff like that.

    Can you capture the query plan that is produced when it gets 10-15K reads and then run a server-side trace to capture the query plans for until you get the one that is generating the 1.5M reads? I think that would help us get to the bottom of this.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 13, 2015 at 5:46 pm

    #1797722

    Hi thank you for the replies.

    What I meant with dinamic query is that part of the SQL code is passed to the stored procedure as a parameters. EXample:

    exec SP_SELECT_ROWS_K2_CLAIMS_WF @SectionId=13,',@FirstRow=1,@RowCount=1500, @WhereClause=' AND ( ((ppt_ClaimsManagerDescription = ''ZURIPI'' and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '''') OR ppt_ClaimsManagerDescriptionManual = ''ZURIPI'') AND ppt_SecurityLevel in (1) AND (ppt_ClaimsManagerDescription in (''ZURIPI'',''ZURMAM'',''ZURPM'') and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '''') OR ppt_ClaimsManagerDescriptionManual in (''ZURIPI'',''ZURMAM'',''ZURPM'')) AND (ppt_BranchCode not in (''086'',''087'') or ppt_BranchCode is null)))',@OrderClause='ArrivalDate ASC, DocumentTitle ASC',@SortDirection='',@FieldPermissionsClause=' ',@TopRows=1500,@ReturnedFields='',@token='AE7D7F9EB2EA4DCEBA5250787D15F7E2',@ProcessName='Claims',@SerialNumbersTable='5',@fila='Sinistros',@userid='1013'

    The procedure with the same parameters sometimes run fast, sometime runs slows.

    I already added the showplan all and showplan xml to the trace. So tomorrow I can compare plans.

    As soon as I have more information I will post it where.

  • May 14, 2015 at 8:23 am

    #1797868

    mdsantos (5/13/2015)

    Hi thank you for the replies.

    What I meant with dinamic query is that part of the SQL code is passed to the stored procedure as a parameters. EXample:

    exec SP_SELECT_ROWS_K2_CLAIMS_WF @SectionId=13,',@FirstRow=1,@RowCount=1500, @WhereClause=' AND ( ((ppt_ClaimsManagerDescription = ''ZURIPI'' and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '''') OR ppt_ClaimsManagerDescriptionManual = ''ZURIPI'') AND ppt_SecurityLevel in (1) AND (ppt_ClaimsManagerDescription in (''ZURIPI'',''ZURMAM'',''ZURPM'') and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '''') OR ppt_ClaimsManagerDescriptionManual in (''ZURIPI'',''ZURMAM'',''ZURPM'')) AND (ppt_BranchCode not in (''086'',''087'') or ppt_BranchCode is null)))',@OrderClause='ArrivalDate ASC, DocumentTitle ASC',@SortDirection='',@FieldPermissionsClause=' ',@TopRows=1500,@ReturnedFields='',@token='AE7D7F9EB2EA4DCEBA5250787D15F7E2',@ProcessName='Claims',@SerialNumbersTable='5',@fila='Sinistros',@userid='1013'

    The procedure with the same parameters sometimes run fast, sometime runs slows.

    I already added the showplan all and showplan xml to the trace. So tomorrow I can compare plans.

    As soon as I have more information I will post it where.

    So, you have an very complex WHERE clause passed in, plus I would imagine you add to the WHERE clause based on variables also? And you clearly have pagination. Like I said, I need to see the entire construct to be able to help. Pagination can be done HORRIBLY badly, and often is. Are you sure that the one's that go out to lunch aren't ones where they have @FirstRow=100000 or something??

    The dynamic nature of the calls will mean any little thing (including a single extra space or character difference) could mean a new plan. Or plans could be aging out if your system is memory constrained badly. So many things could be at play here.

    I also surmise that real help here may go way beyond what a free forum support system can provide you. I have spent tens of hours at numerous clients fixing their search/pagination sprocs.

    If I were you I would be wishing I hadn't designed NULLs into my schema! 🙂

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

  • May 14, 2015 at 9:00 am

    #1797884

    mdsantos (5/13/2015)

    Hi to all,

    In my company we are dealing with a very strange problem in the last couple of week in a SQL 2012 server. And maybe you could give us an hit.

    We have database that supports an ASP.NET application. We have about 500 users, and we use IIS 7 with Web Garden. Usually everything is ok, but 3-5 times a day there is one, and just one, query that takes some minutes to be executed, and usually it takes about (500 ms).

    This represents about 0,1% of the times the same query is called.

    Using profiler, I see that usually the query takes 10k to 20k of reads, but when the problem occurs we get 1.5 Million reads.

    The stored procedure is dynamic, we send the query as a string. If I copy the query and execute it in MSSMS it is always fast.

    We update indexes and statics every week.

    We tried already the

    •WITH RECOMPILE option,

    •copy the parameters to local variables because of parameter sniffing

    •free cache.

    •WITH REPAST

    •WITH NOLOCK

    Nothing seems do work and the problem seems to be random.

    Did you ever come across with something like this? We didn’t detect locks that explain this, because the same query is called almost at the same time by other users with normal responses. CPU and Memory also seems to be ok.

    Any kind of help would be appreciated.

    In many cases the issue is blocking, but the fact that you're seeing such a dramatic increase in reads when the query runs longer tells me that this probably has to do with alternate execution plans. So, forget about the NOLOCK and READPAST hints. The WITH RECOMPILE hint is also useless, because thanks to that dynamic expression being passed into and used for the WHERE clause, SQL Server is probably building a new execution plan on the fly every time it's executed.

    I think what's going on is that SQL Server is so confused by the endless variation of expressions, it's sometimes ignoring available indexes and falling back to table scans and hash joins, and that's when you get this radical increase in reads. Perhaps certain combinations of columns in the expression simply make the existing indexes useless. For example, some of these columns may be covered by indexes and some are not, so whether or not an index is used depends on how the expression is constructed.

    If possible, try to correlate what types of expressions result in good plans versus bad plans. Consider including a couple or three columns in your index. Yes, it will make your indexes somewhat larger, but in this case the pattern of your search expression is very broad, so your indexes need to be broadened as well. I've found that experimenting with column inclusion, giving SQL Server what it knows it needs, is more helpful than trying to tell it what to do with hints.

    Behind the Scenes with SQL Server Included Columns and Covering Indexes

    http://sqlmag.com/database-performance-tuning/behind-scenes-sql-server-included-columns-and-covering-indexes

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • May 15, 2015 at 2:33 am

    #1798108

    It's getting complicated to get the bad execution plans. I'm using profiler -> performance events -> show plan all and show plan XML

    But after some minutes I get " There is insufficient system memory in resource pool 'internal' to run this query." errors, and I have to close the trace.

    Is there any othet way to get the "bad" plan?

  • May 15, 2015 at 2:51 am

    #1798112

    This is the QUERY constructed.

    declare @Destinations table (Destination nvarchar(50))

    insert into @Destinations (Destination)

    Select Distinct 'K2SQL:' + CODE collate Latin1_General_CI_AS as destination from Claims.dbo.t_usersEntityRelation A

    inner join (

    select U.userid, U.Username collate Latin1_General_CI_AS as username

    from Claims.dbo.T_UsersHolidays UH

    INNER JOIN Claims.dbo.T_Users U on UH.UserIDHoliday = U.UserID

    INNER JOIN Claims.dbo.T_Users US on UH.UserIDSubstitute = US.UserID

    INNER JOIN Claims.dbo.T_Users UA on UH.UserIdAddBy = UA.UserID

    Where UH.Active=1 and UH.UserIDSubstitute = 1127 and GETDATE() >= UH.HolidayDateBegin and CONVERT(VARCHAR(10),GETDATE(),112) <= CONVERT(VARCHAR(10),UH.HolidayDateEnd,112)

    Union

    SELECT Userid, username collate Latin1_General_CI_AS

    FROM T_Users

    Where UserID in (SELECT UserIDComAcesso FROM Claims.dbo.T_UsersComAcesso Where UserID = 1127)

    Union

    SELECT Userid, username collate Latin1_General_CI_AS as username

    FROM Claims.dbo.T_Users

    Where UserID = 1127

    ) B on A.userid = b.userid

    inner join Claims.dbo.T_Entity c on c.EntityID = A.EntityID where EntityTypeID in (5)

    SELECT TOP 1500 * FROM (

    SELECT ROW_NUMBER() OVER ( ORDER BY ArrivalDate ASC, DocumentTitle ASC ) AS RowNum, T_WL._StampOfCreation as SerialDate, __referenceId, __previousReferenceId, __status, __stampOfCreation, __OCRStatus, __NativeFormat, __sender, __Recipient, __cc, __bcc, __attachment, __subject, __externalid, __onbehalfof , DOCUMENTTITLE, PPT_COURTACTIONAUTHOR, _PPT_COMPANY.Value as PPT_COMPANY, PPT_COMPANY as PPT_COMPANY_ValueID, PPT_COORDINATORDESCRIPTION, PPT_COORDINATORID, PPT_LOBCODE, _PPT_PROCESSTYPEID.Value as PPT_PROCESSTYPEID,

    PPT_PROCESSTYPEID as PPT_PROCESSTYPEID_ValueID, PPT_BRANCHCODE, PPT_EDITDATE, PPT_ACCIDENTDATE, PPT_INDEXINGDATE, PPT_LOBDESCRIPTION, PPT_BRANCHDESCRIPTION, _PPT_POLICYSTATUS.Value as PPT_POLICYSTATUS, PPT_POLICYSTATUS as

    PPT_POLICYSTATUS_ValueID, _PPT_DOCUMENTSTATUS.Value as PPT_DOCUMENTSTATUS, PPT_DOCUMENTSTATUS as PPT_DOCUMENTSTATUS_ValueID, _PPT_SECURITYLEVEL.Value as PPT_SECURITYLEVEL, PPT_SECURITYLEVEL as PPT_SECURITYLEVEL_ValueID,

    PPT_CLAIMSMANAGERDESCRIPTION, PPT_INSUREDPLATENUMBER, PPT_VICTIMSPLATENUMBERS, PPT_VICTIMSNAMES, PPT_INSUREDNAME, PPT_PENSIONOWNER, PPT_COURTACTIONNUMBER, PPT_INVESTIGATIONNUMBER, PPT_INSUREDNUMBER, PPT_PENSIONNUMBER,

    PPT_LOSSADJUSTERNUMBER, PPT_PROCESSNUMBER, PPT_AGENTNUMBER, PPT_POLICYNUMBER, PPT_RECOVERYNUMBER, _PPT_SOURCE.Value as PPT_SOURCE, PPT_SOURCE as PPT_SOURCE_ValueID, PPT_NOID, _PPT_FOLDER.Value as PPT_FOLDER, PPT_FOLDER as PPT_FOLDER_ValueID, _PPT_PROCESSSTATUS.Value as PPT_PROCESSSTATUS, PPT_PROCESSSTATUS as PPT_PROCESSSTATUS_ValueID, _PPT_DOCUMENTTYPE.Value as PPT_DOCUMENTTYPE, PPT_DOCUMENTTYPE as PPT_DOCUMENTTYPE_ValueID, _PPT_WORKQUEUE.Value as PPT_WORKQUEUE, PPT_WORKQUEUE as PPT_WORKQUEUE_ValueID, _PPT_PROCESSTYPEDESCRIPTION.Value as PPT_PROCESSTYPEDESCRIPTION, PPT_PROCESSTYPEDESCRIPTION as PPT_PROCESSTYPEDESCRIPTION_ValueID, PPT_COURTNAME, _PPT_UNIT.Value as PPT_UNIT, PPT_UNIT as PPT_UNIT_ValueID, PPT_USER, PPT_VALUE, T_WL.DocumentId, ARRIVALDATE, SCHEDULEDATE, COMMENTS, FILETYPE, URL, REMETENTE, MODIFICADOPOR, _INVOICESTATUS.Value as INVOICESTATUS, INVOICESTATUS as INVOICESTATUS_ValueID, POLICYNUMBERNEW, PROCESSNUMBERNEW, _CORRECTIONSTATUS.Value as CORRECTIONSTATUS, CORRECTIONSTATUS as CORRECTIONSTATUS_ValueID, LOBENTITYID, LOBENTITYDESC, PPT_CLAIMSMANAGERDESCRIPTIONMANUAL, PROCESSREGISTRATIONDATE, ADICIONADOPOR , T_WL._SerialNumber, T_WL._Comments

    FROM TD_13 with (nolock)

    LEFT JOIN T_LookUpTable _PPT_COMPANY ON _PPT_COMPANY.ValueId = TD_13.PPT_COMPANY AND _PPT_COMPANY.FieldId = 59

    LEFT JOIN T_LookUpTable _PPT_PROCESSTYPEID ON _PPT_PROCESSTYPEID.ValueId = TD_13.PPT_PROCESSTYPEID AND _PPT_PROCESSTYPEID.FieldId = 63

    LEFT JOIN T_LookUpTable _PPT_POLICYSTATUS ON _PPT_POLICYSTATUS.ValueId = TD_13.PPT_POLICYSTATUS AND _PPT_POLICYSTATUS.FieldId = 70

    LEFT JOIN T_LookUpTable _PPT_DOCUMENTSTATUS ON _PPT_DOCUMENTSTATUS.ValueId = TD_13.PPT_DOCUMENTSTATUS AND _PPT_DOCUMENTSTATUS.FieldId = 71

    LEFT JOIN T_LookUpTable _PPT_SECURITYLEVEL ON _PPT_SECURITYLEVEL.ValueId = TD_13.PPT_SECURITYLEVEL AND _PPT_SECURITYLEVEL.FieldId = 72

    LEFT JOIN T_LookUpTable _PPT_SOURCE ON _PPT_SOURCE.ValueId = TD_13.PPT_SOURCE AND _PPT_SOURCE.FieldId = 88

    LEFT JOIN T_LookUpTable _PPT_FOLDER ON _PPT_FOLDER.ValueId = TD_13.PPT_FOLDER AND _PPT_FOLDER.FieldId = 90

    LEFT JOIN T_LookUpTable _PPT_PROCESSSTATUS ON _PPT_PROCESSSTATUS.ValueId = TD_13.PPT_PROCESSSTATUS AND _PPT_PROCESSSTATUS.FieldId = 91

    LEFT JOIN T_LookUpTable _PPT_DOCUMENTTYPE ON _PPT_DOCUMENTTYPE.ValueId = TD_13.PPT_DOCUMENTTYPE AND _PPT_DOCUMENTTYPE.FieldId = 92

    LEFT JOIN T_LookUpTable _PPT_WORKQUEUE ON _PPT_WORKQUEUE.ValueId = TD_13.PPT_WORKQUEUE AND _PPT_WORKQUEUE.FieldId = 93

    LEFT JOIN T_LookUpTable _PPT_PROCESSTYPEDESCRIPTION ON _PPT_PROCESSTYPEDESCRIPTION.ValueId = TD_13.PPT_PROCESSTYPEDESCRIPTION AND _PPT_PROCESSTYPEDESCRIPTION.FieldId = 94

    LEFT JOIN T_LookUpTable _PPT_UNIT ON _PPT_UNIT.ValueId = TD_13.PPT_UNIT AND _PPT_UNIT.FieldId = 96

    LEFT JOIN T_LookUpTable _INVOICESTATUS ON _INVOICESTATUS.ValueId = TD_13.INVOICESTATUS AND _INVOICESTATUS.FieldId = 152

    LEFT JOIN T_LookUpTable _CORRECTIONSTATUS ON _CORRECTIONSTATUS.ValueId = TD_13.CORRECTIONSTATUS AND _CORRECTIONSTATUS.FieldId = 155

    INNER JOIN (SELECT distinct vw_K2Worklist.DocumentID, vw_K2Worklist._SerialNumber, vw_K2Worklist._Comments, vw_K2Worklist._StampOfCreation FROM vw_K2Worklist

    inner join @Destinations x on x.destination = vw_K2Worklist.destination

    where [queue] = 'Sinistros'

    UNION

    SELECT distinct vw_K2Worklist.DocumentID, vw_K2Worklist._SerialNumber, vw_K2Worklist._Comments, vw_K2Worklist._StampOfCreation FROM vw_K2Worklist

    where Destination = 'K2SQL:GERAL' and [Queue] = 'Sinistros') as T_WL on T_WL.DocumentID = TD_13.DocumentID

    WHERE __status=0 AND ( ((1=1) AND ppt_SecurityLevel in (1) AND (ppt_ClaimsManagerDescription in ('SANT02','SANT20','SANT21','SANT23','SANT24','SANT27','SANT28','SANT29','SANT31','SANT34','SANT35','SANT39','SANT41','SANT42','SANT43','SANT47','SANT48','SANT49','SANT50','SANT53','ZURCJE','ZURPAA') and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '') OR ppt_ClaimsManagerDescriptionManual in ('SANT02','SANT20','SANT21','SANT23','SANT24','SANT27','SANT28','SANT29','SANT31','SANT34','SANT35','SANT39','SANT41','SANT42','SANT43','SANT47','SANT48','SANT49','SANT50','SANT53','ZURCJE','ZURPAA')) AND (ppt_BranchCode not in ('086','087') or ppt_BranchCode is null))) ) AS RowConstrainedResult

    WHERE RowNum >= 1 AND RowNum < 1501

    ORDER BY RowNum

    We create a temp table, because we have to get information from other database, direct join was very slow.

  • May 15, 2015 at 6:03 am

    #1798151

    mdsantos (5/15/2015)

    This is the QUERY constructed.

    declare @Destinations table (Destination nvarchar(50))

    insert into @Destinations (Destination)

    Select Distinct 'K2SQL:' + CODE collate Latin1_General_CI_AS as destination from Claims.dbo.t_usersEntityRelation A

    inner join (

    select U.userid, U.Username collate Latin1_General_CI_AS as username

    from Claims.dbo.T_UsersHolidays UH

    INNER JOIN Claims.dbo.T_Users U on UH.UserIDHoliday = U.UserID

    INNER JOIN Claims.dbo.T_Users US on UH.UserIDSubstitute = US.UserID

    INNER JOIN Claims.dbo.T_Users UA on UH.UserIdAddBy = UA.UserID

    Where UH.Active=1 and UH.UserIDSubstitute = 1127 and GETDATE() >= UH.HolidayDateBegin and CONVERT(VARCHAR(10),GETDATE(),112) <= CONVERT(VARCHAR(10),UH.HolidayDateEnd,112)

    Union

    SELECT Userid, username collate Latin1_General_CI_AS

    FROM T_Users

    Where UserID in (SELECT UserIDComAcesso FROM Claims.dbo.T_UsersComAcesso Where UserID = 1127)

    Union

    SELECT Userid, username collate Latin1_General_CI_AS as username

    FROM Claims.dbo.T_Users

    Where UserID = 1127

    ) B on A.userid = b.userid

    inner join Claims.dbo.T_Entity c on c.EntityID = A.EntityID where EntityTypeID in (5)

    SELECT TOP 1500 * FROM (

    SELECT ROW_NUMBER() OVER ( ORDER BY ArrivalDate ASC, DocumentTitle ASC ) AS RowNum, T_WL._StampOfCreation as SerialDate, __referenceId, __previousReferenceId, __status, __stampOfCreation, __OCRStatus, __NativeFormat, __sender, __Recipient, __cc, __bcc, __attachment, __subject, __externalid, __onbehalfof , DOCUMENTTITLE, PPT_COURTACTIONAUTHOR, _PPT_COMPANY.Value as PPT_COMPANY, PPT_COMPANY as PPT_COMPANY_ValueID, PPT_COORDINATORDESCRIPTION, PPT_COORDINATORID, PPT_LOBCODE, _PPT_PROCESSTYPEID.Value as PPT_PROCESSTYPEID,

    PPT_PROCESSTYPEID as PPT_PROCESSTYPEID_ValueID, PPT_BRANCHCODE, PPT_EDITDATE, PPT_ACCIDENTDATE, PPT_INDEXINGDATE, PPT_LOBDESCRIPTION, PPT_BRANCHDESCRIPTION, _PPT_POLICYSTATUS.Value as PPT_POLICYSTATUS, PPT_POLICYSTATUS as

    PPT_POLICYSTATUS_ValueID, _PPT_DOCUMENTSTATUS.Value as PPT_DOCUMENTSTATUS, PPT_DOCUMENTSTATUS as PPT_DOCUMENTSTATUS_ValueID, _PPT_SECURITYLEVEL.Value as PPT_SECURITYLEVEL, PPT_SECURITYLEVEL as PPT_SECURITYLEVEL_ValueID,

    PPT_CLAIMSMANAGERDESCRIPTION, PPT_INSUREDPLATENUMBER, PPT_VICTIMSPLATENUMBERS, PPT_VICTIMSNAMES, PPT_INSUREDNAME, PPT_PENSIONOWNER, PPT_COURTACTIONNUMBER, PPT_INVESTIGATIONNUMBER, PPT_INSUREDNUMBER, PPT_PENSIONNUMBER,

    PPT_LOSSADJUSTERNUMBER, PPT_PROCESSNUMBER, PPT_AGENTNUMBER, PPT_POLICYNUMBER, PPT_RECOVERYNUMBER, _PPT_SOURCE.Value as PPT_SOURCE, PPT_SOURCE as PPT_SOURCE_ValueID, PPT_NOID, _PPT_FOLDER.Value as PPT_FOLDER, PPT_FOLDER as PPT_FOLDER_ValueID, _PPT_PROCESSSTATUS.Value as PPT_PROCESSSTATUS, PPT_PROCESSSTATUS as PPT_PROCESSSTATUS_ValueID, _PPT_DOCUMENTTYPE.Value as PPT_DOCUMENTTYPE, PPT_DOCUMENTTYPE as PPT_DOCUMENTTYPE_ValueID, _PPT_WORKQUEUE.Value as PPT_WORKQUEUE, PPT_WORKQUEUE as PPT_WORKQUEUE_ValueID, _PPT_PROCESSTYPEDESCRIPTION.Value as PPT_PROCESSTYPEDESCRIPTION, PPT_PROCESSTYPEDESCRIPTION as PPT_PROCESSTYPEDESCRIPTION_ValueID, PPT_COURTNAME, _PPT_UNIT.Value as PPT_UNIT, PPT_UNIT as PPT_UNIT_ValueID, PPT_USER, PPT_VALUE, T_WL.DocumentId, ARRIVALDATE, SCHEDULEDATE, COMMENTS, FILETYPE, URL, REMETENTE, MODIFICADOPOR, _INVOICESTATUS.Value as INVOICESTATUS, INVOICESTATUS as INVOICESTATUS_ValueID, POLICYNUMBERNEW, PROCESSNUMBERNEW, _CORRECTIONSTATUS.Value as CORRECTIONSTATUS, CORRECTIONSTATUS as CORRECTIONSTATUS_ValueID, LOBENTITYID, LOBENTITYDESC, PPT_CLAIMSMANAGERDESCRIPTIONMANUAL, PROCESSREGISTRATIONDATE, ADICIONADOPOR , T_WL._SerialNumber, T_WL._Comments

    FROM TD_13 with (nolock)

    LEFT JOIN T_LookUpTable _PPT_COMPANY ON _PPT_COMPANY.ValueId = TD_13.PPT_COMPANY AND _PPT_COMPANY.FieldId = 59

    LEFT JOIN T_LookUpTable _PPT_PROCESSTYPEID ON _PPT_PROCESSTYPEID.ValueId = TD_13.PPT_PROCESSTYPEID AND _PPT_PROCESSTYPEID.FieldId = 63

    LEFT JOIN T_LookUpTable _PPT_POLICYSTATUS ON _PPT_POLICYSTATUS.ValueId = TD_13.PPT_POLICYSTATUS AND _PPT_POLICYSTATUS.FieldId = 70

    LEFT JOIN T_LookUpTable _PPT_DOCUMENTSTATUS ON _PPT_DOCUMENTSTATUS.ValueId = TD_13.PPT_DOCUMENTSTATUS AND _PPT_DOCUMENTSTATUS.FieldId = 71

    LEFT JOIN T_LookUpTable _PPT_SECURITYLEVEL ON _PPT_SECURITYLEVEL.ValueId = TD_13.PPT_SECURITYLEVEL AND _PPT_SECURITYLEVEL.FieldId = 72

    LEFT JOIN T_LookUpTable _PPT_SOURCE ON _PPT_SOURCE.ValueId = TD_13.PPT_SOURCE AND _PPT_SOURCE.FieldId = 88

    LEFT JOIN T_LookUpTable _PPT_FOLDER ON _PPT_FOLDER.ValueId = TD_13.PPT_FOLDER AND _PPT_FOLDER.FieldId = 90

    LEFT JOIN T_LookUpTable _PPT_PROCESSSTATUS ON _PPT_PROCESSSTATUS.ValueId = TD_13.PPT_PROCESSSTATUS AND _PPT_PROCESSSTATUS.FieldId = 91

    LEFT JOIN T_LookUpTable _PPT_DOCUMENTTYPE ON _PPT_DOCUMENTTYPE.ValueId = TD_13.PPT_DOCUMENTTYPE AND _PPT_DOCUMENTTYPE.FieldId = 92

    LEFT JOIN T_LookUpTable _PPT_WORKQUEUE ON _PPT_WORKQUEUE.ValueId = TD_13.PPT_WORKQUEUE AND _PPT_WORKQUEUE.FieldId = 93

    LEFT JOIN T_LookUpTable _PPT_PROCESSTYPEDESCRIPTION ON _PPT_PROCESSTYPEDESCRIPTION.ValueId = TD_13.PPT_PROCESSTYPEDESCRIPTION AND _PPT_PROCESSTYPEDESCRIPTION.FieldId = 94

    LEFT JOIN T_LookUpTable _PPT_UNIT ON _PPT_UNIT.ValueId = TD_13.PPT_UNIT AND _PPT_UNIT.FieldId = 96

    LEFT JOIN T_LookUpTable _INVOICESTATUS ON _INVOICESTATUS.ValueId = TD_13.INVOICESTATUS AND _INVOICESTATUS.FieldId = 152

    LEFT JOIN T_LookUpTable _CORRECTIONSTATUS ON _CORRECTIONSTATUS.ValueId = TD_13.CORRECTIONSTATUS AND _CORRECTIONSTATUS.FieldId = 155

    INNER JOIN (SELECT distinct vw_K2Worklist.DocumentID, vw_K2Worklist._SerialNumber, vw_K2Worklist._Comments, vw_K2Worklist._StampOfCreation FROM vw_K2Worklist

    inner join @Destinations x on x.destination = vw_K2Worklist.destination

    where [queue] = 'Sinistros'

    UNION

    SELECT distinct vw_K2Worklist.DocumentID, vw_K2Worklist._SerialNumber, vw_K2Worklist._Comments, vw_K2Worklist._StampOfCreation FROM vw_K2Worklist

    where Destination = 'K2SQL:GERAL' and [Queue] = 'Sinistros') as T_WL on T_WL.DocumentID = TD_13.DocumentID

    WHERE __status=0 AND ( ((1=1) AND ppt_SecurityLevel in (1) AND (ppt_ClaimsManagerDescription in ('SANT02','SANT20','SANT21','SANT23','SANT24','SANT27','SANT28','SANT29','SANT31','SANT34','SANT35','SANT39','SANT41','SANT42','SANT43','SANT47','SANT48','SANT49','SANT50','SANT53','ZURCJE','ZURPAA') and (ppt_ClaimsManagerDescriptionManual is null or ltrim(ppt_ClaimsManagerDescriptionManual) = '') OR ppt_ClaimsManagerDescriptionManual in ('SANT02','SANT20','SANT21','SANT23','SANT24','SANT27','SANT28','SANT29','SANT31','SANT34','SANT35','SANT39','SANT41','SANT42','SANT43','SANT47','SANT48','SANT49','SANT50','SANT53','ZURCJE','ZURPAA')) AND (ppt_BranchCode not in ('086','087') or ppt_BranchCode is null))) ) AS RowConstrainedResult

    WHERE RowNum >= 1 AND RowNum < 1501

    ORDER BY RowNum

    We create a temp table, because we have to get information from other database, direct join was very slow.

    Like I suspected, fixing this goes WAY WAY beyond a forum thread! The number of suboptimal things here is pretty impressive. Honestly you should be very happy you get the sub-second response time you normally get on this beast! 😀

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

  • May 15, 2015 at 6:36 am

    #1798171

    Alan.B (5/13/2015)

    RECOMPILE and Copying the parameter as a local variable will likely eliminate that possibility of parameter sniffing.

    Careful, those are two very different possible solutions to a bad parameter sniffing problem. You would sure want to be careful about using the AND word between them as a proposed solution causing people to implement both, which, depending on the situation, could be required, but without caveats in your answer, we could be leading people down a poor path. Other possible solutions such as OPTIMIZE FOR could also work, depending on the problem, that we really haven't quite defined yet.

    If you normally get 10-15K reads then it goes up to 1.5M reads, that sounds like a bad query plan. Perhaps the 1.5M reads is a query plan that is doing a huge scan instead of a quick seek against an index, the wrong join algorithm, a huge sort when it normally does not need to, perhaps an index that is going offline, stuff like that.

    Can you capture the query plan that is produced when it gets 10-15K reads and then run a server-side trace to capture the query plans for until you get the one that is generating the 1.5M reads? I think that would help us get to the bottom of this.

    I agree with everything else.

    "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

  • May 15, 2015 at 6:38 am

    #1798173

    Just this, all by itself, is going to lead to poor performance:

    ltrim(ppt_ClaimsManagerDescriptionManual)

    If you can't bring in a consultant, and I'm with Kevin, you need to, second best choice, get a copy of my book on query tuning and start working through all the poor practices in this query.

    "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

  • May 15, 2015 at 6:39 am

    #1798174

    mdsantos (5/15/2015)

    It's getting complicated to get the bad execution plans. I'm using profiler -> performance events -> show plan all and show plan XML

    But after some minutes I get " There is insufficient system memory in resource pool 'internal' to run this query." errors, and I have to close the trace.

    Is there any othet way to get the "bad" plan?

    You can pull the plan directly from cache using the dynamic management views including sys.dm_exec_query_plan. For details on how to do that, I'd suggest getting a copy of "Performance Tuning with Dynamic Management Views" by Tim Ford and Louis Davidson. It's free to download.

    "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

  • May 15, 2015 at 7:12 am

    #1798184

    Consider replacing the "SELECT TOP 1500" with "SELECT TOP 0".

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • May 18, 2015 at 3:00 am

    #1798554

    Finally I got the good and bad plan.

    Bad Plan

    Bad Plan

    Good Plan

    Good Plan

  • May 18, 2015 at 4:46 am

    #1798577

    If you look at the plans, the very first operator, the SELECT operator, has a little exclamation mark. That's a warning. If you open the properties for this, you'll get this list:

    Type conversion in expression (CONVERT(varchar(10),[UH].[HolidayDateEnd],112)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT(varchar(50),.[Username],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT(varchar(20),[c].[Code],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT(varchar(50),[ZURICHCLAIMSPROD].[dbo].[T_Users].[Username],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT(varchar(10),getdate(),112)<=CONVERT(varchar(10),[UH].[HolidayDateEnd],112)) may affect "SeekPlan" in query plan choice

    That's one of the very first places to start tuning your query. All these conversions when used to filter data cause scans against your tables.

    The query has timed out in the optimizer. That could be one of the very first issues. It's going to lead to plan instability. It also appears that one or more of these tables isn't, it's a view. That's adding to the complexity of what's going on with the query. You're also getting scads of lookup operations. Many of those have a very limited output list. You could try adding those to the nonclustered index through the INCLUDE operator. For example, [REDPROD].[dbo].[TD_13].Documentid could be added to [REDPROD].[dbo].[TD_13].[_dta_index_TD_13_15_1334295813__K44_K1_K8_K57_K17_K60_K16_2_3_4_5_6_7_9_10_11_12_13_14_15_18_19_20_21_22_23_24_25_26_27_28_29_]. But, note, I don't know what's in that index, so that may or may not be a good choice.

    Which by the way suggests that you've been using the Database Tuning Advisor to try to tune things. As attractive as that choice seems, it's unfortunately poor. The DTA makes some pretty poor decisions, some of which can be harmful to performance. Others, like the name of that index, are just harmful to clarity in your system.

    All the estimated values for these queries are extremely low. It makes me question your statistics, especially since you don't have any kind of WHERE clause filtering this data.

    This is a major tuning effort. I'm going to go back to what Kevin said earlier, you need to get a pro in to look at this stuff and teach you.

    "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 15 posts - 1 through 15 (of 18 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy