SQL Server Performance

New PERFORMANCE Problems???

Discussion in 'Performance Tuning for DBAs' started by cgarcia, Jan 24, 2003.

  1. cgarcia New Member

    I have a problem with the Production Server. I#%92m developing a DSS system with SQL Server 7.0 SP 4.
    All the querys run slower in the Production Server!!!!!! and in the Developer Server and in others computers the querys run faster. For example a Query takes 1 hours to run in the Production Server and 20 minutes in the Developer Server. I can´t change the Querys!!! Always in the Production Server the last step are slower than the Developer Server and the other steps are faster.
    The Database, Indexes and so on are the same in all computers.
    I have 1024 MB of Virtual Memory and my TempDB have 1024 Mb and have Unrestricted File Growth.
    The SQL Configuration and the Databases and Indexes in Production and Developer Server are the same.
    I saw the Execution Plan is the same.
    It#%92s a dedicated Server. While I execute a Query only run it. I are running these querys through Query Analyser.


    1) I saw the Production Server have a COMPAQ Smart Array 3200 Array Controllers and the two RAIDS (0+1 and 5) are in 1 Port (Only have a Port). However, the Developer Server have a COMPAQ Smart-2DH Array Controllers and have the RAID 0+1 in the Port 1 and the RAID 5 in the Port 2. IS IT IMPORTANT???

    2) The Compaq utilities said the Server is OK. Could it be that in case of a determinated work charge (the DSS Querys) problems start???
    Could it be a bottleneck in the bus or any other thing???
    Wich counters can I analyse to know it????

    3) I read in a forum the XEON processors have some problems with SQL 7. Is it true???

    4) In the Query statistics I saw in the last step in the Production Server have read-ahead reads 8 and in the Developer Server have read-ahead reads 0 IS IT IMPORTANT??? Above I put the statistics and a Query example, all the Query are similar.


    What Happens???? What#%92s the Problem????
    Thanks.


    ***Production Server***
    COMPAQ Proliant 5500:
    2 Pentium Xeon 400
    1GB RAM
    Net 10/100
    COMPAQ Smart Array 3200 Array Controllers (1 Port)
    2 Hard Disk 4,3 GB Ultra Wide SCSI (RAID 0+1)
    4 Hard Disk 9,1 GB Ultra Wide SCSI (RAID 5)
    NT 4.0 SP6
    SQL 7 SP4

    ***DEVELOPER Server***
    COMPAQ Proliant 2500:
    2 Pentium Pro 200 512 Kb
    256 Mb RAM
    Net 10/100
    COMPAQ Smart-2DH Array Controllers (2 Ports)
    2 Hard Disk 2,1 GB Ultra Wide SCSI (RAID 0+1)
    4 Hard Disk 4,3 GB Ultra Wide SCSI (RAID 5)
    NT 4.0 SP6
    SQL 7 SP4



    QUERY EXAMPLE


    --Step0 - Duración: 0:01:56.56

    select a13.Anio_Mes_Facturacion Anio_Mes_Facturacion,
    a12.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT0401007OMD000
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Cliente_C a12
    on (a11.Cliente_Key = a12.Cliente_Key)
    join Dim_Tiempo_Facturacion a13
    on (a11.Fecha_Factura_Key = a13.Fecha_Facturacion_Key)
    where (a11.FAP = 'Factura'
    and a12.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a13.Anio_Facturacion in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD'))
    group by a13.Anio_Mes_Facturacion,
    a12.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step1 - Duración: 0:00:05.87
    select a13.Anio_Mes_Facturacion Anio_Mes_Facturacion,
    a12.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT0401007OMD001
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Cliente_C a12
    on (a11.Cliente_Key = a12.Cliente_Key)
    join Dim_Tiempo_Facturacion a13
    on (a11.Fecha_Factura_Key = a13.Fecha_Facturacion_Key)
    join Dim_Tipo_Transaccion a14
    on (a11.Tipo_Transaccion_Key = a14.Tipo_Transaccion_Key)
    where (a12.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a13.Anio_Facturacion in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a14.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Anio_Mes_Facturacion,
    a12.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step2 - Duración: 0:00:11.20
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT0401007OMD002
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_Year_To_Month a13
    on (a12.Anio_Mes_Facturacion = a13.Mes_Acum)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    where (a11.FAP = 'Factura'
    and a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD'))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step3 - Duración: 0:00:15.53
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT0401007OMD003
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_Year_To_Month a13
    on (a12.Anio_Mes_Facturacion = a13.Mes_Acum)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    join Dim_Tipo_Transaccion a16
    on (a11.Tipo_Transaccion_Key = a16.Tipo_Transaccion_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a16.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step4 - Duración: 0:00:21.36
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT0401007OMD004
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_last_12months_TAM a13
    on (a12.Anio_Mes_Facturacion = a13.mes_tam)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura')
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step5 - Duración: 0:00:21.18
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Modelo Agrupación_Modelo,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT0401007OMD005
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_last_12months_TAM a13
    on (a12.Anio_Mes_Facturacion = a13.mes_tam)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    join Dim_Tipo_Transaccion a16
    on (a11.Tipo_Transaccion_Key = a16.Tipo_Transaccion_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-12-19 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a16.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Modelo

    --Step6 - Duración: 0:10:41.14
    select distinct coalesce(pa1.Agrupación_Modelo, pa2.Agrupación_Modelo, pa6.Agrupación_Modelo) Agrupación_Modelo,
    a12.Comercial Comercial,
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) Cliente_Id,
    a12.Cliente_Desc Cliente_Desc,
    a11.Anio Anio_Facturacion,
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) Anio_Mes_Facturacion,
    a11.Nombre_Mes Nombre_Mes,
    pa1.WJXBFS1 WJXBFS1,
    pa2.WJXBFS1 WJXBFS2,
    pa3.WJXBFS1 WJXBFS3,
    pa4.WJXBFS1 WJXBFS4,
    pa5.WJXBFS1 WJXBFS5,
    pa6.WJXBFS1 WJXBFS6
    from #ZZT0401007OMD000 pa1
    full outer join #ZZT0401007OMD001 pa2
    on (pa1.Agrupación_Modelo = pa2.Agrupación_Modelo and
    pa1.Anio_Mes_Facturacion = pa2.Anio_Mes_Facturacion and
    pa1.Cliente_Id = pa2.Cliente_Id)
    full outer join #ZZT0401007OMD005 pa6
    on (coalesce(pa1.Agrupación_Modelo, pa2.Agrupación_Modelo) = pa6.Agrupación_Modelo and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion) = pa6.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id) = pa6.Cliente_Id)
    left outer join #ZZT0401007OMD002 pa3
    on (coalesce(pa1.Agrupación_Modelo, pa2.Agrupación_Modelo, pa6.Agrupación_Modelo) = pa3.Agrupación_Modelo and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa3.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa3.Cliente_Id)
    left outer join #ZZT0401007OMD003 pa4
    on (coalesce(pa1.Agrupación_Modelo, pa2.Agrupación_Modelo, pa6.Agrupación_Modelo) = pa4.Agrupación_Modelo and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa4.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa4.Cliente_Id)
    left outer join #ZZT0401007OMD004 pa5
    on (coalesce(pa1.Agrupación_Modelo, pa2.Agrupación_Modelo, pa6.Agrupación_Modelo) = pa5.Agrupación_Modelo and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa5.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa5.Cliente_Id)
    join Agr_Dim_Tiempo_Mes a11
    on (coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = a11.Mes_Key)
    join Dim_Cliente_C a12
    on (coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = a12.Cliente_Id)




    LAST STEP IN THE PRODUCTION SERVER (Poor Performance)

    Table '#ZZT0401007OMD004___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 284, physical reads 0, read-ahead reads 8.
    Table '#ZZT0401007OMD002___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 196, physical reads 0, read-ahead reads 8.
    Table '#ZZT0401007OMD003___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 196, physical reads 0, read-ahead reads 8.
    Table 'Dim_Cliente'. Scan count 2, logical reads 304, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.
    Table '#ZZT0401007OMD001___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 68, physical reads 0, read-ahead reads 8.
    Table '#ZZT0401007OMD005___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 284, physical reads 0, read-ahead reads 8.
    Table '#ZZT0401007OMD000___________________________________________________________________________________________________00000000012C'. Scan count 2, logical reads 67, physical reads 0, read-ahead reads 8.

    SQL Server Execution Times:
    CPU time = 453690 ms, elapsed time = 480799 ms.



    LAST STEP IN THE DEVELOPER SERVER (Correct Performance)

    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 50, logical reads 70, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 503, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_last_12months_TAM'. Scan count 12, logical reads 30, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 144, logical reads 326, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 61548 ms, elapsed time = 34482 ms.





    Claudio
  2. Chappy New Member

    I dont quite understand. If youre running the *exact* same query on both production and development servers, why does the production output show temporary tables, whereas dev output shows human readable table names ?

    I would not expect to see significant performance differences based purely on your RAID configuration. Also Im not sure what determines the read-aheads, but since it is reporting no physical reads anyway, Id guestimate this is not a big factor in the duration differences. You need to start lookin at performance monitor get an idea of potential bottlenecks. Double check my theory on the raid config, by looking at disk read queue length counter. Are the counters on both servers comparable?


  3. Chappy New Member

    Hm also another confusing thing in your output...<br />PRODUCTION -- CPU time = 453690 ms, elapsed time = 480799 ms.<br />DEVELOP -- CPU time = 61548 ms, elapsed time = 34482 ms.<br /><br />I dont understand how develop server CPU can take more than total elapsed time?<br />I was trying to compare the ratio of CPU time to total duration, to see if the production server was using a comparable ratio (a fairly unscientific exercise, but it might have highlighted something <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br />
  4. sqljunkie New Member

    A few things do not make sense in the data you posted at the end of the query. Besides the table names not matching the scan count, logical and read-ahead reads do not match. Are you sure you are comparing the same query on both systems? The CPU and Memory resources on the development box are significantly less than the production so there is no way the query should run slower. What else is running on the Production server when you gathered the statistics? Were the statistics gathered from the Production server when other people were running queries?

    Read-Ahead reads can come from Scan-type operations like Clustered Index Scan or Index scans or hopefully not a Table Scan. SQL Server is reading through an entire index so it is doing read aheads to keep the buffer full of data.
  5. bradmcgehee New Member

    I agree with Chappty and rortloff, we need some more explanation.

    But in general, I doubt that the hardware is the issue, but using Performance Monitor will help you to determine this.

    Most likely the problem is that the databases aren't identical, or the server configuration is not identical. If I were you, I would double-check this.

    There is also the very small possibility that physical disk fragmentation on the two systems is different and contributing somewhat to differences in performance, but I really doubt that it can account for all of the differences.

    Once you answer our questions, we will be glad to help more.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. Chappy New Member

    I think I now understand why the CPU time could be greater than elapsed time. I think it sums the time taken on both CPU's, which is why 61548ms of cpu time could be completed in only 34482ms.

    With this in mind, are you sure the production server is configured to allow sql server to use both CPU's ? This wouldnt account for such a big discrepancy alone, but certainly wouldnt help. Ensure the server processor properties match on both servers.

    On a sidenote I have been using Dual Xeon processors on several servers for quite a long time with sql 6.5/7 and 2000, and there are no known problems I have found.
  7. cgarcia New Member

    Thank you for your answers, I am sorry but I had the statistics saved in a Word file, although I think they are right. Now I executed other Query and I am sending you this statistics.

    Chappy, In both Servers I have selected in the server processor properties the two processors, Boost SQL Server priority on Windows NT and Use all available processors.
    Otherwise, The Database is the same and I execute the sp_configure command and the configuration is the same. I have only the essential Services running in the Production Server.
    The Server has the C (RAID 0+1) FAT with 512 bytes by sector and 8 sectors by cluster and the D (RAID 5) NTFS with 512 bytes by sector and 8 sectors by cluster.


    I executed another query from Query Analyser with nothing executing at that moment. All steps take less time in the production server than in the development server, only in the last one it takes lot of time. So the query takes more time in the production server than in the development server.

    I look forward for your answer.
    Thanks.



    --Step0
    select a13.Anio_Mes_Facturacion Anio_Mes_Facturacion,
    a12.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT040204SZMD000
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Cliente_C a12
    on (a11.Cliente_Key = a12.Cliente_Key)
    join Dim_Tiempo_Facturacion a13
    on (a11.Fecha_Factura_Key = a13.Fecha_Facturacion_Key)
    where (a11.FAP = 'Factura'
    and a12.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a13.Anio_Facturacion in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD'))
    group by a13.Anio_Mes_Facturacion,
    a12.Cliente_Id,
    a11.Agrupacion_Formato


    --Step1
    select a13.Anio_Mes_Facturacion Anio_Mes_Facturacion,
    a12.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT040204SZMD001
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Cliente_C a12
    on (a11.Cliente_Key = a12.Cliente_Key)
    join Dim_Tiempo_Facturacion a13
    on (a11.Fecha_Factura_Key = a13.Fecha_Facturacion_Key)
    join Dim_Tipo_Transaccion a14
    on (a11.Tipo_Transaccion_Key = a14.Tipo_Transaccion_Key)
    where (a12.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a13.Anio_Facturacion in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a14.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Anio_Mes_Facturacion,
    a12.Cliente_Id,
    a11.Agrupacion_Formato

    --Step2
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT040204SZMD002
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_Year_To_Month a13
    on (a12.Anio_Mes_Facturacion = a13.Mes_Acum)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    where (a11.FAP = 'Factura'
    and a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD'))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Formato


    --Step3
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT040204SZMD003
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_Year_To_Month a13
    on (a12.Anio_Mes_Facturacion = a13.Mes_Acum)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    join Dim_Tipo_Transaccion a16
    on (a11.Tipo_Transaccion_Key = a16.Tipo_Transaccion_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a16.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Formato

    --Step4
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Importe_Liquido) WJXBFS1
    into #ZZT040204SZMD004
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_last_12months_TAM a13
    on (a12.Anio_Mes_Facturacion = a13.mes_tam)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura')
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Formato

    --Step5
    select a13.Mes_Key Anio_Mes_Facturacion,
    a14.Cliente_Id Cliente_Id,
    a11.Agrupacion_Formato Agrupacion_Formato,
    sum(a11.Cantidad_Piezas) WJXBFS1
    into #ZZT040204SZMD005
    from Agr_Fact_Ventas_Lineas_C a11
    join Dim_Tiempo_Facturacion a12
    on (a11.Fecha_Factura_Key = a12.Fecha_Facturacion_Key)
    join Trans_last_12months_TAM a13
    on (a12.Anio_Mes_Facturacion = a13.mes_tam)
    join Dim_Cliente_C a14
    on (a11.Cliente_Key = a14.Cliente_Key)
    join Agr_Dim_Tiempo_Mes a15
    on (a13.Mes_Key = a15.Mes_Key)
    join Dim_Tipo_Transaccion a16
    on (a11.Tipo_Transaccion_Key = a16.Tipo_Transaccion_Key)
    where (a14.Tipo_Cliente <> 'T'
    and a11.Fecha_Alta between CONVERT(datetime, '1955-01-01 00:00:00', 120) and CONVERT(datetime, '2002-11-25 00:00:00', 120)
    and a15.Anio in (2002)
    and a11.Empresa_Key in (26, 34)
    and a11.Calidad in ('STD')
    and a11.FAP = 'Factura'
    and a16.Tipo_Transaccion_Id in (1, 100, 103, 104, 105, 106, 110, 116, 150, 151, 152, 153, 154, 160, 162, 5, 500, 501, 502, 503, 504, 505, 506, 600, 650))
    group by a13.Mes_Key,
    a14.Cliente_Id,
    a11.Agrupacion_Formato

    --Step6
    select distinct coalesce(pa1.Agrupacion_Formato, pa2.Agrupacion_Formato, pa6.Agrupacion_Formato) Agrupacion_Formato,
    a12.Comercial Comercial,
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) Cliente_Id,
    a12.Cliente_Desc Cliente_Desc,
    a11.Anio Anio_Facturacion,
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) Anio_Mes_Facturacion,
    a11.Nombre_Mes Nombre_Mes,
    pa1.WJXBFS1 WJXBFS1,
    pa2.WJXBFS1 WJXBFS2,
    pa3.WJXBFS1 WJXBFS3,
    pa4.WJXBFS1 WJXBFS4,
    pa5.WJXBFS1 WJXBFS5,
    pa6.WJXBFS1 WJXBFS6
    from #ZZT040204SZMD000 pa1
    full outer join #ZZT040204SZMD001 pa2
    on (pa1.Agrupacion_Formato = pa2.Agrupacion_Formato and
    pa1.Anio_Mes_Facturacion = pa2.Anio_Mes_Facturacion and
    pa1.Cliente_Id = pa2.Cliente_Id)
    full outer join #ZZT040204SZMD005 pa6
    on (coalesce(pa1.Agrupacion_Formato, pa2.Agrupacion_Formato) = pa6.Agrupacion_Formato and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion) = pa6.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id) = pa6.Cliente_Id)
    left outer join #ZZT040204SZMD002 pa3
    on (coalesce(pa1.Agrupacion_Formato, pa2.Agrupacion_Formato, pa6.Agrupacion_Formato) = pa3.Agrupacion_Formato and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa3.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa3.Cliente_Id)
    left outer join #ZZT040204SZMD003 pa4
    on (coalesce(pa1.Agrupacion_Formato, pa2.Agrupacion_Formato, pa6.Agrupacion_Formato) = pa4.Agrupacion_Formato and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa4.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa4.Cliente_Id)
    left outer join #ZZT040204SZMD004 pa5
    on (coalesce(pa1.Agrupacion_Formato, pa2.Agrupacion_Formato, pa6.Agrupacion_Formato) = pa5.Agrupacion_Formato and
    coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = pa5.Anio_Mes_Facturacion and
    coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = pa5.Cliente_Id)
    join Agr_Dim_Tiempo_Mes a11
    on (coalesce(pa1.Anio_Mes_Facturacion, pa2.Anio_Mes_Facturacion, pa6.Anio_Mes_Facturacion) = a11.Mes_Key)
    join Dim_Cliente_C a12
    on (coalesce(pa1.Cliente_Id, pa2.Cliente_Id, pa6.Cliente_Id) = a12.Cliente_Id)




    PRODUCTION SERVER



    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time:
    CPU time = 3875 ms, elapsed time = 5478 ms.


    SQL Server Execution Times:
    CPU time = 16 ms, elapsed time = 0 ms.

    (14969 row(s) affected)

    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5458, physical reads 0, read-ahead reads 5464.
    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 1, read-ahead reads 479.
    Table 'Dim_Tiempo'. Scan count 2, logical reads 63, physical reads 1, read-ahead reads 59.

    SQL Server Execution Times:
    CPU time = 5327 ms, elapsed time = 3647 ms.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 1, logical reads 5458, physical reads 0, read-ahead reads 0.

    (14969 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Dim_Tiempo'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 1, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 25, logical reads 50, physical reads 3, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 3735 ms, elapsed time = 3980 ms.

    (50562 row(s) affected)

    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5458, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 1, read-ahead reads 0.
    Table 'Trans_Year_To_Month'. Scan count 12, logical reads 32, physical reads 1, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 78, logical reads 188, physical reads 1, read-ahead reads 2.

    SQL Server Execution Times:
    CPU time = 13657 ms, elapsed time = 7904 ms.

    (50562 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5458, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 50, logical reads 75, physical reads 1, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_Year_To_Month'. Scan count 12, logical reads 31, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 78, logical reads 187, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 15532 ms, elapsed time = 9155 ms.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5458, physical reads 0, read-ahead reads 0.

    (75496 row(s) affected)

    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_last_12months_TAM'. Scan count 12, logical reads 31, physical reads 1, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 144, logical reads 327, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 21813 ms, elapsed time = 12161 ms.

    (75496 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5458, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 50, logical reads 72, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_last_12months_TAM'. Scan count 12, logical reads 30, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 144, logical reads 326, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 26405 ms, elapsed time = 14746 ms.


    ...........
    ...........

    (75569 row(s) affected)

    Table '#ZZT040204SZMD004___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 468, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD002___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 314, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD003___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 314, physical reads 0, read-ahead reads 8.
    Table 'Dim_Cliente'. Scan count 2, logical reads 502, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 2.
    Table '#ZZT040204SZMD005___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 468, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD001___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 94, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD000___________________________________________________________________________________________________00000000000B'. Scan count 2, logical reads 94, physical reads 0, read-ahead reads 8.

    SQL Server Execution Times:
    CPU time = 1805359 ms, elapsed time = 1889735 ms.





    DEVELOPMENT SERVER



    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time:
    CPU time = 23344 ms, elapsed time = 48871 ms.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.

    (14969 row(s) affected)

    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 4570.
    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 1, read-ahead reads 477.
    Table 'Dim_Tiempo'. Scan count 2, logical reads 63, physical reads 1, read-ahead reads 59.

    SQL Server Execution Times:
    CPU time = 10984 ms, elapsed time = 8326 ms.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 1, logical reads 5436, physical reads 0, read-ahead reads 0.

    (14969 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Dim_Tiempo'. Scan count 1, logical reads 63, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 1, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 25, logical reads 50, physical reads 3, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 7078 ms, elapsed time = 7195 ms.

    (50562 row(s) affected)

    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 1, read-ahead reads 0.
    Table 'Trans_Year_To_Month'. Scan count 12, logical reads 32, physical reads 1, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 78, logical reads 188, physical reads 1, read-ahead reads 2.

    SQL Server Execution Times:
    CPU time = 31048 ms, elapsed time = 17766 ms.

    (50562 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 50, logical reads 75, physical reads 1, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_Year_To_Month'. Scan count 12, logical reads 31, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 78, logical reads 187, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 36344 ms, elapsed time = 20824 ms.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 0.

    (75496 row(s) affected)

    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_last_12months_TAM'. Scan count 12, logical reads 31, physical reads 1, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 144, logical reads 327, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 51546 ms, elapsed time = 29018 ms.

    (75496 row(s) affected)

    Warning: Null value eliminated from aggregate.
    Table 'Agr_Fact_Ventas_Lineas_C'. Scan count 2, logical reads 5436, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tipo_Transaccion'. Scan count 50, logical reads 72, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.
    Table 'Trans_last_12months_TAM'. Scan count 12, logical reads 30, physical reads 0, read-ahead reads 0.
    Table 'Dim_Tiempo'. Scan count 144, logical reads 326, physical reads 0, read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 59734 ms, elapsed time = 33461 ms.


    ...........
    ...........

    (75569 row(s) affected)


    Table '#ZZT040204SZMD004___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 467, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD002___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 313, physical reads 0, read-ahead reads 0.
    Table '#ZZT040204SZMD003___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 313, physical reads 0, read-ahead reads 0.
    Table 'Dim_Cliente'. Scan count 2, logical reads 500, physical reads 0, read-ahead reads 0.
    Table 'Agr_Dim_Tiempo_Mes'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 2.
    Table '#ZZT040204SZMD005___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 467, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD001___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 92, physical reads 0, read-ahead reads 8.
    Table '#ZZT040204SZMD000___________________________________________________________________________________________________000000000016'. Scan count 2, logical reads 92, physical reads 0, read-ahead reads 8.

    SQL Server Execution Times:
    CPU time = 514405 ms, elapsed time = 529663 ms.


    Claudio
  8. bradmcgehee New Member

    This is certainly very strange. It appears that the execution plans are the same on both servers (I deduced this from examining the I/O for each table). But the CPU is 2 or time greater for the CPU execution time for each query. Based on the information I have, I still can't tell you why. My first guess would be to carefully examine the difference between the two server's CPUs, and also the differences between the server's I/O systems, as these are the most likely to cause the problem. I know you have already taken a look at this, but I would look again, as my best guess is that the problem is hardware-related (or perhaps driver related).

    One thing you might try is to run the SP on the production server and the test server, and watch all of the CPU-related counters, seeing if there is a significant difference between the two. This might provide a clue.



    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  9. cgarcia New Member

    In the Execution Plan I saw the last Query cost the 99% and in this Query the last but one Task cost 100% (Sort/Distinct Sort - I/O Cost: 3448310 - CPU Cost: 838203 - Row Count: 75380).

    I saw in the Production Server in the Performance Monitor:

    1) Avg. Disk Queue Length: Avg. 100!
    2) Avg. Disk Write Queue Length: Avg. 99,50!
    3) Avg. Disk Read Queue Length: Avg. 0,50!
    4) ProcessorDPCs Queued/sec: Avg.170
    5) Disk Transfers/sec: Avg. 60
    6) % Processor Time: Avg. 55

    Could you give me any advice.
    Thanks!



    Claudio
  10. bradmcgehee New Member

    So your avg disk queue length is 100. This is a lot, geneally, it should be less than 2 (except for occasional spikes). Also, your % Processer Time is 55%, which is a lot for a single query (I assume that no other processes were being run at this same time). I imagine that both of these are being caused by the Sort that is occuring. It is also a potential sign that your disk I/O subsystem is not able to handle this sort without causing a bottleneck, which is causing your performance problem.

    What are the Performance Monitor stats on the query when it is run on the development server? If they avg. disk queue length and % Processor Time is much smaller, then I am guessing that the I/O subsystem on the production server has some issue (such as old or incorrect drivers), or maybe its just plain slow. It's hard for me to tell based on the data you provided.

    This guess on my part assumes that your testing technique is consistent and correct.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  11. cgarcia New Member

    I confirm you, No other processes were being run at this same time.
    In the Development Server I saw Avg. Disk Queue Length: Avg. 0,74 but the % Processer Time is bigger. (Avg. 70%). I think it´s right because the proccessors are slower than the Production Server.

    When The SQL Books Online said: “When running on Windows NT, SQL Server performance can be improved further if the databases are created on disks formatted using NTFS and, specifically, 64-KB extent sizes”.
    It ‘s refered to I execute: “FORMAT C: /FS:NTFS /A:64K”????
    The 64-Kb extent sizes are necessary in Windows 2000 too??? Because when I installed the Windows 2000 I didn´t see 64 K (I saw 128 k... and so on).
    Could it be the problem or you would discard it???


    Claudio
  12. bradmcgehee New Member

    I really doubt if this is your problem, although it might play a minor part. Are you using NTFS now, or FAT? NTFS is the preferred format, not only for performance, but for better security.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page