Time for another survey - this time around memory configuration.

Here is some code to run if you're on SQL Server 2008 and 2008 R2 (thanks Jonathan!):

SELECT
    [physical_memory_in_bytes] AS [PhysMemBytes],
    [physical_memory_in_use_kb] AS [PhysMemInUseKB],
    [available_physical_memory_kb] AS [PhysMemAvailKB],
    [locked_page_allocations_kb] AS [LPAllocKB],
    [max_server_memory] AS [MaxSvrMem],
    [min_server_memory] AS [MinSvrMem]
FROM
    sys.dm_os_sys_info
CROSS JOIN
    sys.dm_os_process_memory
CROSS JOIN
    sys.dm_os_sys_memory
CROSS JOIN (
    SELECT
        [value_in_use] AS [max_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'max server memory (MB)') AS c
CROSS JOIN (
    SELECT
        [value_in_use] AS [min_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'min server memory (MB)') AS c2

On 2012, the code is (thanks Bob!):

SELECT
    [physical_memory_kb] AS [PhysMemBytes],
    [physical_memory_in_use] AS [PhysMemInUseKB],
    [available_physical_memory_kb] AS [PhysMemAvailKB],
    [locked_page_allocations_kb] AS [LPAllocKB],
    [max_server_memory] AS [MaxSvrMem],
    [min_server_memory] AS [MinSvrMem]
FROM
    sys.dm_os_sys_info
CROSS JOIN
    sys.dm_os_process_memory
CROSS JOIN
    sys.dm_os_sys_memory
CROSS JOIN (
    SELECT
        [value_in_use] AS [max_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'max server memory (MB)') AS c
CROSS JOIN (
    SELECT
        [value_in_use] AS [min_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'min server memory (MB)') AS c2

And on 2005/2000, if you want to participate, send me the amount of memory on the Windows server, plus the min and max server memory configuration for SQL Server. There's some PowerShell in one of the early comments that will help (thanks Eric!).

You can either send me email with the info or leave a comment on this post. I'll editorialize the results in a couple of weeks.

Thanks!

Back in November I kicked off a survey that had you run some code to get some details about your cluster keys, nonclustered indexes, and table size. I got results from more than 500 systems across the world, resulting in 97565 lines of data - thanks!

The purpose of the survey is to highlight one of the side-effects of not adhering to the general guidelines (i.e. there are exceptions to these) for choosing a clustered index key. It should be, if possible:

1. Narrow
2. Static
3. Ever-increasing
4. Unique

The survey and this post are intended to show how not adhering to Rule #1 can lead to performance problems.

Both Kimberly and I have explained in the past the architecture of nonclustered indexes - where every nonclustered index row has to have a link back to the matching heap or clustered index record. The link must be a unique value as it must definitively match a single record in the heap or clustered index. For nonclustered indexes on a table with a clustered index, this link is the cluster key (or keys) as these are guaranteed to be unique. Ah, you say, but what about when the clustered index is NOT defined as unique? That's where Rule #4 comes in. For a non-unique clustered index, there will be a hidden 4-byte column (called the uniquifier) added when necessary as a tie-breaker when multiple clustered index records have the same key values. This increases the clustered index key size by 4 bytes (the uniquifier is an integer) when needed.

But I digress. The crux of the matter is that every nonclustered index record will include the cluster keys. The wider the cluster key size is (e.g. a few natural keys), the more overhead there is in each nonclustered index record, compared to using, for instance, an integer (4-byte) or bigint (8-byte) surrogate cluster key. This can mean you've got the potential for saving huge amounts of space by moving to smaller clustered index keys - as we'll see from the data I collected.

The survey code I got you to run returned:

1. Number of nonclustered indexes
2. Number of cluster keys
3. Total cluster key size
4. Number of table rows
5. Calculation of bytes used in all the nonclustered indexes to store the cluster keys in each row

I did not take into account filtered indexes in 2008, or variable length cluster key columns, as to be honest although these will make a difference, for the purposes of my discussion here (making you aware of the problem), they're irrelevant. It also would have made the survey code much more complex for me to figure out :-)

Now let's look at some of the results I received. To make things a little simpler, I discarded results from tables with less then ten thousand rows, and with clustered index key sizes less than 9. This dropped the number of data points from 97565 down to 22425.

The graphs below show the estimated amount off savings that could be had in GB from moving to an 8-byte bigint, plotted against the first four factors in the list above.

And here are the top 20 in terms of potential savings so you can see how the rough table schema:

NCIndexes  ClusterKeys  KeyWidth  TableRows      KeySpaceGB  SavingsGB
---------  -----------  --------  -------------  ----------  ---------
6          4            72        891,751,171    358.8       352.1
6          3            16        3,189,075,035  285.1       261.4
1          5            45        4,479,327,954  187.7       154.4
6          4            72        453,251,463    182.4       179.0
4          3            16        2,766,814,206  164.9       144.3
4          2            89        371,745,035    123.3       120.5
2          4            774       76,337,053     110.1       109.5
2          4            774       76,331,676     110.0       109.5
2          4            774       75,924,837     109.5       108.9
2          4            774       75,533,539     108.9       108.3
5          4            72        318,217,628    106.7       104.3
7          1            60        269,590,810    105.5       103.4
22         3            13        389,203,725    103.7       100.8
22         3            13        329,772,049    87.8        85.4
2          2            509       90,311,271     85.6        85.0
17         1            510       9,334,362      75.4        75.3
22         3            13        267,380,864    71.2        69.2
2          7            172       219,929,560    70.5        68.8
22         3            13        261,967,851    69.8        67.8
6          5            31        395,800,250    68.6        65.6

Wow - that's some pretty amazing stuff - and that doesn't even account for the space taken up by page headers etc.

You might be thinking - why do I care? There are plenty of reasons:

• If you can save tens or hundreds of GBs by changing the cluster key to something much smaller, that translates directly into a reduction in size of your backups and data file disk space requirements.
• Smaller databases mean faster backups and restores.
• Making the nonclustered indexes smaller means that index maintenance (from inserts/updates/deletes) and index fragmentation removal will be much faster and generate less transaction log.
• Making the nonclustered indexes smaller means that consistency checking will be much faster - nonclustered index checking takes 30% of the CPU usage of DBCC CHECKDB.
• Reducing the width of nonclustered index records means the density of records (number of records per nonclustered index page) increases dramatically, leading to faster index processing, more efficient buffer pool (i.e. memory) usage, and fewer I/Os as more of the indexes can fit in memory.
• Anything you can do to reduce the amount of transaction log directly affects the performance of log backups, replication, database mirroring, and log shipping.

As you can see, there are many reasons to keep the cluster key as small as possible - all directly translating into performance improvements. For those of you that think that moving to a bigint may cause you to run out of possible keys, see this blog post where I debunk that - unless you've got 3 million years and 150 thousand petabytes to spare...

One thing I'm not doing in this post is advocating any particular key over any other (although bigint identity does fit all the criteria from the top of the post) - except to try to keep it as small as possible. Choosing a good cluster key entails understanding the data and workload as well as the performance considerations of key size that I've presented here. And in some very narrow cases, not having a cluster key at all is acceptable - which means there's 8 bytes in each nonclustered index record (just to forestall those who may want to post a comment arguing against clustered indexes in general :-)

Changing the cluster key can be tricky - Kimberly blogged a set of steps to follow plus some code to help you on our SQL Server Magazine blog back in April 2010.

Later this week I'll blog some code that will run through your databases and spit out table names that could have significant space savings from changing the cluster key.

I won't be blogging or tweeting much in January as we'll be in Indonesia diving, but I will be posting photos later in the month.

Enjoy!

Continuing with my "index health" series, I've got another piece of code for you to run.

This time I'm interested in the number of columns in your clustered indexes and the consequent amount of nonclustered index space used by the clustered index keys.

Again, you're going to be really interested to see the results on your servers. When I editorialize the results I'll provide another query for you to run which will make the data actionable on your server.

Here are some results from a random customer server (yes, we already knew about these - long story :-):

NCIndexes ClusterKeys KeyWidth TableRows            KeySpaceInBytes
--------- ----------- -------- -------------------- --------------------
7         3           16       129902437            14549072944
1         3           12       29199817             350397804
10        2           12       1612919              193550280
5         2           5        4266671              106666775
2         2           8        5887697              94203152
5         4           20       827975               82797500
3         3           16       1215800              58358400
7         2           5        1497746              52421110
1         3           12       2667765              32013180
1         4           25       1033063              25826575
1         3           12       989320               11871840
2         2           8        278989               4463824
1         3           12       293736               3524832
4         2           5        160696               3213920

Feel free to send the results in any format you want - Excel spreadsheet works best though. Try not to add any columns to the result set - complicates the aggregation process.

The more results the better - thanks!

Here's the code:

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] = 'SQLskillsIKSpace')
    DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO
CREATE TABLE tempdb.dbo.SQLskillsIKSpace (
    DatabaseID SMALLINT,
    ObjectID INT,
    IndexCount SMALLINT,
    TableRows  BIGINT,
    KeyCount   SMALLINT,
    KeyWidth   SMALLINT);
GO

EXEC sp_MSforeachdb 
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name]
    FROM sys.databases WHERE [state_desc] = ''ONLINE''
        AND [database_id] > 4
        AND [name] != ''pubs''
        AND [name] != ''Northwind''
        AND [name] != ''distribution''
        AND [name] NOT LIKE ''ReportServer%''
        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
BEGIN
USE [?]

INSERT INTO tempdb.dbo.SQLskillsIKSpace
SELECT DB_ID (''?''), o.[object_id], 0, 0, 0, 0
FROM sys.objects o
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 1
            AND [object_id] = o.[object_id]);

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [TableRows] = (
    SELECT SUM ([rows])
    FROM sys.partitions p
    WHERE p.[object_id] = [ObjectID]
    AND p.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');
 
UPDATE tempdb.dbo.SQLskillsIKSpace
SET [IndexCount] = (
    SELECT COUNT (*)
    FROM sys.indexes i
    WHERE i.[object_id] = [ObjectID]
    AND i.[is_hypothetical] = 0
    AND i.[is_disabled] = 0
    AND i.[index_id] != 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [KeyCount] = (
    SELECT COUNT (*)
    FROM sys.index_columns ic
    WHERE ic.[object_id] = [ObjectID]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

UPDATE tempdb.dbo.SQLskillsIKSpace
SET [KeyWidth] = (
    SELECT SUM (c.[max_length])
    FROM sys.columns c
    JOIN sys.index_columns ic
    ON c.[object_id] = ic.[object_id]
    AND c.[object_id] = [ObjectID]
    AND ic.[column_id] = c.[column_id]
    AND ic.[index_id] = 1)
WHERE [DatabaseID] = DB_ID (''?'');

DELETE tempdb.dbo.SQLskillsIKSpace
WHERE
    ([KeyCount] = 1 AND [KeyWidth] < 9)
    OR [IndexCount] = 0 OR [TableRows] = 0;

END';
GO

SELECT
    [IndexCount] AS [NCIndexes],
    [KeyCount] AS [ClusterKeys],
    [KeyWidth],
    [TableRows],
    [IndexCount] * [TableRows] * [KeyWidth] AS [KeySpaceInBytes]
FROM tempdb.dbo.SQLskillsIKSpace
ORDER BY [KeySpaceInBytes] DESC;

DROP TABLE tempdb.dbo.SQLskillsIKSpace;
GO

Back at the start of August I kicked off a survey (see here) that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world - a big thank you to everyone who sent me data!

It's taken me a while to get to this post because a) I needed a few hours to set aside to aggregate the comments, txt files, and spreadsheets that people sent and load them into a database; and b) I've been really busy with teaching etc. Finally I've had time this week while at SQL Connections in Las Vegas to put together the results and this post.

The winners:

• Highest number of nonclustered indexes on a single clustered index: 1032
• Highest number of nonclustered indexes on a single heap: 148
• Highest number of clustered indexes with zero nonclustered indexes on a single server: 185237
• Highest number of heaps with zero nonclustered indexes on a single server: 88042

Wow!

Now to some of the details...

Tables with Zero Nonclustered Indexes

The two graphs below show the number of servers that have a certain number of tables with zero nonclustered indexes.

For the clustered indexes, there is one case I can think of where having zero nonclustered indexes is acceptable: if all queries return all columns of the table and the query search predicate for all queries is the cluster key (or a left-based subset of the cluster key).

All queries that have a search predicate that does not match the cluster key (or a left-based subset thereof) will be table scans, which can put pressure on the buffer pool (see my post on Page Life Expectancy) and lead to contention on the ACCESS_METHODS_DATASET_PARENT latch (all manifesting as a high percentage of LATCH_EX or PAGEIOLATCH_SH and maybe CXPACKET waits).

For the heaps, all queries are inefficient table scans. Well, efficient if you're returning all the rows in the table every time, I suppose :-)

Bottom line: tables usually need nonclustered indexes to provide efficient access paths to the data requested by the various queries that your workload performs.

There are two things you can do to help find queries that need nonclustered indexes:

1. Use the missing index DMVs (cautiously!) to determine which nonclustered indexes to create. I use the script posted by Microsoftie Bart Duncan in his blog post. However don't just go create all the indexes there. I generally look for Bart's "improvement_measure" column to be above 100k before I'll consider recommending the index to a client (and on systems that already have nonclustered indexes on the table, I'll look for index consolidation possibilities). Note also that the missing index DMVs will sometimes add the cluster key as an INCLUDEd column. This is unnecessary but harmless.
2. Look directly in the plan cache to find query plans that perform scans. I use a variant of a query published by fellow MVP Glenn Berry in this blog post. Using the graphical query plan I can see what columns are being searched for and returned from the scans and then create the correct nonclustered indexes for these.

You can also use the Database Tuning Advisor, but I don't use that, personally.

You'll be amazed at the performance difference by having a good set of nonclustered indexes.

But don't go overboard otherwise you could detrimentally affect performance by having too many nonclustered indexes...

Tables with Nonclustered Indexes

The two graphs below show the number of tables that have a certain number of nonclustered indexes.

The data shows that it is most common to have 10 nonclustered indexes or less, but even that may be too many.

Every nonclustered index incurs overheard when a table row is inserted or deleted, or when any of the nonclustered index key columns (or INCLUDEd columns) are updated. Filtered indexes in SQL 2008+ are a special case, obviously. The overheard takes a few forms:

• Buffer pool (i.e. memory and I/O) overhead of having to search the nonclustered index for the record to update.
• I/O overhead of having to flush the updated index page to disk during the next checkpoint
• Log space for the log records generated by the operation on the nonclustered index
• Resource overheard for those log records in terms of:
  • Time to be read by the replication/CDC log reader Agent job
  • Time to be read by log backups (and data backups, if applicable)
  • Time and bandwidth to send the log records to a database mirroring mirror
  • Disk space to store the log records in a log backup
  • Time to restore the log records on a log shipping secondary or during a disaster recovery
• Locking overhead
• Page split overhead
• Time to consistency check
• Time to examine for fragmentation
• Time to update statistics
• Disk and backup space overhead

As you can see, nonclustered indexes can be a big burden on a system - you have to be careful when creating them so that you don't have too many.

There are three things you can do to reduce the number of nonclustered indexes on your system:

1. Use the sys.dm_db_index_usage_stats DMV to find indexes that are only being updated. I've blogged about this here. Again, be careful though. Just because an index hasn't been used doesn't mean it should be dropped. It may be used only infrequently, but it's critical when it is used. Ideally you need to look at the output from the DMV after an entire business cycle has passed. Even then, be careful about dropping indexes that are enforcing uniqueness constraints as these can be used by the query optimizer without reflecting any user seeks or scans.
2. Remove duplicate nonclustered indexes. Kimberly blogged code to find duplicate indexes here. There is no downside to doing this.
3. Look for consolidation possibilities. Kimberly has code to show you all the key and INCLUDEd columns here. This is harder and is more of an art than a science. You're looking for indexes where you can combine two or more indexes into one without affecting the ability of the optimizer to use them for the various queries that the non-consolidated indexes used to help.
   • For example, an index on c1, c2, c3 INCLUDE c4, c5 can be combined with an index on c1, c2, c3 INCLUDE c4, c6. But only as long as c6 isn't a really wide column that would affect the performance of the queries using the first index.
   • A harder example: would you consolidate an index c1, c2 INCLUDE c3 with an index on c1, c3 INCLUDE c2? Possibly. It would depend on what the indexes are being used for in queries. 

Summary

Nonclustered indexes are essential for the performance of most workloads, but how many should you have? I often get someone in a class that Kimberly's teaching on indexes to ask her "what's the optimum number of indexes a table should have?" because I know it's a nonsensical question. (And she reciprocates by getting someone to ask me "how long will CHECKDB take?" :-)

The answer is a big, fat "it depends" - and hopefully I've given you some pointers to figure it out for yourself.

I'll continue this series of posts with more surveys and code that you can use on your systems to gauge the health of your indexes.

Hope this helps!

A few weeks back I kicked off a survey asking whether you have SharePoint in your SQL Server environment at all. Here are the results:

The "Other" responses were:

• 3 x "Not now but I did in a previous role."
• 3 x "Yes, but the dbas do not manage their servers."
• 2 x "SharePoint 2007 and 2010."
• 1 x "My environment no but it's included in an MES environment."

Almost 75% of respondents have some form of SharePoint in their environment. Although I don't think is a statistically valid representation of the installed SQL Server user base, I think it confirms what I've been thinking - there's a *lot* of SharePoint out there. Remember that this is just a survey of people who read my blog - primarily SQL Server professionals - there is a huge amount more SharePoint installations where there is *no* SQL Server professional involved.

The reason that it's interesting whether SharePoint is involved in your SQL Server environment is that it can have an impact on your regular maintenance and your HA/DR strategy. Ordinarily you'd want to treat a SQL Server instance that's running SharePoint just like any other back-end SQL Server for an application, but SharePoint imposes some restrictions on you.

• It has it's own maintenance jobs which run regularly, which means you either need to exclude that instance from your standardized maintenance setup, or disable the SharePoint jobs completely and run your own, taking into account the SharePoint guidelines.
• There's more than one database required for SharePoint to work properly, which means that database mirroring or log shipping as HA/DR providers becomes extremely tricky. I usually recommend failover clustering with SAN replication for HA/DR to avoid complicated failover logic. Backup and restore can become more complicated too - especially when using an off-database LOB storage layer like RBS.
• You can't create additional filegroups in the large content databases and you can't use partitioning (except for the Web Analytics service), which means taking advantage of filegroup piecemeal restore and partial database availability is not possible.
• Auto-create statistics needs to be disabled for SharePoint, whereas in most situations you want it enabled - a departure from your standardized setup.
• MAXDOP=1 is strongly recommended - again a departure from your standardized setup.

Some of this guidance is unfortunate, and shows that SharePoint's use of SQL Server is not as optimal as it could be, but all of this means that you need to treat a SQL Server instance that underpins SharePoint differently from your other SQL Servers.

You can read more about SQL Server configuration and maintenance when using SharePoint at:

• Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)
• Database maintenance for SharePoint Server 2010

Enjoy!

Here's a survey I've been meaning to run for quite a while - that Kimberly and I are really interested in.

For all the databases on your server, how many nonclustered indexes do you tables have, plus is the table a heap or a clustered index?

The code below will return the following result set (taken from a random client system):

BaseType   NCIndexes TableCount
---------- --------- -----------
Clustered  0         1645
Clustered  1         832
Clustered  2         417
Clustered  3         68
Clustered  4         18
Clustered  5         13
Clustered  6         9
Clustered  7         5
Clustered  8         3
Clustered  9         3
Clustered  10        2
Heap       0         303
Heap       1         19
Heap       2         51
Heap       3         4
Heap       4         1 

I think it'll make some really interesting reading, and you may be really surprised at the results for your system - how many tables with no indexes at all and how many tables with large numbers of nonclustered indexes.

And yes, I'm sure there's a slightly better way to write the code below, but I'm not an expert T-SQL developer (and yes Aaron, I know about your (seriously) fabulous sp_MSforeachdb replacement :-)

If someone wants to bang out a quick post with the PowerShell to run this on multiple servers, I'll link to it. (Update: The PowerShell script can be downloaded from the bottom of this post)

Feel free to send the results in email, in a spreadsheet, in a comment below, or even written on papyrus - anything works.

The more results the better - thanks!

Here's the code:

IF EXISTS (SELECT * FROM msdb.sys.objects WHERE [name] = 'SQLskillsPaulsIndexCounts')
    DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO
CREATE TABLE msdb.dbo.SQLskillsPaulsIndexCounts (
    BaseType CHAR (10),
    IndexCount SMALLINT);
GO

EXEC sp_MSforeachdb
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT [name]
    FROM sys.databases WHERE [state_desc] = ''ONLINE''
        AND [database_id] > 4
        AND [name] != ''pubs''
        AND [name] != ''Northwind''
        AND [name] != ''distribution''
        AND [name] NOT LIKE ''ReportServer%''
        AND [name] NOT LIKE ''Adventure%'') AS names WHERE [name] = ''?'')
BEGIN
USE [?]
INSERT INTO msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Heap'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
    ON o.[object_id] = i.[object_id]
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 0
            AND [object_id] = o.[object_id])
GROUP BY i.[object_id];

INSERT INTO msdb.dbo.SQLskillsPaulsIndexCounts
SELECT ''Clustered'', COUNT (*)-1
FROM sys.objects o
JOIN sys.indexes i
    ON o.[object_id] = i.[object_id]
WHERE o.[type_desc] IN (''USER_TABLE'', ''VIEW'')
    AND o.[is_ms_shipped] = 0
    AND EXISTS (
        SELECT *
        FROM sys.indexes
        WHERE [index_id] = 1
            AND [object_id] = o.[object_id])
GROUP BY i.[object_id];
END';
GO

SELECT DISTINCT [BaseType], [IndexCount] AS [NCIndexes], COUNT (*) AS [TableCount]
FROM msdb.dbo.SQLskillsPaulsIndexCounts
GROUP BY [BaseType], [IndexCount]
ORDER BY [BaseType], [IndexCount];
GO

DROP TABLE msdb.dbo.SQLskillsPaulsIndexCounts;
GO

Pauls Index Survey.ps1 (2.64 kb)

A couple of weeks back I kicked off an off-topic survey about which internet browsers and email clients people use. Here are the results:

The "Other" results are:

• 2 x "Chrome, FF4, IE789"
• 2 x "Firefox 5"
• 1 x "Avant"
• 1 x "Dragon and IE9 for flash content"
• 1 x "Home Firefox 5.0.1 and Safari (iPad) / Work IE 7"
• 1 x "Iron"

The "Other" results are:

• 12 x "Home gmail / work Outlook"
• 4 x "Gmail for private / Outlook 2010 for work"
• 2 x "Work - groupwise / home - gmail"
• 2 X "Zimbra"
• 1 x "GroupWise for work"
• 1 x "Groupwise for work / yahoo mail for personal"
• 1 x "Home Thunderbird / work Outlook 2010"

Summary

Personally I use IE8 on my laptop (don't use any other computers at home or work), plus Safari on my iPhone and iPad2. For email I use Thunderbird because I can't stand Outlook and wanted something like Outlook Express (in fact the only reason I agreed to upgrade to Windows 7 last year was because I found Thunderbird).

Interesting results. Outlook is definitely the most common email client and I'm surprised that Gmail is so far ahead of the other free email providers. The browser results are really split three ways (as the total number of IE users is 35%) between IE, Chrome, and Firefox - I wonder how many of the non-IE users are because Chrome/Firefox is better, or they just don't want to use IE on principal.

Hopefully these results will give some of you insight into where to focus blog/app compatibility.

Three weeks ago I kicked off a survey about using database snapshots - see here - because I don't really know how much they're used or not out in the wild. Here are the results:

The "Other" values are:

• 13 x "No - not at the moment."
• 10 x "In our test environment for reverting to the original database state after a test. Much faster than restoring every time."
• 6 x "For development, to be able to go back to a previous known state after trying out new scripts and stuff."
• 5 x "Haven't really looked at using them."
• 5 x "No - just read about them recently and have yet to determine usefulness."
• 3 x "Yes - every time I do a DBCC CHECKDB (okay, it's hidden, but still a snapshot)."
• 2 x "During application installation in order to do an easy rollback if smoke testing fails."
• 2 x "For rollback of code/data releases."
• 2 x "Never used, but did recommend once as a pre-deployment option so that there was a faster rollback in case a developer's script broke once in Prod. Recommendation was not implemented."
• 1 x "As failsafe for monthly production deployments."
• 1 x "For EOM reporting copies of the production database."
• 1 x "No. Had performance issues so we turned them off."
• 1 x "Not supported in SQL2000."
• 1 x "When adding articles in replication."
• 1 x "Yes - For quick rollback during production changes."
• 1 x "Yes for ETL, and via a linked server connection since snapshot isolation isn't an option."
• 1 x "Yes, for instantaneous restore of development environment."
• 1 x "Yes, rarely - for investigating issues on copy of production DB, way faster than restore."

Very interesting! More than 60% of respondents don't or can't use database snapshots. I've always thought that database snapshots are an under-utilized feature, I'm not entirely sure why though (apart from the obvious Enterprise Edition restriction). It could be some of the limitations of them, such as:

• You can't refresh one without all connections to it being dropped (and then dropping and recreating it)
• You can't back up a snapshot (would require some interesting plumbing in the Storage Engine, as currently backups don't use the buffer pool, and it's the buffer pool that manages what's in a database snapshot)
• You can't modify a snapshot (i.e. like having a base VM with various diff disks)
• They can have a performance impact on the production/mirror workload depending on how heavily they're used

