SQL Server Performance

Unusual case

Discussion in 'Performance Tuning for DBAs' started by Luis Martin, Jan 26, 2008.

  1. Luis Martin Moderator

    Adriaan,
    The 3rd app send an explicit select. Is exactly the same with or without new index. That is what I see with profiler. Now, about appl code, I can't reach them. Is inbuilt and is not sp.
  2. Luis Martin Moderator

    This is the query:
    declare @P1 int
    set @P1=133
    declare @P2 int
    set @P2=180150071
    declare @P3 int
    set @P3=8
    declare @P4 int
    set @P4=1
    declare @P5 int
    set @P5=22
    exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 datetime,@P2 datetime,@P3 char(254),@P4 float,@P5 float,@P6 float,@P7 float,@P8 char(254),@P9 char(254)', N'SELECT CASE WHEN C.Fecha <= @P1 THEN ''S'' ELSE ''N'' END, C.Codplan, J.descuento, C.Fecha, C.Nrocupon, C.Nrotarjeta, CASE WHEN J.codtab is null THEN M.Importe ELSE m.cantidad * isnull((Ind.Valor/Ta.Factor),1) END, M.Codpag, CASE WHEN T.Codcmp=''TV'' THEN M.CodcuePres ELSE M.Codcue END, M.Codcen, M.CodcenB, CASE WHEN cu.codtab is null THEN 0 ELSE (CASE WHEN J.Codtab is null OR isnull(J.Codtab,0) <> isnull(Cu.Codtab,0) THEN M.Importe /(1.0000 / 1 ) ELSE M.Cantidad END) END, M.Nrotrans, M.Secuencia, ''K'', P.Tipovalor, ''E'', A.Codcue, J.codctabcopres, (CASE WHEN Cu.Codtab is null THEN (CASE WHEN J.Codtab is null THEN M.importe ELSE m.cantidad * isnull((Ind.Valor/Ta.Factor),1) END) ELSE (CASE WHEN J.Codtab is null OR isnull(J.Codtab,0) <> isnull(Cu.Codtab,0) THEN M.Importe /(1.0000 / 1) ELSE M.Cantidad END) END ) * J.descuento / 100 FROM Cupones C (NOLOCK) LEFT JOIN cajasreg CR (NOLOCK) ON C.nrotrans=CR.nrotrans JOIN Mvscaja M (NOLOCK) ON (C.Nrotrans=M.Nrotrans AND
    C.Secuencia=M.Secuencia AND CR.Refcaja=M.refcaja) JOIN Mediospago P (NOLOCK) ON (M.Codpag=P.Codpag) JOIN Transac T (NOLOCK) ON (C.nrotrans=T.Nrotrans) JOIN Tarjetplanes J (NOLOCK) ON (C.Codplan=J.Codplan AND C.Codtarjeta = J.Codtarjeta AND J.Codemp=T.Codemp) JOIN Cajas A (NOLOCK) ON (J.Codctabcopres=A.Codcaj AND T.Codemp=A.codemp AND A.Cajprefi=''C'' ) JOIN Cuentas CU (NOLOCK) ON A.Codcue=CU.Codcue LEFT JOIN Tablas Ta (NOLOCK) ON J.Codtab = Ta.Codtab LEFT JOIN Indices Ind (NOLOCK) ON Ta.Codtab = Ind.Codtab AND Ind.Fecha=(SELECT max(N.fecha) FROM Indices N (NOLOCK) WHERE N.codtab=Ta.codtab AND N.fecha <= @P2 ) WHERE C.Nrotransegr is null AND C.Codtarjeta=@P3 AND T.Nrotranselim is null AND T.Codemp=@P4 AND isnull(Cu.Codtab,'''') = isnull(@P5 ,'''') AND T.Codsuc=isnull(@P6 ,@P7 ) AND A.Cajprefi=''C'' AND ((T.Codcmp=''TV'' AND CR.Codcaj2 =@P8 ) OR (T.Codcmp<>''TV'' AND CR.Codcaj = @P9 )) ORDER BY C.Fecha', @P3 output, @P4 output, @P5 output, 'Jan 12 2008 12:00:00:000AM', 'Jan 12 2008 12:00:00:000AM', '04 ', 1.000000000000000e+000, NULL, NULL, 5.900000000000000e+001, '59 ', '59 '
    select @P1, @P2, @P3, @P4, @P5
    Execution plan without new index.

    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Compute Scalar(DEFINE:([Expr1013]=If ([C].[FECHA]<='Jan 12 2008 12:00AM') then 'S' else 'N', [Expr1014]=If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)), [Expr1015]=If
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Ta].[CODTAB]))
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([J].[CODTAB]))
    | |--Sort(ORDER BY:([C].[FECHA] ASC))
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[CODPAG]))
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[SECUENCIA], [CR].[REFCAJA], [T].[NROTRANS]) WITH PREFETCH)
    | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[NROTRANS], [T].[CODCMP]) WITH PREFETCH)
    | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[NROTRANS], [A].[CODEMP]) WITH PREFETCH)
    | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([J].[CODPLAN]) WITH PREFETCH)
    | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[CODCUE]) WITH PREFETCH)
    | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([J].[CODEMP], [J].[CODCTABCOPRES]))
    | | | | | | | | |--Clustered Index Scan(OBJECT:([Mimo].[dbo].[TARJETPLANES].[PK_TARJETPLANES] AS [J]), WHERE:([J].[CODTARJETA]='04'))
    | | | | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CAJAS].[PK_CAJAS] AS [A]), SEEK:([A].[CODEMP]=[J].[CODEMP] AND [A].[CAJPREFI]='C' AND [A].[CODCAJ]=[J].[CODCTABCOPRES]) ORDERED FORWARD)
    | | | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CUENTAS].[PK_CUENTAS] AS [CU]), SEEK:([CU].[CODCUE]=[A].[CODCUE]), WHERE:(isnull([CU].[CODTAB], 0)=0) ORDERED FORWARD)
    | | | | | | |--Index Seek(OBJECT:([Mimo].[dbo].[CUPONES].[IXC061107CTP_CUPONES_NrotanseGr_CodTarjeta_CodPlan_Eduval01] AS [C]), SEEK:([C].[NROTRANSEGR]=NULL AND [C].[CODTARJETA]='04' AND [C].[CODPLAN]=[J].[CODPLAN]) ORDER
    | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[TRANSAC].[PK_TRANSAC] AS [T]), SEEK:([T].[NROTRANS]=[C].[NROTRANS]), WHERE:(((([T].[CODEMP]=[A].[CODEMP] AND [T].[NROTRANSELIM]=NULL) AND Convert([T].[CODEMP])=1) AND
    | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CAJASREG].[PK_CAJASREG] AS [CR]), SEEK:([CR].[NROTRANS]=[T].[NROTRANS]), WHERE:((([T].[CODCMP]='TV' AND [CR].[CODCAJ2]='59 ') OR ([T].[CODCMP]<>'TV' AND [CR].[CODCAJ]='59
    | | | |--Index Seek(OBJECT:([Mimo].[dbo].[MVSCAJA].[IXC061107CTP_MVSCAJA_NroTrans_CodPag_RefCaja_Eduval01] AS [M]), SEEK:([M].[NROTRANS]=[T].[NROTRANS]), WHERE:([C].[SECUENCIA]=[M].[SECUENCIA] AND [CR].[REFCAJA]=[M].[REFCAJA]) OR
    | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[MEDIOSPAGO].[PK_MEDIOSPAGO] AS [P]), SEEK:([P].[CODPAG]=[M].[CODPAG]) ORDERED FORWARD)
    | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[TABLAS].[PK_TABLAS] AS [Ta]), SEEK:([Ta].[CODTAB]=[J].[CODTAB]) ORDERED FORWARD)
    |--Hash Match(Cache, HASH:([Ta].[CODTAB]), RESIDUAL:([Ta].[CODTAB]=[Ta].[CODTAB]))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011]))
    |--Stream Aggregate(DEFINE:([Expr1011]=MAX([N].[FECHA])))
    | |--Top(1)
    | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[INDICES].[PK_INDICES] AS [N]), SEEK:([N].[CODTAB]=[Ta].[CODTAB] AND [N].[FECHA] <= 'Jan 12 2008 12:00AM') ORDERED BACKWARD)
    |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[INDICES].[PK_INDICES] AS [Ind]), SEEK:([Ind].[CODTAB]=[Ta].[CODTAB] AND [Ind].[FECHA]=[Expr1011]) ORDERED FORWARD)
    After insert this index:
    CREATE NONCLUSTERED INDEX [IXC080109CTP_TRANSAC_CodEmp_CodSuc_NroTranselim_Balance01] ON [dbo].[TRANSAC]
    (
    [CODEMP] ASC,
    [CODSUC] ASC,
    [NROTRANSELIM] ASC,
    [FECHA] ASC,
    [NROTRANS] ASC
    ) with fillfactor = 90 on quinto
    go
    This is the execution plan:
    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |--Compute Scalar(DEFINE:([Expr1013]=If ([C].[FECHA]<='Jan 12 2008 12:00AM') then 'S' else 'N', [Expr1014]=If ([J].[CODTAB]=NULL) then Convert([M].[IMPORTE]) else ([M].[CANTIDAD]*isnull([Ind].[VALOR]/Convert([Ta].[FACTOR]), 1.00000000000)), [Expr1015]=If
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Ta].[CODTAB]))
    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([J].[CODTAB]))
    | |--Sort(ORDER BY:([C].[FECHA] ASC))
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([M].[CODPAG]))
    | | |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[SECUENCIA], [CR].[REFCAJA], [T].[NROTRANS]) WITH PREFETCH)
    | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([T].[NROTRANS], [T].[CODCMP]) WITH PREFETCH)
    | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[NROTRANS], [A].[CODEMP]) WITH PREFETCH)
    | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([J].[CODPLAN]) WITH PREFETCH)
    | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[CODCUE]) WITH PREFETCH)
    | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([J].[CODEMP], [J].[CODCTABCOPRES]))
    | | | | | | | | |--Clustered Index Scan(OBJECT:([Mimo].[dbo].[TARJETPLANES].[PK_TARJETPLANES] AS [J]), WHERE:([J].[CODTARJETA]='04'))
    | | | | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CAJAS].[PK_CAJAS] AS [A]), SEEK:([A].[CODEMP]=[J].[CODEMP] AND [A].[CAJPREFI]='C' AND [A].[CODCAJ]=[J].[CODCTABCOPRES]) ORDERED FORWARD)
    | | | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CUENTAS].[PK_CUENTAS] AS [CU]), SEEK:([CU].[CODCUE]=[A].[CODCUE]), WHERE:(isnull([CU].[CODTAB], 0)=0) ORDERED FORWARD)
    | | | | | | |--Index Seek(OBJECT:([Mimo].[dbo].[CUPONES].[IXC061107CTP_CUPONES_NrotanseGr_CodTarjeta_CodPlan_Eduval01] AS [C]), SEEK:([C].[NROTRANSEGR]=NULL AND [C].[CODTARJETA]='04' AND [C].[CODPLAN]=[J].[CODPLAN]) ORDER
    | | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[TRANSAC].[PK_TRANSAC] AS [T]), SEEK:([T].[NROTRANS]=[C].[NROTRANS]), WHERE:(((([T].[CODEMP]=[A].[CODEMP] AND [T].[NROTRANSELIM]=NULL) AND Convert([T].[CODEMP])=1) AND
    | | | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[CAJASREG].[PK_CAJASREG] AS [CR]), SEEK:([CR].[NROTRANS]=[T].[NROTRANS]), WHERE:((([T].[CODCMP]='TV' AND [CR].[CODCAJ2]='59 ') OR ([T].[CODCMP]<>'TV' AND [CR].[CODCAJ]='59
    | | | |--Index Seek(OBJECT:([Mimo].[dbo].[MVSCAJA].[IXC061107CTP_MVSCAJA_NroTrans_CodPag_RefCaja_Eduval01] AS [M]), SEEK:([M].[NROTRANS]=[T].[NROTRANS]), WHERE:([C].[SECUENCIA]=[M].[SECUENCIA] AND [CR].[REFCAJA]=[M].[REFCAJA]) OR
    | | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[MEDIOSPAGO].[PK_MEDIOSPAGO] AS [P]), SEEK:([P].[CODPAG]=[M].[CODPAG]) ORDERED FORWARD)
    | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[TABLAS].[PK_TABLAS] AS [Ta]), SEEK:([Ta].[CODTAB]=[J].[CODTAB]) ORDERED FORWARD)
    |--Hash Match(Cache, HASH:([Ta].[CODTAB]), RESIDUAL:([Ta].[CODTAB]=[Ta].[CODTAB]))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011]))
    |--Stream Aggregate(DEFINE:([Expr1011]=MAX([N].[FECHA])))
    | |--Top(1)
    | |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[INDICES].[PK_INDICES] AS [N]), SEEK:([N].[CODTAB]=[Ta].[CODTAB] AND [N].[FECHA] <= 'Jan 12 2008 12:00AM') ORDERED BACKWARD)
    |--Clustered Index Seek(OBJECT:([Mimo].[dbo].[INDICES].[PK_INDICES] AS [Ind]), SEEK:([Ind].[CODTAB]=[Ta].[CODTAB] AND [Ind].[FECHA]=[Expr1011]) ORDERED FORWARD)
    To me no difference.
    Now, running from application the duration is what I inform before.
  3. ndinakar Member

    I think I see your point now..after re-reading your posts. You are adding a new index which makes Query1 do more reads even though its plan hasnt changed..
    This is weird..
  4. ndinakar Member

    Btw, are you sure the increased reads is for that table TRASAC? Can you run the query under SET STATISTICS IO ON and check the reads?
  5. Luis Martin Moderator

    Ndinakar,
    I'm out of the office now. I'll be back with that information late.
    Thanks,
  6. Luis Martin Moderator

    Whiteout new index:
    Table 'INDICES'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
    Table 'TABLAS'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
    Table 'MEDIOSPAGO'. Scan count 132, logical reads 264, physical reads 2, read-ahead reads 0.
    Table 'MVSCAJA'. Scan count 132, logical reads 827, physical reads 0, read-ahead reads 14.
    Table 'CAJASREG'. Scan count 132, logical reads 565, physical reads 1, read-ahead reads 9.
    Table 'TRANSAC'. Scan count 9733, logical reads 35229, physical reads 27, read-ahead reads 415.
    Table 'CUPONES'. Scan count 25, logical reads 234, physical reads 3, read-ahead reads 70.
    Table 'CUENTAS'. Scan count 25, logical reads 75, physical reads 0, read-ahead reads 1.
    Table 'CAJAS'. Scan count 25, logical reads 50, physical reads 2, read-ahead reads 0.
    Table 'TARJETPLANES'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 5.
    With index:
    able 'INDICES'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
    Table 'TABLAS'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
    Table 'MEDIOSPAGO'. Scan count 132, logical reads 264, physical reads 2, read-ahead reads 0.
    Table 'MVSCAJA'. Scan count 132, logical reads 827, physical reads 0, read-ahead reads 14.
    Table 'CAJASREG'. Scan count 132, logical reads 551, physical reads 1, read-ahead reads 9.
    Table 'TRANSAC'. Scan count 9733, logical reads 35188, physical reads 27, read-ahead reads 415.
    Table 'CUPONES'. Scan count 25, logical reads 234, physical reads 3, read-ahead reads 70.
    Table 'CUENTAS'. Scan count 25, logical reads 75, physical reads 0, read-ahead reads 1.
    Table 'CAJAS'. Scan count 25, logical reads 50, physical reads 2, read-ahead reads 0.
    Table 'TARJETPLANES'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 5.
    Same thing. This are driving me crazy.
  7. ndinakar Member

    Hi Luis
    You said the reads have increased after you added the NC index right? Your stats IO output doesnt show any increase in reads. In fact the reads came down from 35229 to 35188..
  8. Luis Martin Moderator

    Yes, that's right.
    There is no problem when you execute the query using QA.
    But a big difference when you do the same using the application. That is why I don't understand what could be the problem in the application.
    Read my post again and you will find one (using profiler) with index and without .
    Thanks a lot pal!.
  9. ndinakar Member

  10. satya Moderator

    If its causing from application then check what kind of connectivity used such as OLEDB or ODBC and its better to use SQLNative client if its a SQL2005.
  11. Luis Martin Moderator

    No 2005 yet. But you give a good idea. I'll restore the db in 2005 fix with Nk suggest and test.
    With 2000 is using ODBC.
  12. Luis Martin Moderator

    Ndinakar, yes XXX table has more than one NC with column except clustered column.
    But, in any case, the execution plan show XXX using C index only.

Share This Page