TroubleshootingSQL

Category: Data

Posts about data platforms, SQL Server, databases, Azure SQL, analytics, and data engineering.

  • Another SQL VDI error that can mystify you

    While performing a backup of a SQL Server using a backup software which uses SQLVDI, you might encounter a failure which reports the following failure hex code: 0x080070005. This is basically an Access Denied error message. If your backup software logs all the VDI API calls that are made, then you might even know which function call failed.

    If you are using SQL Server Backup Simulator to simulate a VDI backup of the any SQL Server database with the same account that raised the error, you will find the following message in the Backup Simulator logs.

    Trying to perform VDI test on a default instance
    Error: VDS::Create fails: 0x80070005

    The above message tells me that the IClientVirtualDeviceSet2::CreateEx function call failed. Now the important question here is why did the CreateEx function fail. If you look into the application event log, you will find an Error reported by SQLVDI (source) with the following text:

    SQLVDI: Loc=CVDS::CreateEx. Desc=Create Memory. ErrorCode=(5)Access is denied.. Process=24456. Thread=13984. Client. Instance=. VD=Global\SQLBackupSim_SQLVDIMemoryName_0.

    The above message tells me that I am creating a Global Shared Memory Object but I currently do not have the necessary privilege to create the object. Check the output of whoami /priv for the account that is running the Backup Simulator or the backup software and see if the following privilege is listed: SeCreateGlobalPrivilege. This privilege is granted by default to administrators, services, and the local system account. In case you are trying to use Backup Simulator on a Windows version which has UAC, then you need to run Backup Simulator using “Run as Administrator” option to avoid this issue. If the account trying to create this global memory object doesn’t have this privilege, then you can grant the privilege to this account using the following steps:

    1. On the Start menu, click Run. In the Open box, type gpedit.msc.

      The Group Policy dialog box opens.

    2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

    3. Expand Security Settings, and then expand Local Policies.

    4. Select the User Rights Assignment folder.

      The policies will be displayed in the details pane.

    5. In the pane, double-click Create global objects.

    6. In the Local Security Policy Setting dialog box, click Add.

    7. In the Select Users or Groups dialog box, add an account with privileges to run backup software or the Backup Simulator application.

    Once this is done, you need to re-launch Backup Simulator or re-start the backup using the Backup Software as the security privilege will not modify the token that is currently being used by the backup software or Backup Simulator.

    Another issue that you can run into even if you have the above mentioned security privilege is if you have multiple backups trying to create the same global shared memory object. In such scenarios, you would want to create a unique virtual device name. More information about the same is mentioned in the KB Article below:

    903646    An application that uses the Virtual Device Interface feature of SQL Server 2000 to back up a SQL Server 2000 database may not back up the database
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;903646

    del.icio.us Tags: ,,

  • System Health Session: Part 4

    This is the last post for the System Health Session series. In this post, I shall show you how to extract deadlock related information from the deadlock graph captured by the System Health Session.

    The deadlock graph captured typically has three distinct nodes:

    victim-list – Deadlock victim’s process identifier
    process-list – Information pertaining to all the processes involved in the deadlock
    resource-list – Information about the resources involved in the deadlock

    The query below will provide you with the time stamp when the deadlock was reported along with victim process identifier.

     -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'
-- Gets the Deadlock Event Time and Victim Process
SELECT C.query('.').value('(/event/@timestamp)[1]', 'datetime') as EventTime,
CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML).value('(<a>/deadlock/victim-list/victimProcess/@id)[1]','varchar(100)'</a>) VictimProcess
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
-- Drop the temporary table
DROP TABLE #SystemHealthSessionData

    The next query (when provided with an event time from the above query output)
    will provide you a parsed version of the process list in a tabular format which
    can be easier to read when you have a large number of sessions involved in the
    deadlock.

     -- Fetch the Health Session data into a temporary table

SELECT CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

-- Parses the process list for a specific deadlock once provided with an event time for the deadlock from the above output