I'm really glad that no-one confessed to using database snapshots as an alternative to log backups! See this TechNet Magazine Q&A column I wrote last year that explains why not.

Rather than repeat what's already been said about why database snapshots can be useful, I'm going to provide you with some links to further reading:

• Top-level of Books Online on database snapshots
• Books Online on typical uses of a database snapshot
• Whitepaper on performance implications of using database snapshots
• Blog post on what happens to a database snapshot when a transaction rolls back
• Blog post on some of the problems you can see from DBCC CHECKDB using database snapshots under the covers
• Blog post on a bunch of potential problems when a database snapshot fills up or becomes corrupt
• Blog post about memory usage from a database snapshot

Enjoy!

This survey is off-topic and was suggested by Justin Dearing (blog|twitter) and I'm curious about the results too so I agreed to run it.

We're interested to see what internet browsers are most commonly used my members of the database community, along with which email clients. People are often fanatic about one browser/client or another and vehemently opposed to using others (I detest Outlook, for instance, and was overjoyed to be freed from it when I left Microsoft 4 years ago). Consider whatever is your primary machine and answer for that. If you want to answer multiple times, you'll need to do so from different machines so the survey site doesn't recognize you.

I'll report on the results in a couple of weeks or so.

Thanks!

PS No, I'm not changing which survey provider I use, and if you don't like the choices in the survey, that's unfortunate :-)

About a month ago I kicked off a survey around regularly rebooting SQL Server (see here). There is much debate about rebooting SQL Server (or Windows Server hosting SQL Server), regularly or even at all so I've been looking forward to the results.

My view is that there are circumstances where rebooting, even regularly, is acceptable - but you have to have a good reason to do it because of the downsides of restarting the SQL Server process. These downsides include:

• The buffer pool goes completely cold. Warming up again requires reading (potentially a lot of) data back into memory, after allocating that memory from Windows.
• All query plans are lost and will need to be compiled again.
• All information about the state of SQL Server, used to give DMV output, is lost.

In other words, the steady-state for the production workload is lost and has to be attained again.

As you'll see from the results, the most common reason for a regular reboot is part of the Windows/SQL patching process, but there are plenty of reasons cited that are NOT valid in my opinion, and some others that are.

The "Other" responses are:

• 101 x (paraphrasing) "After windows/SQL/firmware updates that require a reboot."
• 30 X (paraphrasing) "Quarterly."
• 10 x "Only if any problems occur or any maintenance activity scheduled in off hours."
• 5 x "Only if I've observed some resource contention after some time. Usually a result of not being able to implement the solution and having to work around the problem."
• 4 x "Every two months."
• 1 x "I normally go 360+ days between reboots. And then it's just for giggles. No indication it is needed or required."
• 1 x "I would like to do it monthly but my boss doesn't support it."
• 1 x "Yes, every two weeks."
• 1 x "Yes, twice a week."

The "Other" responses are (I've commented on those in bold):

• 74 x (paraphrasing) "MS security patches - manually installed after testing them on duplicate system."
• 67 x (paraphrasing) "As part of the monthly MS patching."
• 17 x (paraphrasing) "No regular reboots."
• 9 x "We reboot our SQL servers about twice a year average for updates or other issues."
• 6 x "As required for patching activities and only after ensuring that the potential for patching has been reduced by eliminating non-required components and programs."
• 4 x "After patch installation or maintenance such as password updates to test cluster failover."
• 3 x "Process unkilled and some memory problems."
• 2 x (paraphrasing) "Because the business demands it, for no valid reason."
  • You need to work out why that process was put in place. My guess is that it's based on historical requirements to do so on older operating systems, or because of bugs, and it's just stuck. Very likely the process can be removed if the reboot schedule is very frequent (e.g. daily or weekly).
• 1 x "I have issues with a CLR process."
  • Then try to fix that process/code rather than resorting to a reboot.
• 1 x "I've been told that certain errors won't be reported by Windows without a reboot."
• 1 x "Last rebooted about 50 days ago - only reboot when necessary (patches)."
• 1 x "Microsoft Insists."
  • I don't buy this one, unless it's to work around a specific bug that will be patched at some point.
• 1 x "Nightly reboots of a specific SQL Server are required by the vendor whose app it supports."
  • This is badly messed up and you need to push back on the vendor. My guess here is that they have a leaky application (i.e. memory leaks) and this is their solution.
• 1 x "Non Windows app memory leaks."
  • Again, try to fix the memory leaks rather than resorting to rebooting.
• 1 x "Our application becomes unstable otherwise."
  • Fix the application...
• 1 x "Regular test of clusters AND SQL Server performance degrades without restart."
• 1 x "Save power, turn it off at night."
  • This is interesting and one I haven't considered before. Are you sure the gain in power saving is worth the downsides I mention at the start of the post?
• 1 x "SQL Server connection time out error."
• 1 x "VMware Snapshot."
  • This I don't know about (that's why I employ Jonathan :-), but I'd be surprised if this process requires the server to be rebooted.
• 1 x "We do full server backups weekly. we briefly shut SQL, copy c drive where OS and SQL software live - paging file etc. then bring SQL up and finish server full backup. This takes approx 5 min per week."
  • This is messed up. SQL Server and Windows can both be backed up without having to be shut down.
• 1 x "We have a weekly Maintenance window and it keeps the event logs manageable."
  • This is messed up. Windows and SQL logs can be cycled without rebooting.

Quite interesting results.

There have been many problems of the years which have required regular reboots to clear, but these are *mostly* gone with the advent of Windows Server 2008/2008 R2. I think there are three main reasons which still are valid for rebooting regularly:

1. As part of Windows/SQL/firmware patch installs.
2. To test failover procedures.
3. To work around a Windows or SQL bug.

As an example of #3, there's a problem still in Windows Server 2008 with the file cache taking up all the memory, depending on which APIs are used to call it (see KB 976618). And what about the various SQL Server memory leak bugs over the years (e.g. KB 959007).

There are plenty of other symptoms that can be "fixed" with a server reboot. A great example of this is plan cache bloat from lots of plans for ad-hoc SQL queries. This can eat away at the memory available for the buffer pool to store data and cause the server performance to grind down (see Kimberly's post Plan cache, adhoc workloads and clearing the single-use plan cache bloat). And what about buffer pool memory being wasted by massive index fragmentation, resulting in poor performance (see my post Performance issues from wasted buffer pool memory). Or any of the reasons in bold above that mention and application instability, memory leak, or issue?

For these problems, rebooting the server is addressing the symptom of the problem, not the root-cause. We've had several clients in the last month where massive performance problems were being "solved" by failing over to the other cluster node, and they turned out to be caused from not having Lock Pages In Memory set (see KB 918483, and take care to note all the Windows bugs it explains that can cause working set trimming too). They were addressing the symptom without researching the cause.

Bottom line: if you're regularly rebooting Windows/SQL Server, make sure it's for a good reason and not just because someone thinks it's a good thing to do or it's the chosen way to fix a problem that should be fixed in some other way.

And don't immediately assume that someone who does reboot Windows/SQL Server regularly is an idiot - they may have a very good reason, or may just be ignorant... (see Ignorance is not stupidity).

Thoughts?

As I was writing a quick SQL Server Magazine blog post on database snapshots while waiting for my coffee to brew, I figured it would make an interesting survey to see whether people are making use of database snapshots at all. I don't expect to see much activity on this until tomorrow as it's July 4th here in the USA.

Note: As Books Online says: don't get database snapshots confused with snapshot isolation, snapshot replication, or snapshot backups.

I'll report on the results in a few weeks or so.

Thanks!

A month ago I kicked off a survey about MAXDOP setting - see here for the survey. I received results for 700 servers around the world! Here they are:

The X-axis format is X-Y-Z, where X = number of cores, Y = number of NUMA nodes, Z = MAXDOP setting. I didn't differentiate between hyper-threading or not, or soft vs. hard NUMA.

The results are striking - 75% of all systems out there use a server MAXDOP of zero. Now, this doesn't show whether individual queries are using MAXDOP to override that, or resource governor in 2008+ to override that either and I don't have info on the workload for all these servers - so it's not a result we can draw any concrete conclusions from. However, I do find it interesting that such a high proportion are running fine with MAXDOP 0 - my expectation was that there would be a higher proportion of servers with a non-zero MAXDOP setting.

There are quite a few 'black and white' configuration rules out there - for instance:

1. Set MAXDOP to 1 if you're seeing CXPACKET waits as the prevalent wait type.
2. Set MAXDOP to 1 for OLTP systems, and don't do anything else.
3. Old Microsoft guidance to set MAXDOP to half the number of physical processors.
4. Set MAXDOP to the number of cores in the NUMA node.

These are all incorrect as *rules*. There is no one-size-fits-all rule for MAXDOP - there are only general guidelines. For instance:

• For OLTP systems, it can often be beneficial to set MAXDOP to 1 and then use the MAXDOP query hint to override the server-wide setting for queries that can benefit from parallelism.
• For mixed-workload systems, you need to be careful how you set MAXDOP so you don't inadvertently penalize one of the workloads. Judicious use of the MAXDOP query hint can help here. A more powerful solution for mixed workloads is to use resource governor and have a workload group for each portion of the workload, with a different MAXDOP for each workload group.
• For systems with high CXPACKET waits, investigate why this is the case before reducing MAXDOP. It's easy to come up with a demo where there are lots of CXPACKET waits, and while reducing MAXDOP (for the server or the query) reduces the CXPACKET waits, it also makes the query take a lot longer. CXPACKET waits can be because the statistics are incorrect and the query execution system divides up the work by the out-of-date statistics
• Consider using the cost threshold for parallelism setting - see Jonathan's recent post here.

Using the resource governor as I described above can be a very easy way to mess around with the MAXDOP setting - especially for applications with legacy code that you cannot change, and you don't want to set a server-wide MAXDOP setting. This even works if the legacy code uses MAXDOP query hints, because the resource governor workload group MAXDOP setting *cannot* be overridden.

The key point when making any change for performance tuning is to test the change before putting it into production and work out which setting works best for your workload on your system - rather than blindly following 'this is the best way' rules for settings that people publish.

In other words, what should your MAXDOP be set to? It depends! :-)

One thing to be aware of: if you change the MAXDOP setting for the server, it will flush the plan cache when you do. It shouldn't, but it does. Be careful when doing this on a production server...

Thanks to all those who replied to the original survey!

In this survey I'd like to know if you regularly reboot SQL Server. There are two parts to this: a) no or yes with frequency b) if yes, why?

If you answer the survey on the right, please also answer the one on the left to give the frequency of reboot/restart - thanks! 

I'll report on the results in a couple of weeks. Comments are disabled to avoid polluting the results.

Thanks!

If I told you about an undocumented mod you could make to your car's brakes to make them more efficient, would you try it? What if a random mechanic told you? What if someone who's an expert in your make and model of car told you? What if the car manufacturer asked you to try it to solve a brake problem - and just drive around a bit to see what happens and see if it stops the car crashing again?

:-)

A month ago I kicked off a survey about your comfort level with using trace flags in production and now it's time to look at the results.

The Other values are:

• 15 x "It depends!!!!"
• 7 x "We use them where specifically needed for short amounts of time example - the deadlock trace flags (1204 and 1222) to dump the details to the error log. in some cases as needed we will use startup trace flags - example 845 on 2005 x64 standard to allow."
• 6 x "If Paul says they're okay."
• 4 x "We use Trace flags which well documented by MS *and* reputable sources and tested in a controlled manner to ensure continued stability."
• 3 x "Deadlock full details - because deadlocks are rare, I need full details every time because lightening doesn't strike twice."
• 3 x "I feel comfortable, each trace does different things, if we need it we'll use it."
• 3 x "I rarely if ever use a trace flag in production but I'm comfortable using one if it's documented by a reputable source and it makes sense for us."
• 2 x "Enable it if css asks, or it its documented in ms official documentation but test before implementing."
• "Either from MS source or from a non-MS but reputable source."
• "If I hear/read about it from a non-MS, but reputable source, to fix a bug or boost performance."
• "If some doc is evident and MS knows what the TF actually does, instead of their telling you to turn it on without realizing it affects more than they thought (ex/ TF2330 stops all index usage stats gathering)."
• "Only after testing in a dev/test environment first."
• "Will use them on need basis - for example - deadlock trace 1222 to log the deadlock info in the error log."

Trace flags are interesting things. They change the behavior of SQL Server in a huge variety of sometimes useful, sometimes dangerous, and sometimes bizarre ways. There are many hundreds of trace flags, most of which are undocumented - and usually for the very good reasons that they're only supposed to be used for testing, for debugging problems with Product Support, or to enable specific bug fixes that may be detrimental to others.

I never advise turning on a trace flag (either when teaching a class or discussing with a client) unless I'm rock-solid 100% sure about what it does and what the effects are. You should do the same when considering trace flag use on a production system.

The example I like to use is trace flag 661 that disables the ghost cleanup background task. It's documented in KB 920093 and I've blogged about it here (and in a few other posts). It's a very useful trace flag which is safe to use, is documented by MS, is referenced by trustworthy sources (well, me :-) and can give a performance boost under some circumstances. However, it can have horrible side-effects if you don't understand the ramifications of turning it on - deleted data and index records stay around until the next index rebuild/reorganize, leading to page splits and data file growth.

Where trace flags are concerned, you need to test and validate before enabling in production - no matter *who* you hear about it from.

Be careful out there!

Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools - how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world - thanks!

The reason I'm interested in this, and you should be too, is that memory is one of the most important resources that SQL Server uses. If you don't have anough memory, your workload will suffer because:

• You'll be driving more read I/Os because more of the workload can't fit in the buffer pool.
• You'll be driving more write I/Os because the lazywriter will have to be tossing dirty pages from the buffer pool.
• You may encounter RESOURCE_SEMAPHORE waits because queries can't get the query execution memory grants that they need.
• You may cause excessive plan recompilations if the plan cache is too constrained.

And a bunch of other things.

One of the memory problems that Kimberly discussed in depth last year (and teaches in depth in our Performance Tuning classes) is single-use plan cache bloat - where a large proportion of the plan cache is filled with single-use plans that don't ever get used again. You can read about it in the three blog posts in her Plan Cache category, along with how to identify plan cache bloat and what you can do about it.

This post is about the memory the buffer pool is using to store data file pages, and whether good use is being made from it.

The sys.dm_os_buffer_descriptors DMV gives the information stored by the buffer pool for each data file page in memory (called a BUF structure in the code). One of the things that this structure keeps track of is the free_space_in_bytes for each page. This metric is updated in real-time as changes are made to the page in memory (you can easily prove this for yourself) and so is a reliable view of the data density of the used portion of the buffer pool.

Data density? Think of this as how packed full or data, index, or LOB rows a data file page is. The more free space on the page, the lower the data density.

Low data density pages are caused by:

• Very wide data rows (e.g. a table with a 5000-byte fixed-size row will only ever fit one row per page, wasting roughly 3000 bytes per page).
• Page splits, from random inserts into full pages or updates to rows on full pages. These kind of page splits result in logical fragmentation that affects range scan performance, low data density in data/index pages, and increased transaction log overhead (see How expensive are page splits in terms of transaction log?).
• Row deletions where the space freed up by the deleted row will not be reused because of the insert pattern into the table/index.

Low data density pages can be detrimental to SQL Server performance, because the lower the density of records on the pages in a table:

• The higher the amount of disk space necessary to store the data (and back it up).
• The more I/Os are needed to read the data into memory.
• The higher the amount of buffer pool memory needed to store the extra pages in the buffer pool.

From the survey results I took all the SQL Servers that were using at least one GB of buffer pool memory for data file page storage (900 servers) and plotted that amount of memory against the percentage of that memory that was storing free space in the data file pages.

Wow! That's a lot of servers with a lot of buffer pool memory storing nothing useful.

So what can you do about it? There are a number of solutions to low page density including:

• Change the table schema (e.g. vertical partitioning, using smaller data types).
• Change the index key columns (usually only applicable to clustered indexes - e.g. changing the leading cluster key from a random value like a non-sequential GUID to a sequential GUID or identity column).
• Use index FILLFACTOR to reduce page splits, and...
• Periodically rebuild problem indexes.
• Consider enabling data compression on some tables and indexes.

From the graph above, bear in mind that some of the 'wasted' space on these servers could be from proper index management where data and index pages have a low FILLFACTOR set to alleviate page splits. But I suspect that only accounts for a small portion of what we're seeing in this data.

The purpose of my survey and this post is not to explain how to make all the changes to reduce the amount of free space being stored in memory, but to educate you that this is a problem. Very often PAGEIOLATCH waits are prevalent on systems because more I/O than necessary is being driven to the I/O subsystem because of things like bad plans causing table scans or low data density. If you can figure out that it's not an I/O subsystem problem, then you as the DBA can do something about it.

Below is a script to analyze the buffer pool and break down by database the amount of space being taken up in the buffer pool and how much of that space is empty space. For systems with a 100s of GB of memory in use, this query may take a while to run:

SELECT
   (CASE WHEN ([database_id] = 32767)
       THEN 'Resource Database'
       ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
   COUNT (*) * 8 / 1024 AS [MBUsed],
   SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];
GO

And here's some sample output from a client system (made anonymous, of course):

DatabaseName        MBUsed   MBEmpty
------------------- -------- ---------
Resource Database   51       11
ProdDB              71287    9779
master              2        1
msdb                481      72
ProdDB2             106      17
model               0        0
tempdb              2226     140

Below is a script that will break things down by table and index across all databases that are using space in the buffer pool. I'm filtering out system objects plus indexes where the amount of space used in the buffer pool is less than 100MB. You can use this to identify tables and indexes that need some work on them to allow your buffer pool memory to be used more effectively by SQL Server and increase your workload performance.

[Edit: There was a bug in the script - fixed now 6/10/2011] 

EXEC sp_MSforeachdb
    N'IF EXISTS (SELECT 1 FROM (SELECT DISTINCT DB_NAME ([database_id]) AS [name]
    FROM sys.dm_os_buffer_descriptors) AS names WHERE [name] = ''?'')
BEGIN
USE [?]
SELECT
    ''?'' AS [Database],
    OBJECT_NAME (p.[object_id]) AS [Object],
    p.[index_id],
    i.[name] AS [Index],
    i.[type_desc] AS [Type],
    --au.[type_desc] AS [AUType],
    --DPCount AS [DirtyPageCount],
    --CPCount AS [CleanPageCount],
    --DPCount * 8 / 1024 AS [DirtyPageMB],
    --CPCount * 8 / 1024 AS [CleanPageMB],
    (DPCount + CPCount) * 8 / 1024 AS [TotalMB],
    --DPFreeSpace / 1024 / 1024 AS [DirtyPageFreeSpace],
    --CPFreeSpace / 1024 / 1024 AS [CleanPageFreeSpace],
    ([DPFreeSpace] + [CPFreeSpace]) / 1024 / 1024 AS [FreeSpaceMB],
    CAST (ROUND (100.0 * (([DPFreeSpace] + [CPFreeSpace]) / 1024) / (([DPCount] + [CPCount]) * 8), 1) AS DECIMAL (4, 1)) AS [FreeSpacePC]
FROM
    (SELECT
        allocation_unit_id,
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 1 ELSE 0 END) AS [DPCount],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 0 ELSE 1 END) AS [CPCount],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN CAST ([free_space_in_bytes] AS BIGINT) ELSE 0 END) AS [DPFreeSpace],
        SUM (CASE WHEN ([is_modified] = 1)
            THEN 0 ELSE CAST ([free_space_in_bytes] AS BIGINT) END) AS [CPFreeSpace]
    FROM sys.dm_os_buffer_descriptors
    WHERE [database_id] = DB_ID (''?'')
    GROUP BY [allocation_unit_id]) AS buffers
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100 AND ([DPCount] + [CPCount]) > 12800 -- Taking up more than 100MB
ORDER BY [FreeSpacePC] DESC;
END';

And here's some sample output from the same client system with the more comprehensive script:

Database Object index_id Index        Type         TotalMB FreeSpaceMB FreeSpacePC
-------- ------ -------- ------------ ------------ ------- ----------- -----------
ProdDB   TableG 1        TableG_IX_1  CLUSTERED    531     130         24.5
ProdDB   TableI 1        TableI_IX_1  CLUSTERED    217     48          22.2
ProdDB   TableG 2        TableG_IX_2  NONCLUSTERED 127     27          21.8
ProdDB   TableC 1        TableC_IX_1  CLUSTERED    224     47          21.4
ProdDB   TableD 3        TableD_IX_3  NONCLUSTERED 1932    393         20.4
ProdDB   TableH 1        TableH_IX_1  CLUSTERED    162     33          20.4
ProdDB   TableF 5        TableF_IX_5  NONCLUSTERED 3128    616         19.7
ProdDB   TableG 9        TableG_IX_9  NONCLUSTERED 149     28          19.1
ProdDB   TableO 10       TableO_IX_10 NONCLUSTERED 1003    190         19
ProdDB   TableF 6        TableF_IX_6  NONCLUSTERED 3677    692         18.8
.
.

I have a few surveys to editorialize but I'd like to kick off another one where you have to run a bit of code and send the results.

For this one I'm interested in how you have your system configured for parallelism, given the number of processor cores and NUMA nodes configured.

For systems using SQL Server 2005 onwards, the following code will work:

SELECT
    [value_in_use] AS [MAXDOP],
    os.*
FROM
    sys.configurations,
    (SELECT
        COUNT (DISTINCT [parent_node_id]) AS [Nodes],
        COUNT (*) AS [Cores]
     FROM
        sys.dm_os_schedulers 
     WHERE
        [status] = 'VISIBLE ONLINE') AS os
WHERE
    [name] = 'max degree of parallelism';
GO

Shoot me the results in email, spreadsheet, or as a comment on the blog. Comment #7 below has a Powershell script from Aaron Nelson (b|t) for all your servers - thanks!

If you have any info on the general workload/workload mix on the server, that would be helpful too.

Thanks!

