none
Random Slowness in Insert Operation RRS feed

  • Question

  • We have SQL SERVER 2008 R2 64bit and Windows 2008 as OS in our production environment.Our database is being hitted heavily everyday.Generally all the insert operations are taking 30 - 40 ms to be completed,but recently randomly some of the Insert operations are taking 2 sec to complete.Our insert operation is very time sensetive and it should be below 1 sec.
    We do not have any page split and all of the statistics of tables are uptodate.Also we do not have any File growth in our database,during the insert operation.When I checked the wait Times,I got below(attachment):

    Wednesday, December 21, 2011 6:01 AM

Answers

  • you can use dmv to see what is currently executing, but unless you have long running queries the chances of catching it while executing is slim

    SELECT s.session_id, s.host_name, r.command, r.open_transaction_count
         , coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'
                  + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
                  + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid))
                  , SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset
              END - r.statement_start_offset)/2) + 1)) AS [query],r.total_elapsed_time AS[Msec],r.logical_reads
      FROM sys.dm_exec_sessions AS s
      JOIN sys.dm_exec_requests AS r
        ON r.session_id = s.session_id
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id!=@@SPID
    

    also you can monitor locks on the particular tables you are inserting into to see of some other queries are holding locks for long time

    DECLARE @tableName VARCHAR(50)
    SET @tableName='tablename'
    
    SELECT L.request_session_id,L.request_mode,l.resource_type,L.request_status,s.host_name, r.command
         , coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
          + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '<Adhoc Batch>') AS command_text 
    FROM sys.dm_tran_locks AS L JOIN sys.dm_exec_sessions AS s ON L.request_session_id=s.session_id join sys.dm_exec_requests AS r ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st
    WHERE database_id=DB_ID() 
    AND L.resource_associated_entity_id=OBJECT_ID(@tableName) 
    AND NOT (request_mode='Sch-S' AND request_status='Grant')--don't return schema stability locks unless they are blocked
    ORDER BY L.request_session_id
    
    

    hope that helps.

    Wednesday, December 21, 2011 5:01 PM
  • i'm not aware of such a thing as "lock the transaction log" but what could happen is you have a very transaction intensive job that is causing a lot of writes to the transaction log.

    you should monitor the DISK IO write performance on the drive that holds the transaction log since it could be unable to cope with the increased demand.

    Thursday, December 22, 2011 5:53 AM
  • Yes, various processes are competing for limited resources, so naturally conflict occurs.  It appears you identified the culprit. You need to look at optimization at every level, query to SS, in order to achive a high performance database system.

    The following article deals with general database system optimization:

    http://www.sqlusa.com/articles/query-optimization/

    For high performance keep the transaction log on an independent drive:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Same for tempdb data and tempdb log .

    Consider applying index FILLFACTOR 70 - 90 on dynamic tables to increase performance:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Tuesday, December 27, 2011 2:30 PM
    Moderator

All replies

  • the attached waits don't look too bad.

    you need to consider other things that are running on your server and try to correlate between what is executing at the time the inserts become slow.

    if you can rung profiler for some time, and when you notice inserts are slow now, go to profiler and see what is executing.

    also i would suggest using perfmon, and add a few counters, i would start with physical disk to make sure the issue is not IO related.

    Wednesday, December 21, 2011 6:41 AM
  • Thanks for reply.Unfortunetly, I can not run the SQL profiler(Because the profiler itself have side effect on the performance)
    Wednesday, December 21, 2011 4:32 PM
  • you can use dmv to see what is currently executing, but unless you have long running queries the chances of catching it while executing is slim

    SELECT s.session_id, s.host_name, r.command, r.open_transaction_count
         , coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'
                  + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
                  + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid))
                  , SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset
              END - r.statement_start_offset)/2) + 1)) AS [query],r.total_elapsed_time AS[Msec],r.logical_reads
      FROM sys.dm_exec_sessions AS s
      JOIN sys.dm_exec_requests AS r
        ON r.session_id = s.session_id
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id!=@@SPID
    

    also you can monitor locks on the particular tables you are inserting into to see of some other queries are holding locks for long time

    DECLARE @tableName VARCHAR(50)
    SET @tableName='tablename'
    
    SELECT L.request_session_id,L.request_mode,l.resource_type,L.request_status,s.host_name, r.command
         , coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
          + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '<Adhoc Batch>') AS command_text 
    FROM sys.dm_tran_locks AS L JOIN sys.dm_exec_sessions AS s ON L.request_session_id=s.session_id join sys.dm_exec_requests AS r ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st
    WHERE database_id=DB_ID() 
    AND L.resource_associated_entity_id=OBJECT_ID(@tableName) 
    AND NOT (request_mode='Sch-S' AND request_status='Grant')--don't return schema stability locks unless they are blocked
    ORDER BY L.request_session_id
    
    

    hope that helps.

    Wednesday, December 21, 2011 5:01 PM
  • Also,This slowness happens every 10 minutes.I checked all of our Scheduled jobs, and we don't have any job that runs every 10 min.This Server also acts as Publisher in Transactional Replication.In Distributor we have  a job "Replication Agent Checkup",that runs every 10 min.Can This job affect on Publisher?

     Also,The server is part of Cluster environment.



    • Edited by Mourmansk Thursday, December 22, 2011 1:47 AM
    Wednesday, December 21, 2011 11:06 PM
  • It seems that Transaction log file is being locked for short period of time every 10 min and this affect on the performance of sql statments that runs against SQL server.How can I fond what does lock the transaction log file?
    Thursday, December 22, 2011 3:44 AM
  • i'm not aware of such a thing as "lock the transaction log" but what could happen is you have a very transaction intensive job that is causing a lot of writes to the transaction log.

    you should monitor the DISK IO write performance on the drive that holds the transaction log since it could be unable to cope with the increased demand.

    Thursday, December 22, 2011 5:53 AM
  • Yes, various processes are competing for limited resources, so naturally conflict occurs.  It appears you identified the culprit. You need to look at optimization at every level, query to SS, in order to achive a high performance database system.

    The following article deals with general database system optimization:

    http://www.sqlusa.com/articles/query-optimization/

    For high performance keep the transaction log on an independent drive:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Same for tempdb data and tempdb log .

    Consider applying index FILLFACTOR 70 - 90 on dynamic tables to increase performance:

    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    Tuesday, December 27, 2011 2:30 PM
    Moderator