Things to consider when setting up database mirroring

Over the last week or so I've been helping the Product Support folks at Microsoft write a new KB article which discusses some of the things you should take into consideration when setting up database mirroring.

It discusses memory, CPU, I/O bandwidth, network bandwidth, and transaction volume. One of the most interesting things it discusses is exactly how many worker threads are required on the principal and mirror for each mirrored database.

Check it out at: http://support.microsoft.com/kb/2001270 (the formatting looks a little weird in the final online version, assuming they'll fix that up).

Enjoy!

Penguins, cockatoos, and other sights in Melbourne

Fall really is crazy travel season for us – a week after returning from our trip to Ireland and Poland and now we're in Australia for a few weeks. This evening we went for a bird-watching walk around Albert Park with our good friend, partner, and fellow MVP Greg Linwood, who's our host while we're here. We saw 20+ species of birds (of which 17 were new to me), including 3 species of cockatoo and, finally, my first glimpse of wild PENGUINS!

I've adored penguins for a very long time and it's always been a dream of mine to see them in the wild rather than a zoo. Well, today another bucket-list item was ticked off. There's a little-known colony of Little Penguins right in Melbourne itself, at the end of St. Kilda Pier (here's a website with some info).

I've uploaded a bunch of photos to Facebook, that you can view at http://www.facebook.com/album.php?aid=2040551&id=1293146061&l=f6e55ca540 (no account needed).

Here are a few examples (Eastern Rosella, Sulphur-Crested Cockatoo, Little Penguin).

 

How excellent – penguins!!! :-)

Which index will SQL Server use to count all rows

This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable?

The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I/O cost.

Let me quickly show you. First I'll create a simple table with no indexes.

CREATE TABLE CTest (c1 INT IDENTITY, c2 BIGINT DEFAULT 1, c3 CHAR (1000) DEFAULT 'a');
GO
SET NOCOUNT ON;
GO
INSERT INTO CTest DEFAULT VALUES;
GO 10000

Don't forget to unclick the Include Actual Query Plan button

 

before running the 10000 inserts otherwise it will take forever (generating 10000 graphical plans) and SSMS will barf with:

The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane.

Now if we do the SELECT COUNT (*), the plan is:

 

The query processor has no choice but to do a table scan.

Now I'll add a nonclustered index that will have less pages that the table itself:

CREATE NONCLUSTERED INDEX CTest_1 ON CTest (c2);
GO

And the select plan is now:

 

Notice that the Table Scan operator has changed to an Index Scan operator on the new CTest_1 index. This is because the nonclustered index has fewer pages than the table and so the I/O cost is lower.

Now I'll create an index that's even smaller, over the integer column:

CREATE NONCLUSTERED INDEX CTest_2 ON CTest (c1);
GO

And the plan should change to use the smallest index again:

 

And it does, as I expected.

Let's look at the relative page counts for each index and the table:

SELECT [index_id], [page_count]
FROM sys.dm_db_index_physical_stats (DB_ID (), OBJECT_ID ('CTest'), NULL, NULL, 'LIMITED');
GO

index_id    page_count
———– ——————–
0           1436
2           28
3           19

Any time the query processor is working out what plan to use, one of the key factors in plan choice is the I/O cost.

Hope this helps!