After finishing the salary survey I was kicking around ideas for the next one and thought about one of the sessions I presented at SQL Connections in Spring this year and Fall last year about undocumented tips and tricks (just the tip of the iceberg!). In that presentation I covered a bunch of trace flags that I thought would be useful to people (and I'll be blogging about some of them - just blogged about checkpoint information trace flags over on our SQL Mag blog) and I was wondering when people would use them.

So this survey is about your comfort level with using trace flags in production.

I've left the "Other" option open if you want to go into more detail.

I'll report on the results in a few weeks.

Thanks!

 (Used with permission from Dilbert.com)

Following on from the survey I did about what your company is doing for you and vice-versa (see Job security, loyalty, and employee retention: you need to take control), people asked for a salary survey - so I complied a couple of weeks ago (see here for the survey).

The results show a very wide range in salaries for all ranges of experience. Bear in mind that salaries will vary not just because of experience, but also because of responsibility, full-time vs. contractor, regional cost of living, and other things.

Of course, the only person who can accurately gauge whether you're being paid enough is you but this at least gives you a rough idea of how the DBA community is being paid and how your salary stacks up against those of other people.

US/Canada Results (in US$)

Europe Results (in Euros)

Rest Of The World Results

(Alphabetical by country, slightly sanitized, some results removed because I couldn't figure out what was meant.)

• Argentina
  • 0 years experience, AR$ 90,000
• Australia
  • 5 years experience, AU$ 125,000
  • 8 years experience, AU$ 80,000
  • 8 years experience, AU$ 140,000
  • 10 years experience, AU$ 105,000
  • 10 years experience, AU$ 140,000
  • 10 years experience, AU$ 140,000
  • 10 years experience, AU$ 200,000
  • 13 years experience, AU$ 130,000
  • 15 years experience, AU$ 190,000+
  • 18 years experience, AU$ 125,000
• Brazil
  • 2 years experience, R$ 35,000
  • 3 years experience, R$ 42,000 
  • 7 years experience, R$ 57,000
  • 8 years experience, R$ 96,000
• Hungary
  • 11 years experience, HUF 1,100,000
• India
  • 1 years experience, INR 240,000
  • 1 years experience, INR 384,000
  • 2 years experience, INR 2,100,000
  • 3 years experience, INR 560,000
  • 3 years experience, INR 630,000
  • 5 years experience, INR 500,000
  • 5 years experience, INR 600,000
  • 5 years experience, INR 650,000
  • 5 years experience, INR 900,000
  • 5 years experience, INR 1,100,000
  • 5 years experience, INR 1,500,000
  • 5 years experience, INR 1,600,000
  • 6 years experience, INR 1,000,000
  • 7 years experience, INR 645,000
  • 7 years experience, INR 1,700,000
  • 8 years experience, INR 600,000
  • 8 years experience, INR 930,000
  • 8 years experience, INR 1,560,000
• Mexico
  • 3 years experience, MXN 465,000
  • 5 years experience, MXN 293,000
  • 6 years experience, MXN 450,000
  • 12 years experience, MXN 872,000
• New Zealand
  • 3 years experience, NZ$ 80,000
  • 4 years experience, NZ$ 75,000
  • 8 years experience, NZ$ 125,000
  • 10 years experience, NZ$ 113,000
  • 10 years experience, NZ$ 120,000
  • 15 years experience, NZ$ 125,000
  • 15 years experience, NZ$ 315,000
• Peru
  • 3 years experience, PEN 85,000
• Russia
  • 3 years experience, RUB 409,000
  • 13 years experience, RUB 1,000,000
• Singapore
  • 10 years experience, SGD 45,000
  • 13 years experience, SGD 100,000
• South Africa
  • 7 years experience, ZAR 300,000 after tax
  • 9 years experience, ZAR 380,000
  • 14 years experience, ZAR 666,000+
• Sri Lanka
  • 5 years experience, LKR 1, 536,000
• Ukraine
  • 10 years experience, UAH 16,000

Following my survey on company and employee loyalty, job security, and retention, I think it'll be very useful to the community to do a survey on salary vs experience for the SQL Server world - so here it is!

This survey is completely anonymous - there is no way for anyone to know what you voted. The more people fill this in, the better!

Please fill in the appropriate survey and I'll report on the results in a week or two. Consider your total monetary compensation (salary + bonus) for 2010 and it doesn't matter what your job title or responsibilities are.

Thanks as always for participating! Please publicize so we get some good representative results.

My favorite currency converter site is http://www.xe.com/ucc/ (link opens new window).

If you work in the USA or Canada:

Please choose the survey based on the number of years of IT experience you have and select your compensation range in US$.

If you work in Europe:

Please choose the survey based on the number of years of IT experience you have and select your compensation range in Euros.

If you work anywhere else in the world:

Choose the 'Other' answer in the survey below and enter your country, years of experience, and salary in your local currency. I'll figure out how to represent the data.

(Used with permission from Dilbert.com)

It's an interesting marketplace for DBAs right now. Depending on who you speak to, and what your view into the DBA world is, a few data points are evident:

• There's a view that all the good people already have jobs
• There's a view that it's pretty hard right now to hire good people
• There's a view that some companies are looking for unreasonable amounts of experience for new DBA hires
• There's a view that some companies are looking to give new DBAs way too much responsibililty

So this is all a bit of a problem. How can someone find a new job if many of the jobs are overly-demanding or look to be a recipe for over-stress? And how can companies expect to tempt people away from their jobs if the overall package isn't better?

Smart people working for good companies are going to realize that the grass isn't greener somewhere else, so will be making themselves more attractive to be retained by their present employers. And smart companies with good people are going to realize that they need to make sure they up the ante to prevent people looking for greener grass.

With all this in mind, I thought it would be interesting to conduct a survey to find out what companies are doing, if anything, to retain their talented DBAs, and what talented DBAs are doing, if anything, to show their companies that they're valuable employees. The original survey is here and this post is about the results.

It's worth reading the "Other" answers for each survey as they paint an interesting picture - with some lucky folks in dream jobs and some being treated very badly by their employers.

The "Other" responses are:

• 6 x Not replacing employee's that leave, increasing my work load. yes this is sarcasm...
• 5 x The company already is excellent for most of the above.
• 4 x I just joined the company after years at another company that did none of the above.
• 3 x Increased responsibility/visibility/opportunity.
• 2 x Company is offshoring to China putting me out of a job later this year.
• 2 x Company is providing me oppurtunity to work in the direction of making a change in existing working & make it more beneficial & efficient.
• 2 x I am the company - but trying to increase my rates.
• 2 x Increased compensation after I told them I was going to leave.
• Exposing me to new SQL Server Tech that I have not had in other positions.
• Flexible hours, health plan, trainings.
• I left my previous higher paying gig to focus on what I wanted to do to build up my skillset.
• I left the company as a result of salary. They hired someone new and less capable for more than I asked to stay.
• I love this new job. Running my own SQL practice, autonomy, trust, decision making control, helping great clients. Don't tell 'em but they could withhold raises for a year or two and I'd still stay :-) First time I can ever say that about a job.
• I'm leaving.
• Increased compensation, increased training budget, new laptop.
• Opposite of all: Decrease bene, no comp inc, decrease tellcom, 0 budget for training or hardware, no promo, increased expectations.
• Outsourcing to India and making me redundant.
• Paying for PASS Summit.
• Retaining high quality people to work with.
• The companys noble vision and mission.
• Wait until the buy-out - salaries/bonuses may go up/be paid.

I'm very surprised that half the employers out there are doing nothing to retain their staff given how hard it can be to hire new, capable people. That's pretty depressing to see. Of course, we don't know the reasons why - could be the company is strapped for cash because of the economy or just that they employ Catbert as their HR Director. And you should see some of the private emails I've had from people about how companies are truly screwing the people that work for them.

On the flip-side, 40% of companies are increasing pay, flexible working, or training budgets - that's pretty cool. Savvy companies know they have to invest in their people to stay successful.

For those in the first 50%, I'd seriously think about why the company doesn't seem to value it's employees and whether it's time to consider moving somewhere that does. One of the cool things about the online SQL community is that there's a lot of empathy for people looking to change jobs and twitter can be a very powerful way to get the word out that you're available. Even if it's not the right time to be able to make a move, I'd still start racking up learning experiences (either at work or on your own time) to make yourself a more attractive hire when you are able to make a move.

The "Other" responses are:

• 8 x Increasing work hours, increasing responsibility, learning on my own time, saving company money.
• 4 x Many of the above. More for job satisfaction than for the company.
• 3 x Fixing all the stuff that was bad before i joined such as no database backups (despite multiple on staff dbas).
• 3 x I am the company - keeping my skills relevant via out of hours training.
• 3 x Not what I am doing but what has happen is increasing work hours, increasing responsibility, on-cal, process efficiency and keeping it all together with bubble gum and duct tape.
• 2 x All of the doing, none of the nothing and actively lookin.
• 2 x Increasing responsibility, Learning on your own time, acting as unoffical community rep for company.
• All top seven items.
• Anything and everything a business owner does.
• Building up to become the tech face of the company. New position.
• Conducting SQL trainings.
• Constantly developing relevant new skills.
• I resigned weeks ago but I'm still hanging around & helping them.
• I was a Systems Analyst which was part travel and working from home.
• Increased work load.
• Increasing work hours, increasing responsibility, learning on my own time, on-call biweekly, supporting new gloabl regions, improving proccesses, etc.
• Marketing, Bringing in clients, training others, setting up best practices and processes to have more help in the SQL space.
• Serial consulting...fixing everyone's problems then having the contract-to-hire not hire.

Wow - almost 90% of people who responded are doing something to make themselves more valuable to their employers. These are smart people. If you're stagnating in your job, there's no impetus for the company to value you and so you bubble up the 'next out the door' list. However, it needs to go both ways - the company has to realize that you're increasing your worth and that over time they need to recognize that increase by giving something more to you. As the economy starts to pull itself together there's going to be a point where you should call it quits and move on if the company isn't valuing your extra efforts - there's only so long you can increase your working hours or your stress from increased responsibility until it begins to affect your home life detrimentally.

Summary

When I used to manage teams at Microsoft I was very much a believer in recognizing good people and giving back to them (I was sometimes hobbled by Microsoft's nasty stack-ranking review process though) and I still do that today with the people that work for us here at SQLskills.com (Jonathan's eligible for a bonus of at least two vouchers for McDonalds Happpy Meals every month, and our assistant gets to spend the night in her own home once a week instead of sleeping in the cot under her desk - which is more than fair :-). A company cannot expect to attract and retain good people with a so-so benefits package and work environment that tolerates mediocrity and doesn't encourage people to excel.

But an employee cannot expect to be valued unless they show value. It can be a delicate balancing act, with some folks doing just the minimum to get by and others pushing themselves hard to be the shining example to the rest of the team. I've always been in the latter group, but I know *lots* of people in the former group who would whine and complain when they didn't get the pay rise or bonus they expected.

But this isn't about me, it's about you. IT budgets are increasing this year for sure, and you only have one life to live. Take a good hard look at where you are in your career, and how the company treats you. No one deserves to be treated badly or be unhappy in their job - and only you can do something about it. Back at Microsoft, I helped many people with their careers and my biggest message to them was that no-one is going to manage your career except you. Everyone's too busy with their own lives and careers to stop and push you to manage yours. Even as a manager, I would dedicate time to those that wanted to progress their career instead of those that didn't.

To close, I want to share with you my mantra for life: "There's no fate but what we make for ourselves" (from the movie Terminator 2). It's cliched I know, but when it comes down to it, you need to make sure you're getting the deal you want - life, job, partner, house, hobbies, respect... no-one else is going to do it for you.

In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances).

I want to know how much of your precious server memory is being wasted storing empty space on data file pages. I'm sure you'll be interested in it too!

Here's the code (works on 2005+):

SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO

[Edit: forgot that many systems have way more memory than my 16GB laptop, so updated code above uses BIGINT now.

And here's a PowerShell script courtesy of Nic Cain, assuming server list in c:\temp\serverlist.txt:

$SQLQuery = 'SELECT
COUNT (*) * 8 / 1024 AS MBUsed,
SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO'

$servers = get-content c:\temp\serverlist.txt
foreach ($srv in $servers)
{
invoke-sqlcmd -Server $srv -Database master -Query $SQLQuery
}

/Edit]

Send me the results for as many systems as you can, preferably production systems - either as a comment on this post, in email, or in a spreadsheet in email.

I'll collect all the results and explain what this means, how to drill in further, and what you can do about it in a week or two.

Thanks!

It's all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what's going on with SQL Server than any of these tools can go. Or you have to call Customer Support or Premier Support so *they* can dive in deeper.

Typically you or they are going to make use of four DMVs that give increasingly advanced information about what's going on for use in performance troubleshooting:

• sys.dm_os_wait_stats
• sys.dm_os_waiting_tasks
• sys.dm_os_latch_stats
• sys.dm_os_spinlock_stats (this one isn't documented at all and is only mentioned in a few places online)

A few weeks ago I kicked off a survey to find out whether you've heard of or used these DMVs - see here for the survey.

In this post I'm going to present the survey results and explain a bit about these DMVs, focusing the most attention on latches and spinlocks. This started out as a small post but grew into a 10-page, 2500 word article :-)

Here are the results (in each of the Other values, a few people asked what DMVs are - see Dynamic Management Views and Functions in BOL):

Other values:

• 12 x Yes, more than once or twice but not routinely.
• 2 x Only because of your wait statistics post.

The survey results are not surprising, especially amongst readers of my blog.

Wait statistics are the bread-and-butter of performance tuning. SQL Server is keeping track of what resources threads need to wait for, and how long they need to wait. By analyzing which resources (and combinations of resource) are being waited for the most, you can get an idea of where to start digging in further. An example might be that if most of the waits are PAGEIOLATCH_SH waits, and this wasn't the case in your wait stats baseline, you might look at the I/O subsystem performance using the sys.dm_io_virtual_file_stats DMV (which I blogged about here).

Last December I wrote a long blog post introducing wait statistics, showing how to use the sys.dm_os_wait_stats DMV, giving links to resources, and explaining the most common ones that people see in the field based on data from more than 1800 SQL Servers - see Wait statistics, or please tell me where it hurts.

Other values:

• 6 x Yes, More than once or twice but not routinely.

I'm surprised that these results don't tie in more closely with the results for sys.dm_os_wait_stats, but they're reasonably close.

The sys.dm_os_waiting_tasks DMV shows you what is currently being waited on by everything running on the system.

I created a scenario with 200 clients creating and dropping small temp tables to creat tempdb latch contention. Using the DMV, I can see what's being waited on (I've removed the columns describing blocking from the output in this case to make it fit on screen)

SELECT * FROM sys.dm_os_waiting_tasks;
GO

waiting_task_address session_id exec_context_id wait_duration_ms     wait_type          resource_address   resource_description
-------------------- ---------- --------------- -------------------- ------------------ ------------------ --------------------
0x000000000044E508   2          0               4091305              XE_DISPATCHER_WAIT NULL               NULL
0x000000000044E988   9          0               4121252              FSAGENT            NULL               NULL
0x000000000044EBC8   20         0               4121251              BROKER_TRANSMITTER NULL               NULL
0x000000000044F4C8   63         0               53                   PAGELATCH_EX       0x0000000088FFEED8 2:1:1139
0x000000000044EE08   64         0               8                    PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
0x000000000044F288   87         0               0                    PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
0x000000000044F048   91         0               53                   PAGELATCH_EX       0x0000000088FFEED8 2:1:1139
0x000000000044F948   92         0               61                   PAGELATCH_EX       0x0000000088FFEED8 2:1:1139
0x000000000044F708   101        0               10                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000000044FDC8   103        0               37                   PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
0x000000008744E088   118        0               11                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000008744E2C8   121        0               66                   PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
0x000000008744E508   122        0               33                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000008744E748   155        0               32                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000008744E988   158        0               27                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000008744EBC8   163        0               34                   PAGELATCH_EX       0x0000000080FEEC58 2:1:120
0x000000008744EE08   168        0               66                   PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
0x000000008744F048   179        0               26                   PAGELATCH_UP       0x0000000080FE8AD8 2:1:1
.
.

As you can see, the classic tempdb latch contention is showing - page ID (2:1:1) - the first PFS page in tempdb. (See here for more on tempdb contention, and here for more on PFS pages.)

Joe Sack (blog|twitter) created a script that pulls in data from a bunch of other DMVs to make the sys.dm_os_waiting_tasks output more useful, which I've modified into the following:

SELECT
    owt.session_id,
    owt.wait_duration_ms,
    owt.wait_type,
    owt.blocking_session_id,
    owt.resource_description,
    es.program_name,
    est.text,
    est.dbid,
    eqp.query_plan,
    es.cpu_time,
    es.memory_usage
FROM sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es
    ON owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
    ON es.session_id = er.session_id
OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est
OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp
WHERE es.is_user_process = 1;
GO

There's too much information in the output to usefully show in this post, but I can see the actual T-SQL statements being run (in this case a lot of DROP TABLE and SELECT * INTO of global temp tables) and the XML query plans. Clicking on one of them in 2008 SSMS gives me the actual plan - very cool:

This means I can see from the sys.dm_os_wait_stats DMV what the prevalent resource waits are, then use the sys.dm_os_waiting_tasks DMV to see which queries are waiting for those resources - and then dive in deeper to see why.

As I suspected most readers have heard of latches, but 75% of respondents haven't used the DMV or have only used it once or twice.

A latch is a lightweight syncronization mechanism that protects access to read and change in-memory structures - for instance, 8KB page buffers in the buffer pool (latch type = BUFFER), or the data structure that represents a database's data and log files (latch type = FGCB_ADD_REMOVE). A latch is only held for the duration of the operation, unlike a lock which may be held until a transaction commits. One example of locks and latches - imagine a table where an update query has caused lock escalation so that a table X lock is held on the table. As the query continues updating more records in the table, it won't acquire any more locks, but any data and index pages that are updated in memory must be EX (exclusive) latched before the update can occur. The latch acts as the synchronization mechanism to prevent two threads updating the page at the same time, or a thread reading the page while another is in the middle of updating it. Another example is if you run a select query using NOLOCK - although the query will not acquire SH (share) locks at any level in the table, the threads must acquire SH latches on pages before they can be read - to synchronize with possible concurrent updaters.

If a thread requires a latch it will be moved from RUNNING to SUSPENDED and put on the waiter list to await notification that the latch has been acquired in the requested mode.

Latch waits correspond to LATCH_XX waits in the output from the sys.dm_os_wait_stats DMV, so digging into to which latches are accounting for most waits can show where a bottleneck is on the system.

You can reset latch wait statistics just like regular wait statistics using:

DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);
GO

An example set of output from the DMV is:

SELECT * FROM sys.dm_os_latch_stats
WHERE [waiting_requests_count] > 0
ORDER BY [wait_time_ms] DESC;
GO

latch_class                       waiting_requests_count wait_time_ms         max_wait_time_ms
--------------------------------- ---------------------- -------------------- --------------------
BUFFER                            113181121              466697044            1233
ACCESS_METHODS_HOBT_COUNT         66676                  331193               577
ACCESS_METHODS_HOBT_VIRTUAL_ROOT  15018                  68865                125
LOG_MANAGER                       130                    5610                 234
FGCB_ADD_REMOVE                   299                    5073                 32
TRACE_CONTROLLER                  1                      0                    0
VERSIONING_TRANSACTION_LIST       1                      0                    0
ACCESS_METHODS_HOBT_FACTORY       64                     0                    0

You can also aggregate them in the same way as I described in my big wait stats blog post, using code below:

