none
Performance Issue on Sql server 2008 r2

    Question

  • Hi ,

    There is a performance issue on my Standalone SQL Server there are many concurrences on a database.

    I found in my SQL error log the below one:

    Timeout occurred while waiting for latch: class 'DBCC_CHECK_TABLE_INIT', id 000000007522DCF0, type 2, Task 0x000000000070DDC8 : 12, waittime 300, flags 0x1a, owning task 0x00000000C073EBC8. Continuing to wait.

    Setup is :  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
     Help me on this...
    Thursday, June 20, 2013 12:46 PM

Answers

  • Hi,

    I also have these in my error log

    AppDomain 148 (mssqlsystemresource.sys[runtime].648) is marked for unload due to memory pressure

     SQL Server has encountered 44 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQL_Datafiles\.ndf] in database [dbname] (5).  The OS file handle is 0x000000000000075C.  The offset of the latest long I/O is: 0x0000020bf66000

    There is monthly transaction happening so all users are concurrently doing there invoice it is taking 2hrs of time to finish the transaction......this is the problem


    AppDomains are created when your method hits SQLCLR AppDomains allow your function to operate inside the SQL Server process.  If there is memory pressure on your server, which could be the case with u, then older AppDomains are unloaded to free up resources.
    What were you running in your system (sql server) when you got this error.The MSSQLSYSTEMRESOURCE is is a SQL System Assembly that is loaded for things native to SQL Server that utilize SQLCLR, which was introduced in SQL 2008 for things like the data types from the Microsoft.SqlServer.Types namespace (spatial and others new in 2008), Policy Based Management has a SQLCLR stored procedure (sp_execute_policy), Change Data Capture and transactional replication have CLR functions and procedures, and fuzzy lookup has CLR stored procedures associated with it. So I assume you must be doing any such activity.

    Below link will help you even if ur system is 64 bit...

    AS far as ur error is concerned i think it can be ignored unless its causing some grave issues same is mentioned in the link do check ur query running when ur getting this message.

    http://support.microsoft.com/kb/969962?wa=wsignin1.0


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 21, 2013 6:11 AM