;WITH CTE_HealthSession (EventXML) AS
(
SELECT CAST(C.query('.').value('(/event/data/value)[1]', 'varchar(MAX)') AS XML) EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report'
AND C.query('.').value('(/event/@timestamp)[1]', 'datetime') = '2011-09-28 06:24:44.700' -- Replace with relevant timestamp
)
SELECT DeadlockProcesses.value('(@id)[1]','varchar(50)') as id
,DeadlockProcesses.value('(@taskpriority)[1]','bigint') as taskpriority
,DeadlockProcesses.value('(@logused)[1]','bigint') as logused
,DeadlockProcesses.value('(@waitresource)[1]','varchar(100)') as waitresource
,DeadlockProcesses.value('(@waittime)[1]','bigint') as waittime
,DeadlockProcesses.value('(@ownerId)[1]','bigint') as ownerId
,DeadlockProcesses.value('(@transactionname)[1]','varchar(50)') as transactionname
,DeadlockProcesses.value('(@lasttranstarted)[1]','varchar(50)') as lasttranstarted
,DeadlockProcesses.value('(@XDES)[1]','varchar(20)') as XDES
,DeadlockProcesses.value('(@lockMode)[1]','varchar(5)') as lockMode
,DeadlockProcesses.value('(@schedulerid)[1]','bigint') as schedulerid
,DeadlockProcesses.value('(@kpid)[1]','bigint') as kpid
,DeadlockProcesses.value('(@status)[1]','varchar(20)') as status
,DeadlockProcesses.value('(@spid)[1]','bigint') as spid
,DeadlockProcesses.value('(@sbid)[1]','bigint') as sbid
,DeadlockProcesses.value('(@ecid)[1]','bigint') as ecid
,DeadlockProcesses.value('(@priority)[1]','bigint') as priority
,DeadlockProcesses.value('(@trancount)[1]','bigint') as trancount
,DeadlockProcesses.value('(@lastbatchstarted)[1]','varchar(50)') as lastbatchstarted
,DeadlockProcesses.value('(@lastbatchcompleted)[1]','varchar(50)') as lastbatchcompleted
,DeadlockProcesses.value('(@clientapp)[1]','varchar(150)') as clientapp
,DeadlockProcesses.value('(@hostname)[1]','varchar(50)') as hostname
,DeadlockProcesses.value('(@hostpid)[1]','bigint') as hostpid
,DeadlockProcesses.value('(@loginname)[1]','varchar(150)') as loginname
,DeadlockProcesses.value('(@isolationlevel)[1]','varchar(150)') as isolationlevel
,DeadlockProcesses.value('(@xactid)[1]','bigint') as xactid
,DeadlockProcesses.value('(@currentdb)[1]','bigint') as currentdb
,DeadlockProcesses.value('(@lockTimeout)[1]','bigint') as lockTimeout
,DeadlockProcesses.value('(@clientoption1)[1]','bigint') as clientoption1
,DeadlockProcesses.value('(@clientoption2)[1]','bigint') as clientoption2
FROM (select EventXML as DeadlockEvent FROM CTE_HealthSession) T
CROSS APPLY DeadlockEvent.nodes('//deadlock/process-list/process') AS R(DeadlockProcesses)

