none
Degradación de performance RRS feed

  • Pregunta

  • Hola a todos, que tal? espero que puedan ayudarme.

    Mi consulta es la siguiente, tengo programado un servicio de windows, que inserta información en la base de datos constantemente, practicamente sin respiro, solo a la noche tiene descanso.

    El asunto es que la performance se va degradando con el transcurso del tiempo, ejemplo, al reiniciar el servicio de sql server y retornar con el ingreso de datos, me está dando un registro de alrededor de 30 ingresos por segundo, a las horas de estar en ese registro constantemente el rendimiento empieza a caer lentamente hasta llegar a un promedio de ingreso de 3 por segundo.

    En algunos casos la información se ingresa por Entity Framework, pero la mayoría es todo sql desde C#, y en cada ingreso que comento arriba, generalmente tiene un promedio de 30 registros por ingreso, en algunos casos que llenan distintas tablas relacionadas.

     connection = (SqlConnection)contexto.Database.Connection;
                                            connection.Open();
                                            string query = "delete from detrela where idcompania=@idCompania and idempresa=@idEmpresa and idempfact=@idEmpFact and iddocumento=@idDocumento and letra=@Letra and serie=@Serie and nrodoc=@NroDoc " +
                                                           "delete from detlineas where idcompania=@idCompania and idempresa=@idEmpresa and idempfact=@idEmpFact and iddocumento=@idDocumento and letra=@Letra and serie=@Serie and nrodoc=@NroDoc " +
                                                           "delete from lineas where idcompania=@idCompania and idempresa=@idEmpresa and idempfact=@idEmpFact and iddocumento=@idDocumento and letra=@Letra and serie=@Serie and nrodoc=@NroDoc";
                                            string letra = (entmascara[0].letra == null) ? string.Empty : entmascara[0].letra;
                                            using (SqlCommand cmdDelete = new SqlCommand(query, connection))
                                            {
                                                cmdDelete.Parameters.AddWithValue("@idCompania", 1);
                                                cmdDelete.Parameters.AddWithValue("@idEmpresa", idEmpresa);
                                                cmdDelete.Parameters.AddWithValue("@idEmpFact", entmascara[0].idempresa);
                                                cmdDelete.Parameters.AddWithValue("@idDocumento", entmascara[0].iddocumento);
                                                cmdDelete.Parameters.AddWithValue("@Letra", letra);
                                                cmdDelete.Parameters.AddWithValue("@Serie", entmascara[0].serie);
                                                cmdDelete.Parameters.AddWithValue("@NroDoc", entmascara[0].nrodoc);
                                                cmdDelete.CommandType = CommandType.Text;
                                                int retVal = cmdDelete.ExecuteNonQuery();
                                            }
    
                                            foreach (ELinea entlinea in listalineas)
                                            {
                                                lineas li = new lineas
                                                {
                                                    idcompania = 1,
                                                    idempresa = idEmpresa,
                                                    idempfact = entlinea.idempresa,
                                                    iddocumento = entlinea.iddocumento,
                                                    letra = (entlinea.letra == null) ? string.Empty : entlinea.letra,
                                                    serie = Convert.ToInt32(entlinea.serie),
                                                    nrodoc = Convert.ToInt32(entlinea.nrodoc),
                                                    idlinea = Convert.ToInt32(entlinea.idLinea),
                                                    codart = Convert.ToInt32(entlinea.codart),
                                                    cant = Convert.ToInt32(entlinea.cant),
                                                    resto = Convert.ToInt32(entlinea.resto),
                                                    precio = entlinea.precio,
                                                    bonif = entlinea.bonif,
                                                    iva1 = entlinea.iva1,
                                                    iva2 = entlinea.iva2,
                                                    internos = entlinea.internos,
                                                    per3337 = entlinea.per3337,
                                                    perib = entlinea.perib,
                                                    per212 = entlinea.per212,
                                                    fechafac = entmascara[0].fechafac, //mascara
                                                    idcliente = entmascara[0].idcliente, //mascara
                                                    iddepo = entmascara[0].idDepo, //mascara
                                                    anulado = entmascara[0].anulado, //mascara
                                                    idcomp = entmascara[0].idComp, //mascara
                                                    codtipcmb = Convert.ToInt32(entlinea.codtipcmb),
                                                    iva1fis = entlinea.iva1fis, //iva1fis = //valor por default.
                                                    idlinpadre = Convert.ToInt32(entlinea.idlinpadre),
                                                    expcomboimp = entlinea.expcomboimp, //expcomboimp = //valor por default.
                                                    fecpedido = entmascara[0].fecpedido,
                                                    ndocpadre = entlinea.ndocpadre, //ndocpadre = //valor por default.
                                                    fecpaga = entmascara[0].fecpaga,
                                                    fecentre = entmascara[0].fecentre,
                                                    idsucur = entmascara[0].idSucur,
                                                    c_perso = (entmascara[0].c_perso == 0) ? null : entmascara[0].c_perso,
                                                    peso = entlinea.peso
                                                };
                                                contexto.lineas.Add(li);
                                            }
                                            if (listadetlinea != null)
                                            {
                                                foreach (EDetlinea dlin in listadetlinea)
                                                {
                                                    detlineas dlineas = new detlineas
                                                    {
                                                        idcompania = 1,
                                                        idempresa = idEmpresa,
                                                        idempfact = dlin.idempresa,
                                                        iddocumento = dlin.iddocumento,
                                                        letra = (dlin.letra == null) ? string.Empty : dlin.letra,
                                                        serie = dlin.serie,
                                                        nrodoc = dlin.nrodoc,
                                                        idlinea = dlin.idlinea,
                                                        codpromo = dlin.codpromo,
                                                        bonif = dlin.bonif,
                                                        idusuario = dlin.idusuario,
                                                        idcliente = entmascara[0].idcliente, //mascara
                                                        fechafac = entmascara[0].fechafac, //mascara
                                                        anulado = entmascara[0].anulado,
                                                        tipolin = (dlin.tipolin == null) ? string.Empty : dlin.tipolin//mascara
                                                    };
                                                    contexto.detlineas.Add(dlineas);
                                                }
                                            }
                                            if (listadetrela != null)
                                            {
                                                foreach (EDetrela dlet in listadetrela)
                                                {
                                                    detrela drela = new detrela
                                                    {
                                                        idcompania = 1,
                                                        idempresa = idEmpresa,
                                                        idempfact = dlet.idempresa,
                                                        iddocumento = dlet.iddocumento,
                                                        letra = (dlet.letra == null) ? string.Empty : dlet.letra,
                                                        serie = dlet.serie,
                                                        nrodoc = dlet.nrodoc,
                                                        idlinea = dlet.idLinea,
                                                        riddocumento = dlet.riddocumento,
                                                        rletra = (dlet.rletra == null) ? string.Empty : dlet.rletra,
                                                        rserie = dlet.rserie,
                                                        rnrodoc = dlet.rnrodoc,
                                                        ridlinea = dlet.ridLinea,
                                                        codart = dlet.codart,
                                                        cant = dlet.cant,
                                                        resto = dlet.resto
                                                    };
                                                    contexto.detrela.Add(drela);
                                                }
                                            }
    
                                            int resultado = contexto.SaveChanges();
                                            if (resultado != -1)
                                            {
                                                respuesta = "ok";
                                                transac.Complete();
                                            }
                                            else
                                            {
                                                respuesta = "error";
                                                transac.Dispose();
                                            }
                                        }

    Este código es uno de los que se realiza con entity y es el que empieza con 30 ingresos y despues degrada, lo que hace es simplemente un delete de los datos en la base en caso de que existan y luego inserta todo nuevamente, no tiene update.

    Mi pregunta es porque me puede estar pasando esto que el rendimiento de la base decae considerablemente?

    Si necesitan que pase algo más de información me dicen y la paso.

    Muchas gracias, espero que puedan ayudarme.

    Saludos.

    jueves, 9 de abril de 2015 19:17