All replies

  • This issue occurs because a time-out occurs when SQL Server traverses the Index Allocation Map (IAM) chains. The latch that is mentioned in the error message is used to prevent other threads from accessing a list. This list is being built by a thread that traverses the IAM chains for all indexes that are associated with a given table. If the table is large enough that traversing these IAM chains takes more than 5 minutes, you may experience the latch time-out. Additionally, this issue is typically worse when disk I/O is slow.

    Hi can you give me result for what waits are occuring from

    select

    top(10)* from sys.dm_os_wait_stats order by wait_time_ms desc

    Are you using MAXDOP in query or MAX DEGREE OF PARALLELISM is configured to some value in sp_configure


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 20, 2013 12:57 PM
  • Hi,

    Thank you for the reply...

    How to check wether MAX DEGREE OF PARALLELISM is Configured or not?

    Pls let me know how can i fix this can i increase the memory or shall i allow snapshot isolation level?

    I have rebuilded the indexes and dropped unused indexes on test server the performance was good but on production the same activity the performance is worse.....

    Thank you

    Thursday, June 20, 2013 1:44 PM
  • Hi,

    Thank you for the reply...

    How to check wether MAX DEGREE OF PARALLELISM is Configured or not?

    I have rebuilded the indexes and dropped unused indexes on test server the performance was good but on production the same activity the performance is worse.....

    Thank you

    Hi ,

    SP_configure 'show advance option',1

    go

    reconfigure

    go

    sp_configure

    O/P will show MAX DEGREE OF PARALLELISM WHAT IS ITS VALUE?..has this message repeated it cannot be called as issue always just a kind of timeout...do tell me about perf prob ur facing..please five me o/p of query in prev post ..please read post carefully..

    don't delete or add index are there any other messages in error log reg memory and other things please post

    see this also http://support.microsoft.com/kb/2545989


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, June 20, 2013 2:56 PM link added
    Thursday, June 20, 2013 2:54 PM
  • hi Added this link specifically to go through it...Latch timeout shud not always be concern ...if you face any I/O related error in error log do post

    http://sqlactions.com/2013/02/25/latch-timeout-to-worry-or-not-to/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 20, 2013 2:59 PM
  • Hi,

    I also have these in my error log

    AppDomain 148 (mssqlsystemresource.sys[runtime].648) is marked for unload due to memory pressure

     AppDomain 148 (mssqlsystemresource.sys[runtime].648) unloaded.

    AppDomain 149 (mssqlsystemresource.sys[runtime].656) created.

     SQL Server has encountered 44 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQL_Datafiles\.ndf] in database [dbname] (5).  The OS file handle is 0x000000000000075C.  The offset of the latest long I/O is: 0x0000020bf66000

    There is monthly transaction happening so all users are concurrently doing there invoice it is taking 2hrs of time to finish the transaction......this is the problem

    Friday, June 21, 2013 3:53 AM
  • Hi,

    This is the result of sys.dm_os_wait_stats..

    Configuration Details of MAx degree of parallelism..

    Help me

    Friday, June 21, 2013 4:57 AM
  • Hi,

    I also have these in my error log

    AppDomain 148 (mssqlsystemresource.sys[runtime].648) is marked for unload due to memory pressure

     SQL Server has encountered 44 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQL_Datafiles\.ndf] in database [dbname] (5).  The OS file handle is 0x000000000000075C.  The offset of the latest long I/O is: 0x0000020bf66000

    There is monthly transaction happening so all users are concurrently doing there invoice it is taking 2hrs of time to finish the transaction......this is the problem


    AppDomains are created when your method hits SQLCLR AppDomains allow your function to operate inside the SQL Server process.  If there is memory pressure on your server, which could be the case with u, then older AppDomains are unloaded to free up resources.
    What were you running in your system (sql server) when you got this error.The MSSQLSYSTEMRESOURCE is is a SQL System Assembly that is loaded for things native to SQL Server that utilize SQLCLR, which was introduced in SQL 2008 for things like the data types from the Microsoft.SqlServer.Types namespace (spatial and others new in 2008), Policy Based Management has a SQLCLR stored procedure (sp_execute_policy), Change Data Capture and transactional replication have CLR functions and procedures, and fuzzy lookup has CLR stored procedures associated with it. So I assume you must be doing any such activity.

    Below link will help you even if ur system is 64 bit...

    AS far as ur error is concerned i think it can be ignored unless its causing some grave issues same is mentioned in the link do check ur query running when ur getting this message.

    http://support.microsoft.com/kb/969962?wa=wsignin1.0


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 21, 2013 6:11 AM
  • Hi,

    This is the result of sys.dm_os_wait_stats.

    Configuration Details of MAx degree of parallelism..

    Help me

     SQL Server has encountered 44 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\SQL_Datafiles\.ndf] in database [dbname] (5).  The OS file handle is 0x000000000000075C.  The offset of the latest long I/O is: 0x0000020bf66000

    As per wait stats your I/O subsystem drive on which ndf file resides is slow...so we are getting MAX waits on PAGEIOLATCH and CXPACKET ,immediately ask you storage vendor to look after SAN drives allocated to your SQL server.I think there is problem with your SAN

    In simpler words, when a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat

    You can configure MAX DEGREE OF PARALLELISM to 1or 2 and see for performance gain --its not necessary this will give u any gain

    EXEC sys.sp_configure N'max degree of parallelism', N'1'
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    You have to find queries running in parallel using this comand and tune them

    SELECT TOP 30
    p.*,
    q.*,
    qs.*,
    cp.plan_handle
    FROM
    sys.dm_exec_cached_plans cp
    CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
    CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
    JOIN sys.dm_exec_query_stats qs
    ON qs.plan_handle = cp.plan_handle
    WHERE
    cp.cacheobjtype = 'Compiled Plan' AND
    p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    max(//p:RelOp/@Parallel)', 'float') > 0
    OPTION (MAXDOP 1)


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 21, 2013 6:27 AM
  • Hi,

    Thank you..

    I will set the MAXdop value to 2.

    Iam using a standalone server where there is no concept of SAN , The ndf files are on D:\ drive shall i tell the vendor to look into it.??

    Also one more thing shall i also set true for Allow snapshot isolation level is it necessary?

    waiting for your reply.

    Friday, June 21, 2013 7:22 AM
  • Hi Subathra,

    >>Allow snapshot isolation level is it necessary...i cannot comment on this..

    I think tunig parallel queries and replacing/correcting failty i/o drive wiill give u much relief.

    Make sure you indexes are not fragmenetd

    Make sure stats are updated

    Findout queries using parallelism..tune them ,tune all queries which are performing bad

     I would like to correct MAXDOP is set in queries which is better option,MAX DEGREE Of parallelism is set in sp_configure start from 1 and see, this shud be last option ..after following above points...

    I suggest u keep this Doc handy

    http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 21, 2013 7:35 AM
  • Hi,

    correcting failty i/o drive ? how can i confirm this to vendor..from the error log statement?what shal i tel them to check?

    I have rebulided the indexes where my fragemntation percent is less now..

    Make sure stats are updated . I didn't get this point?

    Friday, June 21, 2013 8:15 AM
  • Hi,

    Finding parallel queries though the query which you have given will it give the cached data when the performance was slow before?

    Friday, June 21, 2013 8:18 AM
  • Hi,

    correcting failty i/o drive ? how can i confirm this to vendor..from the error log statement?what shal i tel them to check?

    I have rebulided the indexes where my fragemntation percent is less now..

    Make sure stats are updated . I didn't get this point?

    Ask them to check I/O response time , Say them I/O is slow its not matching up to sql data i/o requirement so we got I/O error in errorlog..ask them to see if Latest firmwares are there for drives...ask them to look for bad sectors

    Also for Updating stats see http://msdn.microsoft.com/en-us/library/ms187348.aspx

    >>Finding parallel queries though the query which you have given will it give the cached data when the performance was slow before?

    Yes it will give data from cache at that time if you have not restated you SQL server /win server..if u have restated wait for 2-3 days for data to come in cache then run query

    Also for Updating stats u can search on net...searching from ur side will also provide u information.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 21, 2013 8:58 AM
  • Hi,

    I would like to update statistics for Only two tables not whole database...

    Can i use UPDATE STATASTICS Table Name

    How can i check i/o response time through sp_who2?

    Pls reply

    Tuesday, June 25, 2013 9:00 AM