-- Drop the temporary table
DROP TABLE #SystemHealthSessionData

    The script file for the above queries can be downloaded from here.

    A sample output of the above two queries is shown below:

    The second dataset which shows the parsed process list from the deadlock graph is for the timestamp highlighted above.

    As mentioned in one of my previous posts, the custom reports used in the previous posts can be downloaded from here (Filename: System_Health_Session_Custom_Reports.zip).

    Modification April 20th, 2012: Just updated the .sql files and added the Deadlock Report to the SkyDrive location.

    Note: To make use of the deadlock graph captured by the System Health Session, you need to have the required update applied to avoid the issue mentioned in KB978629. The issue is addressed in:
    981355    Cumulative Update package 1 for SQL Server 2008 R2
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;981355
    977443    Cumulative update package 6 for SQL Server 2008 Service Pack 1
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;977443

    If you don’t have the updates installed, then Jonathan (Blog | Twitter) has shown in his article Retrieving Deadlock Graphs with SQL Server 2008 Extended Events how to workaround the issue. Michael Zilberstein’s (Blog) article, Parsing Extended Events xml_deadlock_report, has an updated/corrected version of the T-SQL to fetch the deadlock information.

    References:

    Bart Duncan’s Weblog:
    Deadlock Troubleshooting, Part 1
    Deadlock Troubleshooting, Part 2
    Deadlock Troubleshooting, Part 3

  • Debugging that latch timeout

    My last post of debugging an assertion didn’t have any cool debugging tips since there is not much that you can do with an assertion dump unless you have access to private symbols and sometimes even access to the source code. In this post, I am going to not disappoint and show you some more cool things that the windows debugger can do for you with public symbols for a latch timeout issue.

    When you encounter a latch timeout (buffer or non-buffer latch), the first occurrence of it’s type generates a mini-dump. If there are further occurrences of the same latch timeout, then that is reported as an error message in the SQL Errorlog.

    Buffer latch timeouts are typically reported using Error: 844 and 845. The common reasons for such errors are documented in a KB Article. For a non-buffer latch timeout, you will get the an 847 error.

    Error # Error message template (from sys.messages)
    844 Time out occurred while waiting for buffer latch — type %d, bp %p, page %d:%d, stat %#x, database id: %d, allocation unit id: %I64d%ls, task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p.  Continuing to wait.
    845 Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.
    846 A time-out occurred while waiting for buffer latch — type %d, bp %p, page %d:%d, stat %#x, database id: %d, allocation unit Id: %I64d%ls, task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Not continuing to wait.
    847

    Timeout occurred while waiting for latch: class ‘%ls’, id %p, type %d, Task 0x%p : %d, waittime %d, flags 0x%I64x, owning task 0x%p. Continuing to wait.

    This is what you will see in the SQL Errorlog when a latch timeout occurs.

    spid148     Time out occurred while waiting for buffer latch — type 4, bp 0000000832FE1200, page 3:11234374, stat 0x7c20009, database id: 120, allocation unit id: 72057599731367936, task 0x0000000003C4F2E8 : 0, waittime 300, flags 0x1a, owning task 0x0000000003C129B8.  Continuing to wait.
    spid148     **Dump thread – spid = 148, PSS = 0x000000044DC17BD0, EC = 0x000000044DC17BE0
    spid148     ***Stack Dump being sent to D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.txt

    spid148     * Latch timeout
    spid148     * Input Buffer 84 bytes –
    spid148     *             DBCC CHECKDB WITH ALL_ERRORMSGS
    External dump process returned no errors.

    I have only pasted the relevant portion from the Errorlog for brevity. As I have outlined in my previous blog posts on similar topics, that there is a large opportunity for due diligence that can be done with the help of the Windows Event Logs and the SQL Server Errorlogs before you start spawning off windows debugger to analyze the memory dump on your system. The first few obvious things that you will notice is that SPID 148 encountered the issue while performing a CHECKDB on database ID 120. The timeout occurred while waiting for a buffer latch on a page (Page ID is available in the message above). I don’t see a “Timeout waiting for external dump process” message in the SQL Errorlog which means that I have a good chance of extracting useful information from the mini-dump that was generated by SQLDumper.

    Latch timeouts are typically victims of either a system related issue (hardware or drivers or operating system or a previous error encountered by SQL Server). So the next obvious action item would be to look into the SQL Errorlogs and find out if there were any additional errors prior to the latch timeout issue. I see a number of OS Error 1450 reported by the same SPID 148 for the same file handle but different offsets.

    spid148     The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x0000156bf36000 in file with handle 0x0000000000001358. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

    Additionally, I see prior and post (within 5-15 minutes) the latch timeout issue, multiple other SPIDs reporting the same 1450 error message for different offsets but again on the same file.

    spid137     The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000007461f8000 in file with handle 0x0000000000001358. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

    I also see the latch timeout message being reported after every 300 ms for the same page and the database.

    spid148     Time out occurred while waiting for buffer latch — type 4, bp 0000000832FE1200, page 3:11234374, stat 0x7c20009, database id: 120, allocation unit id: 72057599731367936, task 0x0000000003C4F2E8 : 0, waittime 82800, flags 0x1a, owning task 0x0000000003C129B8.  Continuing to wait.

    Notice the waittime above, it has increased from 300 to 82800!! So the next thing I do is look up issues related to CHECKDB and 1450 error messages on the web using Bing (Yes, I do use BING!!). These are relevant posts related to the above issue.

    http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx
    http://blogs.msdn.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx

    As of now, it is quite clear that the issue is related to a possible sparse file issue related to file fragmentation. Now it is time for me to check if there are other threads in the dump waiting on SyncWritePreemptive calls.

    Use the location provided in the Errorlog snippet reporting the Latch Timeout message to locate the mini-dump for the issue (in this case SQLDump0001.mdmp).

    Now when you load the dump using WinDBG, you will see the following information:

    Loading Dump File [D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0001.mdmp]
    User Mini Dump File: Only registers, stack and portions of memory are available

    Comment: ‘Stack Trace’
    Comment: ‘Latch timeout’

    This dump file has an exception of interest stored in it.

    The above tells you that this is a mini-dump for a Latch Timeout condition and the location from where you loaded the dump. Then I use the command to set my symbol path and direct the symbols downloaded from the Microsoft symbol server to a local symbol file cache on my machine.

    .sympath srv*D:\PublicSymbols*http://msdl.microsoft.com/download/symbols

    Then I issue a reload command to load the symbols for sqlservr.exe. This can also be done using CTRL+L and providing the complete string above (without .sympath), checking the Reload checkbox and clicking on OK. The only difference here is that the all the public symbols for all loaded modules in the dump will be downloaded from the Microsoft Symbol Server which are available.

    .reload /f sqlservr.exe

    Next thing is to verify that the symbols were correctly loaded using the lmvm sqlservr command. If the symbols were loaded correctly, you should see the following output. Note the text in green.

    0:005> lmvm sqlservr

    start             end                 module name
    00000000`01000000 00000000`03668000   sqlservr T (pdb symbols)          d:\publicsymbols\sqlservr.pdb\2A3969D78EE24FD494837AF090F5EDBC2\sqlservr.pdb

     

    If symbols were not loaded, then you will see an output as shown below.

    0:005> lmvm sqlservr
    start end module name
    00000000`01000000 00000000`03668000 sqlservr (export symbols) sqlservr.exe

    I will use the !findstack command to locate all threads which have the function call SyncWritePreemptive on their callstack.

    0:070> !findstack sqlservr!FCB::SyncWritePreemptive 0

    Thread 069, 1 frame(s) match
    Thread 074, 1 frame(s) match
    Thread 076, 1 frame(s) match
    Thread 079, 1 frame(s) match
    Thread 081, 1 frame(s) match
    Thread 082, 1 frame(s) match
    Thread 086, 1 frame(s) match
    Thread 089, 1 frame(s) match
    Thread 091, 1 frame(s) match
    Thread 095, 1 frame(s) match
    Thread 098, 1 frame(s) match
    Thread 099, 1 frame(s) match
    Thread 104, 1 frame(s) match
    Thread 106, 1 frame(s) match
    Thread 107, 1 frame(s) match
    Thread 131, 1 frame(s) match
    Thread 136, 1 frame(s) match

    0:070> ~81s
    ntdll!ZwWaitForSingleObject+0xa:
    00000000`77ef0a2a c3              ret
    0:081> kL100

    ntdll!ZwDelayExecution
    kernel32!SleepEx
    sqlservr!FCB::SyncWritePreemptive
    sqlservr!FCB::PullPageToReplica
    sqlservr!alloca_probe
    sqlservr!BUF::CopyOnWrite
    sqlservr!PageRef::PrepareToDirty
    sqlservr!RecoveryMgr::DoCOWPreWrites

    You could get all the callstacks with the function that you are searching for using the command: !findstack sqlservr!FCB::SyncWritePreemptive 3

    If you look at the thread that raised ended up raising the Latch Timeout warning was also performing a CHECKDB.

    0:074> .ecxr

    0:074> kL100

    kernel32!RaiseException
    sqlservr!CDmpDump::Dump
    sqlservr!CImageHelper::DoMiniDump
    sqlservr!stackTrace
    sqlservr!LatchBase::DumpOnTimeoutIfNeeded
    sqlservr!LatchBase::PrintWarning
    sqlservr!alloca_probe
    sqlservr!BUF::AcquireLatch
    …
    …
    sqlservr!UtilDbccCreateReplica
    sqlservr!UtilDbccCheckDatabase
    sqlservr!DbccCheckDB
    sqlservr!DbccCommand::Execute

    If you cannot find the thread which raised the Latch Timeout warning, you could print out all the callstacks in the dump using ~*kL100 and the searching for the function call in blue above. It is quite clear from the callstack above that the thread was also involved in performing a CHECKDB operation as reported in the SQL Errorlog in the input buffer for the Latch Timeout dump.

    If case you were not able to identify the issue right off the bat, you need to check the build that you are on and look for issues that were addressed related to Latch Timeouts for the SQL Server release  that you are using. The symptoms section would have sufficient amount of information for you to compare with your current symptoms and scenario and determine if the KB Article that you are looking at is applicable in your case.

    Now is the time, when you need to have some context about the operations that were occurring on the server to actually determine what the actual issue is. Based on what I heard from the system administrators that there was a CHECKDB being executed on the database while the application was executing DML operations on the database. Additionally, the volume on which the disk resides on has fragmentation and the database in question is large (>750GB).

    Based on the two MSDN blog posts that I mentioned above, it is quite clear that it is possible to run into sparse file limitations when there is high amount of fragmentation on the disks or if there are a large number of concurrent changes occurring on the database when a CHECKDB is executing on it. A number of Windows and SQL Server updates along with workarounds to run CHECKDB on such databases is mentioned in the second blog post mentioned above. On a separate note, this is not an issue with CHECKDB! It is limitation that you are hitting with sparse files on the OS layer. Remember CHECKDB, starting from SQL Server 2005, creates an internal snapshot (makes sparse file) to execute the consistency check. Paul Randal’s tweet made me add this line to call this out explicitly!

    As always… if you are still stuck, contact Microsoft CSS with the mini-dump file, SQL Errorlog and the Windows Event Logs. It might be quite possible that CSS might ask you to collect additional data as most Latch Timeout issues are generally an after-effect of a previous issue. In this case, it was the OS Error 1450.

    Well… That’s it for today! Hope this is helpful for the next time you encounter a Latch Timeout issue.

    Additional References

    Four stages of NTFS File Growth
    KB 315263 – How to read the small memory dump files that Windows creates for debugging

     

  • Debugging that Assert condition: Maybe Not

    Last week I had shown how to debug non-yielding scheduler and deadlocked schedulers memory dumps. In this post, I shall talk about Assertions. When an assert condition check fails in the SQL Server code base, a mini-dump of the SQL Server process is created which is found by default in the SQL Server LOG folder.

    An assert is basically a predicate (true-false condition) put in a program’s code by the developer which he/she thinks should always evaluate to TRUE. If this fails, then a the assert failure code written by the developer will be executed.

    This is one of those scenarios where you will NOT actually need to debug the assert dump and still be able to achieve a lot without opening a debugger. So if you were hoping for some more cool debugging steps, I will have to disappoint you till my next post! Whenever an assert condition fails, the message is logged in the SQL Server Errorlog along with the failing assert condition.

    Let me walk you through an example. You would see messages similar to the one below in the Windows Application Event log when an Assertion check fails:

    MSSQLSERVER    Error    (2)    3624    N/A    <server name> A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
    MSSQLSERVER    Error    (2)    17066    N/A    <server name> "SQL Server Assertion: File: <""logmgr.cpp"">, line=<line number> Failed Assertion = ‘!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)‘. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted."

    So if you are monitoring your application event logs, 17066 or 3624 is the error number that you need to monitor for. Once you are aware that an exception occurred, you can check the SQL Server Errorlog from the time period when the issue was reported and see what errors were reported. You will find the same verbiage reported in the SQL Errorlog as in the application logs. (See errorlog excerpt below)

    Location: "logmgr.cpp" <line number>
    Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
    …
    …
    While acting as a mirroring partner for database ‘<Database name>’, server instance ‘<database name>’ encountered error 3624, status 1, severity 20. Database mirroring will be suspended.  Try to resolve the error and resume mirroring.

    Notice the additional errorlog entry reported above pertaining to Database Mirroring. I find that the database mirroring setup for an user database encountered an assertion which was reported earlier in the SQL Errorlog. The build number for the SQL Server instance in question is 5000 (SQL Server 2005). The first thing you should do is search for the assert expression [!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)] on Bing (Or any other search engine if you are really persistent on using a particular search engine 😉 ).

    Additionally, as the error message points out, run a CHECKDB on the database in question and find out if there are any inconsistencies reported. If yes, then the first task is to restore the last known good backup or correct the inconsistencies before doing anything else.

    Getting back to the search results, you will find that you get KB981273 and KB2403218 in the search results. Pay close attention to the symptoms section in the KB Article as fixes for assertion failures are very specific. If the symptoms do not match, then the KB Article you found might not apply to your situation.

    Symptoms from KB981273:

    In Microsoft SQL Server 2005, when you restart the SQL Server service, error messages that resemble the following are logged in the SQL Server Errorlog file.

    Symptoms from KB2403218:

    Consider the following scenario:

    • You create a database mirroring session between two instances of SQL Server 2005 and SQL Server 2008 and SQL Server 2008 R2 by using the High-Performance (asynchronous) mode. One instance is the principal SQL Server that contains the principal database, and the other instance is the mirror SQL Server that contains the mirror database.
    • A long delay occurs between the principal and mirror databases. For example, one of the following delays occurs:
      • The redo process of the mirror database is slower than the transfer of the transaction from the principal database to the mirror database.
      • A transaction that requires a long time runs on the principal database. For example, an ALTER INDEX REBUILD query runs on the principal database.

    In this scenario, the session is suspended, and a mini-dump file is generated in the log folder on the mirror server.

    As you can see that KB2403218 is what is applicable in this scenario where as the symptoms described in the first KB Article doesn’t match the issue! Next check the release vehicle for the fix for the SQL Server release that you are using and the releases affected by this issue. You will find that there are two builds for SQL Server 2005 which contain the fix for this issue:

    2438344 Cumulative update package 13 for SQL Server 2005 Service Pack 3 (Build: 4315)
    2489409 Cumulative update package 2 for SQL Server 2005 Service Pack 4 (Build: 5254)

    The interesting point to note here is that the SQL Server version for the instance in question is above the build that had the fix for Cumulative Update #13 for SQL Server 2005 Service Pack 3 which had the issue. However, this fix was present for Service Pack 4 (Build: 5000) but was included for the Cumulative Update #2 for Service Pack 4. So, now you would need to either apply CU2 or the latest Cumulative Update package for Service Pack 4 to resolve this issue.

    As always, if in doubt contact Microsoft CSS with assertion mini-dump, SQL Errorlogs and the Windows Event logs. CSS Engineers might request for additional data like profiler traces, filtered dump from the next occurrence or maybe even a repro if the available data is not sufficient to perform a complete root cause analysis and identify the problem.

    To summarize:
    1. Just because an assert condition matches a KB Article, it doesn’t mean that you have identified your issue.
    2. Verify that the symptoms match the condition that you are facing as assert conditions might be common for two completely different issues.
    3. Even though you might be on a higher build than the build version for a particular release vehicle, you still might not have the fix on the Service Pack level that you are on as the fix might have been included in a later cumulative update.
    4. If you cannot find a KB Article match, don’t’ have any inconsistencies in the database and are able to reproduce the scenario consistently, then check if you can reproduce this on the latest available build for the SQL Server release that you are using or contact CSS.

    Debugging is not always a necessity to investigate an issue!! Smile

  • Debugging the deadlock but for the scheduler: Part 2 of 2

    This is the second part of my Deadlocked Schedulers debugging posts. In the first post, I discussed about the difference between a 17888 and 17884 dump and showed the amount of information that can be extracted from a mini-dump that is generated for the first occurrence of a Deadlocked Scheduler issue. In this post, I shall walk you through a 17884 condition.

    The first difference that you will notice with a 17884 message i.e. Deadlocked Schedulers reported in the SQL Errorlog is that no information about the blocking resource is mentioned in the SQL Errorlog unlike the 17888 message. If you inspect the SQL Errorlog, you will see only an error message of the following type:

    2010-09-20 14:04:07.78 Server      ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\LOG\SQLDump0001.txt
    2010-09-20 14:04:07.78 Server      *   09/20/10 14:04:07 spid 27276
    2010-09-20 14:04:07.78 Server      *
    2010-09-20 14:04:07.78 Server      *     Deadlocked Schedulers
    2010-09-20 14:04:07.78 Server      *
    2010-09-20 14:04:07.78 Server      * *******************************************************************************
    2010-09-20 14:04:07.78 Server      * ——————————————————————————-
    2010-09-20 14:04:07.78 Server      * Short Stack Dump
    2010-09-20 14:04:07.82 Server      Stack Signature for the dump is 0x0000000000000285
    External dump process returned no errors.

    As mentioned in my previous posts, the dump process returned no errors (Note message in green). This means that I can extract some useful information regarding the issue from the dump. One of the important things about a Deadlocked Schedulers (17884) is that you need to look through the Errorlogs for any other Access Violation or Assertion that occurred prior to the Deadlocked Schedulers event. Always check for previous errors first. The 17884 error messages are often symptoms pointing to a problem. Before investigating the dump, always review overall system health information such as performance information, event logs, SQL Server error logs, and other application logging information. Understanding prior problems on the system may quickly clarify why SQL Server detected and reported a health error. Previous errors should always be addressed before the investigation continues.

    The common reasons for a Deadlocked Schedulers are:

    1. All schedulers have encountered a 17883 condition.
    2. All workers are blocked on a critical resource.
    3. All workers are executing a long-running query.

    I have very rarely seen #1 being the cause of Deadlocked Schedulers. More often than not it is #2 or #3.

    Now when you load the dump using WinDBG, as noted in the debugging a non-yielding scheduler issue, you will note similar things. When you load the dump using WinDBG, you will see the following information:

    Loading Dump File [D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQL\LOG\SQLDump0001.mdmp]
    User Mini Dump File: Only registers, stack and portions of memory are available

    Comment: ‘Stack Trace’
    Comment: ‘Deadlocked Schedulers’

    The above tells you that this is a mini-dump for a deadlocked schedulers condition and the location from where you loaded the dump. Then I use the command to set my symbol path and direct the symbols downloaded from the Microsoft symbol server to a local symbol file cache on my machine.

    .sympath srv*D:\PublicSymbols*http://msdl.microsoft.com/download/symbols

    Then I issue a reload command to load the symbols for sqlservr.exe. This can also be done using CTRL+L and providing the complete string above (without .sympath), checking the Reload checkbox and clicking on OK. The only difference here is that the all the public symbols for all loaded modules in the dump will be downloaded from the Microsoft Symbol Server which are available.

    .reload /f sqlservr.exe

    Next thing is to verify that the symbols were correctly loaded using the lmvm sqlservr command. If the symbols were loaded correctly, you should see the following output. Note the text in green.

    0:005> lmvm sqlservr
    start end module name

    00000000`00530000 00000000`040de000   sqlservr T (pdb symbols)          d:\publicsymbols\sqlservr.pdbD90C4EC67D442D692A791A19FBA638F2\sqlservr.pdb

    If symbols were not loaded, then you will see an output as shown below.

    0:005> lmvm sqlservr
    start end module name
    00000000`00530000 00000000`040de000 sqlservr (export symbols) sqlservr.exe

    Since I didn’t have any prior errors to the deadlocked schedulers issue in the Errorlog, I don’t have a search string to look for in the dump. This makes it tricky. So I will have to manually look through the callstacks for all the threads in the dump and establish a common pattern. You can use the !uniqstack command to dump out all the unique callstacks in the dump along with the count of the duplicate callstacks.

    0:005> !uniqstack

    Output excerpt from previous command:

    Total threads: 868
    Duplicate callstacks: 810 (windbg thread #s follow):
    4, 8, 10, … … 865, 866, 867

    As you can see above, there are 868 threads in the dump and there are 810 callstacks which are listed as duplicate.

    On manual inspection of the threads using the thread ids mentioned in the Duplicate callstacks list (italicized numbers above), I did find a common pattern. There are multiple threads waiting for locks and there are quite a few threads which are executing in parallel.

    Two common thread stacks that emerged were:

    Waiting on lock:

    0:804> kL100
    ntdll!ZwSignalAndWaitForSingleObject
    kernel32!SignalObjectAndWait
    sqlservr!SOS_Scheduler::Switch
    sqlservr!SOS_Scheduler::SuspendNonPreemptive
    sqlservr!SOS_Scheduler::Suspend
    sqlservr!EventInternal<Spinlock<153,1,0> >::Wait
    sqlservr!LockOwner::Sleep
    sqlservr!lck_lockInternal
    …
    …
    sqlservr!process_request
    sqlservr!process_commands
    sqlservr!SOS_Task::Param::Execute
    sqlservr!SOS_Scheduler::RunTask
    sqlservr!SOS_Scheduler::ProcessTasks
    sqlservr!SchedulerManager::WorkerEntryPoint
    sqlservr!SystemThread::RunWorker
    sqlservr!SystemThreadDispatcher::ProcessWorker
    sqlservr!SchedulerManager::ThreadEntryPoint
    msvcr80!endthreadex
    msvcr80!endthreadex
    kernel32!BaseThreadInitThunk
    ntdll!RtlUserThreadStart

    Parallel thread:

    0:852> kL100

    ntdll!ZwWaitForSingleObject
    KERNELBASE!WaitForSingleObjectEx
    sqlservr!SOS_Scheduler::SwitchContext
    sqlservr!SOS_Scheduler::SuspendNonPreemptive
    sqlservr!SOS_Scheduler::Suspend
    sqlservr!EventInternal<Spinlock<153,1,0> >::Wait
    sqlservr!EventInternal<Spinlock<153,1,0> >::WaitAllowPrematureWakeup
    sqlservr!XchngWait<Spinlock<38,10,0> >
    sqlservr!CXPort::Open
    sqlservr!CXTransLocal::Synchronize
    sqlservr!CQScanXProducerNew::Open
    sqlservr!FnProducerOpen
    sqlservr!FnProducerThread
    sqlservr!SubprocEntrypoint
    sqlservr!SOS_Task::Param::Execute
    sqlservr!SOS_Scheduler::RunTask
    sqlservr!SOS_Scheduler::ProcessTasks
    sqlservr!SchedulerManager::WorkerEntryPoint
    sqlservr!SystemThread::RunWorker
    sqlservr!SystemThreadDispatcher::ProcessWorker
    sqlservr!SchedulerManager::ThreadEntryPoint
    msvcr80!endthreadex
    msvcr80!endthreadex
    kernel32!BaseThreadInitThunk
    ntdll!RtlUserThreadStart

    The next thing that I shall do is pick one of the common relevant functions from the above callstacks and search for the number of occurrences of these frames in the all the threads present in the dump using the !findstack command.

    !findstack sqlservr!LockOwner::Sleep 0

    Excerpt from the output of the above command:

    Thread 054, 1 frame(s) match
    Thread 056, 1 frame(s) match
    Thread 059, 1 frame(s) match
    Thread 061, 1 frame(s) match
    Thread 062, 1 frame(s) match
    …
    …
    Thread 799, 1 frame(s) match
    Thread 800, 1 frame(s) match
    Thread 801, 1 frame(s) match
    Thread 802, 1 frame(s) match
    Thread 804, 1 frame(s) match
    Thread 805, 1 frame(s) match

    !findstack sqlservr!FnProducerThread 0

    Excerpt from the output of the above command:

    Thread 055, 1 frame(s) match
    Thread 057, 1 frame(s) match
    Thread 066, 1 frame(s) match
    Thread 082, 1 frame(s) match
    Thread 087, 1 frame(s) match
    Thread 093, 1 frame(s) match
    …
    …
    Thread 840, 1 frame(s) match
    Thread 843, 1 frame(s) match
    Thread 844, 1 frame(s) match
    Thread 850, 1 frame(s) match
    Thread 852, 1 frame(s) match

    I found that there were 584 threads (67%) waiting for locks and 166 threads (19%) involved in parallelism. Note that the lines colored in blue are the callstacks which I had mentioned above to determine that there were waits on locks and parallelism. This confirms that the problem is due to concurrent requests waiting for lock(s) on object(s) and some of the queries are executing in parallelism. If a CSS engineer is examining this dump, then they could probably extract additional information like the queries being executed by the threads (if the information is available in the dump) using private symbols. If you were monitoring the server using a monitoring tool which captured the query requests, then you could go back to your monitoring repository and get the queries that the above threads were executing using the thread ids that you have identified.

    TIP: The interesting thing to note is the thread IDs that you see above will directly match the OS Thread ID reported in the sys.dm_os_threads os_thread_id column value or the sys.sysprocesses kpid column value. So if you have a monitoring tool that is capturing the output of queries being executed by the active SQL Server sessions, then you can go back to your monitoring repository and dig up the queries which these threads were executing. If such data is not available from the time of the issue, more often that not you will have to end up collecting additional data (profiler traces along with DMV outputs to track blocking chains) for the next problem occurrence!

    Also check if the Deadlocked Schedulers matches any KB Article (update) already released. Remember that deadlocked schedulers is a symptom and the not the cause!! So make sure that if you find a KB Article which talks about a Deadlocked Schedulers issue being addressed in a later build, the symptoms match the symptoms that you are experiencing on your server! If in doubt, ask CSS!

    If you are stuck and have no clue why the issue happened, then this would be a good time to engage Microsoft CSS Support and provide them with the SQL Errorlogs, the dump file and the windows event logs in .txt format for further analysis.

    Reference:
    KB 315263 – How to read the small memory dump files that Windows creates for debugging
    How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888 whitepaper