Respuestas

  • Saludos el paralelismo y el network IO son waits, o tiempos de espera en los que SQL Server no esta haciendo nada porque esta esperando.

    Parallelismo tiene que ver con el numero de procesadores que usa tu maquina para hacer las operaciones (aunque en tus planes no los vi), seguramente si buscas la configuración en tu server esta en 0.

    http://sqlservertoolbox.blogspot.mx/2015/02/paralelismo-en-sql-server.html

    Network IO (aunque puede ser network) normalmente tiene que ver con que tu aplicación no puede consumir tan rápido los datos como sql se los envía lo cual causa que sql tenga que esperar la entrega de datos.

    Ahora veamos si tienes 28,000 millones de registros ahorita llevas 80,000 con unos 120,000 al día x digamos un promedio de 10 serian 1,200,000 cambios. Existe algo que se llaman auto estadísticas pero estas se disparan basados en cambios 20% a la base, o sea en tu caso 5.2 Millones.

    http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

    Tus índices son clustered, no tienes índices no clustered o los filtraste (estos no se pueden defragmentar)?.

    De inicio te diría que corras más seguido (posiblemente cada dia o menos) un upadate statistic with full scan.

    http://blogs.msdn.com/b/chrissk/archive/2008/08/27/do-we-need-to-run-update-statistics-with-fullscan.aspx

    Cualquier duda o comentario quedo a esperas de que nos comentes como van los cambios.

    • Marcado como respuesta gch1987 miércoles, 15 de abril de 2015 18:57
    viernes, 10 de abril de 2015 19:07
  • Saludos estaras disponible en unos 70 minutos? te podría ayudar en modo remoto si me lo permites en ese tiempo.

    mmm una pregunta porque lo mandas desde aqui y no haces un store procedure? Parece que le estas mandando un mal plan o esta usando un mal plan. En este caso lo unico que se me ocurre sin hacerlo store es o mejorar la consulta o ponerle que tome ad-hoc queries a SQL.
    • Editado Enrique AA martes, 14 de abril de 2015 23:26
    • Propuesto como respuesta Karen MalagónModerator miércoles, 15 de abril de 2015 16:26
    • Marcado como respuesta gch1987 miércoles, 15 de abril de 2015 18:57
    martes, 14 de abril de 2015 20:49

