Survey: what is the most worrying wait type?

Here’s another survey for you, and I’m really looking forward to seeing and discussing the results of this one.

Imagine you’re the new DBA for a company (which shouldn’t be hard for most of you :-) and a week ago a new release of the client and server code was rolled out, and you cleared out the wait statistics using DBCC SQLPERF. You’ve never monitored wait statistics on this production SQL Server before, although you’ve done so extensively in previous places you’ve worked. Today you look at the aggregate wait statistics from the last week, using a script that presents them in descending order of total wait time for that wait type. You’ve been on vacation for the last week and so don’t know if there have been any complaints about performance since the roll-out.

Survey is now closed – see here for results

Without considering anything except the wait type name, which of the following wait types (listed in alphabetical order) would you be most concerned to see as the top wait type for the past week (i.e. the most aggregate wait time)?

It’s deliberate that there is ┬áno ‘it depends’ option, and no ‘Other’ option for you to type something, so please don’t leave comments complaining about that.

I’ll editorialize the results next week, along with what my pick would be and rationales for picking each of them.


Corruption recovery using DBCC WRITEPAGE

A couple of week ago I kicked off a survey about the extent of your experience with the DBCC WRITEPAGE command. Here are the results:


The “Other” values are:

  • 9 x “Read your post on it, may practice it one day, but it’ll always be last resort to use.”
  • 6 x “I read your post on it.”
  • 4 x “I know about it (from your posts/immersion event) and it’s in my toolbox whenever I get called in to perform disaster recovery.”
  • 1 x “It’s like knowing the Unix recursive delete command as root, dangerous and powerful.”
  • 1 x “Not since 2k pages, and then PageEdit.exe was easier.”

For those of you who don’t know what DBCC WRITEPAGE is, it’s an undocumented command that allows you to make direct changes to data file pages. It’s an extremely powerful command that is very useful for creating corrupt databases, and in extreme cases, for helping to repair otherwise irretrievably-corrupt databases. I blogged about it a year ago in this post.

I never advocate using it to repair a corrupt database and then continue using that database in production, and in fact I refuse to do that if a client asks as there are just too many potential problems that could occur. I’ll only ever use it to help recover data from a damaged database, where there’s already a copy of the databases files in existence, and the client has signed an agreement acknowledging that any data file I use it on is a duplicate, is not on a production instance, and changes to it will in no way affect production.

There are all kinds of things I’ve used DBCC WRITEPAGE for to allow data recovery to progress, and in my Advanced Corruption Recovery Techniques course on Pluralsight course I demonstrate one of them, which I’m going to describe here.

Imagine a database that’s suffered corruption and there’s an off-row LOB data value that’s inaccessible because the owning data row is corrupt. Your mission is to retrieve the data. And to make it impractical to manually piece together the value using DBCC PAGE dumps, the LOB value is a few megabytes, so it’s stored on hundreds of linked text pages.

The process:

  • Create another row with an off-row LOB value (the ‘dummy’ row)
  • Find out the dummy row’s page and offset on the page
  • Calculate the offset of the dummy LOB value’s in-row root in the variable-length portion of the record
  • Calculate the offset of the dummy LOB in-row root’s size, text timestamp, and pointer fields
  • Find the corrupt row’s LOB in-row-root’s size, text timestamp, and pointer field values
  • Use DBCC WRITEPAGE to overwrite the dummy LOB in-row root fields
  • Select from the dummy row and you’ll get back the LOB value you wanted to save

Cool, eh? Desperate times call for clever measures, and with procedures like this I regularly recover data from client databases that Microsoft and other data recovery firms either can’t help with or have given up on. It’s not a fast process as it can take quite a while to figure out exactly what to modify to make things work, but when the data comes back, the clients are always ecstatic.

And with some knowledge of the data structures in a data file, careful use of DBCC WRITEPAGE, and plenty of practice, you can do it too.

Or call us to help you :-)

More on using Transaction SID from the transaction log

Back in 2012 I blogged about using fn_dblog and fn_dump_dblog to figure out the point at which something occurred that you’d like to restore to just before (e.g. a table drop). I also mentioned that you can use the SUSER_SNAME () function on on the [Transaction SID] column for the LOP_BEGIN_XACT log record of the operation to find out who performed the operation.

Yesterday in our IE2 Performance Tuning class in Tampa, someone asked me what the [Transaction SID] column would show if someone had run EXECUTE AS. As I wasn’t 100% certain, I decided to test and write a quick blog post.

First off I’ll set up a database and table to use:

USE [master];
USE [Test];
GO 5

Next I’ll create a Kimberly user for a SQL login, and a Katelyn user for a Windows login:

-- Create Kimberly login and user
CREATE LOGIN [KimberlyLogin] WITH PASSWORD = 'NiceWife';
CREATE USER [KimberlyUser] FOR LOGIN [KimberlyLogin];
EXEC sp_addrolemember N'db_owner', N'KimberlyUser';

-- Create Katelyn user
EXEC sp_addrolemember N'db_owner', N'KatelynUser';

Now I’ll delete a single row as me and each of the users and logins:

-- Delete as me
DELETE FROM [TestTable] WHERE [c1] = 1;

-- Now delete as Kimberly user
EXECUTE AS USER = N'KimberlyUser';
DELETE FROM [TestTable] WHERE [c1] = 2;

-- Now delete as Kimberly login
EXECUTE AS LOGIN = N'KimberlyLogin';
DELETE FROM [TestTable] WHERE [c1] = 3;

-- Now delete as Katelyn user
EXECUTE AS USER = N'KatelynUser';
DELETE FROM [TestTable] WHERE [c1] = 4;

-- Now delete as Katelyn login
DELETE FROM [TestTable] WHERE [c1] = 5;

Finally I’ll pull the [Transaction SID] for each of the delete operations and pass it into SUSER_SNAME ():

	[Operation], [Transaction Name], [Transaction SID],
	SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?]
FROM fn_dblog (NULL, NULL)
AND [Transaction Name] = 'DELETE';
Operation       Transaction Name  Transaction SID                                             WhoDidIt?
--------------- ----------------- ----------------------------------------------------------- -------------------
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EE8030000  APPLECROSS\paul
LOP_BEGIN_XACT  DELETE            0x9A9A69BEACF67E4994E2F2DEE35BC02F                          KimberlyLogin
LOP_BEGIN_XACT  DELETE            0x9A9A69BEACF67E4994E2F2DEE35BC02F                          KimberlyLogin
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EFE030000  APPLECROSS\Katelyn
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EFE030000  APPLECROSS\Katelyn

So the answer is that the log record contains the SID of who you’re executing as. The only way to tell who is really running the code would be through auditing.