none
Rendimiento consulta SQL RRS feed

  • Pregunta

  • Hola a todos.

    En un servidor con un SQL SERVER 2008, tengo la siguiente consulta:

    SELECT [dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCantidad0_0] AS [dbo_v_movimientosv_olapCantidad0_0],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapPrecio_venta0_1] AS [dbo_v_movimientosv_olapPrecio_venta0_1],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCantidad_facturada0_2] AS [dbo_v_movimientosv_olapCantidad_facturada0_2],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapPrecio_compra0_3] AS [dbo_v_movimientosv_olapPrecio_compra0_3],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCoste_Transporte0_4] AS [dbo_v_movimientosv_olapCoste_Transporte0_4],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapImporte0_5] AS [dbo_v_movimientosv_olapImporte0_5],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCoste_unitario0_6] AS [dbo_v_movimientosv_olapCoste_unitario0_6],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olap0_7] AS [dbo_v_movimientosv_olap0_7],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapPrecio_compra_Unitario0_8] AS [dbo_v_movimientosv_olapPrecio_compra_Unitario0_8],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCantidad_Real0_9] AS [dbo_v_movimientosv_olapCantidad_Real0_9],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapGrupo_contable_producto0_10] AS [dbo_v_movimientosv_olapGrupo_contable_producto0_10],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCodTransportista0_11] AS [dbo_v_movimientosv_olapCodTransportista0_11],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCodigoCliente0_12] AS [dbo_v_movimientosv_olapCodigoCliente0_12],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapccoste0_13] AS [dbo_v_movimientosv_olapccoste0_13],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCod__almacen0_14] AS [dbo_v_movimientosv_olapCod__almacen0_14],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapFecha_envio0_15] AS [dbo_v_movimientosv_olapFecha_envio0_15],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapRuta0_16] AS [dbo_v_movimientosv_olapRuta0_16],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapUnidad_medida0_17] AS [dbo_v_movimientosv_olapUnidad_medida0_17],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCodigoArticulo0_18] AS [dbo_v_movimientosv_olapCodigoArticulo0_18],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapTrimestre0_19] AS [dbo_v_movimientosv_olapTrimestre0_19],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapMes0_20] AS [dbo_v_movimientosv_olapMes0_20],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapTipo0_21] AS [dbo_v_movimientosv_olapTipo0_21],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapNumero0_22] AS [dbo_v_movimientosv_olapNumero0_22],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapN__linea0_23] AS [dbo_v_movimientosv_olapN__linea0_23],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapGrupo_contable_negocio0_24] AS [dbo_v_movimientosv_olapGrupo_contable_negocio0_24],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCodigo_Proveedor0_25] AS [dbo_v_movimientosv_olapCodigo_Proveedor0_25],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapEstado0_26] AS [dbo_v_movimientosv_olapEstado0_26],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCampaña0_27] AS [dbo_v_movimientosv_olapCampaña0_27],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapExpediente0_28] AS [dbo_v_movimientosv_olapExpediente0_28],[dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapCodigoClienteFact0_29] AS [dbo_v_movimientosv_olapCodigoClienteFact0_29],[dbo_v_Fincas_Ventas_18].[Ciudad] AS [dbo_v_Fincas_VentasCiudad1_0],[dbo_v_Fincas_Ventas_20].[Cliente] AS [dbo_v_Fincas_VentasCliente4_0]
    FROM 
    (
        SELECT [Cantidad] AS [dbo_v_movimientosv_olapCantidad0_0],[Precio_venta]AS [dbo_v_movimientosv_olapPrecio_venta0_1], [Cantidad_facturada] AS [dbo_v_movimientosv_olapCantidad_facturada0_2],[Precio_compra] AS [dbo_v_movimientosv_olapPrecio_compra0_3], [Coste_Transporte] AS [dbo_v_movimientosv_olapCoste_Transporte0_4], [Importe] AS [dbo_v_movimientosv_olapImporte0_5], [Coste_unitario] AS [dbo_v_movimientosv_olapCoste_unitario0_6], 1   AS [dbo_v_movimientosv_olap0_7], [Precio_compra_Unitario] AS [dbo_v_movimientosv_olapPrecio_compra_Unitario0_8], [Cantidad_Real] AS [dbo_v_movimientosv_olapCantidad_Real0_9],[Grupo_contable_producto] AS [dbo_v_movimientosv_olapGrupo_contable_producto0_10], [CodTransportista] AS [dbo_v_movimientosv_olapCodTransportista0_11], [CodigoCliente] AS [dbo_v_movimientosv_olapCodigoCliente0_12], [ccoste] AS [dbo_v_movimientosv_olapccoste0_13], [Cod__almacen] AS [dbo_v_movimientosv_olapCod__almacen0_14], [Fecha_envio] AS [dbo_v_movimientosv_olapFecha_envio0_15], [Ruta] AS [dbo_v_movimientosv_olapRuta0_16], [Unidad_medida] AS [dbo_v_movimientosv_olapUnidad_medida0_17], [CodigoArticulo] AS [dbo_v_movimientosv_olapCodigoArticulo0_18],[Trimestre] AS [dbo_v_movimientosv_olapTrimestre0_19], [Mes] AS [dbo_v_movimientosv_olapMes0_20], [Tipo] AS [dbo_v_movimientosv_olapTipo0_21], [Numero] AS [dbo_v_movimientosv_olapNumero0_22], [N__linea] AS [dbo_v_movimientosv_olapN__linea0_23], [Grupo_contable_negocio] AS [dbo_v_movimientosv_olapGrupo_contable_negocio0_24], [Codigo_Proveedor] AS [dbo_v_movimientosv_olapCodigo_Proveedor0_25], [Estado] AS [dbo_v_movimientosv_olapEstado0_26], [Campaña] AS [dbo_v_movimientosv_olapCampaña0_27], [Expediente] AS [dbo_v_movimientosv_olapExpediente0_28], [CodigoClienteFact] AS [dbo_v_movimientosv_olapCodigoClienteFact0_29]
    FROM 
    (
     SELECT     Tipo, Numero, CodigoCliente, NombreCliente, Grupo_contable_negocio, CodTransportista, Codigo_Proveedor, Ruta,  Cod__almacen, CodigoArticulo, Descripcion, Unidad_medida, Cantidad, Precio_venta, ccoste, ISNULL(Expediente, '                     ') AS Expediente, Cantidad_facturada, Envio_directo, N__pedido_compra, N__linea_pedido_compra, Grupo_contable_negocio_lin, Grupo_contable_producto, Estado, Precio_compra, Coste_Transporte, 
    Importe, Fecha_envio, N__linea, Coste_unitario, Precio_compra_Unitario, YEAR(Fecha_envio) AS Año, DATEPART(qq, Fecha_envio) AS Trimestre, MONTH(Fecha_envio) AS Mes, DAY(Fecha_envio) AS Dia, { fn MONTHNAME(Fecha_envio) } AS MesNom, Cantidad_Real, CONVERT(varchar, Fecha_envio, 111) AS Fecha_Format, dbo.fCampanya(CodigoArticulo, Fecha_envio) AS Campaña, CodigoClienteFact
    FROM  v_movimientosv_olap
     ) AS [v_movimientosv_olap]
    )  AS [dbo_v_movimientosv_olap],
    (
    SELECT     COD_FINCA, Nombre, Empresa, Expediente, Hectareas, Cultivo, Poligono, Parcela, SubParcela, Rendimiento, Analitica, Cliente, CodigoPostal, Ciudad, SAU, Tipo, Provincia_Codigo, NombreCliente
    FROM         v_Fincas_Ventas
    )  AS [dbo_v_Fincas_Ventas_18],
    (
    SELECT     COD_FINCA, Nombre, Empresa, Expediente, Hectareas, Cultivo, Poligono, Parcela, SubParcela, Rendimiento, Analitica, Cliente, CodigoPostal, Ciudad, SAU, Tipo, Provincia_Codigo, NombreCliente
    FROM         v_Fincas_Ventas
    ) AS [dbo_v_Fincas_Ventas_20]
      WHERE
      (
         (
         [dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapExpediente0_28]   =  [dbo_v_Fincas_Ventas_18].[COD_FINCA]
         )
         AND 
         (
         [dbo_v_movimientosv_olap].[dbo_v_movimientosv_olapExpediente0_28]   =  [dbo_v_Fincas_Ventas_20].[COD_FINCA]
         )
      )

    Dicha consulta, se procesa en 13 minutos devolviendo unos 175.000 registros (que son todos los que debe devolver).

    En otro servidor con un SQL SERVER 2014, con la misma base de datos de origen, la consulta no acaba de procesarse. En 1 hora, solo ha devuelto 800 registros.

    Si ejecuto a trozos cada select de la query general, las consultas se ejecutan y devuelven los resultados de manera correcta y en los tiempos esperados. El problema es cuando se ejecuta la consulta entera en la nueva versión de SQL.

    ¿Alguien sabe a que puede ser debido? ¿Es por la consulta que en la nueva versión no la asimila bien? ¿Se debe a algún parámetro de SQL 2014? Aunque son servidores distintos, cumplen los requisitos óptimos del SQL Server para cada versión.

    Saludos.


    P.D: En el primer servidor con el SQL 2008, la base de datos origen tiene un nivel de compatibilidad de SQL Server 2005 y en el segundo servidor con el SQL 2014, el nivel de compatibilidad de SQL Server es de 2008.
    • Editado raulsang miércoles, 16 de noviembre de 2016 10:06
    miércoles, 16 de noviembre de 2016 10:02

