Paul’s awesome ribs recipe

On Friday I made my signature pork ribs for our good friend Bob Ward (along with Kimberly’s truffled cauliflower mash and caramelized broccoli) and I promised to blog the ribs recipe. I’ve been making ribs like this for about ten years – they’re dry and you have to tear the meat off the ribs with your teeth, rather than the usual American ribs where the meat falls off on its own. I don’t remember which BBQ recipe book I originally got the recipe from but it’s evolved over the years into what’s below.

Let me know if you try it – enjoy!

Ingredients:

  • 1 large rack of baby-back pork ribs
  • 1 full bulb of garlic
  • 1/2 cup soy sauce
  • 1/4 cup cream sherry (e.g. Sheffield)
  • 2 tablespoons honey

Prepare the ribs: wash the rack and cut it into individual ribs. Arrange in a deep rectangular dish (e.g. Pyrex or Le Creuset) on their side – layed out like ( ( ( ( (. Optional: discard weird-shaped or extra pieces of meat.

Prepare the sauce: pour the soy sauce into a Pyrex measuring jug. Pour the sherry in too. Pour the honey into the mixture. It’ll settle at the bottom of the jug. Peel the garlic (vary the amount based on your garlic-loving preference) and use a garlic press (I use this one) to get the ‘meat’ of each garlic glove into the sauce jug. Discard the skin of each garlic glove.

Marinate the ribs: Mix up the sauce with a small fork to get the honey mixed in. Pour the sauce evenly over the ribs. Cover the rib dish with cling wrap and let it sit in the fridge for 4-24 hours.

Prepare to cook: set the oven to broil and set the top oven shelf to be one level down from the very top. Line a deep rectangular baking/broiling pan with aluminum foil. Arrange the ribs on the side on the foil in two rows like ( ( ( ( (.

Broil: broil the ribs as prepared for 5 minutes. Remove from the oven and turn the ribs over in place. Drizzle the remains of the sauce and garlic over each rib using a small spoon. Broil for 4 more minutes. Remove from the oven.

Bake: after broiling the ribs the second time, set the same over to bake at 325F. Turn the ribs a second time and drizzle any remaining sauce on them. Bake on the bottom shelf for 20 minutes. Increase heat to 350F and bake for 10 more minutes. Increase heat to 375F and bake for 10 more minutes. You want the outside of the ribs to be blackening and caramelizing.

Remove and place in a serving dish. Eat immediately with your hands.

For garlic lovers, get a teaspoon and scoop the concentrated garlic mush and sauce from the bottom of the baking pan – yummmmm!

Extra ribs can be eaten cold over the next few days or reheated by baking for 5-8 minutes at 375.

Delayed Durability in SQL Server 2014

One of the cool new features in SQL Server 2014 is delayed durability (available in all Editions), which is described in detail in Books Online here.

I think I’m going to see a lot of people turn this on, as you can get a profound increase in transaction throughput with the right workload. However, I also think a lot of people are going to turn this on without realizing the potential for data loss and making the appropriate trade off.

Why can it give a throughput boost?

I put together a contrived workload with a small table where 50 concurrent clients are updating the same rows, and the database log is on a slow I/O subsystem. Here’s a graph showing my test:

delayed Delayed Durability in SQL Server 2014

At the obvious change point, that’s where I enabled delayed durability, with all transactions being forced to use it. Before the change, the number of Transactions/sec is equal to the number of Log Flushes/sec, as each transaction is holding locks that block all other transactions (I told you it’s a contrived workload). So why the profound jump in Transactions/sec when I forced delayed durability?

Under normal circumstances, when a transaction commits, the commit doesn’t complete until the log block (see this blog post for more details) containing the LOP_COMMIT_TRAN log record for the transaction has been flushed to disk and the write is acknowledged back to SQL Server as having completed, providing the durability of the transaction (the D in the ACID properties of the transaction). The transaction’s locks cannot be dropped until the log flush completes.

In my workload, all the other transactions are waiting for the one that is committing, as they all need the same locks, so Transactions/sec is tied to Log Flushes/sec in this case.

With delayed durability, the transaction commit proceeds without the log block flush occurring – hence the act of making the transaction durable is delayed. Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB. This means that transactions commit a lot faster, hold their locks for less time, and so Transactions/sec increases greatly (for this workload). You can also see that the Log Flushes/sec decreased greatly as well, as previously it was flushing lots of tiny log blocks and then changed to only flush maximum-sized log blocks.

Note:

  • I was forcing all transactions to be delayed durable, but the facility exists to make the delayed durability choice per transaction too (see Books Online for more details).
  • There’s a bit more to the log block flushing too: under delayed durability, a log block will flush when it fills up, or if a non-delayed durable transaction commits, or if the new sp_flush_log proc is executed.

My good friend Aaron Bertrand over at SQL Sentry has a long post about delayed durability that looks into it’s performance implications in a little bit more depth so I recommend you check out his post as well.

So this looks great, for the right type of workload. But I bet you’re thinking:

What’s the catch?

Your transactions aren’t durable when they commit. Simple.

Now you may be thinking that if the system crashes, the most you’re going to lose is up to 60KB of transaction log. Wrong. If that last log block contains the LOP_COMMIT_TRAN log record for a long-running transaction, when the system crashes, and that log block isn’t on disk, that whole transaction will roll back during crash recovery. So the potential for work/data loss is greater than just 60KB.

And there’s more:

  • Log backups will not back up that unflushed log block, as it’s not on disk, so non-durable committed transactions may not be contained within a log backup.
  • Non-durable transactions that have committed are not protected by synchronous database mirroring or a synchronous availability group either, as these rely on log block flushes (and transmission to the mirror/replica).

For critical transactions, an sp_flush_log can be used, or per-transaction delayed durability used instead.

So the million-dollar question is:

Should I enable delayed durability?

It depends. Is your business comfortable making the throughput vs. durability trade off? Does enabling it give a throughput boost? If yes to both, go ahead. If no to either, don’t enable it. That’s a very simplistic way of thinking about it, but that’s what it boils down to really.

There are lots of other things you can do to increase the throughput and performance of the transaction log, and I explained them in a blog post series:

As I stated above though, I think a lot of people are going to be wowed by the throughput boost (if their workload benefits) from delayed durability and see this as a no-brainer, without considering the potential for data loss.

Tempting, isn’t it?

Bug: Error: 3449 and server restart during DBCC CHECKDB

This is a quick post to let you know about a bug that a few people are hitting when running DBCC CHECKDB. The symptoms are a series of errors in the error log plus SQL Server forcibly shuts itself down and restarts. I’ve heard of people hitting the bug on SQL Server 2014 and SQL Server 2012 SP1. Microsoft has confirmed to me that it’s a bug and they’re “investigating a fix for the issue.”

The error log symptoms look something like I show below:

2014-11-18 09:04:15.69 spid64      The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001EE6FD8000 in file 'C:\SQLskills\\Company_file2.ndf:MSSQL_DBCC23'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-11-18 09:04:15.72 spid64      Error: 3314, Severity: 17, State: 3.
2014-11-18 09:04:15.72 spid64      During undoing of a logged operation in database 'Company', an error occurred at log record ID (887654:3321:14). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2014-11-18 09:04:34.38 spid64      Error: 831, Severity: 20, State: 1.
2014-11-18 09:04:34.38 spid64      Unable to deallocate a kept page.
2014-11-18 09:04:34.40 spid64      Error: 3449, Severity: 21, State: 1.
2014-11-18 09:04:34.40 spid64      SQL Server must shut down in order to recover a database (database ID 23). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

My speculation: the 665 error is from the snapshot file that DBCC CHECKDB creates hitting an NTFS limitation on the number of file fragments in a sparse file. This causes the snapshot creation to fail. The failure causes the undo of a log record in the snapshot to fail (remember that a database snapshot undergoes crash recovery to make it transactionally consistent). This failure then leads to SQL Server thinking it has to forcibly restart to recover the snapshot database, which is should never do for a snapshot – that seems to be the bug IMHO.

I’ll update this post as soon as I hear about a fix and the builds that the fix is in.

I think this is a rare bug to hit, it seems IMHO to be a regression (from builds people are reporting), and you can help yourself to avoid it by:

  • Creating your own database snapshot, on a volume without file-system free space fragmentation, and running DBCC CHECKDB against the snapshot
  • OR, trying to run DBCC CHECKDB when there isn’t a significant amount of change occurring in the database, so the database snapshot doesn’t have to become very large
  • OR, using the backup-copy-restore-check method of running DBCC CHECKDB on a restored backup of the database on another server

If this bug has happened to you, please leave a comment with the build number you’re using and I’ll pass on the information to Microsoft.

Thanks

Calling all user group leaders! We want to present for you in 2015!

Starting in 2015 we at SQLskills would love to present remotely for your user group, anywhere in the world. Kimberly and I recently did a series of remote presentations from Redmond, WA, US to Australia and New Zealand, including two-way video in some cases, and the bandwidth worked great. It’s not feasible for us to travel to user groups or SQL Saturdays unless we’re already in that particular city, but remote presentations are easy to do and are becoming more and more popular. This way we can spread the community love around user groups everywhere that we wouldn’t usually get to in person.

All SQL Server user groups can get Lync accounts for free and that’s what we used for our presentations. If you have some other mechanism you usually use, we can accommodate that too.

Calling all user group leaders! If you’d like one of us (me, Kimberly, Jon, Erin, Glenn) to present remotely for you in 2015 (or maybe even multiple times), send me an email with:

  • Details of which user group you represent
  • The usual day of the month, time, and timezone of the user group
  • Which months you have available, starting in January 2015

And I’ll let you know who’s available with what topics so you can pick. We’ll also send you some swag to give out to attendees.

What’s the catch? There is no catch. We’re just stepping up our community involvement next year and we all love presenting :-)

And we’re serious about anywhere in the world – doesn’t matter what the timezone difference is – we’ll make it work.

Edit on November 25th: FYI – it’s filling up fast as we’ve already heard from more than 45 user groups! Second Tuesday of the month has gone all year.

We’re really looking forward to engaging with you all!

Cheers

PS By all means pass the word on to any SharePoint and .Net user group leaders you know too.

Capturing IO latencies for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I/O latencies that occurred over a period of time.

The script does the following:

  • Creates two temporary tables
  • Captures the output from sys.dm_io_virtual_file_stats into the first table
  • Waits for a configurable delay (line 41 in the script – I made it 30 minutes in the example)
  • Captures the output from sys.dm_io_virtual_file_stats into the second table
  • Provides my usual virtual file stats output on the results

The original code and explanation about using the DMV are here.

Enjoy!

/*============================================================================
  File:     ShortPeriodIOLatencies.sql

  Summary:  Short snapshot of wait stats

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out

  For more scripts and sample code, check out http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes (e.g. in a
  for-sale commercial tool). Use in your own environment is encouraged.
  You may republish altered code as long as you include this copyright and
  give due credit, but you must obtain prior permission before blogging
  this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2];
GO

SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
	   [num_of_writes], [io_stall_write_ms], [io_stall],
	   [num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats1
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
GO

WAITFOR DELAY '00:30:00';
GO

SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
	   [num_of_writes], [io_stall_write_ms], [io_stall],
	   [num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats2
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
GO

WITH [DiffLatencies] AS
(SELECT
-- Files that weren't in the first snapshot
        [ts2].[database_id],
        [ts2].[file_id],
        [ts2].[num_of_reads],
        [ts2].[io_stall_read_ms],
		[ts2].[num_of_writes],
		[ts2].[io_stall_write_ms],
		[ts2].[io_stall],
		[ts2].[num_of_bytes_read],
		[ts2].[num_of_bytes_written]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[file_handle] = [ts1].[file_handle]
    WHERE [ts1].[file_handle] IS NULL
UNION
SELECT
-- Diff of latencies in both snapshots
        [ts2].[database_id],
        [ts2].[file_id],
        [ts2].[num_of_reads] - [ts1].[num_of_reads] AS [num_of_reads],
        [ts2].[io_stall_read_ms] - [ts1].[io_stall_read_ms] AS [io_stall_read_ms],
		[ts2].[num_of_writes] - [ts1].[num_of_writes] AS [num_of_writes],
		[ts2].[io_stall_write_ms] - [ts1].[io_stall_write_ms] AS [io_stall_write_ms],
		[ts2].[io_stall] - [ts1].[io_stall] AS [io_stall],
		[ts2].[num_of_bytes_read] - [ts1].[num_of_bytes_read] AS [num_of_bytes_read],
		[ts2].[num_of_bytes_written] - [ts1].[num_of_bytes_written] AS [num_of_bytes_written]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[file_handle] = [ts1].[file_handle]
    WHERE [ts1].[file_handle] IS NOT NULL)
SELECT
	DB_NAME ([vfs].[database_id]) AS [DB],
	LEFT ([mf].[physical_name], 2) AS [Drive],
	[mf].[type_desc],
	[num_of_reads] AS [Reads],
	[num_of_writes] AS [Writes],
	[ReadLatency(ms)] =
		CASE WHEN [num_of_reads] = 0
			THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
	[WriteLatency(ms)] =
		CASE WHEN [num_of_writes] = 0
			THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
	/*[Latency] =
		CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
			THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,*/
	[AvgBPerRead] =
		CASE WHEN [num_of_reads] = 0
			THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
	[AvgBPerWrite] =
		CASE WHEN [num_of_writes] = 0
			THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
	/*[AvgBPerTransfer] =
		CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
			THEN 0 ELSE
				(([num_of_bytes_read] + [num_of_bytes_written]) /
				([num_of_reads] + [num_of_writes])) END,*/
	[mf].[physical_name]
FROM [DiffLatencies] AS [vfs]
JOIN sys.master_files AS [mf]
	ON [vfs].[database_id] = [mf].[database_id]
	AND [vfs].[file_id] = [mf].[file_id]
-- ORDER BY [ReadLatency(ms)] DESC
ORDER BY [WriteLatency(ms)] DESC;
GO

-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2];
GO

Survey: how much server memory on your largest machine?

One of the trends I’m seeing this year is a slow increase in the number of people who have servers with very large amounts of memory – so time for a survey!

I’d like to know how much memory is installed on your largest server that runs SQL Server (not what max server memory is set to, how much physical memory is installed).


I’ll editorialize the results in a week or two.

Thanks!

PS If you can’t see the vote button, it’s a browser issue so please try a different one or just hit ‘Enter’ after making a selection and it should register your vote.

Capturing wait statistics for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The first one on the list is a simple script to allow you to capture all the waits that occurred over a period of time.

The script does the following:

  • Creates two temporary tables
  • Captures the output from sys.dm_os_wait_stats into the first table
  • Waits for a configurable delay (line 41 in the script – I made it 30 minutes in the example)
  • Captures the output from sys.dm_os_wait_stats into the second table
  • Provides my usual wait stats output on the results

Enjoy!

/*============================================================================
  File:     ShortPeriodWaitStats.sql

  Summary:  Short snapshot of wait stats

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes (e.g. in a
  for-sale commercial tool). Use in your own environment is encouraged.
  You may republish altered code as long as you include this copyright and
  give due credit, but you must obtain prior permission before blogging
  this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2];
GO

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
       [max_wait_time_ms], [signal_wait_time_ms]
INTO ##SQLskillsStats1
FROM sys.dm_os_wait_stats;
GO

WAITFOR DELAY '00:30:00';
GO

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
       [max_wait_time_ms], [signal_wait_time_ms]
INTO ##SQLskillsStats2
FROM sys.dm_os_wait_stats;
GO

WITH [DiffWaits] AS
(SELECT
-- Waits that weren't in the first snapshot
        [ts2].[wait_type],
        [ts2].[wait_time_ms],
        [ts2].[signal_wait_time_ms],
        [ts2].[waiting_tasks_count]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[wait_type] = [ts1].[wait_type]
    WHERE [ts1].[wait_type] IS NULL
    AND [ts2].[wait_time_ms] > 0
UNION
SELECT
-- Diff of waits in both snapshots
        [ts2].[wait_type],
        [ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [wait_time_ms],
        [ts2].[signal_wait_time_ms] - [ts1].[signal_wait_time_ms] AS [signal_wait_time_ms],
        [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] AS [waiting_tasks_count]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[wait_type] = [ts1].[wait_type]
    WHERE [ts1].[wait_type] IS NOT NULL
    AND [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] > 0
    AND [ts2].[wait_time_ms] - [ts1].[wait_time_ms] > 0),
[Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM [DiffWaits]
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2];
GO

August 2015 London IE1/IEPTO-1 class open for registration

Through popular demand we’ve managed to juggle some of our schedule around and found space to fit in our only class of 2015 in the UK, and it’s open for registration!

We’ll be teaching our signature IEPTO-1 (formerly IE1) Immersion Event on Performance Tuning and Optimization.

The class will be August 24-28, and we’ve got a discount for registering in 2014 and a discount for prior students:

  • Regular price is US$3,995
  • Early-bird price is US$3,295 for registrations in 2014 (increasing to $3,495 in 2015)
  • Prior student special price is US$3,195 for registration at any time

Sneak preview: we’ll likely be bringing IEPTO2 to Dublin in October 2015, through our lovely friends at ProData.

You can get all the details on the class page here.

We hope to see you there!

How to answer questions politely and correctly

Back in August I wrote a long post about how to ask questions politely and correctly to address what I see as a growing volume of poorly asked questions by people who often don’t have the common courtesy to be polite and also often come across as feeling entitled to an answer. Based on the responses, and private discussions, many of you out there see the same trend and are dismayed by it.

This is the necessary corollary to that post – how to answer questions politely and correctly, in my opinion – because I see people being discourteous and sometimes unprofessional in their replies. I’ve woven in some of the replies and private distribution list discussions I’ve had over the last two months as well – thanks to those involved – you know who you are.

I’ll start out by saying that some of you will disagree with elements of what I state below. That’s cool, this is just my opinion – vive la différence, and all that – but don’t expect to convince me to change my views. We’ll agree to disagree :-)

Ignorance is Not Stupidity

Probably the number one sin I see people committing when answering questions is giving an attitude to the original poster (who I’ll call the OP from now on) that they’re stupid/lacking/deficient/lazy in some way for not knowing the answer themselves.

Now, if a simple Google search would have found the answer, then I can understand some frustration on the part of the answerer, and I suffer from it myself, but that’s no excuse to be rude or belittling. I even shy away from posting Let Me Google That For You links in such cases as I think that comes across as too snarky, and I like to stay polite as much as I can. But sometimes I’ll just post a Google search URL (especially on Twitter), which does the same thing, but without the added snark, as that would just make me look angry. Others disagree with this sentiment, I know, and will happily post LMGTFY links – each to his/her own.

If it’s not a simple Google search, or I can tell from the question that the OP wouldn’t know what to search on, or how to make sense of the search results, or know which one to choose, then I’ll answer politely and explain the answer. Even if it’s something really simple about SQL Server. (Also check out the insightful comments about Google searches in the comment from @sqlhandle.)

As I explained in my post Ignorance is not stupidity back in 2011, everyone in the world starts with zero knowledge about SQL Server. I knew zero about SQL Server when I joined Microsoft from DEC in February 1999 (15 years ago – OMG – I’m getting old!! :-). Especially if the OP is someone you don’t know, give them the benefit of the doubt and treat them with respect for having the guts to ask a question publicly to get help. Don’t slap someone down for not knowing the answer. That’s the height of conceit, it’s bullying, and it’ll likely drive the OP away from our wonderful online SQL Server community.

Ok – that’s one of my hot-button topics done.

Posting Links in Question Answers

Here’s another one: posting links in question answers.

There was an interesting debate on the MVP email list a week or so ago about this, where someone said they hesitate to post blog post links as an answer because it can come across as self-promotion. I vehemently argued against that point of view, and I continue to believe that posting blog post links in the answer (or even as the answer) is entirely justified.

Self-promotion is where you’re posting something solely to get clicks on a link, or to drive traffic to your website for some business purpose – which of course is bad and you shouldn’t do it in the answer to a technical question – unless it’s directly relevant in some way. Posting a link to a blog post that contains the answer that the OP needs, or helps explain the answer, is certainly not self promotion. It doesn’t matter that the link is to a blog post on your company website, that’s just where you blog, and if it makes them aware of your company, then I think that’s fair recompense for your time in answering a community question. it’s not blatant self-promotion.

I also don’t buy the argument that a bunch of the contents of said blog post should be reiterated in the answer, just to give some meat to the answer in the thread so the thread is ‘self-contained’, or because blog posts move. One of the reasons I blog about things is so I can reference them in classes and online, so I can avoid repeating myself and point people at a deeper reference as part of answer, or as the complete answer. This is especially important for question mediums like Twitter.

If in doubt, consult whatever guidelines exist for the forum/distribution list/medium on which you’re answering. And if I just drop in a blog post link, I’ll always say something like ‘If you have any follow-up questions, feel free to ask’.

Now, saying that, you need to be very confident that your blog post is actually correct and actually answers the question (or contributes to your answer).

Answering the Actual Question

Which brings me to my next point: make sure you’re answering the actual question.

Often I see someone post an answer to the question that shows that either a) they didn’t read the question to see what the OP was actually asking for, or b) they didn’t understand the question or what the OP was actually asking for.

This is quite prevalent on forums such as MSDN, where I’ll see people post just for the sake of posting to try to increase their forums points/score. This is just daft. What’s really interesting is that I don’t see this behavior at all on #sqlhelp, as the only merit gained from answering questions on Twitter is community respect, not some silly score. Other forums do it better by allowing up and down voting, which increases of decreases your score. I applaud people who answer lots of questions correctly, but if you’re only doing it to have a higher score than everyone else, then IMHO you need to go out and get a life.

Don’t post at all if you don’t know the answer and can’t help, otherwise you’re just noise that’s obscuring the answer for the OP and putting people off from following and helping out in that forum. This is what stopped me answering corruption questions on MSDN and other forums – having to continually (nicely) correct people who were answering incorrectly and then being berated for it.

If there isn’t enough information in the question to answer it correctly, ask for more information. Don’t just assume. Or maybe give a couple of different answers, for different conditions or SQL Server versions, state as much, and ask for clarification. If you assume some piece of information that can change the answer, you may be doing a huge disservice to the OP by giving them an answer that’s wrong for their situation.

Don’t just answer: It Depends.

Much of the time the answer really does start with It Depends, but you then need to explain why it depends, what it depends, how it depends, etc. It Depends is a valid start to an answer IMHO. See my post It Depends. It really, really does for more on this.

If you don’t have time to answer properly, don’t answer at all. A half answer, or a non-answer wastes everyone’s time. Step away and let someone else answer.

If the medium where the question is being asked isn’t appropriate to the question, direct the OP at an alternative medium. For instance, if someone posts a #sqlhelp question on Twitter asking for an explanation of whether to use one join type or another, or a comparison between mirroring and availability groups based on some facet of operation, they’ll be directed by someone to post the question on a forum so that longer answers can be given.

Summary

It all comes down to this: give a good answer, that answers the question, provides references if necessary, and leaves the OP feeling like they’ve had a good interaction with the SQL Server community.

Even if they don’t ask politely or correctly, don’t be a jerk when you answer. You can politely point out how to ask the question. The days of being able to hide anonymously on the Internet are well past – and your response is captured for all eternity, so take pride in answering politely and correctly.

Again, don’t be a jerk. Treat people with respect, and if they seem to be lacking in some way, educate them. But be nice about it.

Thanks

February 2015 Sydney IE2/IEPTO-2 class open for registration

We’ve managed to juggle some of our schedule around and found space to fit in our only class of 2015 in Australia, and it’s open for registration!

We’ll be bringing Jonathan and Erin with us to Sydney to teach our signature IEPTO-2 (formerly IE2) Immersion Event on Performance Tuning and Optimization.

The class will be February 23-27, and we’ve got a deep discount for prior students who’ve taken our IE1/IEPTO-1 class:

  • Regular price is US$3,995
  • Early-bird price is US$3,495 for registrations in 2014
  • Prior student special price is US$2,750

We’ll be giving registration priority to prior students as this is the only class we’ll be teaching in Australia in 2015, due to our schedule constraints.

You can get all the details on the class page here.

We hope to see you there!