First published on MSDN on May 06, 2010
I thought i would post some answers to questions I received during my pre-conference seminar at Europe PASS 2010:
Q: You said that trace flag 2528 disables parallelism for DBCC CHECKDB. Is there any way to force CHECKDB to use a parallel threads?
A: No. If the trace flag 2528 is not enabled and ‘max degree of parallelism’ is 0 or > 1, then the engine can decide to use parallel threads to scan the information required for DBCC CHECKDB. How do you know? Well, one way is to see if you see CXPACKET waits for the request. Another is to see if more than one task in sys.dm_os_tasks shows up for your session running CHECKDB. But there is no trace flag to force a CHECKDB to run in parallel (like you force a MAXDOP query hint for a query).
Q: You mentioned that for a deferred transaction a session might get blocked by a session_id = –3. What other “negative” session ids exist?
A: There are only two others:
-2 = Active DTC transaction with no enlisted sessions. Often called an “orphaned” DTC transaction. This term may not be the best for this situation. See Robert Dorr’s recent blog on this at
http://blogs.msdn.com/psssql/archive/2010/04/20/how-it-works-orphan-dtc-transaction-session-spi...
-4 = In the code this means “latch in transition”. You should only see this in the “blocking session” such as the blocking_session_id column of sys.dm_exec_requests. So what the heck does this mean? Well, when we are trying to show who owns a latch you are waiting on, we use an internal mechanism called a spinlock to find the owner of the latch. If you wait on a spinlock for a long time you can chew up CPU so we use the spinlock here in a “minimal” mode as to not use up much CPU. This means that we may not be able to acquire the spinlock to tell you the true session_id that owns the latch. When this happens we simply mark it as –4. These should be very rare situations and I don’t expect actually you will ever see –4, but if you do the wait_type should be some type of latch.
After reading this you may be asking why isn’t there a –1 SPID value? Well there used to be. Prior to SQL 7.0, SPID=-1 was reserved for a special case designating an orphaned lock. It only showed up in defects found with the product such as this article:
http://support.microsoft.com/kb/216370
Q: You said in the talk that -m<app name> would limit the SQL Server to single user mode and only allow a single connection from the Application name as listed in the parameter. Does this syntax allow for Application Names with embedded spaces?
A: Yes it does. Let’s say you want the server to start in single user mode and only allow the Query Window of SSMS to connect. You would start the server like this: (In my example I have a named instanced called sql2008)
net start mssql$sql2008 /m”Microsoft SQL Server Management Studio – Query”
Any attempt by an application other than the Database Engine Query feature of SSMS will be denied a connection to the server
Q: How do I drop the statistics or indexes that seem to get left around if Database Tuning Advisor has a problem?
A: We actually have this one documented in our Books Online. Read up on how to find these objects at:
http://msdn.microsoft.com/en-us/library/ms190172.aspx
There are all types of examples in various blogs on the web to write a script to automatically detect and delete these. If DTA is closed properly this should never be a problem but if you close the application unexpectedly it is possible for some of these to be left around.
Bob Ward
Microsoft