WITH Latches AS
    (SELECT
        latch_class,
        wait_time_ms / 1000.0 AS WaitS,
        waiting_requests_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 sys.dm_os_latch_stats
    WHERE latch_class NOT IN ('BUFFER')
)
SELECT
    W1.latch_class AS LatchClass,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(14, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S
FROM Latches AS W1
INNER JOIN Latches AS W2
    ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.latch_class, W1.WaitS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO

Here's an example after clearing the latch stats and running the tempdb contention test (I described above) for 30 seconds:

LatchClass                        Wait_S  WaitCount  Percentage  AvgWait_S
--------------------------------- ------- ---------- ----------- ----------
ACCESS_METHODS_HOBT_COUNT         7.92    1471       75.41       0.0054
ACCESS_METHODS_HOBT_VIRTUAL_ROOT  1.38    393        13.15       0.0035
LOG_MANAGER                       1.20    12         11.44       0.1001

Most of the latch classes are undocumented, but I'll be shedding light on them as I blog more about latch stats.

Other values:

• 1 x Just learned of this at SQLskills training!
• 1 x Learned about it, after you tweeted about it on 3/23 to a co-worker.

This is really cool that more than 40% of respondents have never heard of this DMV or spinlocks - education time!

A spinlock is another lightweight synchronization mechanism used to control access to certain data structures in the engine - used when the time that the spinlock will be held is very short. They are different from latches because a thread waiting for a latch will yield the scheduler and go onto the waiter list whereas a thread waiting to acquire a spinlock will burn some CPU "spinning" to see if it can get the CPU before giving up and backing off (yielding the scheduler) before trying again. This may allow another thread to execute that is holding the spinlock and eventually release it, allowing the system to proceed (yes, a thread can yield the scheduler and move to the waiter list while holding a spinlock!) because another thread can then acquire the spinlock.

It is perfectly normal for spinlock collisions and spins to occur on a busy system, but sometimes a bottleneck can occur on systems with larger numbers of CPUs where collisions are more likely - this can drain CPU resources while many threads are spinning trying to acquire the spinlock.

Running the DMV shows you the list of all spinlocks on the system (all of which are undocumented - but I'll be working on that going forward) - here is some partial output:

SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY [spins] DESC;
GO

name               collisions           spins                spins_per_collision sleep_time           backoffs
------------------ -------------------- -------------------- ------------------- -------------------- -----------
LOCK_HASH          3629624              4402099957           1212.825            561                  817819
SOS_CACHESTORE     11992297             3352117666           279.5226            6093                 71948
OPT_IDX_MISS_KEY   63329610             2036811058           32.16207            15830                180845
SOS_TLIST          9769744              574740437            58.82861            320                  3619
SOS_SCHEDULER      2137875              107392996            50.23352            557                  7753
MUTEX              676406               83493780             123.4374            340                  3300
LOGCACHE_ACCESS    2210697              83204315             37.63714            0                    252366
SOS_RW             264489               70122059             265.1228            14                   799
XDESMGR            346005               61031449             176.3889            216                  3788
SOS_SUSPEND_QUEUE  3397384              53752545             15.82174            120                  2384
OPT_IDX_STATS      129814               19800952             152.5332            27                   356
BACKUP_CTX         29730                16784471             564.5635            192                  1645
LOCK_RESOURCE_ID   17558                4363116              248.4973            20                   375
SOS_TASK           206597               1898063              9.187273            16                   171
XVB_LIST           266112               882691               3.316991            1                    63
.
.

[

On 2005 you'll need to use DBCC SQLPERF ('spinlockstats') and use INSERT/EXEC to get the results into a table. Eric Humphrey (blog|twitter) put the code together:

-- Baseline
IF OBJECT_ID('tempdb..#TempSpinlockStats1') IS NOT NULL
DROP TABLE #TempSpinlockStats1;
GO
CREATE TABLE #TempSpinlockStats1(
[name] NVARCHAR(30) NOT NULL,
[collisions] BIGINT NOT NULL,
[spins] BIGINT NOT NULL,
[spins_per_collision] FLOAT NOT NULL,
[sleep_time] BIGINT NOT NULL,
[backoffs] BIGINT NOT NULL
)
INSERT INTO #TempSpinlockStats1
EXEC ('DBCC SQLPERF(''spinlockstats'')')
GO

]

The LOCK_HASH spinlock, for instance, is used by the lock manager to look at one of the hash buckets holding lock resource hashes to tell whether lock can be granted or not.

The sleep_time is an aggregate of how much time is spent sleeping between spin cycles when a backoff occurs.

I've put together some code that will allow you to see what spinlock activity occurs between two times. The code captures the output from the DMV into two temp tables, with whatever time period you want in between (to allow you to run a command), and then shows the difference between the two sets of data. I'll show an example of running DBCC CHECKDB.

-- Baseline
DROP TABLE ##TempSpinlockStats1;
GO
SELECT * INTO ##TempSpinlockStats1 FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Do some stuff
DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS;
GO

-- Capture updated stats
DROP TABLE ##TempSpinlockStats2;
GO
SELECT * INTO ##TempSpinlockStats2 FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Diff them
SELECT
    '***' AS [New],
    ts2.[name] AS [Spinlock],
    ts2.[collisions] AS [DiffCollisions],
    ts2.[spins] AS [DiffSpins],
    ts2.[spins_per_collision] AS [SpinsPerCollision],
    ts2.[sleep_time] AS [DiffSleepTime],
    ts2.[backoffs] AS [DiffBackoffs]
FROM ##TempSpinlockStats2 ts2
LEFT OUTER JOIN ##TempSpinlockStats1 ts1
    ON ts2.[name] = ts1.[name]
WHERE ts1.[name] IS NULL
UNION
SELECT
    '' AS [New],
    ts2.[name] AS [Spinlock],
    ts2.[collisions] - ts1.[collisions] AS [DiffCollisions],
    ts2.[spins] - ts1.[spins] AS [DiffSpins],
    CASE (ts2.[spins] - ts1.[spins]) WHEN 0 THEN 0
        ELSE (ts2.[spins] - ts1.[spins]) / -- > 0 spins = > 0 collisions
        (ts2.[collisions] - ts1.[collisions]) END AS [SpinsPerCollision],
    ts2.[sleep_time] - ts1.[sleep_time] AS [DiffSleepTime],
    ts2.[backoffs] - ts1.[backoffs] AS [DiffBackoffs]
    --, ts2.*
FROM ##TempSpinlockStats2 ts2
LEFT OUTER JOIN ##TempSpinlockStats1 ts1
    ON ts2.[name] = ts1.[name]
WHERE ts1.[name] IS NOT NULL
    AND ts2.[collisions] - ts1.[collisions] > 0
ORDER BY [New] DESC, [Spinlock] ASC;
GO

And the output is as follows:

New  Spinlock           DiffCollisions       DiffSpins            SpinsPerCollision DiffSleepTime        DiffBackoffs
---- ------------------ -------------------- -------------------- ----------------- -------------------- ------------
***  DBCC_CHECK         4                    24                   6                 0                    0
***  DIAG_MANAGER       1                    0                    0                 0                    0
***  FCB_REPLICA_SYNC   10                   16147                1614.7            0                    0
***  LSID               9                    0                    0                 0                    0
***  QUERYEXEC          5                    0                    0                 0                    0
***  X_PACKET_LIST      2                    0                    0                 0                    0
***  X_PORT             8                    227                  28.375            0                    0
***  XACT_WORKSPACE     11                   0                    0                 0                    0
***  XID_ARRAY          7                    0                    0                 0                    0
     BUF_FREE_LIST      2                    0                    0                 0                    0
     HOBT_HASH          2                    1                    0                 0                    0
     LOCK_HASH          3                    1818                 606               0                    0
     SOS_RW             2                    500                  250               0                    0
     SOS_SCHEDULER      5                    6                    1                 0                    0
     SOS_SUSPEND_QUEUE  11                   39                   3                 0                    0
     SOS_TASK           1                    0                    0                 0                    0
     SOS_TLIST          1                    0                    0                 0                    0

You can see here which spinlocks were acquired to run the DBCC CHECKDB commands - those marked with *** did not appear in the 'before' set of spinlock stats. More on all of these in future posts.

You can also investigate spinlocks using extended events - again, more on that in future.

Summary

It's possible to dive really deeply into what's happening inside SQL Server using these four DMVs. Spinlocks in particular - what each means, what each controls and what contention on each them means (plus what you can do about it) - involve a lot of knowledge of what's going on inside the engine, and I'm planning to spread some of that knowledge going forward - there's an enormous amount of information to be published about latches and spinlocks.

Hope you'll join me to learn about these - let me know if you find this stuff interesting and useful.

Thanks!

After a discussion on Twitter this morning, my good friend Steve Jones (blog|twitter) said it would be great to have a survey polling people on how their companies are retaining them this year, and vice-versa - so here it is!

The survey is completely anonymous, so don't be afraid to answer. Also, if you'd like to post a comment, feel free too, anonymously or not.

Please vote multiple times - the free survey site does not allow multiple selections, but does allow multiple votes.

The first survey is what is the company doing for you.

The second survey is what are you doing for the company.

I'll report on the results in a couple of weeks - see if you can publicize this survey - the more responses there are, the better it will be for anyone wanting evidence to show their management.

Thanks!

A couple of week ago I kicked off a survey about common causes of performance problems - see here for the survey.

Firstly I asked what was the root cause for the most recent performance problems you looked at - here are the results:

Secondly I asked what you think the overall most common cause of performance problems is - here are the results:

The results are not surprising, but it's good to have empirical confirmation from the community - T-SQL code and then poor indexing strategy are the top two in both surveys, accounting for roughly 50% of all performance issues.

No matter how powerful the hardware and how much memory or IOPS capacity you have, if you write crappy code that means SQL Server has to use really non-optimal query plans then performance is going to be poor. It's really quite a low proportion of the time that hardware (including the I/O subsystem) is the root-cause of a performance problem - usually it's just a symptom of a deeper problem.

One of the most common causes of poorly-performing T-SQL code and indexing strategy is that developers don't write and test the code in an environment that simulates a true production environment and workload. What works for 2 connections with 100 rows in a table isn't necessarily going to work for 1000 concurrent connections and millions of rows in a table. Another cause is making code changes directly in production without testing their impact.

Nothing much else to say here as the theme has been hit on by many people in the past, but now we have some numbers.

Thanks to all those who responded!

In this week's survey I've got four mini-surveys for you, all to do with in-depth performance analysis.

I'd like to know whether you've ever used each of four DMVs that look progressively more deeply into the workings of the database engine.

I'll report on the results in a week or two and start blogging about using these - let me know if that would be interesting to you.

Thanks!

A few weeks ago I kicked off a survey about disaster recovery testing and how the plan copes with human factors. You can see the original survey here.

This Dilbert cartoon is a pretty accurate portrayal of most clients' disaster recovery plans when I first start working with them: http://dilbert.com/strips/comic/2000-08-15/.

Here are the survey results:

The "other" responses are:

• 6 x As a company we haven't done it in decades + but I do test my backups every day.
• 2 x We have a dr strategy but know it's broken.
• 1 x 4x per year, but no fixed schedule.

I'm pretty disheartened by these results - only 41% of respondents test their DR plan at least once a year, but at least 80% of respondents actually *have* a DR plan.

Apart from the obvious reason to test a DR plan initially so you know it works, it's very important to test it regularly as very often assumptions made when the DR plan was written are no longer valid. For instance, if the database size increased then it's going to take longer to restore, and so may break the RTO agreement with your management. What if someone made a change to the backup procedures and now your restore sequence is broken? What if you're not monitoring database mirroring correctly and the REDO queue on the mirror is such that a failover takes longer than the RTO? What if the backup generator is broken? The list goes on and on.

I really didn't expect anyone to pick answer #4 - I'm shocked. How can sane management include preventing the technologists from testing the plan that's going to potentially save the company if a disaster occurs?

One line I like to use as a consultant when talking to senior executives is: would you rather find out that your disaster recovery plan is broken through a controlled test when all the senior folks are standing by to put things right or when an actual disaster happens in the middle of the night on a public holiday when only the most junior folk are on duty and the chances of monetary losses are significant?

(Don't get me wrong - junior does not equal incompetent in any way in my book, but that's the kind of reasoning I've found to work with senior executives in corporations who are far removed from the technological coal-face.)

So how does human nature factor in here? Well, it's human nature to not be worried about disaster recovery - until a disaster happens. It's kind of the "out-of-sight, out-of-mind" mentality. There's also the possibility that people know the DR plan sucks, but no-one wants to confront that fact and have to go fix it - this is sheer irresponsibility on someone's part (maybe not the DBA if they're not given the time to go fix it). There's also the "in won't ever happen to me" mentality. How many of you reading this post have walked around your house with a video camera making note of your belongings in case your house is destroyed? I know I haven't gotten around to it yet - it keeps getting pushed down the to-do list. It takes an effort of willpower to make these things bubble to the top of the to-do list and stop procrastinating.

Go test your disaster recovery plan - you'll be amazed at what you'll find is broken. I wrote a blog post about this back in 2009 after conducting a survey of what people discovered when testing their DR plan - see here.

The "other" responses are:

• 1 x I am so not coming to work on that day.
• 1 x Our DR site is 1200 miles away, but assumes compliance by the DR site folks. A nationwide disaster would be tough to overcome.

These results are not surprising at all. The majority of companies do not consider human nature during a disaster. Saying that, however, I think a distinction should be made between countries that are highly disaster-prepared and disaster-conscious, like Japan, and countries that in general aren't, like the US (go read this blog post that discusses Japanese disaster preparedness if you think I'm wrong here).

I think it comes down to what I said above: "it won't happen to us". Most DR plans that I've seen assume that the disaster being recovered from is one that's only affecting that company and isn't affecting the personal lives of those responsible for doing the disaster recovery. But in a widespread disaster most people are going to be focusing on themselves and their family, not thinking about whether the production database is still available. Does your company realize that?

Time to rethink your disaster recovery plan? No-one else is going to do it for you... that's human nature.

This survey follows on from the survey results I just blogged about, and is particularly apt given we're here in Dallas this week teaching our Immersion Event on Performance Tuning.

I'm interested to know the root causes of your last few performance problems. I know that we all debug multiple performance problems every week, so have a think about the issues you debugged over the last week or month and vote a few times (or as many times as you want) to reflect the eventual root cause that was determined.

In the second survey, I'd like your opinion on what the most common cause of SQL Server performance problems is. Slightly different way of looking at things, and it'll be interesting to see how the results differ from the first survey.

Please vote as many times as you want on the first survey - free survey website only allows ten choices and no multi-select - but only vote once on the second survey.

(Damn - forgot to tick the box for 'Allow other' as a response - feel free to leave a comment with another response!) 

I'll report on the results in a couple of weeks.

Thanks!

About a month ago I kicked off a survey asking what you look for when first analyzing a plan for a poorly performing query. You can see the original survey here.

Here are the survey results:

The "Other" values are as follows:

• 13 x "Most expensive as percentage of total cost of batch"
• 7 x "It depends on what I am trying to fix/improve"
• 4 x "Fat arrows"
• 3 x "Operator cost"

First of all - what would my answer be? I tend to look for scans and the most expensive operators, plus big fat arrows that stand out.

There's a clear winner here in opinion amongst my readers - by far the most common first consideration when analyzing a query plan is to look for scans - I'm not surprised by that.

In this editorial I'm not going to write about how to go about analyzing query plans - there's so much to cover that I'd end up writing a book-length blog post. Instead I'd like to point you at a bunch of resources that will help you with query tuning, including a few books.

I use SQL Sentry's fabulous free Plan Explorer tool to do this as I can switch back/forth between showing costs by CPU, by IO or combined. I can also see cumulative costs for a branch of the query plan, rather than having to look through a bunch of operators for expensive ones - this is invaluable to me day-to-day when working on client systems. It has a host of other features that SSMS does not have and I know lots of people who use it - why wouldn't you?

If you've never look at a query plan before, I strongly recommend Grant Fritchey's free e-book Dissecting SQL Server Execution Plans. I'd also recommend Grant's regular book SQL Server 2008 Performance Tuning Distilled.

As far as the choices I gave in the survey, each of them can be a major problem but aren't necessarily a problem at all. It's like the misconception that if you have wait stats, then you must have a performance problem. See my long blog post on wait stats for more info.

Here is some specific info that will help you understand the ramifications of each problem in a plan that I listed in the survey:

• Different row counts or executions between the estimated and actual plans usually indicates that either statistics are out of date leading to a bad plan, or maybe a plan was cached for a stored proc based on atypical parameters. Checkout the following links:
  • Conor vs. Dynamic SQL vs. Procedures vs. Plan Quality for Parameterized Queries (Conor Cunningham)
  • Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints (Conor Cunningham)
  • Estimated rows, actual rows and execution count (Gail Shaw)
  • Plan cache and optimizing for adhoc workloads (Kimberly)
• Sorts can sometimes be caused by unneeded ORDER BY statement or by missing nonclustered indexes. A good, quick overview of sorts is in Showplan Operator of the Week - SORT (Fabiano Amorim)
• Joins are very often misunderstood and there are a whole host of reasons why one join may be chosen over another. Best thing to do here is point you at Craig Freedman's excellent series that I link to here.
• There are all kinds of reasons why scans appear in query plans, not all bad at all. One of the bad reasons is that there are insufficient nonclustered indexes which mean the table has to be scanned to retrieve the data - see the books and search on Google/Bing for an enormous amount written about this. Another reason is T-SQL code written so that an index *cannot* be used because an expression does not isolate the table column correctly, forcing a scan. I blogged about this here.
• Yet another reason why scans occurs is code written/schema designed so that an operation called an implicit conversion occurs, where the table column must be converted to a different data type before a comparison can take place - forcing a scan, as each value has to be converted to the comparison type. Jonathan blogged about this here.
• Key/RID lookups are where the query plan uses a nonclustered index to find a value, but the nonclustered index is not covering, so the other result set columns must be retrieved from the table row. When a Key Lookup occurs, the retrieval is from a clustered index and when a RID Lookup occurs the retrieval is from a heap. Both of these are undesirable because of the extra processing required. The fix for this is simply to ensure the correct indexes exist to support the queries, and that the queries are pulling in the correct columns - many times I've seen client code that pulls in columns that aren't necessary.
• The most expensive operators in a plan are usually a good place to look to see where gains can be made by changing code, statistics, or indexing. I blogged a short post on using SET STATISTICS to watch IO and CPU costs here.
• Parallelism is again often misunderstood and not necessarily a bad thing. For a large report query in a data warehouse, parallelism is good. For frequent queries in a busy OLTP system, parallelism can be a problem. The best presentation I've seen by far on parallelism is by Craig Freedman - check it out here.

And that's what I have for you on query plans - lots of information for you to go exploring and learning. Analyzing a query plan is a skill that all DBAs and Developers should have IMHO and all the information is out there for you to try it out on your systems.

Enjoy!

The recent earthquake in New Zealand and this morning's devastating earthquake and subsequent tsunami in Japan set off a lot of discussion on twitter about DR planning and testing.

I wrote a blog post on our SQL Server Magazine blog this morning asking people to think about their testing - see here.

I'm conducting two surveys here. One is asking about testing your DR strategy and the other is asking about whether the DR strategy considers human nature in the event of a natural disaster. Depending on the disaster, people won't necessarily be thinking about the company as their first priority.

Feel free to leave comments about your DR strategy and testing experiences and I'll include some of them when I editorialize the results in a week or two. And by all means send me private email if you want to vent in more depth! (But nothing sent in email will be included in the editorial.)

Thanks

A few weeks ago I kicked off a survey about tempdb configuration - see here for the survey. I received results for more than 600 systems! Here they are:

These are very interesting results, for several reasons:

• It shows the relative distribution of core-count for SQL Servers, with a pronounced shift to 8+ cores (55%), but still with a quarter of respondents using 4-core machines - they've got a lot of life left in them.
• It shows a result from someone with 128 cores on their server (at time of writing I didn't know what kind of machine, but the machine owner was kind enough to add a comment - it's an 8x8 with hyperthreading enabled and 2TB memory!).
• It shows that across the board, just over half of all servers are configured to have a single tempdb data file, regardless of the number of cores.
• For the 7 systems with 48 or more cores, none had a single tempdb data file.

One of the hidden causes of poor performance can be incorrect tempdb configuration. There has been a lot of info posted about tempdb, so I don't want to repeat it here, but instead give you some pointers.

Is tempdb I/O a bottleneck? Use the sys.dm_io_virtual_file_stats DMV (one of my favorites) to see the read/write latencies. Jimmy May (blog|twitter) has a great script using this DMV here that calculates per-I/O latency for all files on a SQL Server instance. If you see latencies higher than you'd expect from your I/O subsystem (say 15ms or more), then you need to take action. Adding more files or moving tempdb to a faster I/O subsystem is not necessarily the answer. It could be that you have some workload making use of tempdb that shouldn't be - e.g. a large sort or hash join operation that's spilling to tempdb, or someone using snapshot isolation in a database and causing a lot of read/write activity because of the version store. Take a look at the whitepaper Working with tempdb in SQL Server 2005. It also applies to 2008 and will show you how to diagnose tempdb performance issues. If you know what's going on with tempdb and it all looks ok, then you will have to reconfigure tempdb. This could mean adding more files to allow I/O parallelizing (just to be clear - I mean I/Os being serviced by different portions of the I/O subsystems, not multiple outstanding I/O requests from SQL Server - which happens all the time), moving to faster storage, moving away from other database files. The solution will be different for everyone, but a generalization is to separate tempdb from other databases, and sometimes separate tempdb log from tempdb data files.

Is tempdb allocation a bottleneck? This is where in-memory allocation bitmaps become a contention point with a workload that has many concurrent connections creating and dropping small temp tables. I've discussed this many time - most recently in the post A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core. This post has lots of links to scripts where you can see if this is a problem for you, and discusses trace flag 1118 and how to work out the cores:tempdb-data-files ratio for your situation.

Note: if you are going to add more tempdb data files, ensure that all tempdb data files are the same size. There is a known bug (which has been there forever and is in all versions) that if tempdb data files are not the same size, auto-growth will only occur on the largest file. You can work around that using trace flag 1117 (which forces all files to auto-grow at the same time) but that applies to *all* databases, just like trace flag 1118, which may not be the desired behavior.

Bottom line: you've only got one tempdb - take care of it!

Back at the end of January I kicked off a survey about the relationship between your DBA and dev teams. See here for the survey.

Here are the results:

The 22 Other values are:

• 5 x The devs are great - it's the apps and the process that suck.
• 4 x It depends on the development team.
• 3 x I'm the team lead and the DBA.
• 2 x It's fine with the internal developers, but the consultants... not so much.
• 2 x It's non-existent. Management does not have the DBA and dev team work together until there is an issue.
• 1 x Essentially non-existant, between all groups: Infrastructure, DEV, DBA. Management has no interest in fixing it. Total crap gets moved to prod all the time, DBA's are the bad guys cause I've stopped the practice of moving db side "things" into prod without testing.
• 1 x It is spread out across country, DEV team think they are gods, what's a DBA?
• 1 x The DBA/Dev relationship is good, it's the DBA/Vendor relationship that sucks because vendor apps suck!
• 1 x The dev team have no clue what Data Architecture is all about 1The dev team is learning. Getting better all the time.
• 1 x We are a small Admin team supporting scores of development teams. The teams are very segregated so relationships are very weak.

A very interesting variety of situations and views. Good to see that the majority of people are experiencing good relations between teams (answers 1 + 2 + 7 + 8 = 59%).

Here's a comment from the original post that sums up what I see a lot of the time when first working with clients:

The developers think the DBAs are a bunch of idle boneheads, and the DBAs think the developers are a bunch of cowboys with little regard for the availability of the live servers.

Strong words, which demonstrate a fundamental lack of understanding about what the various teams do. That is very often the root cause of all the animosity between teams - not understanding the goals and motivations of the other team. For instance, a dev team is often under a lot of pressure to get the next release finished and put into production. A DBA team needs to preserve the availability and performance characteristics of the production system. These two goals are often at odds as the dev team wants to throw code over the wall and get it deployed, whereas the DBA team wants the code to be tested to ensure it will not compromise the production system in any way.

There's also the case of those who answered #6 - one guy who's the problem between the teams. This could be someone in an architect or lead developer role, who has a lot of experience, but maybe some misconceptions, who has a domineering personality and is always right - no matter who is trying to show them that they are in fact incorrect. We've never come across this person during our consulting work, oh no. :-)

How do you work towards solving these problems?

Education.

It's no good just complaining and blaming each other when things don't go well. You need to have the other team understand *why* your team does what it does, *why* it's concerned about certain things, and *why* it operates under the pressures it does. I have a lot of experience managing development and program management teams and managing the relationships between the dev teams and the test teams, the dev teams and the build teams, the dev teams and the program management teams - so I'm speaking from experience here.

Last year in London we did a 1-day workshop where the focus was bridging the gap between development and production. We highlighted a bunch of problems we've seen over the years and how some education could have helped. Here are some of the takeaways people had from the day:

• Avoiding known design anti-patterns - don't repeat mistakes that others have made. Once you find a performance problem, give feedback on the design anti-pattern that caused it to everyone involved. A great example of this is thinking that single-column non-clustered indexes on every table column will be useful. There are some great posts and books about SQL anti-patterns, such as this one.
• Prototype and test design strategies - it's imperative that the developers have a test system that reproduces production workloads to the extent that proper scalability and load testing can be performed; otherwise it's not going to work in production. I see this over and over. It's also very important that developers and architects understand the ramifications of data type and schema design choices on the operational aspects of SQL Server (for instance, a LOB column in a table prevents online index operations on the clustered index).
• Workload analysis and performance base-lining – nothing allows you to identify that a new set of code has a problem faster than having a performance baseline, and monitoring so you can easily identify that the latest code change caused a performance change.
• Troubleshooting to identify problems rather than throwing hardware at the problem - it's always better to find the root cause of a problem rather than trying to work around it with bigger iron or faster disks. You'll eventually run into the problem again. A good example is writing a query that is non-SARGable and requires an index or table scan. I blogged about this recently here and many others have too. Implicit conversions are another great example - Jonathan has a great blog post about finding them here.
• Build trust and partnership between teams – success is not one-sided and both teams look bad when an application/project fails. A company with teams at odds will suffer.
• Consistent post-mortems and “lessons-learned” from problems and mistakes rather than laying blame - being able to say mea culpa and proposing changes will elevate you above your peers whether you're a dev or a DBA. Just remember that if it's someone else's fault, constructive criticism needs to be well-phrased to be well-received. Don't make it personal.

And the one we stressed the most was:

• Work to cross-train and educate both teams for best practices

This is the most powerful way to foster relationships between teams in my experience - offering to educate the other team on, say, one issue a week. This will show them that you're willing to put skin in the game (and hopefully they'll reciprocate) and in the long-term it will save you time. And it's a great way to show initiative and that you're a team player  and motivator to your management. Some ideas for you on this:

• After a post-mortem, put together a list of links to blog posts and articles that explain facets of the problem and how to identify and or avoid it
• Organize a weekly self-study group that involves both teams. Use online material like our MCM prep videos as a base. Management will love this, and people will almost feel compelled to be part of it, rather than showing management that they don't care about working better together. Even the nay-sayers will eventually see the benefit and come around. Persevere!
• Get the team leads together to voice concerns and clear the air in a pre-agreed no-offense-will-be-taken closed-door meeting. You may be surprised at what lingering misconceptions, grudges, and misunderstandings can be cleared up by letting people vent and clearing the air.
• Bring in an outside consultant who knows what they're talking about to be an independent voice of reason with no political baggage for either team. We get asked to do this every so often - it can be challenging but very effective, especially for the "there's this one guy" problems where no-one can convince them, or wants to publicly stand up to them. Often helping the person see the light and think they've made the leap themselves is the best way forward - which can be hard to do from within the team, but a lot easier as an outsider, where we don't care about a position in the company pecking-order.
• Send one dev and one DBA to a training class where they can both learn and bring back the learning to the teams with the two perspectives. Of all the classes we teach, the Internals and Performance class is the best for this.

To summarize, a bad working environment is toxic. You can fix it. It'll take some effort on your part but the pay-off is really worthwhile. And there are lots of resources out there to help you out.

What are you waiting for?

In this week's survey, I want to know how you've got tempdb configured compared to the number of processor cores SQL Server thinks it has. I'll correlate, analyze, and present the results like the log file survey I did last year where I got results for 17000 databases.

The code I'd like you to run is as follows:

SELECT os.Cores, df.Files
FROM
   (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
   (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;
GO

It works on 2005 onwards (thanks to Robert Davis (blog|twitter) for doing a quick 2005 test for me).

I'll take as many results as you can be bothered gathering for me - anywhere from 1 to 100s!

To help you out, Eric Humphrey (blog|twitter), who's in our class this week, put together a PowerShell script to help you run the code against multiple servers. See here - very cool!

Either add your results as a comment or drop me a mail with the results. Feel free to add in any explanation you want as to the reasoning for your setup. Everything will remain anonymous as always.

I'll report on the results in a week or two.

[Edit: the survey is now closed.]

Thanks!

Last week I kicked off a survey about the size of the largest single SQL Server database in your company - see here for the survey. I was interested to see how the distribution has changed since I did the same survey a few years ago.

2011 results:

2009 results:

Very interesting! As far as raw percentages are concerned, the distribution looks really the same at first glance. The raw numbers are higher, but that just reflects increased blog readership I think.

Looking a bit more carefully, there's a slight 2% increase in databases > 5TB, a 4% shift out of the 1-10GB range into the 10-100GB range, and a 3% shift from the 500GB-1TB range into the 1TB to 5TB range.

I must admit that I expected to see a more pronounced shift in the distribution towards the higher database sizes, so I was surprised. However, there's a definite overall increase in the distribution of database sizes out there - which means more people are wrestling with the challenges of maintaining a true VVVLDB - very very very large database :-)

How do you take backups of a 10TB+ database?

How do you do index maintenance on a 10+TB database?

How do you do consistency checks on a 10+TB database?

None of these operations are going to fit inside your maintenance window any longer. The trick is splitting the database up (using filegroups and/or partitioning) and rearchitecting all these processes to be done piecemeal over a period of time.

For example, one of the things I always help clients with VVVLDBs to do is architect an index maintenance process that does the absolute minimum of work, with tracking of what operations are being performed on what indexes, and heuristics to automatically change how often and what operations are performed on what indexes - almost like a self-tuning index maintenance process. This takes time to implement and get right, and makes things more complicated.

Backup and restore become much more complicated because you have to start dealing with filegroup-level full and diffferential backups, which the majority of people do not do.

I already blogged about consistency checking options for VLDBs back at the end of 2007 - I stand by those recommendations still.

Bottom line - as your databases grow in size, your database maintenance and disaster recovery practices have to change to be smarter so they don't start to take prohibitively long.

Thanks to all who responded!

In my survey this week I'd like to find out how large the largest single SQL Server database is at your company (how much space does the data take up on disk, roughly). I ran a similar survey almost two years ago and I think it will be interesting to compare the results.

[Edit: the survey is closed now.] 

I'll report on the results next week. If you answer with a database size more than 20TB, I'd be really interested to know the size so I can tell people - completely anonymously of course. Leave a comment if you want.

Thanks!

PS Don't forget the early-bird registration for our March performance tuning class in Dallas ends this Friday!

Last week I kicked off a survey about network latencies and database mirroring. See here for the original post.

Here are the results of the survey:

I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn't a statistically valid sampe by any means, it does show that the answer is no. However, we're missing some data that would help explain what we see here: how long are the average transactions and is there a response time SLA?

The latency between the principal and the mirror is a big deal for synchronous mirroring, because a transaction on the principal cannot be acknowledged to the user/app as having committed until all of it's log records have been written to the mirror database's log drive.

NOTE: the transaction does NOT have to be replayed/committed on the mirror, simply the log records have to be durable to guarantee the transaction is durable if the principal has a disaster. This is a very common misconception.

If the average transaction length is quite long, say 20 seconds, then the addition of another 500ms of latency when the commit is issued is not a big deal. But if the average transaction length is 100ms then an extra 500ms is a *very* big deal. This is when using asynchronous mirroring starts being considered - as transactions on the principal do NOT have to wait, but at the expense of potential data loss if the principal experiences a disaster. However, if there is no response time SLA, then the company may be fine with the extra delay with synchronous mirroring to guaranteezero data loss (as long as the mirror session stays SYNCHRONIZED).

As always, the choice of HA and DR technology comes down to analyzing requirements and limitations before choosing a technology. I go into this in more detail in the whitepaper I wrote in 2009 for Microsoft: High Availability with SQL Server 2008. There is also an excellent whitepaper on database mirroring: Database Mirroring Best Practices and Performance Considerations.

If you're one of the people who responded that you don't know your network latency even though you're using mirroring, check out the post I wrote last week: Importance of monitoring a database mirroring session.

Thanks!

In my survey for this week I'm interested in what you look for first when analyzing a query plan.

I'll report on the results around mid-February.

Thanks!

PS Post comments are disabled to avoid skewing the results.

Last week I kicked off a survey about how you monitor your database mirroring sessions. See here for the original post.

Here are the results of the survey:

The "Other" values were:

• 6 people monitor mirroring session state changes
• One uses a 3rd-party product to do monitoring
• One monitors response time for the application
• One made a sheep joke - very good
• 8 people don't use mirroring, which isn't relevant here

In my opinion, the only people who are monitoring mirroring correctly are those who picked:

• All of the above metrics
• SEND and REDO queue sizes
• Comprehensive monitoring

The problem with database mirroring is the hype around it providing instant failure detection and instant failover.

These properties are mythical.

In reality, the time it takes to detect a failure depends. The time it takes to failover depends. I've written about these in the past:

• A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately
• A SQL Server DBA myth a day: (11/30) database mirroring failover is instantaneous

It is absolutely essential that you monitor at least the SEND queue size and the REDO queue size of a mirroring session.

• The SEND queue size shows how much transaction log has been generated on the principal server but has not yet been sent to the mirror server. If it is non-zero, means the mirroring state is not SYNCHRONIZED, meaning that an automatic failover cannot occur. Furthermore, the SEND queue size is an indication of the data loss that will occur if the principal database were to suffer a disaster. You need to monitor this to ensure the size of the SEND queue does not exceed your maximum allowable data loss SLA (or RPO) for the database being mirrored.
• The REDO queue size shows how much transaction log exists in the mirror database that has not yet been replayed on the mirror database. (Remember that log records just have to be hardened on the mirror database's log drive, not replayed - that is done as an ongoing process on the mirror server.) If a mirroring failover occurs, the mirror database cannot be accessed until all transaction log records in the REDO queue have been replayed on the mirror database - essentially crash recovery has to occur. The larger the REDO queue, the longer a failover will take. (Remember that in Enterprise Edition, fast recovery comes into play and the database becomes available after the REDO phase of recovery has completed and before the UNDO phase begins.) You need to monitor this to ensure the size of the REDO queue does not exceed your maximum allowable downtime SLA (or RTO) for the database being mirrored.

The oldest unsent transaction is another way to monitor the instantaneous amount of data loss you would suffer if the principal database suffered a disaster. It applies in all modes of database mirroring, because even if you are using synchronous mirroring, the principal and mirror can become disconnected, or you may pause mirroring.

The mirror commit threshold is good to monitor to see what kind of delay is being added to transactions waiting to commit on the principal because their log records have not been acknowledged as written to disk on the mirror.

Books Online discusses all of these here.

There are a bunch of performance counters for database mirroring but unfortunately the Books Online entry for them is very sparse. The best place these are documented online that I know if is a blog post of mine from 2008 (which the KB article about configuring database mirroring actually references too). See SQL Server 2008: New Performance Counters for Database Mirroring.

For those of you who don't know how to monitor database mirroring, there is a half-decent tool in SSMS called the Database Mirroring Monitor. It allows you to easily configure alert thresholds the four metrics I discuss above.

Here's a picture of it running inside the VPC I use to demo mirroring for the MCM prep videos.

It's very easy to use and you can read more about it in Books Online here.

MCM Robert Davis (blog | twitter) also covers monitor database mirroring in his excellent book - see here.

Bottom line: you must monitor database mirroring to ensure the mirroring session is preserving your downtime and data-loss SLAs.

Happy monitoring!

I'm going to do two surveys this week. In this one I want to know what the relationship is like between the DBA team and the dev team wherever you work (or whatever comes closest team-wise). Remember the survey is completely anonymous. If you want to, feel free to add an anonymous or otherwise comment below too.

I'll present the results during the second week of February.

Thanks!

In my survey for this week, I'd like to know what you what the network latency is between your mirroring principal and mirror servers, and what kind of mirroring you're using.

I'll report on the results during the second week of February.

Thanks!

In my survey for this week, I'd like to know what you monitor if you're using database mirroring.

[Edit: The survey has closed now. See here for the results.]

I'll report on the results during the first week of February.

Thanks!

Just before Christmas last year I created a survey to ask you what your job title is and additionally, if you wanted, what you actually did in your job. See here for the original post.

Here are the results of the survey:

I think that clearly shows the type of people reading my blog - more than 60% are a DBA of some kind - and I'm not surprised, given what I blog about.

The "Other" values were:

• 11 x Consultant
• 7 x Database Engineer
• 6 x Data Architect
• 5 x Database Analyst
• 4 x Database Solution Architect
• 4 x Subject Matter Expert
• 2 x Data Analyst
• 2 x Database Architect
• 2 x Emerging Technology Expert
• 1 each for Chief Data Architect, Data Administrator, Database and BI Developer, Database Delivery Architect, Database Systems Coordinator, DBA for Reporting and BI, Default Blame Acceptor, Enterprise Architect, Enterprise Data Architect, Lead DBA, Network Administrator, Operations Engineer, Product Manager, Production DBA, Programmer/Analyst, Project Manager, SAP Admin DBA, Senior DBA, Solution Architect, Solutions Designer, Senior Director, System Analyst, System Engineer, System Engineer for MS Project Server, Virtual World Database Administrator

WOW!! That's a lot of different titles for people who all need to understand some facets of how SQL Server works, behaves, and can be tuned.

Here are some of the 40 comments that people left on the original post describing what they do (with names where people put their names for all to see, but I didn't bother with blog/twitter links this time):

• David Maxwell: 'My official title is DBA. (Which is what I voted.) My official duties are mostly production support, a little bit of assistance with stored procs and such, mostly performance related. Oh, that and, "if anyone screws up entering data, fix that too..."'
• Rob Sullivan: 'My title is DBA. That means anything dealing with databases (performance, design, ha/dr, dev), applications or the network usually ends up at my feet. From troubleshooting firewall issues (so users can get to the db) to helping the AppDevs use their ORM's correctly (very rarely do they take the time to learn to use them correctly on their own), I need to be on top of it. It is a large hat for sure, but I have a huge head so bring it on.'
• Adrian Hills: 'Developer: Strong focus on backend development (SQL Server & .NET) including database design, development, query optimisation and performance testing. Also work on maintenance-related scripts such as manual archiving/partitioning of data (no Enterprise Edition here!). Heavy focus in my role on the performance/scalability side of things so SQL Profiler/execution plans etc are not things to shy away from - some people feel that's crossing more into DBA territory but I personally think it's a very key part of being a developer.'
• Gail Shaw: 'Consultant: Meaning: If it's even tangentially associated with SQL Server, I'm generally expected to be able to do it.
  Seriously, these days it's mostly a mix of database design, database development, query optimisation and SSIS development with occasional forays into admin (backups and configuration)'
• Michael J Swart: 'My title is "Database Developer." My company sells enterprise solutions to colleges. Other coworkers take care of the hardware, clustering and security. Here's an (incomplete) list of what I do.
  * I'm an in-house consultant for a number of products.
  * I'm responsible for the performance and scalability of the database.
  * Day to day, I often audit sql scripts and schema changes.
  * DB design.
  * Troubleshoot when our support team determines a problem is database-side, but not hardware.
  * Give lunch-and-learns for .net developers on best practices and standards.'
• Matt Velic: 'I wrote in "Data Administrator." While there is an actual DA position, and it has to do with metadata management, my position is far beyond that. When I was given the title, it was explained to me that it was so they could give me any and all data related tasks from SQL Server administration, helping out with reports, to data entry and cleanup. It's quite the cluster...'
• Michelle Ufford: 'I'm currently a Developer DBA. What that means, at least in my current environment, is that I primarily focus on new database development. And by that, I mean I primarily create new schema and stored procedures, with some SSIS packages when necessary. I'm a Developer DBA and not a Database Developer because I'm also responsible for things like maintenance, ad hocs, query optimization, and troubleshooting. However, backups, security, replication, etc. all fall under a different group's responsibilities.'
• Jonathan Allen: 'I get to be a Database systems Coordinator. Not "Administrator" as that applies to a role that is lower in the Co. hierarchy than a "Coordinator". Roles that I fulfill Systems Analysis, Requirements Analysis, Project Management, Application Development (web and .exe), Database Admin (checking backups, security, config etc), Database Development (new databases, changes to existing), Performance Tuning/Code checking other BI users, Capacity Planning and Upgrades (hard/soft ware), Liaison between ICT and MI teams, HA/DR.'
• Kelly Martinez: 'Official title is programmer/analyst. Work takes the analyst part to the max and I find myself doing everything from system administration to database development to web. The key phrase in my job description is "...and additional duties as assigned".'
• Emil Fridriksson: 'My job title is "Senior virtual world database administrator" and I have a hard time translating it to Icelandic. I'm the senior DBA over the team that manages the massively multiplayer online game EVE Online and other projects CCP Games is working on. My duties are maintenance and monitoring of the production and test database servers for EVE Online, consultation with developers about database issues, hardware scoping for database servers, resolving customer support issues escalated from the customer support department, development of maintenance scripts, maintaining PCI compliance, mentoring the other DBAs in the team, SAN configuration, maintenance and monitoring as well as some operations of the application servers that run Eve Online. All the regular DBA things and a few that would go under developer, SAN admin or IT admin. I'm probably forgetting something '
• Robert Miller: 'Enterprise DBA, Architect, and Developer responsible for Development, QA, and Production environments of a 24x7 Social-Site Aggregator.
  
  Responsibilities include:
  1) Design and implement an environment able to maintain 100% up-time (2 years and running -- ignoring the painful speed-bump called Database Mirroring). This includes propping new hardware, O/S, Clustering, SAN-management, and ongoing maintenance while balancing needs with budgetary constraints.
  2) Participate in all database related changes.
  3) Participate in application architecture, design and implementation as DBA and Developer (T-SQL and Application code).
  4) Responsible for BI-related development and support
  5) Responsible for Data-Warehouse activity
  6) Involved in all Release Pushes from Development to QA to Production
  7) Responsible for performance issues in Production and communicating any needed changes to the development team or implementing them myself.
  8) Responsible for providing a recovery mechanism if everything fails -- Having valid backups helps.
  9) Always on-call. If not able to be near a system, then able to coordinate activity until I am able to be in-front of a system.
  10) I am certain I missed something and simply tossing in the anything a DBA, DB-Developer, BI-Developer would do or be responsible for should cover it.
  
  I would like to include a serious jealousy of "Emil Fridriksson's" job at CCP -- See his comment above.'
