Anith Gopal

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up.

Hallo, Normalerweise schreibe ich nur ungern ab. Aber in diesem Fall erscheint es mir wichtig, da man immer wieder auf Probleme stößt, die damit einhergehen: Mein Kollege J.C

I recently received a request to shed some light on a few advanced questions about latches and SQL Server. These were good questions so I thought the information might be useful to share with the community.

Pour faciliter la prise en main de cet outil légendaire et pour que vous puissiez tester vous même, je suis parti avec le Starter Kit "BlogEngine” ( http://www.asp.net/community/projects/ ) ; Vous pourrez ainsi prendre les dumps, les ouvrir et utiliser WinDbg en suivant les étapes ci-dessous. J’ai donc copié les sources du Starter Kit dans le répertoire "C:inetpubwwwroot" :   Ensuite, dans la console IIS, j’ai créé une application pour ce répertoire en choisissant le pool d’application "Classic .NET AppPool" : C’est bon, l’application tourne, nous pouvons passer aux choses sérieuses…   Nous retrouvons le processus w3wp.exe avec le gestionnaire de taches, "process explorer" ou la commande suivante : tasklist /FI "IMAGENAME eq w3wp.exe" La prise de dumps peut se faire facilement avec adplus. Voici toutes les informations pour ce faire – Billet précédent : Plan d‘action pour la capture des informations lors d’un problème de production IIS Pour nos tests aujourd’hui, je fais un simple clic droit sur processus dans le gestionnaire de taches puis "Create Dump File" Ouvrons le dump par un double-clic ou en lançant WinDbg puis un glisser/déplacer du fichier .DMP dans l’interface de WinDbg… Un mot : Magnifique !!   Informations disponibles   Nous avons entre les mains une photographie de la mémoire utilisée par le processus

I continue to answer questions about the number of TEMPDB files and trace flag –T1118.   It seems there are plenty of advice blogs, wikis, articles and other resources.   To help clarify this I am going to post my latest e-mail exchange. From: Robert Dorr Sent: Thursday, June 04, 2009 9:08 AM Subject: RE: TempDB / data files per processor   It is not just SGAM in SQL 2000 that encountered contention. SQL Server can still heat up the PFS and other allocation pages on SQL 2005 and 2008 under very heavy use conditions.   The temp table caching added to SQL 2005 has helped but on larger systems we can still get issues when the limits are pushed.

An interesting question came in from a customer a few weeks ago. Did SQL Server change its traditional FIFO method for granting locks in SQL Server 2005 and 2008? Although there is no official documentation (not yet, I’ve put in a request to get this changed), the answer to the question is yes.

A series of questions related to start time, duration, end time, T-SQL waitfor delay command and others crossed my path again this week.     As I was trying to explain it to a fellow engineer and I realized it has become a bit complicated.

In the previous posts we have discussed the working set trim message “ A significant part of sql server process memory has been paged out ” that gets written to the SQL Server Error log. You can find the details about the various parameters that determine when this message is written to the error log in the KB Article

  You might be familiar with the sparse file problem that Bob Dorr has blogged about in the past.

I was asked by our support team to help outline performance counter troubleshooting when the counters don’t appear, are missing or don’t seem to be working as expected.     I agreed to look into it and as you can see from the information below it turned into a much larger effort than I originally planned

SQL Server 2005 introduced a background worker to flush trace event streams.   The trace buffer is flushed when it becomes fully populated but a partially populated trace buffer remains in-memory until events fill the buffer or the background worker flushes the events to the stream.  Periodically the background worker wakes up, checks outstanding trace buffers and flushes them as required. A small 17883 trend has surfaced regarding the trace output taking too long and stalling a scheduler.  The issue occurs when the background worker attempts to flush a buffer and the IO subsystem stalls.  Using the public symbols for the SQL Server the stack would look like the following. ntdll!ZwWriteFile+0xa kernel32!WriteFile+0xf6 sqlservr!DiskWriteAsync+0xee ß This should take very little time but a stalled scheduler indicates it took 60+ seconds sqlservr!CTraceIoBuf::IncrementalFlush+0x18b sqlservr!CTraceFileIoProvider::IncrementalFlush+0×46 sqlservr!CTraceController::IncrementalFlush+0xb9 sqlservr!CTraceManagementTask::Invoke+0×71 sqlservr!CTraceBatchTask::Task+0×183 The trace file location can make a big difference.   The always on trace, controlled by the sp_configure value ‘default trace enabled’, is placed in the LOG directory

From: SQL Server Customer Sent: Friday, February 20, 2009 5:08 AM   I’m often asked by professionals whether CheckDB is still recommended on SQL2k8.  SQL is calculating a checksum to avoid physical page corruption since 2k5. However in my experience (starting with SQL 7.0) I’ve never been able to clearly say that CheckDB is not necessary .. Does anyone have a good advice