Respuestas

  • Hola a todos.

    Ya he podido solucionar el rendimiento de la consulta sin cambiarla. Lo que he hecho es cambiar en el segundo servidor con el SQL 2014, el nivel de compatibilidad de SQL Server de 2008 (que es el que tenía) al nivel de 2014 (el de la misma versión que la instancia instalada). Con este cambio, ahora la consulta se procesa entera correctamente y tarda unos 15 minutos en completarse (un tiempo más que aceptable).

    Yo creo que al traspasar la base de datos origen desde la versión anterior, también paso el nivel de compatibilidad a la versión más inferior aceptada por el nuevo servidor, como estaba en el anterior.

    De todas maneras, muchas gracias a todos por vuestro consejos que me han ayudado mucho a encontrar la solución.

    Hasta la próxima!

    • Marcado como respuesta raulsang jueves, 17 de noviembre de 2016 16:46
    jueves, 17 de noviembre de 2016 16:46

Todas las respuestas

  • Se necesitaría algo mas de información para aventurar alguna hipótesis:

    1) Ambos servidores manejan la misma cantidad de memoria?

    2) Verificaste que las tablas involucradas tienen los mismos índices en los dos servidores?

    3) Actualizaste estadísticas y reindexaste en el nuevo servidor?

    4) generaste los planes de ejecución en ambos servidores para verificar  si hay diferencias significantes?

    miércoles, 16 de noviembre de 2016 10:13
  • Se necesitaría algo mas de información para aventurar alguna hipótesis:

    1) Ambos servidores manejan la misma cantidad de memoria?

    No. El primero 8 GB (SQL2008) y el segundo 16 GB (SQL2014)

    2) Verificaste que las tablas involucradas tienen los mismos índices en los dos servidores?

    Si. En los dos servidores existe la misma base de datos con los mismos índices

    3) Actualizaste estadísticas y reindexaste en el nuevo servidor?

    Si.

    4) generaste los planes de ejecución en ambos servidores para verificar  si hay diferencias significantes?

    Si. Los planes arrojan el mismo resultado

    Hola Anibal. Te contesto en negrita y cursiva arriba
    miércoles, 16 de noviembre de 2016 10:30
  • Actualiza las estadísticas en el servidor donde no acaba de procesarse.

    Por otro lado el problema de rendimiento, vistas, sobre vistas, sobre vistas.... es bastante típico.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 16 de noviembre de 2016 10:31
    Moderador
  • Prueba descartando problemas de bloqueo, después de cada tabla en un From colócale  With (NoLock): Ej: FROM         v_Fincas_Ventas with (nolock)

    En el siguiente nivel habría que analizar con Profiler

    miércoles, 16 de noviembre de 2016 10:43
  • Hola Miguel.

    He vuelto a actualizar las estadísticas de la base de datos en el servidor con SQL2014 y sigue igual. Si, es posible que el rendimiento al realizar este tipo de consultas no sea el mejor, pero lo que me sorprende es que en el servidor de SQL viejo tenga un rendimiento mucho más grande que en el nuevo.

    Gracias.

    miércoles, 16 de noviembre de 2016 11:05
  • A veces pasa, pero no es lo normal.  En cualquier caso entiendo tu frustración. Te recomiendo que vayas en la consulta que mandabas por partes, revisando si alguna parte se va a ver beneficiada por ese índices. Con gusto te ayudaremos a encontrar esas mejoras


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 16 de noviembre de 2016 11:29
    Moderador
  • Y alguna manera de optimizar la consulta sin realizar vistas sobre vistas?

    Gracias.

    miércoles, 16 de noviembre de 2016 12:26
  • Seguro que si, pero tendrías que pasarnos la estructura e ir paso a paso. No parece que sea un query sencillo. Tampoco ayudan las funciones escalares como esta dbo.fCampanya()

    ¿Qué tan rápido es un select * FROM  v_movimientosv_olap?


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA


    miércoles, 16 de noviembre de 2016 13:05
    Moderador
  • Me pregunto si el optimizador esta usando la nueva estimacion de cardinalidad y si esta de alguna manera esta afectando el plan de ejecucion que se escogio.

    Chequea en el plan de ejecucion (modo XML) el atributo "CardinalityEstimationModelVersion", si es 120 entonces usa la nueva version de CE, sino entonces pudieras cambiar y usar el nuevo modelo (Compatibility Level a nivel de db).

    Pudieras probar un modelo diferente al actual si usas en el query la clausula OPTION con QUERYTRACEON.

    2312 -> nuevo modelo
    9481 -> modelo viejo

    Dejanos saber lo que encuentras para poder darte alguna mano y resolver tu problema.

    https://blogs.msdn.microsoft.com/psssql/2014/04/01/sql-server-2014s-new-cardinality-estimator-part-1/


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    miércoles, 16 de noviembre de 2016 14:16
  • Saludos

    Fuera de lo ya comentado por Miguel y Anival, las estadisticas fueron realizadas por full scan me espero, haz intentado cambiar el nivel de compatibilidad a 2014, este trae un nuevo estimador de cardinalidad que es posible que funcione mejor con la compatibilidad en 120.

    Si pudieras compartir los planes tambien seria una buena opcion para poder ayudarte.

    miércoles, 16 de noviembre de 2016 14:19
  • En este caso me inclinaría mas a que fuese al revés que el nuevo pudiera beneficiarle. El caso es que nuestro amigo comentaba en un mensaje esto

    "P.D: En el primer servidor con el SQL 2008, la base de datos origen tiene un nivel de compatibilidad de SQL Server 2005 y en el segundo servidor con el SQL 2014, el nivel de compatibilidad de SQL Server es de 2008."

    si no estoy equivocado, en ambos casos , por el nivel de compatibilidad, se usará el viejo estimador.


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 16 de noviembre de 2016 17:53
    Moderador
  • Saludos 


    Si lo lei y estoy seguro que en ambos casos esta usando el viejo por la compatibilidad.


    • Editado Enrique AA miércoles, 16 de noviembre de 2016 19:54
    miércoles, 16 de noviembre de 2016 17:57
  • Gracias, Miguel!

    Yo no lei esa parte y por eso le digo que pruebe ambos casos para que identifique cual version del CE le da mejor resultado.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    miércoles, 16 de noviembre de 2016 19:45
  • De nada!. A ver si el año que viene voy al MVP Open day que me muero de envidia  :)

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    jueves, 17 de noviembre de 2016 6:54
    Moderador
  • Hola Miguel.

    En 38 segundos devuelve todos los registros (unos 175.000)

    jueves, 17 de noviembre de 2016 12:58
  • Hola Hunchback.

    Este parámetro muestra lo siguiente:

    StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70"

    ¿Cómo cambio el valor del CardinalityEstimationModelVersion a 120 como comentas?

    Gracias

    jueves, 17 de noviembre de 2016 13:03
  • Hola Enrique.

    Si, la actualización de estadísticas fueron:

    Objetos -> Tablas y vistas

    Actualizar -> Todas las estadísticas existentes

    Tipo de examen -> Examen completo

    Gracias.

    jueves, 17 de noviembre de 2016 13:07
  • Puedes cambiar el nivel de compatibilidad de la db a 120 (SQL Server 2014) pero prueba primero en un servidor de desarrollo.

    Esto lo puedes hacer con el GUI de SSMS o con el comando ALTER DATABASE.

    https://msdn.microsoft.com/en-us/library/bb510680.aspx


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    jueves, 17 de noviembre de 2016 13:37
  • Hola a todos.

    Ya he podido solucionar el rendimiento de la consulta sin cambiarla. Lo que he hecho es cambiar en el segundo servidor con el SQL 2014, el nivel de compatibilidad de SQL Server de 2008 (que es el que tenía) al nivel de 2014 (el de la misma versión que la instancia instalada). Con este cambio, ahora la consulta se procesa entera correctamente y tarda unos 15 minutos en completarse (un tiempo más que aceptable).

    Yo creo que al traspasar la base de datos origen desde la versión anterior, también paso el nivel de compatibilidad a la versión más inferior aceptada por el nuevo servidor, como estaba en el anterior.

    De todas maneras, muchas gracias a todos por vuestro consejos que me han ayudado mucho a encontrar la solución.

    Hasta la próxima!

    • Marcado como respuesta raulsang jueves, 17 de noviembre de 2016 16:46
    jueves, 17 de noviembre de 2016 16:46
  • No es optimo realizar Select anidados podrias usar CTE(common table expression), aqui te paso un ejemplo Common Table Expression.

    Saludos,

    Jorge Muchaypiña G.


    Business Intelligence Analyst

    jueves, 17 de noviembre de 2016 20:24
  • Muchas gracias Jorge por tu apunte.

    Lo probaré!!!

    viernes, 18 de noviembre de 2016 13:26