• John Pertell: 'My title is DBA but like most other commenters here I wear multiple hats. I'm the senior DBA in our company only because I have the most knowledge and I'm responsible for the day-to-day management of 10 servers plus dev and testing. Because of that I'm usually brought in when other servers have issues. I do some database development work and some BI tasks. I'm probably not an Enterprise DBA since most decisions about SQL architecture (server configuration, SAN configuration, HA/DR, etc.) are made by other non-DBA managers.'

This is some really interesting reading - thanks to everyone who shared a comment on the original post.

It's very clear that a job title in our portion of the IT industry doesn't always equate to the actual job duties performed, and nearly always does not imply a strict boundary limit of responsibilities. There are just too many facets of the SQL Server environment and data-tier linked application lifecycle to neatly compartmentalize someone's responsibilities and provide clear boundaries. I know the demarcation lines have been further blurred over the last few years during the general recession where companies have laid off part of the IT staff and the remaining employees have had no choice but to pick up the extra duties their previous colleagues took care of. I know there are lots of over-worked and stressed-out DBAs/whatevers out there because I deal with some of them at clients I work with. Sometimes that's a sucky situation to be in, sometimes it makes someone thrive.

But I didn't want this to devolve into a rant about companies over-working people. I wanted to illustrate with the survey and its results that lines are blurred.

For instance, whose responsibility is it to troubleshoot badly performing code on a production SQL Server? The DBA or the developer who wrote the code? I'd say that I hear most of the time that it's the DBA, but is that right? Shouldn't the developer who wrote the code have tested it under load and scale to ensure it performed well? I'd say yes. But what if they don't know what to look for, or how to stress test it? I'd say it's the DBA's responsibility to help educate the developer so the developer knows how to properly QA the code before handing it to production.

To take the example a little further, what if the badly performing code is because of a design decision that a business analyst/architect made around table structures. Who's responsibility is it to fix the code? I'd argue that the business analyst/architect should be aware of the ramifications of design choices on how SQL Server will operate and perform. Which comes down to education again.

Very often I see serious enmity between developers and DBAs because of ill-defined or implied responsibilities - or lack thereof - and people lose site of the fact that they're playing on the same team - the company they work for.

I could boil all of this down to say that anyone involved in dealing with SQL Server needs some education so they can make informed design choices, perform proper testing, perform efficient troubleshooting, and on and on and on... regardless of your job title.

And there's really no excuse for ignorance these days, with so much information freely available and easily searchable.

Hope you found this an interesting read!

At the end of December I showed you how to discover if power saving is enabled on your server, which can lead to variable and often degraded performance. I also included a survey to let me know what you found after running the free CPU-Z tool on your servers. See here for the original post.

I want to do a quick post to show you the results of the survey.

The five 'other' results were:

• "As part of my server build scripts I disable power management"
• "I thought I had power savings on and it was. However, I had never confirmed. (Personal Computer)"
• "I thought we had power saving OFF and it was OFF. Good deal."
• "Not sure if power saving is on -- "Core Speed" is 75% of rated speed"
• "Told IT that they need to change the bios settings and reboot. have they ? have they hell!"

As you can see, almost 40% of the people who tried the tool AND took the time to fill in the survey reported that they discovered power saving was erroneously enabled.

Have you tested your system yet?

Thanks!

(Yes, I know I haven't editorialized the last survey on What's in a Job Title - I will in the New Year.)

Over the last couple of weeks I've signed up a bunch of new customers for maintenance/ops audits and general perf work and I've had all of them check whether power saving mode is enabled for their CPUs. And I've been astounded by the results - as have some of the customers who thought they were running at full speed all the time.

In a nutshell, to save power the CPUs can essentially down-shift to a lower clock speed and then automatically speed up again when the load increases. But at what point do they speed up? That varies - you might have to really push things to make them speed up.

And do you really want your workload running slower until the CPU usage hits the magic speed-up threshold? Most likely the answer is no.

So the point of this blog post - I'd like you to go check the CPU speeds on your systems and see whether they're in power-saving mode without you knowing. I think you'll be surprised. And then I want you to tell me what you saw.

You can check the CPU speed using the free CPU-Z tool - it's awesome. It's just asks the CPU it's speed - no load is put on the system.

The picture below shows a CPU-Z snapshot from a client system. The CPUs are spec'd at 2.4GHz but as you can see, they're only running at 1.2GHz - half speed.

Go download the tool and run it on your system - what did you see?

If you found power savings on, you can read about how to turn it off in the BIOS and Windows Server in a blog post by Glenn Berry ("Windows Power Plans and CPU Performance").

I'll report on the results in a few weeks. [Edit: The survey results can be found HERE.]

You can another read example of this problem in a blog post by Brent Ozar ("SQL Server on Power-Saving CPUs? Not So Fast.") about helping Stack Overflow upgrade.

Have fun!

PS There's also a KB article that discusses how Windows Server 2008 R2 sets the Balanced power plan by default! See here.

There's been a lot of discussion on the MVP distribution list about various certifications and what SQL Server roles they cater to. Furthermore, there's a lot of disagreement on what the multitude of job titles mean. In this survey I'd like to find out what you think your primary job role is, and if you're up for it, add a comment or send email about what you job role is, what you're expected to do under that job role, and what you're not expected to do.

[Edit: The survey is closed. You can read the results HERE.]

Note: I don't imply anything by the order of job roles in the survey.

I'll report on the results during the first week of January.

Thanks!

One of the most under-utilized performance troubleshooting methodologies in the SQL Server world is one called "waits and queues" (also known simply as "wait stats"). The basic premise is that SQL Server is permanently tracking why execution threads have to wait. You can ask SQL Server for this information and then use the results to narrow down where to start digging to unearth the cause of performance issues. The "waits" are what SQL Server tracks. The "queues" are the resources that the threads are waiting for. There are a myriad of waits in the system and they all indicate different resources being waited for. For example, a PAGEIOLATCH_EX wait means a thread is waiting for a data page to be read into the buffer pool from disk. A LCK_M_X wait means a thread is waiting to be granted an exclusive lock on something.

The great thing about all of this is the SQL Server *knows* where the performance issues are, and you just need to ask it.... and then interpret what it tells you, which can be a little tricky.

Now - where people sometimes get hung up is trying to track down every last wait and figure out what's causing it. Waits *always* occur. It's the way SQL Server's scheduling system works.

A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it's resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed. You can see processes in these states using the sys.dm_exec_requests DMV. 

SQL Server keeps track of the time that elapses between leaving the RUNNING state and becoming RUNNING again (called the "wait time") and the time spent on the RUNNABLE queue (called the "signal wait time" - i.e. how long does the thread need to wait for the CPU after being signaled that its resource is available). We need to work out the time spent waiting on the SUSPENDED list (called the "resource wait time") by subtracting the signal wait time from the overall wait time.

Tom Davidson of Microsoft wrote a fantastic and very accessible whitepaper on "waits and queues" which I encourage you to read: Performance Tuning Using Waits and Queues. My good friend Joe Sack (blog|twitter) who runs the MCM program also published an excellent slide deck on the subject that you can download here. And of course Books Online has a section on the sys.dm_os_wait_stats DMV that gives info on some of the newer wait types. PSS is putting together a repository of info on all the wait types but not much progress has been made. And there's a free video+demo recording as part of the MCM online training we recorded for Microsoft - see here.

You can ask SQL Server for the cumulative wait statistics using the sys.dm_os_wait_stats DMV, and many people prefer to wrap the DMV call in some aggregation code. I use code based on a query that I got from fellow-MVP Glenn Berry (blog|twitter) and then modified quite a bit. See below for the version updated to take account of the results discussed below:

WITH 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 sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
        'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
        'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
    )
SELECT
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 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

This will show the waits grouped together as a percentage of all waits on the system, in decreasing order. The waits to be concerned about (potentially) are those at the top of the list as this represents the majority of where SQL Server is spending it's time waiting. You can see that a bunch of waits are being filtered out of consideration - as I said above, waits happen all the time and these are the benign ones we can usually ignore.

You can also reset the aggregated statistics using this code:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

And of course you can very easily come up with a way to persist the results every few hours or every day and do some time-series analysis to figure out trends or automatically spot problems as they start to happen. You can also use Performance Dashboard to see these graphically in 2005 and Data Collector in 2008. On SQL Server 2000 you can use DBCC SQLPERF ('waitstats').

Once you get the results, you then start figuring out how to interpret them and where to go looking. The whitepaper I referenced above has a ton of good info on most of the wait types (except those added in 2008). There are various ways you can dig in deeper to this information that I'll go into in later posts.

I'm going to start blogging about wait stats analysis, either as standalone posts or as part of other things - and I've already done so in the last post (at time of writing this) in my benchmarking series.

For now, I want to report on the results of the wait stats survey I posted a couple of months back. I asked people to run the code above and let me know the results. I received results for a whopping 1823 SQL Servers out there - thank you!

Here's a graphical view of the results:

I'm not surprised at all by the top four results as I see these over and over on client systems.

For the remainder of this post, I'm going to list all the top wait types reported by survey respondents, in descending order, and give a few words about what they might mean if they are the most prevalent wait on your system. The list format shows the number of systems with that wait type as the most prevalent, and then the wait type.

• 505: CXPACKET
  • This is commonly where a query is parallelized and the parallel threads are not given equal amounts of work to do, or one thread blocks. One thread may have a lot more to do than the others, and so the whole query is blocked while the long-running thread completes. If this is combined with a high number of PAGEIOLATCH_XX waits, it could be large parallel table scans going on because of incorrect non-clustered indexes, or out-of-date statistics causing a bad query plan. If neither of these are the issue, you might want to try setting MAXDOP to 4, 2, or 1 for the offending queries (or possibly the whole instance). Make sure that if you have a NUMA system that you try setting MAXDOP to the number of cores in a single NUMA node first to see if that helps the problem. You also need to consider the MAXDOP effect on a mixed-load system.
• 304: PAGEIOLATCH_XX
  • This is where SQL Server is waiting for a data page to be read from disk into memory. It commonly indicates a bottleneck at the IO subsystem level, but could also indicate buffer pool pressure (i.e. not enough memory for the workload).
• 275: ASYNC_NETWORK_IO
  • This is commonly where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it's consuming it reeeeeally slowly because of poor programming.
• 112: WRITELOG
  • This is the log management system waiting for a log flush to disk. It commonly indicates a problem with the IO subsystem where the log is, but on very high-volume systems it could also be caused by waiting for the LOGCACHE_ACCESS spinlock (which you can't do anything about). To be sure it's the IO subsystem, use the DMV sys.dm_io_virtual_file_stats to examine the IO latency for the log file.
• 109: BROKER_RECEIVE_WAITFOR
  • This is just Service Broker waiting around for new messages to receive. I would add this to the list of waits to filter out and re-run the wait stats query. 
• 086: MSQL_XP
  • This is SQL Server waiting for an extended stored-proc to finish. This could indicate a problem in your XP code. 
• 074: OLEDB
  • As its name suggests, this is a wait for something communicating using OLEDB - e.g. a linked server. It could be that the linked server has a performance issue.
• 054: BACKUPIO
  • This shows up when you're backing up directly to tape, which is slooooow. I'd be tempted to filter this out.
• 041: LCK_M_XX
  • This is simply the thread waiting for a lock to be granted and indicates blocking problems. These could be caused by unwanted lock escalation or bad programming, but could also be from IOs taking a long time causing locks to be held for longer than usual. Look at the resource associated with the lock using the DMV sys.dm_os_waiting_tasks.
• 032: ONDEMAND_TASK_QUEUE
  • This is normal and is part of the background task system (e.g. deferred drop, ghost cleanup).  I would add this to the list of waits to filter out and re-run the wait stats query.
• 031: BACKUPBUFFER
  • This shows up when you're backing up directly to tape, which is slooooow. I'd be tempted to filter this out.
• 027: IO_COMPLETION
  • This is SQL Server waiting for IOs to complete and is a sure indication of IO subsystem problems.
• 024: SOS_SCHEDULER_YIELD
  • If this is a very high percentage of all waits (had to say, but Joe suggests 80%) then this is likely indicative of CPU pressure.
• 022: DBMIRROR_EVENTS_QUEUE
• 022: DBMIRRORING_CMD
  •  These two are database mirroring just sitting around waiting for something to do. I would add these to the list of waits to filter out and re-run the wait stats query.
• 018: PAGELATCH_XX
  • This is contention for access to in-memory copies of pages. The most well-known cases of these are the PFS, SGAM, and GAM contention that can occur in tempdb under certain workloads. To find out what page the contention is on, you'll need to use the DMV sys.dm_os_waiting_tasks to figure out what page the latch is for. For tempdb issues, my friend Robert Davis (blog|twitter) has a good post showing how to do this. Another common cause I've seen is an index hot-spot with concurrent inserts into an index with an identity value key.
• 016: LATCH_XX
  • This is contention for some non-page structure inside SQL Server - so not related to IO or data at all. These can be hard to figure out and you're going to be using the DMV sys.dm_os_latch_stats. More on this in future posts.
• 013: PREEMPTIVE_OS_PIPEOPS
  • This is SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means.
• 013: THREADPOOL
  • This says that there aren't enough worker threads on the system to satisfy demand. You might consider raising the max worker threads setting.
• 009: BROKER_TRANSMITTER
  • This is just Service Broker waiting around for new messages to send. I would add this to the list of waits to filter out and re-run the wait stats query. 
• 006: SQLTRACE_WAIT_ENTRIES
  • Part of SQL Trace. I would add this to the list of waits to filter out and re-run the wait stats query.
• 005: DBMIRROR_DBM_MUTEX
  •  This one is undocumented and is contention for the send buffer that database mirroring shares between all the mirroring sessions on a server. It could indicate that you've got too many mirroring sessions.
• 005: RESOURCE_SEMAPHORE
  • This is queries waiting for execution memory (the memory used to process the query operators - like a sort). This could be memory pressure or a very high concurrent workload. 
• 003: PREEMPTIVE_OS_AUTHENTICATIONOPS
• 003: PREEMPTIVE_OS_GENERICOPS
  • These are SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means.
• 003: SLEEP_BPOOL_FLUSH
  • This is normal to see and indicates that checkpoint is throttling itself to avoid overloading the IO subsystem. I would add this to the list of waits to filter out and re-run the wait stats query.
• 002: MSQL_DQ
  • This is SQL Server waiting for a distributed query to finish. This could indicate a problem with the distributed query, or it could just be normal.
• 002: RESOURCE_SEMAPHORE_QUERY_COMPILE
  • When there are too many concurrent query compilations going on, SQL Server will throttle them. I don't remember the threshold, but this can indicate excessive recompilation, or maybe single-use plans.
• 001: DAC_INIT
  • I've never seen this one before and BOL says it's because the dedicated admin connection is initializing. I can't see how this is the most common wait on someone's system... 
• 001: MSSEARCH
  • This is normal to see for full-text operations.  If this is the highest wait, it could mean your system is spending most of its time doing full-text queries. You might want to consider adding this to the filter list. 
• 001: PREEMPTIVE_OS_FILEOPS
• 001: PREEMPTIVE_OS_LIBRARYOPS
• 001: PREEMPTIVE_OS_LOOKUPACCOUNTSID
• 001: PREEMPTIVE_OS_QUERYREGISTRY
  • These are SQL Server switching to pre-emptive scheduling mode to call out to Windows for something. These were added for 2008 and haven't been documented yet (anywhere) so I don't know exactly what it means. 
• 001: SQLTRACE_LOCK
  • Part of SQL Trace. I would add this to the list of waits to filter out and re-run the wait stats query.

I hope you found this interesting! Let me know if there's anything in particular you're interested in seeing or just that you're following along and enjoying the ride!

I've recently been creating some content about wait stats analysis and I think it would be really interesting to see what kind of waits people are seeing out there in the wild. Hopefully it'll also introduce a bunch of people to the waits-and-queues performance troubleshooting methodology and how it can be really useful to them.

[Edit: comments are closed on this post as I have all the info I need - thanks!]

Here's what I'd like you to do:

• Run the T-SQL query from the end of this post. It's completely benign and only reports on statistics that SQL Server is already gathering. It will not cause any perf issues on your production systems.
• Look at the output - it'll be something like (from random anonymous client system):

• Look at the top WaitType and fill in the simple survey below. If your top wait isn't listed, click on Other and cut-and-paste the top WaitType into the box provided.

The free survey system only allows a single vote per IP address - if you have any other results, send them in email (mailto:paul@SQLskills.com?Subject=Wait stats) or attach a comment below. 

In a week or two I'll report on the results. It would be great to get a few hundred responses. 

Thanks!

T-SQL code to run (works on 2005 onwards) - based on code by Glenn Berry:

WITH 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 sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType,
     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
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

Back at the start of July I kicked off a survey around your plans for SSDs (see here) and now I present the results to you. There's not much to editorialize here, but the numbers are interesting to see.

The "other" answers were (verbatim):

• 3 x 'have bought and am trying them out'
• 3 x 'not sure if we need them or not'
• 2 x 'all production servers are hosted'
• 1 x 'bought them, tried them..not good enough yet for tempdb'
• 1 x 'Have some, want more, could you really every have enough?'
• 1 x 'We get every penny from or spinning media, and have no need for SSD'

The results reflect what I've been hearing when teaching classes and talking to customers/conference attendees over the last six months. People are becoming more interested in SSDs but there's still a lot of wariness about them and of course the whole money issue of being able to buy them. I'm also not surprised (given the general readership demographics of this blog) by the number of people who've analyzed their IOPS requirements and concluded that they don't need SSDs to accomplish that.

The "other" answers were (verbatim):

• 3 x 'not in the budget'
• 1 x 'I plan to buy expensive drives and throw them at you, paul! love, conor'
• 1 x 'I'm going to do the same thing Conor will do. Denny'
• 1 x 'OLAP Scale Out'
• 1 x 'Use them as cache'
• 1 x 'Using in an EMC V-MAX SAN to dynamically move high workloads to SSD temporarily'

Ahem - thanks Conor and Denny :-)