Todas las respuestas

  • gch1987,

    Como estas? hablandote desde el lado de base de datos.... te diria que te fijes en el plan de ejecucion de cada delete/insert para ver que es lo que realiza para realizar dichas acciones..... al ser tan transaccional puede ser que tengas bloqueos (por select update delete etc...)  para evitar esto te diria que habilites el snapshot isolation level...  empezamos por ahi y despues vemos que indices utiliza para las consultas (puede ser que tambien tengas fragmentacion por la cantidad de operaciones en ellos). Ademas agrega (si podes hacelo desde el managment studio) el set statistics io on para ver cuantas lecturas hacen tus qrys (me refiero a los select..... si va a memoria o si esta yendo mucho a disco)

    Saludos!!


    jueves, 9 de abril de 2015 19:47
  • Hola gonzalo, muchisimas gracias por tu pronta respuesta y por la información!

    Mañana a primera hora me pongo a buscar información sobre lo que me  mencionas, lo implemento y te mando la información que obtengo o como salieron las cosas.

    Muchas gracias.. Saludos!!

    jueves, 9 de abril de 2015 22:48
  • Saludos,

    Difiero un poco de Gonzalo, por default .net usa serialisable en el modelo de isolation level, cambiarlo por snapshoot no hara gran cambio.

    Por favor intentan buscar un query que nos diga que tan fragmentados estan tus indices, hace cuando se hizo el update statistics, subir el plan de ejecucción como menciona Gonzalo.

    Y revisemos tus wait stats con el query que encontraremos aqui.

    http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

    jueves, 9 de abril de 2015 23:32
  • Hola enrique muchas gracias,

    En estos momentos no estoy en el trabajo, mañana a primera hora cuándo este ahi realizó lo que me estan diciendo y lo subo aca para ver como estan las cosas.

    Muchas gracias..!! saludos..

    viernes, 10 de abril de 2015 0:11
  • Sugiero analizar el flujo de datos que tiene teniendo en cuenta lo siguiente para la tarea de borrado de lo anterior e inserción de lo nuevo:

    • Identificar sentencias costosas (ya sean los delete o los generados por el EF) utilizando SQL Profiler
    • Analizar los planes de ejecución de las sentencias mas demoradas
    • Volumen de registros afectados (indices teniendo en cuenta la distribucion de datos y los where)
    • Simplificar la tarea de inserción con sqlbulkcopy 

    viernes, 10 de abril de 2015 3:31
  • Hola a todos.. gracias por las respuestas. Me costo armar esto jaja, espero que se entienda.

    Disculpen mi ignorancia no soy muy entendido con la optimización de sql, aca mis dudas:

    - En cuanto al plan de ejecución de las operaciones, aca paso uno de las tantas que hay pero con sql, el metodo que indico arriba esta realizado por EF y nose como realizar el plan en eso. Pongo el código de sql y luego el plan de ejecución.

    Este metodo revisandolo hoy me creaba un cuello de botella, realentizandome los demas ingresos a traves del servicio de windows, este ya tiene una mejora, antes era distinto y despues de un tiempo iba demorando cada vez mas en ingresar los datos.

    create table #tmplincarga (idcompania int,  
                               idempresa int,  
                               idempfact int,  
                               idsucur int,  
                               nroped int,  
                               idlinea int,  
                               idcliente int,  
                               codart int,  
                               cant int,  
                               resto int,  
                               cantbultos decimal(15, 6),  
                               cantunimed decimal(15, 6),  
                               peso decimal(15, 6),  
                               precio decimal(15, 6),  
                               bonif decimal(5, 2),  
                               fecentre date,  
                               iva1 decimal(15, 6),  
                               iva2 decimal(15, 6),  
                               internos decimal(15, 6),  
                               per3337 decimal(15, 6),  
                               perib decimal(15, 6),  
                               per212 decimal(15, 6),  
                               codtipcmb int,  
                               iddocumento char(5),  
                               idcomp char(1),  
                               idlinpadre int,  
                               c_perso int,  
                               fecalta date,  
                               iddepo int,  
                               origen varchar(50),  
                               operador varchar(50));
    
    insert into #tmplincarga values (1,20,1,2,393854,1,21031,2853,3,0,3.000000,0.241200,0.000000,0.000000,0.00,'2015-04-09',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0,'FCVTA','P',0,32,'2015-04-07',2,'PREVENTA','MBENITEZ')
    insert into #tmplincarga values (1,20,1,2,393854,3,21031,823,1,0,1.000000,0.180000,0.000000,0.000000,0.00,'2015-04-09',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0,'FCVTA','P',0,32,'2015-04-07',2,'PREVENTA','MBENITEZ')
    insert into #tmplincarga values (1,20,1,2,393854,4,21031,2458,2,0,2.000000,0.120000,0.000000,0.000000,0.00,'2015-04-09',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0,'FCVTA','P',0,32,'2015-04-07',2,'PREVENTA','MBENITEZ')
    insert into #tmplincarga values (1,20,1,2,393854,5,21031,7633,12,0,15.000000,1.260000,0.000000,0.000000,0.00,'2015-04-09',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0,'FCVTA','P',0,32,'2015-04-07',2,'PREVENTA','MBENITEZ')
    insert into #tmplincarga values (1,20,1,2,393854,6,21031,7633,12,0,15.000000,1.260000,0.000000,0.000000,0.00,'2015-04-09',0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0,'FCVTA','P',0,32,'2015-04-07',2,'PREVENTA','MBENITEZ')
    
    select * from #tmplincarga
    
    update #tmplincarga
    set cantbultos=tcalculos.cantbultoscalculo, cantunimed=tcalculos.cantunimedcalculo
    from #tmplincarga tmp, (select lc.idcompania,lc.idempresa,lc.idempfact,ar.codart,lc.cant,lc.resto as restolc,
                              ar.resto,ar.valor,td.iddocumento,td.estadisticas,lc.idlinea, lc.nroped, lc.idsucur,  
                              CASE td.estadisticas
                                WHEN '-'    
                                    THEN CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * -1         
                                WHEN ''    
                                    THEN 0
                                ELSE CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto)))
                              END as cantbultoscalculo,
                              CASE td.estadisticas
                                WHEN '-'     
                                    THEN CONVERT(decimal(15,6),CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * ar.valor) * -1
                                WHEN ''    
                                    THEN 0
                                ELSE CONVERT(decimal(15,6),CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * ar.valor)  
                              END  as cantunimedcalculo
                              from articulos ar,#tmplincarga lc, tipodocs td  
                              where ar.idcompania=lc.idcompania  
                              and ar.codart=lc.codart  
                              and lc.idcompania=td.idcompania  
                              and lc.iddocumento=td.iddocumento) tcalculos                  
    where tmp.idcompania=tcalculos.idcompania
        and tmp.idempresa=tcalculos.idempresa
        and tmp.idempfact=tcalculos.idempfact
        and tmp.idsucur=tcalculos.idsucur
        and tmp.nroped=tcalculos.nroped
        and tmp.idlinea=tcalculos.idlinea
    
    select * from #tmplincarga  
        
    
     merge into lincarga as Target  
      using (SELECT idcompania,idempresa,idempfact,idsucur,nroped,idlinea,idcliente,codart,cant,resto,cantbultos,cantunimed,
               peso,precio,bonif,fecentre,iva1,iva2,internos,per3337,perib,per212,codtipcmb,iddocumento,idcomp,idlinpadre,
               c_perso,fecalta,iddepo,origen,operador FROM  
               (SELECT idcompania,idempresa,idempfact,idsucur,nroped,idlinea,idcliente,codart,cant,resto,cantbultos,cantunimed,
                    peso,precio,bonif,fecentre,iva1,iva2,internos,per3337,perib,per212,codtipcmb,iddocumento,idcomp,idlinpadre,
                    c_perso,fecalta,iddepo,origen,operador FROM #tmplincarga  
                    where idcompania=1 and idempresa=20 and idempfact=1 and idsucur=2 and nroped=393854  
                  EXCEPT  
                SELECT idcompania,idempresa,idempfact,idsucur,nroped,idlinea,idcliente,codart,cant,resto,cantbultos,cantunimed,
                    peso,precio,bonif,fecentre,iva1,iva2,internos,per3337,perib,per212,codtipcmb,iddocumento,idcomp,idlinpadre,
                    c_perso,fecalta,iddepo,origen,operador FROM lincarga) as tdif) as Source  
      on  
      Target.idcompania=Source.idcompania  
      and  
      Target.idempresa=Source.idempresa  
      and  
      Target.idempfact=Source.idempfact  
      and  
      Target.idsucur=Source.idsucur  
      and  
      Target.nroped=Source.nroped  
      and  
      Target.idlinea=Source.idlinea  
      when matched then  
          update set  
          Target.idcliente=Source.idcliente,  
          Target.codart=Source.codart,  
          Target.cant=Source.cant,  
          Target.resto=Source.resto,  
          Target.cantbultos=Source.cantbultos,  
          Target.cantunimed=Source.cantunimed,  
          Target.peso=Source.peso,  
          Target.precio=Source.precio,  
          Target.bonif=Source.bonif,  
          Target.fecentre=Source.fecentre,  
          Target.iva1=Source.iva1,  
          Target.iva2=Source.iva2,  
          Target.internos=Source.internos,  
          Target.per3337=Source.per3337,  
          Target.perib=Source.perib,  
          Target.per212=Source.per212,  
          Target.codtipcmb=Source.codtipcmb,  
          Target.iddocumento=Source.iddocumento,  
          Target.idcomp=Source.idcomp,  
          Target.idlinpadre=Source.idlinpadre,  
          Target.c_perso=Source.c_perso,  
          Target.fecalta=Source.fecalta,  
          Target.iddepo=Source.iddepo,  
          Target.origen=Source.origen,  
          Target.operador=Source.operador  
      when not matched then  
          insert (idcompania,idempresa,idempfact,idsucur,nroped,idlinea,idcliente,codart,cant,resto,cantbultos,cantunimed,peso,precio,bonif,fecentre,iva1,iva2,internos,per3337,perib,per212,codtipcmb,iddocumento,idcomp,idlinpadre,c_perso,fecalta,iddepo,origen,operador)  
          values (Source.idcompania,Source.idempresa,Source.idempfact,Source.idsucur,Source.nroped,Source.idlinea,Source.idcliente,Source.codart,Source.cant,Source.resto,Source.cantbultos,Source.cantunimed,Source.peso,Source.precio,Source.bonif,Source.fecentre,Source.iva1,Source.iva2,Source.internos,Source.per3337,Source.perib,Source.per212,Source.codtipcmb,Source.iddocumento,Source.idcomp,Source.idlinpadre,Source.c_perso,Source.fecalta,Source.iddepo,Source.origen,Source.operador)  
      WHEN NOT MATCHED BY SOURCE  
      AND Target.nroped IN(SELECT nroped FROM #tmplincarga where idcompania=Target.idcompania and idempresa=Target.idempresa and idempfact=Target.idempfact and idsucur=Target.idsucur) THEN  
          DELETE    
      OUTPUT $action as accion, deleted.*, inserted.*;
     
    drop table #tmplincarga

     Perdon por los links a parte pero no me deja mandarlos, me dice que no puedo hasta que verifiquen mi cuenta, así que los pongo al lado.

     Plan de ejecución:

        Imagen 1 - http://s11.postimg.org/jvsnjd3oz/imgeplan1.png

        Imagen 2 -  http://s4.postimg.org/ka8upzwwd/imageplan2.png

        Imagen 3 - http://s16.postimg.org/um26ewd51/imageplan3.png

       Parti las imagenes era muy largo, las siguientes son todas de la misma query (la 9)

           Imagen4 - http://s17.postimg.org/q71rqim67/imageplan4.png

           Imagen5  - http://s22.postimg.org/4njoeqrpd/imageplan5.png

           Imagen6 - http://s16.postimg.org/qwd3zluv9/imageplan6.png

     En formato xml ahora lo paso en otro mensaje, sino me queda muy largo.

    - El update statistics por lo que estuve viendo, es como un rearmar o reordenar los indices?, si es así, a mi entender no lo realizaron nunca, me parece.

    - Pude ejecutar la query de los waits y me arrojo esto

        Link: http://s3.postimg.org/c9jhgunr7/wait_statistics.png

    Bueno espero que puedan ayudarme o guiarme un poco para poder mejorar este problema que la base se satura. Desde ya muchas gracias a todos.

    SAludos!


    viernes, 10 de abril de 2015 17:09
  • Gracias, en el mensaje de arriba puse los stats como para ver que puede estar pasando..

    Saludos..

    viernes, 10 de abril de 2015 17:12
  • Hola gonzalo, ahi mande los stats para ver que puede estar pasando..

    Gracias.. Saludos!

    viernes, 10 de abril de 2015 17:13
  • EL plan necesitamos el .sqlplan para poder bien los detalles, veo alto paralelismo en uso y posible lentitud en la capa aplicativa en su consumo.

    Por lo que veo en el plan están bien tus índices (de menos su uso aunque no pusiste su fragmentación)

    El problema fue la tempdb y la tabla que creas y los table scans.

    Pregunta importante cuantos registros tiene tu base y cuantos se actualizan en promedio por dia?.

    viernes, 10 de abril de 2015 17:52
  • Hola enrique, la tabla temporal la creó porque los datos que recibo no los puedo recuperar de la base y necesito tenerlos en una tabla para realizarle los calculos, esta mal esa query?

     - No entendí muy bien esto "veo alto paralelismo en uso y posible lentitud en la capa aplicativa en su consumo."

     - La cantidad total de registros que tiene la base ahora es de 28.134.384 de registros.

     -  En cuanto a la cantidad de movimientos por día, por lo que pude ver ahora en este momento tengo alrededor de 80.000 ingresos hasta recien, cada uno de esos ingresos nose puede que tenga alrededor de 10 o 20 (en algunos casos más) registros que se ingresan o actualizan en distintas tablas.

     - Te paso el resultado de la query de fragmentación de los índices

              LINK: http://s3.postimg.org/qjn2sgzwj/fragmentacion.png

     - Necesitas que suba el archivo del plan o ya con eso pudiste revisarlo?

    El tema es que al reiniciar el servicio de sql.. los ingresos se hacen muy rapidos, pero al paso de las horas decae notablemente!

    Gracias por tu ayuda... saludos!

    viernes, 10 de abril de 2015 18:54
  • Saludos el paralelismo y el network IO son waits, o tiempos de espera en los que SQL Server no esta haciendo nada porque esta esperando.

    Parallelismo tiene que ver con el numero de procesadores que usa tu maquina para hacer las operaciones (aunque en tus planes no los vi), seguramente si buscas la configuración en tu server esta en 0.

    http://sqlservertoolbox.blogspot.mx/2015/02/paralelismo-en-sql-server.html

    Network IO (aunque puede ser network) normalmente tiene que ver con que tu aplicación no puede consumir tan rápido los datos como sql se los envía lo cual causa que sql tenga que esperar la entrega de datos.

    Ahora veamos si tienes 28,000 millones de registros ahorita llevas 80,000 con unos 120,000 al día x digamos un promedio de 10 serian 1,200,000 cambios. Existe algo que se llaman auto estadísticas pero estas se disparan basados en cambios 20% a la base, o sea en tu caso 5.2 Millones.

    http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

    Tus índices son clustered, no tienes índices no clustered o los filtraste (estos no se pueden defragmentar)?.

    De inicio te diría que corras más seguido (posiblemente cada dia o menos) un upadate statistic with full scan.

    http://blogs.msdn.com/b/chrissk/archive/2008/08/27/do-we-need-to-run-update-statistics-with-fullscan.aspx

    Cualquier duda o comentario quedo a esperas de que nos comentes como van los cambios.

    • Marcado como respuesta gch1987 miércoles, 15 de abril de 2015 18:57
    viernes, 10 de abril de 2015 19:07
  • Hola enrique, muchas gracias por tu información me re sirvieron para revisar y aprender mas cosas..

    Definitivamente la configuración del paralelismo esta en 0.

    Los índices son clustered, por el momento no tenemos otros, pero hay que agregarlos porque las tablas se estan cargando demasiado, lo que no entendí es cuales no se pueden desfragmentar si los clustered o los no clustered?

    En cuanto a los update statistic with full scan, lo vamos a hacer como decis correrlo mas seguido.. voy a profundizarme un poco mas y ponerlo en marcha.

    Gracias por todo!! el lunes comento como fueron las cosas implementando los pasos que me decis, en estos momentos estoy fuera del trabajo y no puedo probar las cosas.. muy agradecido enrique.. el lunes te comento.. buen finde!

    SAludos..!

    viernes, 10 de abril de 2015 22:33
  • Saludos

    Los indices clustered so aquellos que no pueden ser defragmentados. Con todo gusto el lunes vemos como va esto, suerte y buen fin de semana.

    viernes, 10 de abril de 2015 23:28
  • Hola enrique como va? perdon por la demora, surgió otro incoveniente y recien hoy me pude poner, estuve investigando lo que me decias..

     1 ) Te comento aplicamos la desfragmentación de los indices..

      - Utilice la siguiente query para detectar los indices fragmentados

    SELECT DB_NAME(database_id) AS DatabaseName, database_id, 
    OBJECT_NAME(ips.object_id) AS TableName, ips.object_id,
    i.name AS IndexName, i.index_id, p.rows,
    ips.partition_number, index_type_desc, alloc_unit_type_desc, index_depth, index_level,
    avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count,
    avg_page_space_used_in_percent, record_count, ghost_record_count, version_ghost_record_count, min_record_size_in_bytes,
    max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ips
    INNER JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
    INNER JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 10.0 AND ips.index_id > 0 AND page_count > 1000
    ORDER BY avg_fragmentation_in_percent DESC


       - Y luego aplique la desfragmentacion de los indices con esta query:

    -- Ensure a USE  statement has been executed first.
    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(130); 
    DECLARE @objectname nvarchar(130); 
    DECLARE @indexname nvarchar(130); 
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @command nvarchar(4000); 
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
    -- and convert object and index IDs to names.
    SELECT
        object_id AS objectid,
        index_id AS indexid,
        partition_number AS partitionnum,
        avg_fragmentation_in_percent AS frag
    INTO #work_to_do
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;
    
    -- Declare the cursor for the list of partitions to be processed.
    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
    
    -- Open the cursor.
    OPEN partitions;
    
    -- Loop through the partitions.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT
               FROM partitions
               INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas as s ON s.schema_id = o.schema_id
            WHERE o.object_id = @objectid;
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE  object_id = @objectid AND index_id = @indexid;
            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;
    
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
            IF @frag < 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
            IF @partitioncount > 1
                SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
            PRINT N'Executed: ' + @command;
        END;
    
    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;
    
    -- Drop the temporary table.
    DROP TABLE #work_to_do;
    GO

       - Por ende me acomodo los indices fragmentados, al menos al ejecutar de nuevo la query no me trajo ninguno mas.

      2) Realice los update statics with full scan con esta query:

     
     SET NOCOUNT ON;
    -- Declaración de Variables --
    Declare
    @Table varchar(30),
    @Static varchar(30),
    @SQL nvarchar(MAX),
    @SampleSize varchar(20)
     Set @SampleSize = ' WITH FULLSCAN'
    
     -- Declaro cursor --
     Declare Cur_Stats Cursor For
    
     -- Guardo en el Cursor --
     SELECT
     distinct
     OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) as 'Table'
     FROM sys.stats st WITH (nolock)
      --
     Join sys.stats_columns AS scol (NOLOCK)
     ON st.stats_id = scol.stats_id AND
      st.object_id = scol.object_id  
      WHERE
      STATS_DATE(st.object_id, st.stats_id) is not null and -- que alguna vez se hayan actualizado
      DATEDIFF(DAY, STATS_DATE(st.object_id, st.stats_id), GETDATE()) > 6 and --ult upd > 6 d
      OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)not like'sys%' -- que no   --sean objetos del sistema
      ORDER BY
      OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)
    
    
       -- Abro el Cursor -- 
      Open Cur_Stats
    
       -- Lo recorro --
       Fetch Next From Cur_Stats
            into
            @Table
        --
           While @@FETCH_STATUS = 0
            Begin
                 SET @SQL = N'UPDATE STATISTICS ' + @Table +@SampleSize
                 EXEC sp_executesql @SQL
                       
                   --Paso al siguiente valor del vector
                  Fetch Next From Cur_Stats
                   into
                      @Table
            End
             
        -- Cierro vector --
        Close Cur_Stats
    
          -- Lo saco de memoria --
         Deallocate Cur_Stats 


     - Y me actualizo las estadisticas en todas las tablas, creo que demoro 06:30 minutos en hacer ese update statics, me fije y estan actualizadas todas a la fecha de hoy.

     - Aclaro, obviamente ninguna de estas querys son mias jaj las busque para ejecutarlas.

    La performance no mejoro, sigue en el mismo estado. El tema es que sigo con el problema y ahora es especificamente en una tabla, el log me esta dando un registro cada 3 segundos y no lo puedo acomodar, ya nose que hacer.

    Esta es la query de la consulta, la tabla tendra 500.000 registros

    DataTable tablaLinCargas = new DataTable(); foreach (ELincarga ped in lincarga) { DataRow fila = tablaLinCargas.NewRow();

    //aca lleno la tabla que viene para insertar a sql

    } string sqlQuerydel = "delete from lincarga " + "where idcompania=@idCompania " + "and idempresa=@idEmpresa " + "and idempfact=@idEmpFact " + "and idsucur=@idSucur " + "and nroped=@nroPed"; int idEmpFact = Convert.ToInt32(carga[0].idempresa); int idSucur = Convert.ToInt32(lincarga[0].idsucur); int nroPed = Convert.ToInt32(lincarga[0].nroped); dbconn.Open(); cmd.Connection = dbconn; using (SqlCommand cmdDelete = new SqlCommand(sqlQuerydel, dbconn)) { cmdDelete.Parameters.AddWithValue("@idCompania", 1); cmdDelete.Parameters.AddWithValue("@idEmpresa", idEmpresa); cmdDelete.Parameters.AddWithValue("@idEmpFact", idEmpFact); cmdDelete.Parameters.AddWithValue("@idSucur", idSucur); cmdDelete.Parameters.AddWithValue("@nroPed", nroPed); cmdDelete.CommandType = CommandType.Text; int retVal = cmdDelete.ExecuteNonQuery(); } using (SqlBulkCopy bulkInsert = new SqlBulkCopy(dbconn)) { bulkInsert.DestinationTableName = "lincarga"; bulkInsert.WriteToServer(tablaLinCargas); } string queryCalculo = @"update lincarga set cantbultos=tcalculos.cantbultoscalculo, cantunimed=tcalculos.cantunimedcalculo from lincarga tmp, (select lc.idcompania,lc.idempresa,lc.idempfact,ar.codart,lc.cant,lc.resto as restolc, ar.resto,ar.valor,td.iddocumento,td.estadisticas,lc.idlinea, lc.nroped, lc.idsucur, CASE td.estadisticas WHEN '-' THEN CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * -1 WHEN '' THEN 0 ELSE CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) END as cantbultoscalculo, CASE td.estadisticas WHEN '-' THEN CONVERT(decimal(15,6),CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * ar.valor) * -1 WHEN '' THEN 0 ELSE CONVERT(decimal(15,6),CONVERT(decimal(15,6),lc.cant + (CONVERT(decimal(15,6),lc.resto) / CONVERT(decimal(15,6),ar.resto))) * ar.valor) END as cantunimedcalculo from articulos ar,lincarga lc, tipodocs td where ar.idcompania=lc.idcompania and ar.codart=lc.codart and lc.idcompania=td.idcompania and lc.iddocumento=td.iddocumento) tcalculos where tmp.idcompania=tcalculos.idcompania and tmp.idempresa=tcalculos.idempresa and tmp.idempfact=tcalculos.idempfact and tmp.idsucur=tcalculos.idsucur and tmp.nroped=tcalculos.nroped and tmp.idlinea=tcalculos.idlinea"; cmd.CommandText = queryCalculo; cmd.ExecuteNonQuery(); dbconn.Close(); dbconn.Dispose(); respuesta = "ok";

    La verdad nose si hago algo mal en esa query que me pueda estar realentizando el entorno, me parece que no, me parece que es algo general y en esa tabla especificamente ahora tengo el cuello de botella.

    Nose que más puedo hacer, desfragmente indices y actualice estadisticas, sera un problema de memoria? tendre que liberarle memoria a sql cada una hora como lei en algunos foros?

    Muchas gracias enrique por tu ayuda y paciencia!

    Saludos..!




    martes, 14 de abril de 2015 19:39
  • Saludos estaras disponible en unos 70 minutos? te podría ayudar en modo remoto si me lo permites en ese tiempo.

    mmm una pregunta porque lo mandas desde aqui y no haces un store procedure? Parece que le estas mandando un mal plan o esta usando un mal plan. En este caso lo unico que se me ocurre sin hacerlo store es o mejorar la consulta o ponerle que tome ad-hoc queries a SQL.
    • Editado Enrique AA martes, 14 de abril de 2015 23:26
    • Propuesto como respuesta Karen MalagónModerator miércoles, 15 de abril de 2015 16:26
    • Marcado como respuesta gch1987 miércoles, 15 de abril de 2015 18:57
    martes, 14 de abril de 2015 20:49
  • Hola enrique, como estas? ayer no pude entrar estuve con problemas en la red.

    Te comento, hoy mirando los logs parece que la performance mejoro un poco despues de aplicarle las querys de manteminiento que me dijiste.

    Pero lo que hice en cuanto al metodo anterior que escribi, el que me decis que pase a store procedure, hice exactamente eso y mejore la consulta completa y ahora es una luz a comparacion de lo que era antes, me estan ingresando entre 20 y 25 por segundos en algunos casos menos o mas.

    Asi que visto esto, lo primero que haría es pasar todos los metodos con incovenientes a stores y mejorar las consultas, que probablemente la degradacion sea por esto mismo.

    Sinceramente nose como agradecerte enrique por el tiempo prestado y la ayuda que me diste!! muchisimas gracias!! Cualquier cosa si aplico todos estos cambios y la degradacion continua vuelvo por aqui jaja

    Muchas gracias!! Y si en algo puedo ayudarte por aquí estoy!

    De nuevoo.. graciass!!

    Saludos...

    miércoles, 15 de abril de 2015 18:57
  • Saludos,

    Me da mucho gusto escuchar esto, recuerda agendar como Jobs de mantenimiento los trabajos de manutención semanalmente, en un horario que no te afecte mucho. Cualquier cosa házmelo saber.

    miércoles, 15 de abril de 2015 20:25
  • Si vamos a implementar ese mantenimiento con jobs en horarios que no afecten a la base, nuevamente muchas gracias por todo!!

    Suertee! saludoss..

    jueves, 16 de abril de 2015 22:29