none
Lentidão da Aplicação no consumo do Banco de dados. RRS feed

  • Pergunta

  • Olá pessoal, estou com um problema em um projeto. O Problema é a lentidão da aplicação quando consome o banco de dados.

    Dados que:

    *Tenho 3 ambientes (DEV - VM's diferentes (Appl/DB) em uma mesma rede, QAS - VM's diferentes (Appl/DB) em uma mesma rede, PRD - Appl em VM e banco em um servidor físico dentro da mesma rede mas em local diferente) 

    *Todos os ambientes apresentam o mesmo problema de lentidão.

    *Banco de dados SQL Server 2008

    *Appl web desenvolvida em DOT.NET (C#)

    *Consumo baixo de processamento e memória. (PRD trabalhando com 8 núcleos de processamento e 16 GB de memória.)

    *Link de 100 Mbps

    *Quando como teste, colocado Appl (PRD) na máquina junto ao (DB) a lentidão acabado e o desempenho fica excelente.

    *Testes de ping entre servidores tem latência (média) 12mms

    Sou GP do projeto, a equipe de infraestrutura diz que está tudo OK pois o consumo de processamento e memória estão baixo, a equipe de redes diz que está tudo OK pois a resposta entre os links está na média e ambas as equipes alegam que o problema é com a aplicação (terceiro contratado para desenvolvimento). Porém a equipe de desenvolvimento usa como argumento o fato da aplicação ter ótimo desempenho quando colocado junto com o DB.

    Tenho outros Sistemas / Bancos (muito mais robustos) trabalhando no mesmo ambiente sem problema algum de performance.

    Alguém já passou por situação similar e conseguiu resolver esse problema?

    Agradeço desde já qualquer idéia!

    Thks.

    André Lima

    sexta-feira, 30 de novembro de 2012 18:07

Respostas

  • André,

    Execute estes Scripts:

    -- Look at current expensive or blocked requests
    SELECT  r.session_id ,
            r.[status] ,
            r.wait_type ,
            r.scheduler_id ,
            SUBSTRING(qt.[text], r.statement_start_offset / 2,
                ( CASE WHEN r.statement_end_offset = -1
                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                       ELSE r.statement_end_offset
                  END - r.statement_start_offset ) / 2) AS [statement_executing] ,
            DB_NAME(qt.[dbid]) AS [DatabaseName] ,
            OBJECT_NAME(qt.objectid) AS [ObjectName] ,
            r.cpu_time ,
            r.total_elapsed_time ,
            r.reads ,
            r.writes ,
            r.logical_reads ,
            r.plan_handle
    FROM    sys.dm_exec_requests AS r
            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    WHERE   r.session_id > 50
    ORDER BY r.scheduler_id ,
            r.[status] ,
            r.session_id ;

    -- Shows the memory required by both running (non-null grant_time) and waiting queries (null grant_time)
    -- SQL Server 2008 version
    SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
            mg.requested_memory_kb ,
            mg.ideal_memory_kb ,
            mg.request_time ,
            mg.grant_time ,
            mg.query_cost ,
            mg.dop ,
            st.[text]
    FROM    sys.dm_exec_query_memory_grants AS mg
            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE   mg.request_time < COALESCE(grant_time, '99991231')
    ORDER BY mg.requested_memory_kb DESC ;

    -- Monitoring transaction activity
    SELECT  st.session_id ,
            DB_NAME(dt.database_id) AS database_name ,
            CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'
                 ELSE 'read-write'
            END AS transaction_state ,
            dt.database_transaction_begin_time AS read_write_start_time ,
            dt.database_transaction_log_record_count ,
            dt.database_transaction_log_bytes_used
    FROM    sys.dm_tran_session_transactions AS st
            INNER JOIN sys.dm_tran_database_transactions AS dt
                ON st.transaction_id = dt.transaction_id
    ORDER BY st.session_id ,
            database_name
    
    -- Look at active Lock Manager resources for current database
    SELECT  request_session_id ,
            DB_NAME(resource_database_id) AS [Database] ,
            resource_type ,
            resource_subtype ,
            request_type ,
            request_mode ,
            resource_description ,
            request_mode ,
            request_owner_type
    FROM    sys.dm_tran_locks
    WHERE   request_session_id > 50
            AND resource_database_id = DB_ID()
            AND request_session_id <> @@SPID
    ORDER BY request_session_id ;
    
    -- Look for blocking
    SELECT  tl.resource_type ,
            tl.resource_database_id ,
            tl.resource_associated_entity_id ,
            tl.request_mode ,
            tl.request_session_id ,
            wt.blocking_session_id ,
            wt.wait_type ,
            wt.wait_duration_ms
    FROM    sys.dm_tran_locks AS tl
            INNER JOIN sys.dm_os_waiting_tasks AS wt
               ON tl.lock_owner_address = wt.resource_address
    ORDER BY wait_duration_ms DESC ;

    -- Missing Indexes in current database by Index Advantage
    SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
                                                           AS [index_advantage] ,
            migs.last_user_seek ,
            mid.[statement] AS [Database.Schema.Table] ,
            mid.equality_columns ,
            mid.inequality_columns ,
            mid.included_columns ,
            migs.unique_compiles ,
            migs.user_seeks ,
            migs.avg_total_user_cost ,
            migs.avg_user_impact
    FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
            INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
               ON migs.group_handle = mig.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
               ON mig.index_handle = mid.index_handle
    WHERE   mid.database_id = DB_ID()
    ORDER BY index_advantage DESC ;

    -- List unused indexes
    SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
            i.name
    FROM    sys.indexes AS i
            INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE   i.index_id NOT IN ( SELECT  s.index_id
                                FROM    sys.dm_db_index_usage_stats AS s
                                WHERE   s.[object_id] = i.[object_id]
                                        AND i.index_id = s.index_id
                                        AND database_id = DB_ID() )
            AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
    
    
    -- Possible Bad NC Indexes (writes > reads)
    SELECT  OBJECT_NAME(s.[object_id]) AS [Table Name] ,
            i.name AS [Index Name] ,
            i.index_id ,
            user_updates AS [Total Writes] ,
            user_seeks + user_scans + user_lookups AS [Total Reads] ,
            user_updates - ( user_seeks + user_scans + user_lookups )
                AS [Difference]
    FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
            INNER JOIN sys.indexes AS i WITH ( NOLOCK )
                ON s.[object_id] = i.[object_id]
                AND i.index_id = s.index_id
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
            AND s.database_id = DB_ID()
            AND user_updates > ( user_seeks + user_scans + user_lookups )
            AND i.index_id > 1
    ORDER BY [Difference] DESC ,
            [Total Writes] DESC ,
            [Total Reads] ASC ;


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 28 de dezembro de 2012 13:22
    Moderador

Todas as Respostas

  • André,

    Somente este banco de dados esta apresentando lentidão?

    Foi realizada alguma migração deste banco de dados?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Sugerido como Resposta Marcos FrecciaMVP quinta-feira, 27 de dezembro de 2012 17:45
    • Não Sugerido como Resposta Marcos FrecciaMVP quinta-feira, 27 de dezembro de 2012 17:45
    segunda-feira, 3 de dezembro de 2012 18:05
    Moderador
  • Olá André,

    O mais aconselhável seria você ter uma baseline do produto, mas nem sempre temos isso e como o problema de performance pode ser muito genérico, comece a monitorar itens utilizando o perfmon como: Memoria, utilização de CPU do Servidor e do processo do SQL, Network Entrada e Saida, Disco: Avg Disk Sec/read e Avg Disk sec/write.

    Analise também se existem missing index, pois isso pode ajudar.

    Ja passei por casos onde a aplicação junto com o banco de dados é mais rápido do que quando em servidores dedicados e isso pode ser normal.

    Se a resposta foi útil, classifique-a


    Att, Marcos Freccia [MCTS|MCITP|MCT SQL Server 2008]

    quinta-feira, 27 de dezembro de 2012 17:49
  • André,

    Execute estes Scripts:

    -- Look at current expensive or blocked requests
    SELECT  r.session_id ,
            r.[status] ,
            r.wait_type ,
            r.scheduler_id ,
            SUBSTRING(qt.[text], r.statement_start_offset / 2,
                ( CASE WHEN r.statement_end_offset = -1
                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                       ELSE r.statement_end_offset
                  END - r.statement_start_offset ) / 2) AS [statement_executing] ,
            DB_NAME(qt.[dbid]) AS [DatabaseName] ,
            OBJECT_NAME(qt.objectid) AS [ObjectName] ,
            r.cpu_time ,
            r.total_elapsed_time ,
            r.reads ,
            r.writes ,
            r.logical_reads ,
            r.plan_handle
    FROM    sys.dm_exec_requests AS r
            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
    WHERE   r.session_id > 50
    ORDER BY r.scheduler_id ,
            r.[status] ,
            r.session_id ;

    -- Shows the memory required by both running (non-null grant_time) and waiting queries (null grant_time)
    -- SQL Server 2008 version
    SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
            mg.requested_memory_kb ,
            mg.ideal_memory_kb ,
            mg.request_time ,
            mg.grant_time ,
            mg.query_cost ,
            mg.dop ,
            st.[text]
    FROM    sys.dm_exec_query_memory_grants AS mg
            CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE   mg.request_time < COALESCE(grant_time, '99991231')
    ORDER BY mg.requested_memory_kb DESC ;

    -- Monitoring transaction activity
    SELECT  st.session_id ,
            DB_NAME(dt.database_id) AS database_name ,
            CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'
                 ELSE 'read-write'
            END AS transaction_state ,
            dt.database_transaction_begin_time AS read_write_start_time ,
            dt.database_transaction_log_record_count ,
            dt.database_transaction_log_bytes_used
    FROM    sys.dm_tran_session_transactions AS st
            INNER JOIN sys.dm_tran_database_transactions AS dt
                ON st.transaction_id = dt.transaction_id
    ORDER BY st.session_id ,
            database_name
    
    -- Look at active Lock Manager resources for current database
    SELECT  request_session_id ,
            DB_NAME(resource_database_id) AS [Database] ,
            resource_type ,
            resource_subtype ,
            request_type ,
            request_mode ,
            resource_description ,
            request_mode ,
            request_owner_type
    FROM    sys.dm_tran_locks
    WHERE   request_session_id > 50
            AND resource_database_id = DB_ID()
            AND request_session_id <> @@SPID
    ORDER BY request_session_id ;
    
    -- Look for blocking
    SELECT  tl.resource_type ,
            tl.resource_database_id ,
            tl.resource_associated_entity_id ,
            tl.request_mode ,
            tl.request_session_id ,
            wt.blocking_session_id ,
            wt.wait_type ,
            wt.wait_duration_ms
    FROM    sys.dm_tran_locks AS tl
            INNER JOIN sys.dm_os_waiting_tasks AS wt
               ON tl.lock_owner_address = wt.resource_address
    ORDER BY wait_duration_ms DESC ;

    -- Missing Indexes in current database by Index Advantage
    SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
                                                           AS [index_advantage] ,
            migs.last_user_seek ,
            mid.[statement] AS [Database.Schema.Table] ,
            mid.equality_columns ,
            mid.inequality_columns ,
            mid.included_columns ,
            migs.unique_compiles ,
            migs.user_seeks ,
            migs.avg_total_user_cost ,
            migs.avg_user_impact
    FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
            INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
               ON migs.group_handle = mig.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
               ON mig.index_handle = mid.index_handle
    WHERE   mid.database_id = DB_ID()
    ORDER BY index_advantage DESC ;

    -- List unused indexes
    SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
            i.name
    FROM    sys.indexes AS i
            INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE   i.index_id NOT IN ( SELECT  s.index_id
                                FROM    sys.dm_db_index_usage_stats AS s
                                WHERE   s.[object_id] = i.[object_id]
                                        AND i.index_id = s.index_id
                                        AND database_id = DB_ID() )
            AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;
    
    
    -- Possible Bad NC Indexes (writes > reads)
    SELECT  OBJECT_NAME(s.[object_id]) AS [Table Name] ,
            i.name AS [Index Name] ,
            i.index_id ,
            user_updates AS [Total Writes] ,
            user_seeks + user_scans + user_lookups AS [Total Reads] ,
            user_updates - ( user_seeks + user_scans + user_lookups )
                AS [Difference]
    FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
            INNER JOIN sys.indexes AS i WITH ( NOLOCK )
                ON s.[object_id] = i.[object_id]
                AND i.index_id = s.index_id
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
            AND s.database_id = DB_ID()
            AND user_updates > ( user_seeks + user_scans + user_lookups )
            AND i.index_id > 1
    ORDER BY [Difference] DESC ,
            [Total Writes] DESC ,
            [Total Reads] ASC ;


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    sexta-feira, 28 de dezembro de 2012 13:22
    Moderador