Another unsurprising set of results that reflects what I've been hearing. One number I'd be interested in drilling deeper into is answer #3 - are people putting/planning to put tempdb on SSDs because that's what they've heard is the best thing to do, or because tempdb truly is the largest I/O bottleneck that can benefit the most from SSDs? That's a set of experiments I'd like to try out with my Fusion-io drives.

The final "other" answer is also interesting - I was talking to a couple of folks from EMC in Ireland about the V-MAX when we were there earlier this month. Very cool idea to migrate data up and down a set of devices with varying latencies (at the block level, not the file level) - I'd like to see more on how the technology copes with one-off operations like consistency checks or backups - do those IOs affect which layer a block resides in?

Anyway, hope you find these results interesting.

Thanks to all those who responded!

It's been a while since I conducted blog surveys so I'm going to do a few through July and August.

First up - I'd like to know what your plans are for purchasing SSDs - do you already have them or are you planning to buy some in the next year?

Secondly - I'd like to know what you are (or will be) doing with them.

I'll report on the results in a couple of weeks. The surveys are completely anonymous. Spread the word so we get a good volume of responses! 

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

Cheers!

(PS Comments are deliberately disabled.)

(Happy Independence Day to everyone back home in the USA! We're in Dublin for another week...) 

Back at the end of April I conducted a survey of transaction log characteristics (see here) and now I present the results to you.

I had a great response to the survey from almost 100 people, and the total data set for these observations is 16841 databases on 847 SQL Server instances (SQL Server Edition is irrelevant).

This isn't the most exciting blog post, but I at least found the numbers interesting - hope you do too! I've got some pointers in the summary to log management blog posts.

Number of Databases Per Instance 

Each plot on the graph below is a count of the number of instances having that many databases, including the four system databases.

Just under 75% of instances have 20 or fewer databases (including system databases). The highest number per instance is 571.

Number of Log Files Per Database

This wasn't worth plotting. The results are:

Log Files   Number of Databases
1           16657
2           158
3           9
4           8
5           1
6           4
7           1
24          1

I'm impressed by this - I expected to see more databases with multiple log files. The one with 24 is tempdb - someone setting the number of log files to equal the number of processor cores I'd guess.

System Databases: Log File Size

Each plot on the graph below shows the number of log files of that size across all the instances, color-coded for master, msdb and tempdb.

89% of master database log files are less than 10MB (a number I arbitrarily chose as a comparison point).

Only 40% of msdb log files are less than 10MB. This would suggest that there are a high proportion of msdb databases not using the SIMPLE recovery model. However, only 5% of them aren't using SIMPLE.

Only 33% of tempdb log files are less than 10MB. I would expect this number to be low.

Looking at the distribution on the graph, master log files (blue) are generally smaller, tempdb log files (green) are generally larger, with msdb (red) in the middle. Again, this is what I'd expect to see.

System Databases: Number of VLFs

Each plot on the graph below shows the number of log files with that many VLFs across all the instances, color-coded for master, msdb and tempdb. (If you're unfamiliar with the term 'VLFs', see the TechNet Magazine article I link to at the bottom).

98.6% of all master log files have less than 100 VLFs (our arbitrary 'you may have too many VLFs' tipping point), with the maximum being 331.

Only 65% of all msdb log files have less than 100 VLFs. From the other 35%, only 5% of them aren't using the SIMPLE recovery model. The maximum number of VLFs is 7646.

Surprisingly, 75% of tempdb log files have less than 100 VLFs. I expected this number to be higher in general, because of tempdb autogrowth. The maximum number of VLFs is 59708.

Looking at the graph, master (blue) is as I expected, but msdb (red) and tempdb (green) are the opposite of what I expected - with more msdb log files having more VLFs.

User Databases: Log File Size

Out of all 13451 user databases in the sample:

• 6797 use the SIMPLE recovery model (50.5%)
• 177 use the BULK_LOGGED recovery model (1.3%)
• 6477 use the FULL recovery model (48.2%)

This in itself is surprising - I expected to see a higher proportion of FULL recovery model databases. 

Given that the log file behavior is the same for FULL and BULK_LOGGED recovery models, and as so few databases use the BULK_LOGGED recovery model, I lumped the FULL and BULK_LOGGED databases together for analysis.

Each plot on the graphs below shows the number of log files of a certain size across all the instances.

The graphs above have different sizes because more databases in the SIMPLE recovery model are larger sized. The overall distribution looks very similar though.

90.7% of user databases using the SIMPLE recovery model have a log file size less than 1GB, compared with 83.0% for non-SIMPLE user databases.

98.2% of SIMPLE user databases have a log file size less than 10GB, compared with 96.8% for non-SIMPLE user databases.

The maximum size of a log file for a SIMPLE user database is 1020GB, dropping to 326GB for a non-SIMPLE user database.

The non-SIMPLE user databases seem to be slightly better managed. My guess would be this represents databases that have been in the FULL or BULK_LOGGED recovery models, have grown out of control and been switched to SIMPLE, but not cleaned up.

User Databases: Number of VLFs

Each plot on the graphs below shows the number of log files with a certain number of VLFs across all the instances.

As with the log file sizes, the distributions of these two graphs look very similar. 

76.9% of user databases using the SIMPLE recovery model have less than 100 VLFs, dropping to 67.8% for those not using SIMPLE.

98.4% of user databases using the SIMPLE recovery model have less than 500 VLFs (definitely a point at which maintenance is required), dropping to 97.0% for those not using SIMPLE.

The maximum number of VLFs for the SIMPLE user databases is 34057, and 27093 for those not using SIMPLE.

Given that more user databases using SIMPLE are larger, but have slightly fewer VLFs, I'd guess this indicates that autogrowth of thse databases was slightly higher.

User Databases: Log File Size vs. Number of VLFs

At first glance, the graph below looks confusing. It shows the number of VLFs each log file has compared to its size in MB. If you look at the first few log file sizes, you'll see that there are multiple points plotted on the Y-axis. This is because there are databases that have the same size log files, but the log files have different numbers of VLFs.

There's definitely a loose trend-line in the graph, with a knee at around 50MB log file size.

Summary

I'm impressed. 75% of all databases in the sample have less than 100 VLFs, and 97.4% have less than 500 VLFs. I expected the results to be worse, but they seem show that the majority of you are managing your logs well. Now, these results are a bit skewed though because they're coming from people who read my blog, where I bang on and on about good maintenance and management.

I was very surprised by the 50/50 split of SIMPLE/non-SIMPLE recovery model for user databases. My guess is that more of these *should* be in FULL to comply with business SLAs around RPO/data-loss, but for some reason aren't, or aren't any longer because of out-of-control growth issues.

Thanks to everyone who sent me results!

Here are some links for further reading:

• Importance of proper transaction log size management
• TechNet Magazine: Understanding Logging and Recovery in SQL Server
• Transaction Log VLFs - too many or too few?
• 8 Steps to better Transaction Log throughput
• New script: is that database REALLY in the FULL recovery mode?

Enjoy!

Over the last few months there's been some noise (mostly of my making) on Twitter about the number of VLFs in transsction logs. Given the large numbers of people who read the blog and follow me on Twitter, I thought it would be very interesting to collect some statistics from a few hundred of you about how your transction logs are configured - in terms of the size, number of log files, and number of VLFs.

To that end, I've created a little script that you can download and run which will generate output of the form:

DB ID  Recovery Model  Log Size (MB)  Log Used (%)   Log File Count  VLF Count 
------ --------------- -------------- -------------- --------------- ----------
1      SIMPLE          6.12           34.42          1               22
2      SIMPLE          0.49           75.60          1               2 
3      FULL            14.68          97.90          1               51
4      SIMPLE          2.49           45.92          1               10
11     SIMPLE          1.99           33.55          1               4
12     FULL            359.99         77.84          1               96
13     FULL            0.48           45.67          1               2

I'd like as many of you as possible to send me the results for some of your systems - there are no database names in the output and the results will be completely anonymous. I'll collect all the results together and blog some scatter graphs showing how people have things set up.

You can get the code from SQLSkillsLogInfo.zip (2.05 kb) (it works on 2000, 2005, 2008) and send me as many results as you like (paul@sqlskills.com) either in plain text or spreadsheet format - or paste them totally anonymously in a comment. The script only takes a few seconds to run, won't affect performance in any way, and won't affect your transaction logs. Now - if your transaction log has many thousands of VLFs, it might take a few minutes to run...

You might think your system is boring - but as far as this is concerned, all results are useful results.

I'll publish the results in a couple of weeks.

Thanks!

This survey is based on a suggestion from Jonathan Kehayias (blog/Twitter).

In this survey I'm asking two questions:

1. When you're designing/implementing a database, do you manually create statistics or let SQL Server create them automatically?
2. How do you maintain statistics? Manually or letting SQL Server do it for you? Or both? 

I'll probably collaborate with Kimberly on the editorial for this, as she's forgotten more about statistics than I'll ever know!

*Please* no comments on this post - wait for the survey results post to avoid skewing the answers (comments are unfortunately moderated anyway, now that my blog gets a lot of spam). I'm very interested in your reasoning, but not until everyone else responds.

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

A few weeks ago I kicked off a survey asking what kind of disaster recovery guide/run-book/plan (I'll just call it a plan from now on) you have (see here for the survey). Here are the results as of 9/15/09:

Out of all these answers, IMHO the last answer is the only acceptable one for a production DBA responsible for recovering critical databases, to a defined RTO and RPO (recovery-time objective and recovery-point objective, respectively).

The problem is though, getting a wonderful and comprehensive disaster recovery plan together is waaay easier said than done. For a start, disaster recovery isn't a sexy topic with management UNTIL a disaster actually happens and the RTO and RPO are completely blown - so it's hard to justify the time and effort needed to put together a good plan. It's especially hard to put a plan together if you're an involuntary DBA, with no idea about what disasters could occur and what you'd do to recover from them.

The reasons to have a plan worked out in advance are pretty much common sense: in a disaster situation, where time is often of the essence, adrenaline and stress levels run high and it can be hard to remain cool, calm, and collected. For an unprepared DBA, this can very easily lead to costly mistakes being made. No-one wants to be the one that overwrote the only existing copy of a database with a corrupt backup and caused the business to be offline for several days. Good-bye job.

A pre-defined disaster recovery plan allows the DBA (or responsible person) to follow a set of tested steps to resolve problems that can occur. A really comprehensive plan covers more than just how to restore the database, but instead will contain troubleshooting information to determine what needs to be done (rather than just immediately taking everything offline and running a restore), and then coping with twists and kinks that could crop up while performing the recovery operation. What makes a plan wonderful is that it gets tested regularly to make sure everything in it is a) still appropriate b) still works c) still works within the defined RTO and RPO. Kimberly and always like to say that a disaster recovery plan should be written by the most experienced DBA you have and tested by everyone else, down to the most junior DBA - everyone needs to be able to work with it.

Bottom line (not a very long editorial this time) - you can't expect to be able to recover within any defined limits if you're just going to wing-it when a disaster strikes. Doesn't matter how experienced you are, crazy stuff happens that can trip you up.

Next up - the next survey!

In this survey I'd like to find out what kind of disaster recovery guide/run-book/plan you have, if any. When a disaster occurs, do you just wing-it, or is there something written-down that was worked out in advance to help you along the way. I'll report on the results in a couple of weeks.

*Please* no comments on this post - wait for the survey results post to avoid skewing the answers (comments are unfortunately moderated anyway, now that my blog gets a lot of spam). I'm very interested in your reasoning, but not until everyone else responds.

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

A few weeks ago I kicked off a survey on how you add geo-redundancy to a failover cluster (see here for the survey). The results as of 8/26/09 are as follows:

So why is this interesting? Well, many people will suggest failover clustering as the best way to provide high-availability for a database (or group of databases). And it is a great technology to protect against server failure, but there's only a single copy of the database, which is the Achilles' heel of failover clustering. If that copy of the database is damaged, the application is down unless there's another copy of the database available. This is where providing geo-redundancy comes in. With that in mind, I'm surprised at the percentage of respondents that don't provide any geo-redundancy at all.

There are a bunch of options for providing a redundant copy of a database that is hosted on a failover cluster, with pros and cons to each, and that's what I'm going to spend the rest of this post on.

SAN replication: This is where the SAN hardware itself mirrors all write I/Os to a remote SAN, thus maintaining a remote copy of the database. The hardware has to provide disk-block size and write-order preservation; otherwise the database on the remote SAN could become corrupt. Imagine if write-ordering was not preserved and some data pages write I/Os were completed on the remote SAN before log records write I/Os (thereby breaking the write-ahead logging protocol) - recovery wouldn't be able to work properly! This mechanism requires a remote SAN, a second failover cluster connected to the remote SAN, a network including both clusters, and a big, fat network pipe between the two SANs. The bigness and fatness of the pipe depends, of course, on how many write I/Os are performed on the local SAN, and whether the SAN replication is synchronous or asynchronous.

Synchronous replication requires that the I/O is completed on the remote SAN and acknowledged back to the local SAN before the local I/O can be acknowledged to the local server. If the network bandwidth and latency can't support the volume of write I/Os trying to be replicated to the remote SAN, the I/Os will start to queue up and delays will be incurred on the local server. This will lead to the workload slowing down as SQL Server has to wait longer and longer for I/Os to complete. Now, with synchronous replication you have the guarantee that the remote copy of the database is completely in-sync with the local copy, so if a failure occurs, no committed data will be lost. If the network can't keep up though, you may have to switch to asynchronous replication. This means the local I/Os don't have to wait for the remote I/Os to complete, and so no performance penalty is incurred. BUT as the replication is now asynchronous, committed data may be lost if the local copy of the SAN is damaged.

Apart from the potential for performance problems with SAN replication, it's also very expensive - as another SAN, another cluster, and some beefy network hardware/bandwidth is required. This isn't a technology I'd expect a small company to be using or considering. Finally, the portion of the remote SAN that's being replicated to cannot be accessed at all. On the MAJOR plus side, all databases on the SAN are replicated at once, without having to setup a technology to provide a redundant copy of each. For application ecosystems that include multiple databases, this is what I like to recommend.

Log shipping: This is the simplest way to maintain a redundant copy of the database - it's just backup log, copy, restore log; repeat. It works seamlessly with failover clustering and is really easy to setup and maintain. The only problem with this is that you open yourself up to data loss, as a log shipping secondary is usually not right up-to-date with the primary. You can use the secondary for reporting/querying by restoring the log backup WITH STANDBY (which requires a little more configuration, but not much), and you can protect against accidental data damage by having a secondary with a load-delay configured, so the database is, say, 8 hours behind the primary. In my experience, this is the most common technology that's used in conjunction with failover clustering as it's the cheapest and easiest. On the downside, it's a single database solution so its not suitable for complicated application ecosystems.

Transactional replication: This isn't very commonly used at all, although again, it works seamlessly with failover clustering after a failover. The reason this isn't used very often for geo-redundancy is that transactional replication doesn't provide database-level redundancy, only table-level. It's also much more complicated to setup and troubleshoot when things go wrong, plus there's varying latency between a transaction committing in the publication database and it being applied to the subscription database(s).

Database mirroring: Database mirroring is the only technology apart from SAN replication that can provide a zero data-loss solution when configured for synchronous operation. It works by shipping the log records from a database rather than the raw I/Os, so doesn't require anywhere near as much capital expenditure, but the network has to be able to cope with sending the log generated on the principal, otherwise performance on the principal can be affected. Mirroring is relatively easy to setup and maintain, and the mirror database can only be accessed, but only through a database snapshot. When combined with failover clustering, you need to be careful about setting the mirroring partner timeout, so that the local failover cluster gets a chance to fail over before mirroring does. Checkout my blog post on this: Search Engine Q&A #3: Database mirroring failover types and partner timeouts. You can configure database mirroring for synchronous or asychronous operation, with the same performance and data-loss exposure caveats as SAN replication. SQL Server 2008 provides log stream compression and automatic page repair, which make this more attractive (see SQL Server 2008: Performance boost for Database Mirroring and SQL Server 2008: Automatic Page Repair with Database Mirroring, respectively), but only supports a single database. I'm seeing this combination start to be used more, but again, it's a single database solution so isn't suitable for complicated application ecosystems.

Backups/homebrew: Good old backups can easily be used to provide a very low cost way of maintaining a redundant copy of a database, and if you think about it, this is really do-it-yourself log shipping. At the very least, databases should *always* be included in a backup strategy, no matter what other high-availability technology(s) you may have implemented.

3rd-party solution: There are a few non-Microsoft solutions for providing redundancy with failover clustering which don't involve traditional SAN replication. I'm not an expert in any of them, but I've heard of anecdotal issues with the two I mentioned in the survey and worked with customers who've had real issues with PolyServe (one of which I blogged about).

Summary 

When you're planning a high-availability strategy, you always need to consider the limitations of technologies while evaluating them. The big limitation of failover clustering is that there's no redundant copy of the database so you need to add another technology to provide that. I've just finished writing a 35-page whitepaper for Microsoft on the high-availability technologies in SQL Server 2008, as well as how to go about planning a strategy. It will be published before PASS in November, but in the meantime, this should have given you lots of food for thought.

Next post - the next survey!

I'm moving away from strictly weekly surveys as they're a little demanding, especially when we're on the road.

In this survey I'd like to know which technology you use to provide geo-redundancy to databases that are protected locally using a failover cluster. I'll report on the answers sometime around the middle of August.

*Please* no comments on this post - wait for the survey results post to avoid skewing the answers. I'm very interested in your reasoning, but not until everyone else responds.

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

A long time ago, in a blog post far, far away (well before I went offline in July) I kicked off a weekly survey about how often you run consistency checks (see here for the survey). Now I'm back online again, and so here are the results as of 8/3/09.

The results are actually surprising - I didn't expect so many people to be running consistency checks so frequently - 25% daily and another 37% weekly - very cool!

The number of people who don't know what consistency checks are may look a little depressing but I think it's probably a symptom of the number of people coming into the SQL world as involuntary DBAs. For those people, consistency checks are a way of proactively checking for database corruption, which is nearly always caused by the I/O subsystem. You can read a good introduction to consistency checking and other database maintenance topics in the article I wrote for TechNet Magazine in August 2008 - Top Tips for Effective Database Maintenance and bit more in the previous survey Importance of how you run consistency checks.

Basically you need to run regular consistency checks. There's a myth that you don't need to run consistency checks - this was perpetuated by various marketing folks when SQL 7.0 shipped, because SQL 6.5 used to cause allocation corruptions and the rewrite for 7.0 removed all the corruption problems. Now, of course there have been bugs in SQL Server that cause problems, but they account for a tiny fraction of the corruptions out there. Nearly all corruptions are caused by something going wrong in the I/O subsystem - and you can't predict when that will or won't happen. Jim Gray once likened the disk heads in a hard drive as akin to a 747 flying at 500mph 1/4 inch above the ground - scary stuff. You DO need to run consistency checks, because corruptions do happen. You can read more about the causes of corruptions in this blog post: Search Engine Q&A #26: Myths around causing corruption.

So - how often should you run consistency checks? Well, it depends. When I'm teaching I like to give two examples:

• You have a dodgy I/O subsystem that is causing corruptions. You have no backups. You have a zero data-loss requirement. With no backups, your only way to get rid of corruptions is to run repair, which usually leads to data loss (see Misconceptions around database repair). In that case, you want to know about corruption as soon as possible to limit the amount of data loss from running repair. Contrived example for sure, but you'd be surprised what I've seen...
• You have a rock-solid I/O subsystem, with all drivers and firmware up-to-date. You have a comprehensive backup strategy which you've extensively tested and you've confident you can recover from corruptions with zero-to-minimal downtime and data loss. In this case you may be comfortable running consistency checks once a week, say.

The overwhelming factor in how often to run consistency checks is you. How comfortable are you with the integrity of your I/O subsystem and your ability to recover from a corruption problem. If you have corruptions in your database today, you'll probably run DBCC CHECKDB on it every day for a month, right?!?

Part of any database maintenance of high-availability strategy is proactively making sure that corruption doesn't exist in the database - otherwise when you DO discover it, it may be more pervasive and it will take you longer to recover, and potentially with more downtime. Therefore the answer really is that you should run it as often as you can. Sometimes that can be difficult for very large databases (your definition is likely to be different than mine - I think 500GB and larger - depends on your hardware etc) as CHECKDB can take a long time to run (see CHECKDB From Every Angle: How long will CHECKDB take to run?), but there are ways you can effectively consistency check even a VLDB - see CHECKDB From Every Angle: Consistency Checking Options for a VLDB.

So - I'm very pleased to see so many people running regular consistency checks. However, maybe I'm just a pessimist but I did expect some of the less than optimal options to have higher numbers. Let's look at each in turn.

Only when corruption is detected some other way. By the time corruption has occurred and is detected through regular operations, it's likely to be more pervasive. Many databases do not see all their data read/updated as part of regular operations, which means that if a part of the database that's not used for a while gets corrupted, and you're not running consistency checks, you're not going to know. That means you're not going to know that your I/O subsystem is causing problems - and so more corruption will occur. You might think this isn't a big deal, but it can be depending on what part of the database (or maybe system databases) gets corrupted. Some things can be restored or repaired relatively simply - but what if, say, the boot page of master is corrupted? Your entire instance is down until you sort that out. It's always better to proactively discover corruption before it hits you in a way that really messes you up.

Only during an event like an upgrade or migration. In this category I see people running consistency checks AFTER doing an upgrade but not before. From release to release, DBCC CHECKDB has improved - especially from 2000 to 2005, where system catalog consistency checks were added. If you run consistency checks after an upgrade and find the database is corrupt, what do you do? Run repair? Go back to the earlier version and run repair? Restore your backups? The odds are that if you've got corruption, it's also in your backups too. Also, you may think that the upgrade itself caused the corruption - I've never seen this. It's always been that the corruption was there before and only got discovered after the upgrade. Always run consistency checks BEFORE doing an upgrade - to make sure you've got a chance of putting things right before (potentially irrevocably) moving to the newer version.

Only after performing a restore, or after a failover. This is, of course, a good practice, but shouldn't be the only time a consistency check is performed - for most of the same reasons as I've explained above. What do you do if the database is found to be corrupt after restoring backups? You're looking at data loss now.

To summarize: make sure you're running regular consistency checks - with the regularity in-line with your comfort zone and your maintenance/high-availability strategies.

Next up - the next survey!

In this week's survey I'd like to know how often you run consistency checks on your *most critical* production database, regardless of *how* you run them (we did that survery already - see Importance of how you run consistency checks). I'll report on the results around July 4th.

I'd only like you to answer for your *most critical* production database, as the frequency will probably vary wildly by database, server, production vs. dev/QA and so on. If everyone answers for their most critical database then we won't get skewed results.

*Please* no comments on this post - wait for the survey results post to avoid skewing the answers. I'm very interested in your reasoning, but not until everyone else responds.

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

PS Thanks to Pat Wright for suggesting this week's topic on Twitter.

In last week's survey I asked how you manage the size of your database *data* files - see here for the survey. Here are the results as of 6/24/09.

The 'other' values are as follows:

1. 5 x 'manual file growths and a custom mom alter to when the datafiles are 98% full. autogrow set to fixed amount in case we miss the mom e-mail'
2. 1 x 'auto-grow with a procedure to keep the log file size at 20% relative to the total data file size'
3. 1 x 'Create with very large initial file size, set auto-growth to %'
4. 1 x 'Lots of white space in files. Auto-grow to a fixed size (in case of emergency).'
5. 1 x 'set initial size for 1 yr usage, monitor size, manual grow, autogrow percentage - send alert if it does grow'
6. 1 x 'set initial size for 2 year growth, capture growth stats daily, monitor physical disk space daily'
7. 1 x 'Set to autogrow by fixed size to cater for emergencies, otherwise maintain 80-90% free space by daily reporting then manual off-peak size increase if necessary.'

As I mentioned in the survey itself, this is just about database *data* files. I covered log file size management in a previous survey - see Importance of proper transaction log size management.

There are really four parts to data file size management, which I'll discuss below.

The first thing I'll say is that if you're able to, enable instant file initialization on 2005+ - as it will vastly reduce the time required to create data files, grow data files, and restore backups (if the restore has to create data files). We're talking minutes/hours down to single-digit seconds. See Misconceptions around instant file initialization. If you're not one of the miniscule fraction of a percent of customers who have volumes shared between secure file servers and SQL Server instances, turn this on ASAP. Most DBAs don't know about this feature, but everyone I teach it to or show it to are amazed and then go turn it on. No brainer.

Initial data file sizing. This can be tricky. Without getting into the how-to-calculate-the-database-size quagmire, I'll simply say that you should provision as much space as you can, based upon your sizing estimates. Don't just provision for the here-and-now - if you're able to, provision space for the next year's worth of anticipated growth - to prevent auto-growth having to kick-in at all. I rewrote all the Books Online pages for 2005 (links are to the 2008 BOL) on Estimating the Size of Heaps, Clustered Indexes, and Nonclustered Indexes and in the blog post Tool for estimating the size of a database I link to a tool someone put together that codified all my formulas. You can also get sizing tools from hardware vendors too.

Data file growth. If you're able to, auto-grow should ALWAYS be turned on, as an emergency measure in case your monitoring fails - you don't want the database to have to grow but it's unable to and then it stops and the application is offline. However, you shouldn't *rely* on auto-grow - it's just for emergencies. The auto-growth default for data files used to be 10% for 2000 and before, but then changed to 1MB from 2005 onwards (log file default auto-growth remained at 10%). Neither of these are good choices. A percentage-based auto-growth means that as your files get bigger, so does the auto-growth, and potentially the time it takes if you don't have instant file initialization enabled. A 1MB autogrowth is just nonsensical. Your auto-growth should be set to a fixed size - but what that should be is a great big 'it depends'. You need to decide whether you want the auto-growth to be a quick stop-gap, or whether the auto-growth will replace manual growth after monitoring. You also need to consider how long the auto-growth will take, especially without instant file initialization. I can't give any guidance here as to what a good number is, but I'd probably settle on something around 10% (fixed), with the percentage steadily falling as the database size grows. It's very important that you have alerts setup to you can tell when auto-growth does occur, so you can then take any necessary action to grow it even more or tweak your settings.

'Other' response #2 is interesting. There's been a 'best-practice' around for a while that the log file should be sized to be a percentage of the data file size. It's totally unfounded and in most cases bears no relation to reality. The vast majority of the time, the size of the log is *NOT* dependent on the data file sizes in any way. Imagine a 10TB database - would you provision a 2TB log? Of course not. Now, I can see special cases where the operations performed on the tables in the database might affect a fixed portion of the largest table in a single batch, and that could generate enough log (with reserved space too) to equal 20% of the data file size - but that's a pretty nonsensical special case, to be honest. You shouldn't use 'set the log as a percentage of the data file' as a guideline.

Data file size/usage monitoring. There's a growing movement towards monitoring the data file usage and manually growing the files as they approach 100% full - avoiding auto-growth altogether, but still having it enabled for emergencies. In my book, this is the best way to go as you have all the control over what happens and more importantly, when it happens - especially without instant file initialization. There are some quirks here though. SCOM, for instance, has logic that disables file size and usage monitoring if you enable auto-grow. It assumes that if you enable auto-grow then you're not interested in monitoring. I happened to have one of the SCOM devs in my last maintenance class I taught on the Redmond MS campus and he's going to try to get that logic fixed.

Data file shrinking. Just this morning I wrote a long blog post about this - see Why you should not shrink your data files. Running data file shrink causes index fragmentation, uses lots of resources, and the vast majority of the time when people use it, is unnecessary and wasteful. It should NEVER be part of a regular maintenance plan - as you get into the shrink-grow-shrink-grow cycle which becomes a zero-sum game with a ton of transaction log being generated. Think about this - if you run a shrink, which is fully logged, then all the log has to be backed up, log-shipped, database mirrored, scanned by the replication log reader agent, and so on. And then the database will probably auto-grow again through normal operation, or some part of the maintenance job that rebuilds indexes. And then you shrink it again. And the cycle continues...

Bottom line - make sure you size the data files with some free space, have auto-growth set appropriately, have instant file initialization enabled if you can, monitor file sizes and usage, alert on auto-grows, and don't use shrink. And don't use shrink. Really.

Next up - this week's survey!

In this week's survey I'd like to know how you manage the sizes of your database *data* files (remember we've already done log file size management). I'll report on the results around 6/21/09.

I say this every week in the PS, but I'm moving it up here because I don't like having to delete comments and send email, *please* no comments on this post - wait for the survey results post to avoid skewing the answers. I'm very interested in your reasoning, but not until everyone else responds.

As always, a big Thanks! for contributing to the blog/community by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

PS Thanks to Steve Jones (of SQL Server Central fame) for suggesting this week's topic on Twitter.

In last week's survey I asked you what you think is the hardest thing when becoming an involuntary DBA - see here for the survey. Here are the results as of 6/15/09.

The 'other' responses were:

• 3 x 'Learning to tell good advice from bad advice'
• 1 x 'Learning to know *that* you don't know'
• 1 x 'Management having unrealistic expectation of my capabilities'

All great answers.

We all know the definition of 'involuntary DBA' - someone who is made to assume a DBA role or take on DBA responsibilities, usually against their will. I used to call them 'accidental DBAs' but too many people thought I meant DBAs who had accidents, so I changed my phrase. Let's go through some of the answers, ranked by number of responses.

Learning to know what you don't know. Actually the first step here is learning to know *that* you don't know - if you think you know everything (really in any field and with any expertise level) then you're most likely wrong, no matter who you are and what the field is. It can be a humbling experience making (and accepting) that realization and then quite daunting when you realize that there is maybe *so much* that you don't know and might have to learn. For an involuntary DBA this can be doubly daunting, because not only do you realize that there's a ton you don't know about being a DBA, but that means there's a ton you don't know about managing this (maybe mission-critical) SQL Server that you're suddenly responsible for and your job security is now suddenly dependent on doing the right thing.

Being the DBA as well as my actual job. Many times an involuntary DBA is actually a bolted-on side-role for someone with a real daytime job too - sometimes not even an especially technical daytime job. I'm teaching a class right now for a large customer of ours and about 1/3 of the 30+ people in the class are involuntary DBAs as well as operations engineers. Some other common examples - SharePoint admins who suddenly find themselves with an Enterprise-class SQL Server instance underpinning their SharePoint farm, developers using SQL Express as part of a client application who have to think about the maintenance implications of having SQL Server installed, the person who sits nearest the SQL Server instance when the real DBA leaves or is fired. Sometimes becoming an involuntary DBA can be very rewarding and take you down an unexpected but ultimately welcome career path, but many times its an unwelcome burden, and very occasionally it can be disastrous. A few weeks ago I heard on the forums from someone who had become an involuntary DBA after the actual DBA has been fired - and his job was on the line if he couldn't get the broken database fixed in 24 hours. Brutal. Being a DBA can be a full-time job in itself, and if you ask many production DBAs, they'll tell you that it can be way more than the standard 40-hour work week (seriously, does that even exist any more in our industry?) - so to try and do that *as well* as a regular 9-5 job might be nigh on impossible.

Finding information on what to do. This can be the real kicker - you've realized that there's lots you don't know - and you want to learn - but where to start? Who to ask? There's a great community of SQL Server folks that are very willing to help out - if you're reading this then you've already stumbled in to the community somehow. There are lots of blogs with information on - some of what I post is deeper information than you *really* need to know to get by, but the editorials in the Weekly Survey series are a good overview of some of the topics to be concerned about, and Kimberly also posts a ton of good info on her blog. In TechNet Magazine, I've been writing many articles aimed specifically at involuntary DBAs - here are some links:

• Top Tips for Effective Database Maintenance (August 2008)
• Understanding Logging and Recovery in SQL Server (February 2009)
• Common SQL Server Security Issues and Solutions (May 2009)
• Understanding SQL Server Backups (July 2009) (with follow-ups on performing restores and recovering without backups coming in September and November respectively)

Here's a quote from an interview I did with SQLRockstar recently (hope you don't mind Tom):

SR: What advice would you give to new SQL DBA’s just entering the field?

PR: There’s a huge amount you don’t know - that’s just a fact. SQL Server can appear very easy to setup and get running, but there are lots of ways to shoot yourself in the foot if you’re not careful. I’m not trying to be scary, just engender a healthy amount of trepidation. Get yourself a mentor if possible, get some training, practice, practice, practice, read a bunch of people’s blogs, follow folks on Twitter, and so on. You need to be an information sponge for a couple of years at least, and get as much experience as you can with the varied aspects of SQL Server - perf tuning, disaster recovery, security, design, etc. And the more you tinker, play, and learn - the more you’ll realize there’s lots you still don’t know. Accept that and be willing to take advice, make mistakes, and learn and you’ll go a long way. Don’t be intimidated to ask people questions, most of us are happy to help - we all started with zero SQL Server knowledge, no matter where we are now.

The last sentence is the most critical in that paragraph. No matter how knowledgeable anyone in the SQL community might seem, every single one of us started with *no* SQL Server knowledge at all. I had never even heard of SQL Server until I joined Microsoft in early 1999 - and there's a *huge* amount of SQL Server I know very little about - I can barely even spell BI for instance. But I know what I don't know and I know where to go to search for information, or who to ask.

And for all those of you in the SQL community, when you're answering what might, at first glance, look like a stupid question from someone with zero reputation or forum points, consider the fact that they may well be an involuntary DBA trying to find an answer because they know they need help and don't have anywhere better to go. Be kind. There's no such thing as a stupid question - the only stupid thing is *not* asking a question when you know you don't know the answer. A sure-fire way to put someone off asking any more questions is to slap them down when they ask their first.

Now, apart from asking the questions on forums etc, another problem that involuntary DBAs have is knowing what information/answers to trust. This one's very hard, as unless you get to know people in the community, you've got no clue who these people are and why you should believe them. The onus is also on us in the community to make damn sure that when we answer a question it's the *right* answer. This is especially true in the corruption forums where telling someone the wrong answer can deepen the corruption hole the poor DBA is already in.

Management that don't understand databases. And also don't understand that the new involuntary DBA isn't going to immediately and magically become 'super-DBA' overnight and fix all the problems with the SQL Server instance. It takes quite a while to learn the ins-and-outs of being a good DBA, and having the confidence to be able to argue with management about what is and isn't possible. There's no easy solution to this one I'm afraid - education is the only option.

Dealing with actual DBAs and developers. There's a tendency in human nature to be scornful and dismissive of people in the same field who are not as knowledgeable or talented - this can be especially true in the technical area we inhabit where sometimes the people we work with may be egotistical and possibly even lacking in social skills - the 'typical' geek developer. Apologies to all those out there who *are* nice and *do* have great social skills - but after 9 years as a developer and manager of various technical disciplines at Microsoft, I know of what I speak. As an involuntary DBA you may come up against some of this antipathy. My advice - you're just going to have to deal with it until you become more knowledgeable. It's unfortunate, but it's human nature. One way to gain respect and trust from these people is to learn some stuff and solve some problems - don't ever BS about something you don't know about - guaranteed way to quickly lose whatever respect you've built up. You might try complaining to your management, but if it's the same management that added being an involuntary DBA to your regular job, from what I've heard, they're unlikely to listen to you. I don't mean to sound depressing, I'm just being honest.

Performance tuning, disaster recovery, database maintenance, implementing an HA/DR strategy. It's hard to pick which of these is the most important for a new involuntary DBA to focus on - but I think I'd go for database maintenance. That addresses some perf issues (index fragmentation management, statistics maintenance) and some disaster recovery issues (taking backups, running consistency checks). Checkout the TechNet Magazine article above for (what I think is) a great primer, and for perf tuning, see the editorial from a couple of weeks ago that has lots of links: Important considerations when performance tuning.

So if you're an involuntary DBA, the bottom line is that it's going to be a struggle for you - but there are lots of resources out there and people willing to help out.

Don't be afraid to ask.

PS By all means add comments with more involuntary DBA resources and I'll collect them together.

This week's survey is all about being a DBA or involuntary DBA - what do you think is the hardest thing when becoming an involuntary DBA? I'll report on the results sometime over the weekend of 6/13/09.

As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

PS And again, as always, no comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers. I'm very interested in your reasoning, but not until everyone else responds.

In last week's survey I asked what you think is the most important thing when it comes to performance tuning, if you *had* to choose one - see here for the survey. Here are the results as of 6/7/09.

Now of course you're all calling 'foul' because I didn't put an 'it depends' option on there which you all would have chosen, but that would have been boring, and I wanted you to pick what you really think is the #1 thing in the majority of cases. Of course, in general the answer is always 'it depends', but some things turn out to be the #1 problem more often than others. For the record, mine and Kimberly's answers were overall indexing strategy, but I deliberately broke them out into clustered and nonclustered to see what people would pick. I'm not surprised that application issues and nonclustered indexing strategy came out top, but good to see that all answers were represented and there's a variety of opinion out there. Variety is the spice of life as they say, or is it garlic? Depends who's cooking I suppose - but I digress.

Let's walk through each option and see how badly they can affect performance if something's wrong. This is by no means an exhaustive list of what could cause problems, just me rambling on at the end of a Sunday, as usual. Blog posts are so much easier than articles because you can have run-on sentences, short little fragments and all sorts of other verboten grammatical beasts.

IO subsystem design/tuning, including RAID: Quite a few things here could be wrong. Is there a write-intensive workload (either data files or log files) sitting on a RAID-5 array (with RAID-5 there's a performance penalty on writes)? Is the disk partitioning alignment, RAID stripe size, or NTFS allocation unit size incorrectly set? (See Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly? for details.) Is the I/O subsystem just not up to the load being placed on it - high disk queue lengths, page IO latch wait errors, long-running IO errors? Is tempdb the bottle-neck because there a multiple procs with multiple users all trying to create/drop lots of temp tables? (See Misconceptions around TF 1118 for details.) Are there multiple databases using the same I/O subsystem such that disk-head contention is causing performance issues? Is there a network bottleneck with iSCSI storage? Are there filter drivers getting in the way and slowing things down?

Server hardware (CPUs/memory/other): Ok - I'm going to sound like a stuck-record saying 'quite a few things here could be wrong'. Let's just take it as read that each category has lots that could go wrong. A heavy load with a single CPU isn't going to perform well. However, sometimes excessive parallelization can lead to the dreaded CX Packet waits - multiple threads in the query processor waiting for information from each other because the server is overloaded - or blocking issues as locks start getting in the way - but that's more an application problem - oops, wrong section. Having more CPUs is usually a good thing as parallel plans usually run faster than single-threaded plans. Having too little memory can severely limit the buffer pool size and force buffer pool thrashing (low page life expectancies), and large operations to spool data to tempdb, placing further load on resources. Not sure what I meant now by 'other' - maybe having a wireless keyboard could slow down commands getting to SQL Server? Ok - I remember - CPU architecture. 64-bit vs. 32-bit. With 64-bit there's no real limit on the amount of memory that can be addressed, and no mucking around with AWE. There are some tricky issues with NUMA to make sure you don't get cross-node memory accesses (just like CPU cache invalidation could hit you on SMP architectures) but that's getting a little advanced and geeky. Btw - watch out for this when you're using Extended Events in SQL 2008 on multi-proc boxes - there's a setting where you can tell it what kind of CPU architecture you have.

Virtualized vs. 'real' server: Now, I'm the first to admit that I'm not an expert in virtualization, but now I have 3 8-ways and lots of disks to play with at home, I want to be. That's why I picked up a Hyper-V book today after reading Tom's virtual bookshelf post here. What I do know is what I've heard from talking to people at conferences and during classes - the main thing here is I/O virtualization. If there's software virtualization of I/Os, then performance is going to suck under load and you better not do it in production. Microsoft's Hyper-V gets around this, as do things like VMware ESX, by allowing you to assign actual real physical resources to virtual machines. And that's as much as I know about it until the book arrives and I get back from Houston to read it. And no doubt I'll be posting more about it.

Database physical layout (files/filegroups): Tempdb is the obvious case, and I've covered that above. User databases are a bit trickier, and it really depends on the underlying I/O subsystem. A single monolithic data file on a single physical disk isn't going to perform well under heavy concurrent load. But it may do fine on a RAID-10 array sliced-and-diced in a clever way by an expensive SAN. In today's world, more and more databases are on SANs so user database performance is taken care of in that respect, and all you need to worry about is tempdb. I touched on this a little bit in the editorial of a previous survey Physical database layout vs. database size.

Table design: Both ends of the spectrum work well here - from the plastic Costco tables with the fold-down metal legs to antique French-oak farmhouse tables. Both will give many years of service with no degradation of performance. Just like regular tables, SQL Server tables come in all shapes and sizes and you can easily pick the wrong one for what you need, leading to poor performance. This is a massive can of worms - in fact Kimberly spends 2 brain-busting days on this in one class she teaches and she could easily spend a week of 10-hour days on it (oh yes, it's called the SQL Masters program :-) From picking the right column types (see Michelle's post at Performance Considerations of Data Types) to deciding how to store you LOB values (see my post at Importance of choosing the right LOB storage technique) to deciding on a good primary key. Just don't pick a random GUID, or if you have one, replace it. Clustered index keys should be unique, narrow, static and ever increasing. Table design encompasses so many things I can't do it justice in a late evening paragraph. But you get the idea.

Heaps vs. clustered indexes: Kimberly likes to wrap this up with nonclustered indexes too, but I wanted to break them out so I could reference a whitepaper that strongly suggests you should use clustered indexes: Comparing Tables Organized with Clustered Indexes versus Heaps. Read it yourself and draw your own conclusions. Basically, with the possibility of forwarding records in heaps, and the majority of a clustered index's upper levels being in memory, the extra random physical I/Os to access a record in a heap outweigh the aggregated cost of the in-memory binary-searches at each level of clustered index tree navigation. I'm sure some people will argue about this - bring it on. There are very special cases where heaps are better, but not in general.

Nonclustered index strategy: Biggie #1. Where to start here and what to say? For the majority of scenarios, if you don't have a good nonclustered indexing strategy you're not going to have good performance. There's a simple reason for this - without the right nonclustered indexes to allow the query optimizer to choose them, you're going to get table scans. Of your 124 billion row table. How fast is that going to be? Again, Kimberly has this class that she teaches on this where she goes into details on how to index for ORs, for ANDs, for JOINs, and all sorts of other kinky stuff. I need to sit and listen to her one day so I can appear knowledgeable about this. I just know that you need to have indexes with the right selectivity. Ok - I just called Kimberly downstairs in the office to make sure I'm right before I blog something and look like an idiot and she starts with 'erm, well it depends'. I give up. I do *corruption* and *HA* and *maintenance*, not performance tuning. It's bloody hard. Luckily I'm married to one of the best people on the planet for SQL index tuning - wow, what hope do you have? (Of index tuning, not of marrying Kimberly :-) Seriously, index tuning isn't that bad - I just had a moment of weakness. You need to make sure you have the right indexes and no completely unused indexes otherwise you're wasting resources maintaining them. You could do worse than listen to her on RunAs Radio Interview Posted - "Kim Tripp Indexes Everything".

Statistics: If the statistics are out-of-date, the optimizer will not be able to pick a good plan and your performance will suck. It's that simple. Turn on AUTO_UPDATE_STATISTICS and make statistics updating is part of your regular maintenance. Don't forget to update non-index statistics too.

Application (design and code): Biggie #2. Sometimes no amount of cleverness can wring good performance from the twisted logic of a deranged application programmer. An application that is written with absolutely no concern for how SQL Server works is likely to not perform well. An application that is written with too much knowledge of how SQL Server works is likely to fall foul of relying on 'accidental' behaviors, or behaviors that are limited to certain data volumes or workloads.  An application that is going to make heavy use of SQL server has to take into account how SQL Server is going to behave under a variety of workloads, on a variety of servers, and on a variety of I/O subsystems. The common application test framework? A ten-row customer table with a single connection. 'Excellent - my 16-table join with CLR-based aggregations runs in less than 2 seconds'. For 10 rows. Six months later: 'Now we've got 3 million customers, why does performance suck so bad?' Go figure.

Database maintenance: Ok - starting to get tired now and I still need to do this week's survey. Maybe I could just say 'do maintenance' and be done with it? No? Ok - how about go checkout the article I wrote last August for TechNet Magazine: Effective Database Maintenance article. If you don't maintain your indexes, they'll likely get fragmented and affect performance in one way or another and you'll need to play with FILLFACTOR (see Kimberly's Database Maintenance Best Practices Part II – Setting FILLFACTOR). If you don't take care of transaction log VLF fragmentation, it will affect log-based operations (Kimberly again at Transaction Log VLFs - too many or too few?). Seriously - she says I blog so much - how many links are in this post to her stuff? She blogs a lot too!. If you have corruption, it could manifest itself as long-running I/Os. If I don't put my food on to cook, then I'll be eating way too late again. That was a 'Paul maintenance' one that slipped in - and can have disastrous effects on blogging performance. Seriously, you can't just put a database into production and walk away. It will slowly degrade over time. Like red jello melting on a hot summer's day, but probably not as fast, unless you use random GUID primary keys, or it's really hot. What?!?

Performance tuning is an art and a science. But there's a huge amount of science behind it before you have to get into the art side of things. I've just scratched the surface here in a blog post that took me more than an hour to write, banging away non-stop as I do. And I don't do the art side of things. I leave that to Kimberly - she just got one of our client's batch jobs from a 72-hour run-time down to a 6.5 hour run-time. They were pretty happy. We can't all be the Goddess of Performance Tuning

Next post - this week's survey!

This week's survey is going to provide some fun debating topics. I'd like to know what the number 1 thing is that YOU go after when performance tuning - what do you think is most important? (Assuming you're not just randomly walking up to a server to do some perf tuning - there's an actual problem).

[Edit: And, as I say below - please no comments about what you picked until the results post next week - it skews the results. I've already had to delete 6 comments and ask for them to be held until next week.]

There is deliberately no 'it depends' answer, no 'combination' answer, and no way to suggest something else. If what you think is most important isn't on the list, don't vote, but I'd like to hear your opinion after the editorial next week.

As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

PS And again, as always, no comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers. I'm very interested in your reasoning, but not until everyone else responds.

In last week's survey I asked you two things, as a precursor to a whitepaper I'm writing for Microsoft

The first question was what is your maximum allowable downtime SLA (either for 24x7 operation or not). See here for the survey. Here are the results as of 5/30/09.

The Other values were all about not having SLAs defined. And I think that's why this survey had a poor response rate - most of you out there don't have defined SLAs.

If you take a standard 365-day year, which means the year has 524160 minutes. Some example maximum allowable downtimes for 24x7 operations:

• 99.999% (a.k.a 'five-nines') = slightly over 5 minutes downtime per year
• 99.99% (a.k.a 'four-nines') = almost 52.5 minutes downtime per year
• 99.9% (a.k.a 'three-nines') = almost 8.75 hours downtime per year
• 99% (a.k.a 'two-nines') = just over 3.5 days of downtime per year
• 98.5% = almost 5.5 days of downtime per year
• 98% = just over a week of downtime per year
• 95% = just over 2.5 weeks of downtime per year

You might be thinking - wow - whoever only has a target of 95% uptime must have a pretty crappy setup BUT it totally depends on the application and business requirements. 95% may be absolutely fine for some companies and utterly devastating to others (imagine the revenue loss if Amazon.com was down for more than 2 weeks...)

On the other end of the spectrum, you might be thinking - wow - 99.999% target is completely unattainable for the majority of businesses out there, BUT again it totally depends on the application and business requirements. This may be attainable for a simple application and database that doesn't generate much transaction log, or for a large, very busy OLTP database that generates lots of log but the company has lots of money to throw at the HA problem and can afford redundant clusters in separate data-centers with fat, dark-fiber links between them.

Defining SLAs, and not just for maximum allowable downtime, is incredibly important. When done properly, defining SLAs shows that the technical staff in a company and the business management in a company are in tune. It shows they have extensively analyzed the business requirements of an application and balanced them against the technical, space, power, HVAC, manpower, budgetary, and other limitations which may prevent a higher percentage being set as the target. It provides a meaningful input into the design and architecture of systems, and the choices of technologies (note the plural) required to achieve the target SLAs. It shows the company shareholders that the business managers understand the criticality of applications involved in running the business and that they are taking steps to safeguard the shareholders' equity interests.

Note I said 'when done properly'. Technical staff picking SLAs without business input, or vice-versa is a recipe for disaster. If the technical staff don't understand the business requirements, how can they pick appropriate SLAs? If the business managers don't understand the technical limitations, how can they pick achievable SLAs? Everyone has to be involved. And one of the most important things to consider is whether the SLA covers 24x7 operation, or just the time that the application has to be available, if not 24x7.

How to go about using the SLAs to pick appropriate technologies is one of the things I'll be going into in the whitepaper (due for publication around the end of September).

The second questions was what was your measured uptime (again, either for 24x7 operation or not) over the last year. See here for the survey. Here are the results as of 5/30/09.

The Other values for 24x7 were 2 x '96', and 1 x 'it varies across customers'. The Other value for non-24x7 was '92'.

Of course, this would be a little more meaningful if we could correlate target downtime with actual downtime achieved, but the free survey site doesn't reach that level of sophistication (and I don't think people have enough time to get into that much detail) while casually reading a blog post.

Nevertheless, the results are interesting, although not really statistically valid from this small a sample-size. The most interesting data point is that some respondents don't know what they achieved last year, or just didn't measure. I think this is the case for the majority of readers. Having well-defined and appropriate SLAs is the key to defining a workable strategy, and what's the point of defining the SLAs if you don't measure how well you did against them? If you don't meet the target you need to revisit the strategy, the SLAs, or maybe even both.

The fact that many people don't have SLAs speaks to the general poor state of high-availability and disaster-recovery planning in the industry, IMHO. And Kimberly just chimed in stating that it also shows how the PC-based server market doesn't focus anywhere near as much on regimented policies and procedures as the older, more traditional, mainframe market did/does.

There's a lot of work to be done. Many times this stuff just isn't high up in the priority list until a disaster actually happens. Invariably it then becomes very, very important. Get ahead of the curve and be proactive - one of the things we always tell our clients.

Next post - this week's survey!

This week's survey is inspired by this morning's Kimberly+Paul hot-tub conversation around data-dependent routing vs. network load balancing, which then turned to SLAs. Yes, we lead an exciting life .

This survey is a *four*-parter. Part 1 is for each of your databases, which survey option is closest to your target maximum allowable downtime SLA (Service-Level Agreement, a.k.a. RTO - Recovery Time Objective)?

• Survey 1 is if your SLA is based on 24x7 operation
• Survey 2 is if your SLA is based on non-24x7 operation (e.g. allowing for a 6 hour daily maintenance window, the rest of the time we must be available 99.9%)

Part 2 is for each of your databases, what can you actually achieve?

• Survey 3 is your achievement over the last year for 24x7 operation
• Survey 4 is your achievement over the last year for non-24x7 operation

I'll report on the result sometime during the first week of June - as this is a big survey I'd like to get lots of results. Please repost/Tweet/whatever.

What do I mean by that downtime SLA? Well, however you have it defined. It could be that your SLA is an absolute value based on 24x7 operation, or it could be based on the 9-5 work-day. In all cases, what percentage of the defined time does the system have to be available? Note that I'm calling out a 5, 4, and 3 nines of 24x7 as separate answers as I'd like to see how often these occur and can be met.

As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact button, bottom left of the blog), or ping me on Twitter (@PaulRandal) if you have an idea for a good survey.

PS No comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers.

In last week's survey I asked whether you're ever tested your disaster recovery plan, and if so, what happened? (See here for the survey). Here are the results as of 5/25/09:

The 'other' responses are:

• 2 x "restored to test env regularly. don't know if sla would be met."
• 2 x "test it regularly, most goes according to plan"
• 2 x "Test it regularly, people screw up. Was a great win when I obtained the budget for this activity."
• 2 x "test it regulary and learn new things every time but overall it works"
• 2 x "we have annual drp test company-wide"
• 1 x "We have lots of DR plans, some tested, some not."
• 1 x "We test the dr failover with mirror.however chain replications (subscriber becomes pulisher)we can't"

A good mixture of results, but only around 25% of respondents test it regularly. Rather depressingly, 35% of respondents either don't have a DR plan or have one but have never tested it. Given the stories I see almost every day on the various forums, this doesn't surprise me - but it's still depressing nevertheless.

The term 'disaster recovery' means different things to different people. The word 'disaster' in my mind spans everything that could go wrong and affect whether your system and data is online, available, and performing to spec. The word 'recovery' means any process that allows you to bring your system and data back online, available, and performing to spec. Your disaster recovery plan could be as simple as restoring from the last full database backup, or as complicated as failing over all processing to a remote data center and engaging a 3rd-party company to distribute new DNS routing entries across the Internet. It, of course, depends (I have to work that into every editorial ). Lots of people interchange DR and high-availability (HA), but HA is really a set of technologies that you implement to help protect against disasters causing problems. For instance, you might implement database mirroring so that part of the DR plan for a database is to failover to the mirror, keeping the database highly-available, while DR happens on the old principal. Or you might implement auto-grow on the transaction log file so that if it runs out of space the database doesn't become unusable. Neither of these are doing DR, they're preventing a disaster from affecting availability. DR in the second case would be what you do to provision more space for the log so the database can come online again.

Now, this editorial's not going to be about putting together your disaster recovery plan - that's an entire book in itself, as there are many techniques depending on the disaster and the resources you have available to facilitate recovery. If you don't have a disaster recovery plan (which I'm going to start calling DR plan), then you should *know* that come disaster time, you're risking whoever's on duty floundering around making mistakes and potentially leading to more downtime and data-loss than if you had a plan to follow. People panic in times of high-stress and crisis, and without a set of steps to follow, bad things happen. Enough said. You know who you are - go get a DR plan before a disaster happens and you lose time, data, your job, or all of the above. I see them all happen regularly.

Once you have a DR plan in place, the ONLY way to know whether you're going to be able to recover from a variety of disasters is to simulate some, in production. Yes, this is far easier said than done - persuading your business owners to take planned downtime (and possibly lose a bit of revenue) can be a hard argument to make, but unless you do, you can't know that your DR plan will work. One argument I've found effective is wouldn't you rather have all the various admins and DBAs on-site and expecting the test and things to potentially go wrong, than wait until a real disaster occurs in the middle of the night and THEN find out that the DR plan doesn't work and everyone has to scramble when they're least expecting it? Of course, business owners often aren't interested in low-probability potential problems. DR and HA aren't sexy topics UNTIL the company experiences a disaster. Then it's likely to be the top thing on the CEO's mind and you have to have a DR plan in production by Tuesday.

Seriously though, if you're responsible for your system meeting certain SLAs (downtime and data loss - a.k.a. RTO and RPO) then your DR plan actually has to work, no matter how carefully you've designed it. This means you have to try restoring from your backups and seeing if you can do it within your downtime SLA. What about if you have to setup a new server first? What about if there's no power in your building? What if your off-site backups are 200 miles off-site and the network link is down? What if none of your backups work? What if your differential backups are bad, do you still have all the log backups? How does that affect your recovery time? And so on and so on. You might think I'm just making stuff up and these things don't happen, but they do, and everything I'm citing as an example has happened to a customer I've personally been involved with while at Microsoft or since then. And they keep happening over and over again to different people.

If I had to list the most common reasons I see why disaster recovery fails, they are:

• There are no backups, meaning recovery = data loss
• Backups don't work or all contain the corruption, meaning recovery = data loss
• The data volume has increased since last DR test, meaning recovery time exceeds downtime SLA
• The initial failover when the disaster happens doesn't work because the failover site only has part of the application ecosystem, meaning recovery involves getting the application working on the failover site AND then recovering from the disaster on the main site

Doing an initial test when the DR plan is first produced is great, because at least you know that it works, or there are some things you've missed (which is almost invariably the case). The DR (and HA plans) should be written by the most experienced DBAs, as they're the ones who've "seen it all" and have a good idea of what could go wrong at any point during the recovery. And the plans should be tested by the most junior DBAs, as you can bet that if a disaster occurs at 2am on Thanksgiving morning, it won't be the most senior DBA who's on duty.

Doing a regular test is critical because things change. Data volume increases. Databases get added into the mix. Personnel change. SLAs change. And after a change, if you don't test regularly, then you won't know if your DR plan still works until you have a real disaster. If you can push for a DR plan test and everything works, everyone has increased peace of mind. If you can push for a DR plan test and things go south, you'll be praised for having exposed the problems. But if you wait, and things go south, no-one likes being responsible for unnecessary downtime or data loss - and that doesn't look good on a resume.

Next post - this week's survey!

This week's survey is inspired from many stories I saw on the forums and Twitter this week - mostly bad, one good (someone I'm following is spending the weekend testing their disaster recovery plan - cool!). I'd like to know whether you're ever tested your disaster recovery plan, and if so, what happened? I'll report on the results sometime of the weekend of 5/24/09.

And what do I mean by disaster recovery? The definition varies from just restoring a backup to conducting a full failover of a datacenter - but I'd like you to decide for yourself. I'll editorialize more about this next week.

As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact button, bottom left of the blog) if you have an idea for a good survey.

PS No comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers.

PPS Probably a lot less blogging/Twitter (@PaulRandal) this coming week - I'm teaching all 5 days.

Last week's survey was another two-fold one - when you buy new servers, what architecture to you predominantly buy, and why?; when you buy new servers, which Edition of SQL Server do you predominantly buy, and why? Here are the results as of 5/17/2009.

For the first survey, the 'other' values were basically that 64-bit is purchased only if required. For the second survey, nine of the 'other' values were basically that Enterprise Edition is purchased only if required, one person uses Web Edition, and one person uses Developer Edition as they're just a developer.

I don't have a huge amount to say about these two surveys, I really just wanted to confirm my gut feel (and give you all a view of what's happening out there in the field).

For the architecture survey, I was entirely unsurprised to see that almost 80% of respondents are using 64-bit. It's widely known that 64-bit can give you better performance through the availability of more memory for SQL Server to use. Although you can use more than 4GB using AWE on 32-bit, that extra memory is only available for the buffer pool to use - not for general query processing - and AWE access does incur a little overhead. Much has been written about this and I'm not going to duplicate it here. A selection of articles can easily be found at http://www.google.com/search?hl=en&q=sql+server+64+bit+vs+32+bit&aq=1&oq=sql+server+64  More interesting is the small number of respondents who are not able to use 64-bit, either because their corporate policy is 32-bit (maybe because of cost?), they have software that doesn't run on 64-bit systems, or specifically because 64-bit is too expensive.

A few people only use 64-bit if required, and (I imagine) prefer to save money if it not by sticking with 32-bit. I wonder how long it will be until 32-bit servers are not available at all? Certainly future versions of Microsoft server software are starting to become 64-bit only - for instance Exchange 2010 (see here) and SharePoint Server 2010 (see here), will the next version of SQL Server be 64-bit only?

For the Edition survey, again, unsurprising results. 55% use Enterprise Edition for one or a combination of the various "-abilities" (if you make performancability a new word), with almost 10% more having the option to use it if required, and 16% having to stay with Standard because of budgetary constraints. What you may be surprised to see (but I wasn't from my time working side-by-side with the SQL marketing team) is that 20% of respondents don't need Enterprise Edition. There were a lot of improvements to the database engine in SQL Server 2000 and SQL Server 2005 that meant that for intermediate workloads (hmm - just made that up, but you know what I'm trying to say), Enterprise Edition isn't needed. And with synchronous database mirroring available in Standard Edition, you can implement a great, low-cost high-availability plan without paying for Enterprise. As you can see, I don't get any kickbacks from the SQL team for selling Enterprise Edition  Saying that, however, there are a lot of very cool features in Enterprise Edition in the various versions - but if you don't *need* them, why pay for the higher Edition just for the sake of it?

One thing I will say to summarize, it's important to look at your requirements in terms of performance, availability, etc to make the right choice of server architecture and SQL Server Edition. If you make the wrong choice, do you think your company will pay to rectify your mistake before the next round of capital-expenditure? Probably not - but you'll definitely pay for the wrong choice with several years worth of hassle trying to make the system be more performant and easily recoverable than your choices allow.

As always, thanks for participating in the surveys - I've had a bunch of mail from people who like to see what other people are doing.

Next up - this week's survey!

This week's survey is another two-parter, part 1 of which suggested by Mike Walsh on Twitter:

1. When you buy new servers, what archictecture do you predominantly buy, and why?
2. When you buy new servers, which Edition of SQL Server do you predominantly buy, and why?

I know the surveys below don't have a 'whatever suits the project' (thanks Dave Dustin), if that's the high-order bit for you, please use the Other option and I'll collate those results manually.

I'll report on the result some time over the weekend of 5/16/09.

As always, a big Thanks! for contributing to the blog by responding. Please shoot me an email (Contact button, bottom left of the blog) if you have an idea for a good survey.

PS No comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers.

Last week's survey was two-fold - what's the largest SQL Server database you manage, and how many SQL Server databases are you responsible for managing. Here are the results as of 5/10/2009.

As far as the database sizes are concerned, the distribution curve isn't surprising, but the average size of databases *is*. 70% of respondents have a database over 100GB, with half of those being 500GB or more. One lucky (or unlucky, depending on how you look at it!) person gets to play with a 20+TB database. There some truly huge SQL Server databases out there - for instance the 1.1 petabyte astronomical database described here, which adds 1.4TB every night. Back in December 2003, the largest SQL Server database was a 5TB Verizon database. Now multi-TB databases seem quite common when talking to large customers.

The distribution curve for the number of databases also seems unsurprising, except for the uptick at the 150+ databases mark. Fully 1/4 of respondents are responsible for more than 200 databases! Even if the majority of the databases are small, that's still a huge amount of management to consider and context to have to know about.

So what was the point of this survey? Well, I've heard from quite a few people this year that some of their DBA colleagues have been let go and they've had to pick up more responsibility, whether they want to or not. Just last week, Microsoft laid off 3000 more people worldwide, including some in their various IT departments. In the DBA class we were teaching last week, it was obvious that some of the attendees were affected and had a lot more on their plates.

With the average size of databases growing, and the number of databases any one person is responsible for also growing, it's imperative that the overall environment is as easily managed as possible. What does this mean? Here's a 2-minute list of stuff off the top of my head that I think helps make an environment more manageable:

• Setting up each database so that the important parts can be recovered as quickly and easily as possible
• Setting up each database so that there are no performance bottlenecks (these two were both kind of addressed by my previous survey Physical database layout vs. database size)
• Making sure each database has the right backup strategy (from last week's survey Importance of having the right backups)
• Making sure that each database has the correct monitoring set up. For instance:
  • SQL Agent alerts on the instance for high-severity errors (see Easy monitoring of high-severity errors: create Agent alerts)
  • Monitoring data and log file sizes to avoid auto-growths
  • Monitoring index fragmentation levels
  • Pro-active monitoring for things like high disk-queue lengths or runaway queries
  • Regular consistency checks to find corruption
• Security is setup appropriately (see TechNet Magazine: feature article on Common SQL Server Security Issues and Solutions)
• Maintenance jobs are automated as much possible (e.g. backups, consistency checks, index maintenance, and statistics maintenance). There are a bunch of scripts people have published to help with this: Tara Kizer has a lot on her blog, Ola Hallengren has a good one here, as do many others (no offense to anyone else with scripts, these are just the two that spring to mind - by all means reply with a comment to let me know of your popular script)
• Database settings adhere to common best-practices (e.g. auto-shrink off, auto-grow on (and managed!), auto-create and auto-update statistics on, page checksums on)

And I'm barely touching the tip of the iceberg here. Most of these are just database maintenance best practices - with the idea that the healthier a database is, the less likely something will go wrong that demands DBA attention. And in an environment where potentially hundreds of databases are under the control of a single person, avoiding problems is of paramount importance.

Ok - cup of coffee later and I have more stuff to add to the list:

• Consistency of settings and scripts across database and instances, so what you're familiar with in one database context translates to all others
• Environment-wide run-book that covers all aspects of day-to-day operations, so all DBAs do the same thing and a DBA can help out or pick up databases easily
• Environment-wide disaster recovery guide - written by the most senior DBA and tested by all DBAs down to the most junior
• T-SQL source code control
• Management of access to databases
  • Physical access to servers is limited
  • Network access to servers is limited
  • SA access is limited
  • Developers can't deploy new code without going through QA first
  • And so on
• High-availability technologies in place to aid recoverability, with plenty of practice of failing over applications
• Use of tools to increase productivity. Most major 3rd-party tool vendors have tools that can help in a myriad of DBA activities - I'm not going to name any in particular as I don't want to favor any over any of the others.

Talking about tools, SQL Server 2008 itself has some stuff built in that can increase productivity. Kimberly did a great post last November on Central Management Servers - see SQL Server 2008 Central Management Servers - have you seen these?. There's also Policy-Based Management, which is a good first step along the road to centralized policy setting, testing, and enforcement (although it has some nits that need to be fixed in v2), and Performance Data Collection which can be used to easily collect perf data (same kind of stuff that Activity Monitor has) for multiple instances and stored in a single location - Kimberly will be blogging more about these two features in the future.

Overall, having a haphazard way of setting up and maintaining databases and instances is going to make it really hard to scale up size and number of databases in an environment without investing heavily in more people. Taking a more disciplined, planned approach, learning from others - using others' scripts and practices, and developing streamlined processes will lead to increased productivity, decreased stress, and happier, stronger DBAs. No-one wants to be in constant fire-fighting mode.

This has been a bit of a ramble, but that's the point of these editorial-style posts. I'm sure you know a lot of this, and I'm sure I've missed some obvious points, so feel free to comment with more tips, tricks, and links!

Next up - this week's survey!

Jason Massie posted an interesting statistic yesterday - Facebook has 1.5 petabytes of image storage, and it grows by 25TB daily - I wonder how they store and manage it?

In this week's survey, I'm interested in two things: what's the largest single SQL Server database in your company and how many SQL Server databases are you responsible for managing? I'll report on the results sometimes over the weekend of 5/9/09.

A note on the second survey: if you manage a team of DBAs, but don't manage databases directly yourself, answer for your individual DBAs, rather than the team as a whole.

Thanks!

PS No comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers.

Last week's survey was on what kind of backups you take, along with the recovery model used (see here for the survey). Here are the results as of 5/2/2009.

The 'other' responses were combinations of the other answers.

This survey is a bit of a pre-cursor to my article on Understanding SQL Server Backups that will be in the July TechNet Magazine (available start of June). In that article I explain how the three major backup types work, and then how to combine them into an effective backup strategy, so I don't want to steal all my own thunder.  In the spirit of my weekly surveys, this is a 20-minute, brain-dump editorial, rather than a very carefully planned out article.

When I'm teaching classes on Database Maintenance or High-Availability, I always tell people not to plan a backup strategy; plan a restore strategy. You don't want to come up against a disaster recovery situation and find that the only backup you have of your multi TB 24x7 busy OLTP database is a full from several weeks ago. Kiss your job goodbye on that one. You have to make sure that you have backups that allow you to recover as quickly as possible and with the minimum of data loss.

So, you're probably taking backups so that you can restore when disaster strikes. Some of you may also be taking backups because:

1. You periodically restore the database onto a reporting server, onto a data warehouse server, or onto test/dev servers
2. You've got log shipping implemented (backup, copy, restore; backup, copy, restore;...)
3. You've got database mirroring implemented (and so you must use the FULL recovery model, and thus must take log backups to manage the transaction log)
4. You're in the FULL recovery model, even though you're not interested in up-to-the-minute or point-in-time recovery, and must take backups to manage the size of the transaction log

The survey included what recovery model you're running in too - basically SIMPLE or FULL. I don't know anyone that runs all the time in the BULK_LOGGED recovery model; and most 24x7 systems cannot switch into BULK_LOGGED because of the possibility of crashing and not being able to take a tail-of-the-log backup, if a minimally-logged operation has occurred since the last log backup.

In the SIMPLE recovery model, you cannot take log backups - so you're basically saying that you don't need to be able to do up-to-the-minute recovery, point-in-time recovery, or single-page restores. That's cool - it totally depends on your situation, and is you're running in SIMPLE it means you understand that there's no point running in FULL and having to take log backups if you're not interested in using them. If you're in the group covered by #4 above, switch to SIMPLE!

In the FULL recovery model, you have to take log backups - plain and simple - otherwise the log will grow out of control and eventually your database will grind to a halt when the log runs out of space. I've done a few blog posts about that so I won't labor the point (see Search Engine Q&A #1: Running out of transaction log space, Search Engine Q&A #23: My transaction log is full - now what?, and Importance of proper transaction log size management).

In either recovery model, there's the question of just how often should the various kinds of (I like to think of) 'mandatory' backups be taken, and whether to use the 'optional' differential backups.  Again, not stealing my own thunder from TechNet Magazine: a full backup is a copy of all the data in the database; a log backup is all the transaction log generated since the last log backup (or first full/differential following a break in the log backup chain); a differential backup is all data that has changed since the last *full* backup.

You need to take regular full backups - but just how regular depends. If the database is very large, and you need to keep your backups around for regulatory purposes, you might choose to take a full backup every couple of weeks or a month, with compression. Commonly I see people taking a full backup once a week.

Almost 20% of respondents are in SIMPLE and only take full backups - I wonder how many realize that a full backup only gives you a single point-in-time to which you can recover - and you lose all work since the last full backup. In the SIMPLE recovery model, you can't take log backups, so if you want to reduce the amount of data loss when a disaster occurs, you'll need to take differentials too, which only a handful of respondents do. Although this still means you'll have some data loss, it's vastly reduced. The amount of potential data loss will be the amount of work since your last (e.g. daily) differential backup, rather than since your last (e.g. weekly) full backup.

In the FULL recovery model, only a few % of respondents are NOT taking log backups - which means they shouldn't be in the FULL recovery model, or are being forced to (e.g. from using mirroring) and don't realize that having a redundant copy of the database isn't a good enough HA strategy - you have to have backups too in case of a secondary failure.

The vast majority of respondents use the FULL recovery model taking full and log backups (45% - which is what I expected) and about 25% are also taking differentials too. This is a more advanced strategy and can seriously REDUCE your downtime in the event of a disaster. A differential backup basically short-circuits the need to restore all the log backups that were taken in the time between the last full backup and that differential backup. You'll find out more on this in the article. For the ultimate in flexibility and fast recovery, this is the way to go - but at the cost of a little more complicated backup strategy, and extra storage space for the differential backups.

Now, what about the exotic answers at the end of the survey?

• OS-level backups: this isn't a popular solution *at all* because of the complexity of getting the SQL data back out from the OS-level backup and recovered. I'm not surprised that no-one selected this.
• SAN-level backups: I've seen a few customers do this, with mixed results. You must make sure that the SAN admin knows what they're doing - I heard of one customer who had data and log files on different LUNs. The SAN admin snapped the data LUN, and *then* the log LUN. Every so often, corruption would occur...
• Shutdown SQL and copy the files: Just don't even think about this. Taking downtime to take a backup is daft, and what if the database is corrupt and won't re-attach?
• Don't take backups: No need to discuss this one.

Ok - that's a quick blast that should give you some idea of why you'd want to make sure you've got the *right* backups, not just any old backup strategy.

Next post - this week's survey!

(PS I'm really enjoying being on Twitter - lots on interesting stuff. See me at http://twitter.com/PaulRandal)

In this week's survey I'm interested in what kind of backups you take, and also what recovery model you mostly use. If you have multiple database with different strategies, by all means respond multiple times. The more responses the better! I'll report on the survey results some time over the weekend of 5/2/2009.

A couple of notes on the survey:

• If you're using BULK_LOGGED at all, just answer as FULL.
• When I say full backup, I mean either full database, full filegroup, or full file backup. Same for differential.
• I don't care whether you use a tool outside SQL Server to take your SQL backups (e.g. LiteSpeed) - it's the type of backup that's interesting.

Thanks!

PS No comments on this post please (I'll delete them straight away) - please wait for the survey results post to avoid skewing the answers.

A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are the results as of 4/27/2009.

Just like any other 'best practice' kind of topic, the question of how to design the physical layout of a database provokes a lot of (sometimes heated) discussion. There are lots of options and there are even more factors to consider - so the best answer is my perennial favorite "it depends"! In this post, I don't want to tell you how I think you should layout your database - instead I want to discuss some of the options and let you make up your own mind, with the added benefit of data on what your peers are doing with their databases. The main point of this survey was to see what people are doing, rather than as a driver for an editorial blog post.

As you can clearly see from the results above, and predictably, the distribution of layout types shifts as the database size increases - but I was very surprised by the number of single file databases over 10GB. Rather than go through each option in the survey, I'm going to talk a bit about some of the things to consider when planning a layout.

Underlying I/O subsystem

This could be the most important factor to consider. If you only have a single physical drive, for instance, there's arguably not much point creating multiple data files, as that will force the disk heads to bounce back and forth to the different file locations on the disk. On the other hand, if you have a SAN with several thousand drives grouped together into multiple LUNs, your possibilities are a lot wider (and maybe much harder to come up with the optimal layout). Several people asked if I'd go into depth around having multiple controllers, and different drive layouts in a SAN - and my answer is no. I'm not an expert at storage design, which, like indexing, is both an art and a science. There's a good whitepaper that discusses some of this: Physical Database Storage Design, which I helped review back in my MS days.

Performance, recoverability, manageability

Having multiple files with separate storage for each allows reads and writes to be parallelized for increased performance, lowering the amount of disk head contention. When a checkpoint occurs, and pages are written to disk, spreading the I/O load over multiples files can speed up the checkpoint and reduce the IOPS spikes that you may see. It can also lead to reduced contention for the various allocation bitmaps - in the same way as I've described for tempdb. In user databases with a very high rate of allocations, contention can arise on the GAM pages - but it's not common. Some people also advocate having separate filegroups for tables and indexes, and although this can sometimes be more trouble than it's worth, and often turns into a religious debate, I have heard of people getting a perf boost from this.

One of the most convincing reasons (I find) for having multiple filegroups is the ability to do much more targeted recovery. With a single file database, if it gets corrupted or lost, you have to restore the whole database, no matter how large the file is - and this can seriously affect your ability to recover within the RTO (Recovery Time Objective) agreement. By splitting the database into multiple filegroups, you can make use of partial database availability and online piecemeal restores (in Enterprise Edition) to allow the database to be online as soon as the primary filegroup is online, and then restore the remaining filegroups in priority order - bringing the application online as soon as the relevant filegroups are online. You can even use this layout to spread your backup workload - moving to filegroup-based backups instead of database backups, although this isn't very common.

As far as manageability is concerned, there are a few reasons to have multiple filegroups. Firstly, you can isolate a table that requires a lot of I/O (e.g. in terms of index maintenance) on separate storage from other tables, so that maintenance operations (and the I/O overhead of doing them) doesn't interfere with the I/O of the other tables. Also, you can provision different kinds of storage for different tables - in terms of disk speed and RAID level (redundancy), for instance. If you want to be able to move data around, you can do it much more easily if the database is split up, than if it's a single file.

Summary 

Ok - so I lied. I *am* going to offer advice - against one of the options: single filegroup, single file. For smaller databases, this is fine - but as the database size gets larger, say, over tens of GB, then having a single file can become a serious liability. With a single file database (or even a single filegroup database), you lose most of the benefits mentioned above.

Bottom line - as your databases get larger, you're going to need to think more carefully about their layout, otherwise you could run into big problems as your workload increases or when disaster strikes. As the survey results show, this is what your peers are doing.

Next post - this week's survey!