Mitch Wheat - Treat the cause, not the symptoms!

Windows 7 Bins

2012-02-01T13:43:00.001+08:00

I love it when Scott Hanselman updates his Ultimate Developer and Power Users Tool List for Windows. I saw Bins and immediately went and purchased it ($4.99 PayPal). For each program pinned to the taskbar, you can create a group and put four shortcuts in the space of just one regular pinned icon. You can even choose which is the default click program, or hover to see all the programs in the group.

[The very same author created the very useful (and free) Fences)]

97 Things Every Programmer Should Know

2012-01-30T18:41:00.001+08:00

I happened to be on StackOverflow perusing a few questions (not that I spend much time there, ahem!), and I came across this great List of freely available programming books, which includes 97 Things Every Programmer Should Know

A few of my favorites are:

Ask "What Would the User Do?" (You Are not the User)
Code Reviews
Comment Only What the Code Cannot Say (I have actually seen a ‘programmer’ write i = i + 1 // increment i by 1, no one I currently work with, I hasten to add!)
Improve Code by Removing It
Use the Right Algorithm and Data Structure (if code has quadratic complexity, it will not scale: BubbleSort springs to mind!)
Write Code as If You Had to Support It for the Rest of Your Life

Perth .NET User Group Meeting, Thurs Feb 2nd, 5:30pm: Visual Studio and Team Foundation Server vNext Preview with Anthony Borton

2012-01-19T09:07:00.001+08:00

Join us at the Perth .NET user group, Thurs Feb 2nd 5:30pm, where you’ll see what’s coming up in the next version of Visual Studio and Team Foundation Server. Anthony will demonstrate features from the Developer Preview versions of both Visual Studio and Team Foundation Server and whet your appetite for the Beta releases expected in February. Some of the things you’ll see include the new team explorer, code review workflow, exploratory testing, local workspaces and much, much more.

TOPIC: Visual Studio and Team Foundation Server vNext Preview: Anthony Borton
DATE: Thurs Feb 2nd, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Anthony Borton is the lead ALM consultant for Enhance ALM Pty Ltd, an Australian consulting and training company specializing in Application Lifecycle Management and Microsoft Visual Studio. He has been working with Visual Studio Team System full-time since 2005 and has worked with a variety of companies ranging up to some of Australia’s largest companies and financial institutions. Anthony is a sought after trainer and has delivered technical training and consulting in the United States and all across the Asia Pacific region. He is a Microsoft MVP (Visual Studio ALM), a Professional Scrum Developer Trainer and a Microsoft Certified Trainer. Anthony also runs the QLD ALM Users Group and the Canberra ALM Users Group.

In addition to the usual JetBrains license raffle prize, Anthony has arranged to provide everyone that attends our February user group meeting with an invitation code for the preview of Microsoft’s Hosted TFS offering.

Windows Azure Training Kit December 2011 Refresh

2012-01-16T11:04:00.005+08:00

The Windows Azure Training Kit includes a comprehensive set of technical content including hands-on labs, presentations, and demos that are designed to help you learn how to use Windows Azure.

The December 2011 refresh of the training kit includes the following updates:

33 updated/new PowerPoint presentations
[New Hands-On lab] Running a parametric sweep application with the Windows Azure HPC Scheduler
[New Hands-On lab] Running SOA Services with the Windows Azure HPC Scheduler
[New Hands-On lab] Running MPI Applications with the Windows Azure HPC Scheduler
[New Demo] Node.js On Windows Azure
[New Demo] Service Bus Relay
[New Demo] Service Bus Queues
[New Demo] Image Rendering Parametric Sweep Application with the Windows Azure HPC Scheduler
[New Demo] BLAST Parametric Sweep Application with the Windows Azure HPC Scheduler
[Renamed Lab] Connecting Applications through Windows Azure Service Bus (formerly Introduction to Service Bus Part 1)
[Renamed Lab] Windows Azure Service Bus Advanced Configurations (formerly Introduction to Service Bus Part 2)

To coincide with the new Azure web site, Scott Guthrie's talk is worth watching: Keynote: Getting Started with Windows Azure

Non-Uniform Distribution from a Uniform Distribution

2012-01-08T11:36:00.001+08:00

Given a uniformly distributed random variable x (such as the output from a standard Random Number Generator), how do we generate some other non-uniform distribution, q(y)?

Solution: Generate x, and take y = f(x). But how do we calculate f() given q() ?

The required function is the inverse cumulative distribution function given by:

Example: Exponential distribution (to simulate the lifetimes of radioactive nuclei)

, (τ≥0,and = 0 otherwise)

So inverse is

Ref.

http://en.wikipedia.org/wiki/Cumulative_distribution_function

http://en.wikipedia.org/wiki/Inverse_transform_sampling

Windows 7: Reclaiming Drive Space

2012-01-07T14:46:00.001+08:00

Note: Everything that follows is performed at your own risk.

If, like me and may others, you installed Windows 7 Service Pack 1 over Windows 7 vanilla, you probably have quite a bit of wasted space on your primary drive (around 1GB). This is obviously not desirable if you have a relatively small SSD as your primary OS drive.

Given that it is extremely unlikely that anyone would ever remove Service Pack 1 and go back to vanilla, you can quite easily reclaim this space (Note: you will no longer be able to uninstall Windows 7 Service Pack 1):

Open an elevated command prompt (i.e. Run as administrator).
Run this command: dism /online /cleanup-image /spsuperceded /hidesp

2009, 2011 Mahler Lectures

2011-12-19T15:03:00.001+08:00

For anyone interested in mathematics/science, the Mahler lectures for 2009 and 2011 are now available as pdf’s.

They vary in complexity, but most have a wide audience such as Terence Tao’s “The Cosmic Distance Ladder”, which I attended in person when held at UWA.

DevJam 2011

2011-12-02T08:19:00.001+08:00

A big thank you to all of the presenters at this year’s DevJam event. It is great to see so many local developers willing to give up their time to present a talk. It was a great turnout: 51 people attended last night’s event, and I counted 5 regular attendees who couldn’t make it.

It was a great line-up of topics with a nice mixture of content. Every talk received audience votes, but we have to have some winners in order to give out the prizes! The audience voted favourite talks (in order) were:

Mike Minutillo - Crankpad
Piers Williams - Kinect SDK
Jake Ginnivan - Real World MVVM

Thank you to all the sponsors who help make these events possible: Microsoft for the venue, pizza and refreshments, and major prize funding through UGSS, Pluralsight, Scooter Software, JetBrains, Joe Albahari, RedGate, TekPub. Special thanks to Andrew Coates and Roseanne Stamell for their invaluable support.

Thank you to everyone who attended DevJam and regular meetings throughout the year, and thank you to all the talk presenters, who make running a user group possible.

[If you are interested in running one of these events at your User Group, I put together some notes here: Running a DevJam Community Event]

December DevJam – Talk Lineup

2011-11-29T21:11:00.001+08:00

Subject to any last minute changes, here is the line-up of talks for Thursday’s DevJam event :

Doug Paice - Using Reactive Extensions and Caliburn.Micro to simplify event chaining in WPF
Jake Ginnivan - Real World MVVM
Michael Minutillo – Crankpad
Alistair Waddell - Compare and Synchronize your Database with VS2010
Piers Williams - Kinect SDK
Adrian McGrath - Testing Async
Ben Leane - SP2010 Client Object Model

It’s happening at our usual venue:

DATE: Thursday, Dec 1st, 5:30pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

If you are attending, please sign up at EventBrite (http://www.eventbrite.com/event/2402588206) so I can get an idea of catering numbers.

If you have any questions, please send me an email.

http://perthdotnet.org/blogs/events/archive/2011/10/22/december-devjam-2011.aspx

December DevJam 2011

2011-11-15T09:05:00.001+08:00

For December's meeting of the Perth .NET User Group (Thurs, 1st Dec), we are running another DevJam style community event, where local developers give a short talks about something .NET software development related.

DATE: Thursday, Dec 1st, 5:30pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

There are still several talk slots available. For presenter entry details see here: http://perthdotnet.org/blogs/events/archive/2011/10/22/december-devjam-2011.aspx

The prize for the best audience voted talk is an Xbox 360 with Kinect. There will also be a door prize of an Xbox 360 with Kinect!

There will be pizza and refreshments provided. Please sign up at EventBrite (http://www.eventbrite.com/event/2402588206) so I can get an idea of catering numbers. If you have any questions, please send me an email.

Perth .NET User Group Meeting, Thurs Nov 3rd, 5:30pm: Hosting in Azure - without the blues: Joe Albahari

2011-10-23T13:55:00.001+08:00

Had enough of your data centre or web hosting company?

Join us at the Perth .NET user group, Thurs Nov 3rd 5:30pm, and find out how to move your MVC or Web Forms apps into the cloud and enjoy high availability, security and scalability - without the responsibility of OS installation, updates or security. This session will be full of practical advice and lessons learned from Joe’s own experience migrating two web sites and a licensing server into Azure, all of which now run inexpensively on a redundant pair of extra small instances.

TOPIC: Hosting in Azure - without the blues: Joe Albahari
DATE: Thurs Nov 3rd, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Joe Albahari is a C# MVP and author of ‘C# 4.0 in a Nutshell’ and LINQPad. He has presented at TechEd and JAOO and is a regular speaker at the Perth .NET User Group.

More details here: http://perthdotnet.org/blogs/events/archive/2011/10/16/hosting-in-azure-without-the-blues-joe-albahari.aspx

Perth .NET User Group Meeting, Weds 19th Oct, 5:30pm: Amazon Web Services (AWS) in Action with Andy Marks

2011-10-17T08:37:00.001+08:00

Join us at the Perth .NET user group, Weds Oct 19th 5:30pm, where Andy Marks will introduce the philosophy and principles behind AWS and briefly describe some of the key components around storage and compute capabilities. The session will include a short hands-on activity for participants to build and deploy their own cloud-based application.

TOPIC: Amazon Web Services (AWS) in Action with Andy Marks
DATE: Wednesday, Oct 19th, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Andy serves ThoughtWorks principally as the Perth and Melbourne Technical Lead in the design and implementation of enterprise applications. He also has considerable experience in coaching and mentoring development teams adopting Agile practices.

Optional Requirements: Laptop with internet connectivity (for the hands on section of the talk).

Free Flights?

2011-10-10T19:04:00.001+08:00

Japan is offering free flights to boost tourism.

Here’s a wild idea: Maybe they should STOP slaughtering whales and pretending it’s science (which no one believes by the way).

Imagine how much goodwill that would generate.

Perth .NET User Group Meeting, Thurs 6th Oct: C#/Mono with Ben Last

2011-10-03T08:20:00.001+08:00

Join us at the Perth .NET user group, Thurs Oct 6th 5:30pm, where Ben Last will discuss a real world application using C# and Mono. nearmap.com make heavy use of C#, but in a very non-Windows environment. Plenty of people have played with mono as a .NET platform, but there are not that many who have used it in a large-scale, high-traffic, n-tier environment. Hear our story, and find out if mono delivers what it promises.

TOPIC: C#/Mono with Ben Last
DATE: Thursday, Oct 6th, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Ben is a Software Engineer and Architect with more than twenty years' experience in the design and implementation of large-scale software systems. He joined NearMap in April 2009 and is the Development Manager for HyperWeb, which delivers very high volumes of image data via the nearmap.com website and over the Internet. Before joining NearMap, Ben was R&D Manager at EMCC Software in the UK, leading product development teams for mobile enterprise and consumer applications and consulting on mobile and networked software development.

TSQL: An Efficient Dynamic Numbers Table

2011-10-02T13:42:00.001+08:00

A numbers table is simply a table containing sequential integer values. The size of the required table will vary depending upon its use, but it is not uncommon to find permanent tables containing 1,000,000 rows. A numbers (or tally) table forms the basis of solving problems in a set-based way, and is a powerful technique for finding gaps in sequences, string manipulation (which if done purely with TSQL’s built-in functions can run very slowly), etc. It is also often used in the form of a date table, enabling some very efficient set based join constructs to be used on date range calculations.

While it is usual to create a fixed table in the database, there is the occasional need for a dynamic version. There are several ways not to do this! Such as the recursive CTE or a while loop inserting values one after another. A better option is the CROSS join against system tables method, and better still is Itzik’s often cited, cross joined CTE method:

--===== Itzik's CROSS JOINED CTE method 
 WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), 
      E02(N) AS (SELECT 1 FROM E00 a, E00 b), 
      E04(N) AS (SELECT 1 FROM E02 a, E02 b), 
      E08(N) AS (SELECT 1 FROM E04 a, E04 b), 
      E16(N) AS (SELECT 1 FROM E08 a, E08 b), 
      E32(N) AS (SELECT 1 FROM E16 a, E16 b), 
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32) 
SELECT N 
FROM cteTally 
WHERE N <= 1000000; 
GO

After a little experimentation, I found that reducing the number of cross joins by increasing the size of the first table results in a more efficient query plan. I wrapped this up in an inline table valued function with the option of a start and end value for the sequence:

IF OBJECT_ID (N'dbo.tvf_DynamicNumbersTable') IS NOT NULL
   DROP FUNCTION dbo.tvf_DynamicNumbersTable
GO

CREATE function dbo.tvf_DynamicNumbersTable
(
    @pStartValue int = 1,
    @pEndValue   int = 1000000
)
returns table
as
return
(
    with rows16(N) as 
    (
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 union all
        select 1 
    ),
    rows256(N) as 
    (
        select t1.N from rows16 t1 cross join rows16 t2
    ),
    rows65536(N) as 
    (
        select t1.N from rows256 t1 cross join rows256 t2
    ),
    rows2pow32(N) as 
    (
        select t1.N from rows65536 t1 cross join rows65536 t2
    ),
    Numbers(N) as
    (
        select row_number() over (order by N) as N from rows2pow32 t1
    )
    select N from Numbers
    where N between @pStartValue AND @pEndValue
);

Windows 7: Fast and Flexible File Copy with RoboCopy

2011-10-01T09:39:00.001+08:00

Did you know that RoboCopy is included with all editions of Windows 7? I didn’t until very recently, when a colleague (Mike Minutillo) mentioned it.

Robocopy (short for Robust File Copy) can do so much more than the standard Copy and Xcopy commands.

The basic syntax is as follows:

robocopy source destination [file [file]...] [options]

The source and destination parameters are specified as drive:\path or \\server\share\path. The file parameter can contain one or more literal file names, or it can use ? and * wildcards.

For instance, this command copies the contents of one folder and all its subfolders from a local drive E to a shared folder:

robocopy "E:\test" \\server\public\test\ /MIR /W:30 /R:10 /MT:8 /LOG:\\server\public\logs

The /MIR switch mirrors the two folders, copying all folders (even empty ones) from the source directory and removing folders from the destination if they no longer exist on the source. The /W and /R switches set the wait and retry options; in this case, Robocopy will retry each copy up to 10 times, waiting 30 seconds between attempts. The /MT[:n] switch performs a multi-threaded copy, where n indicates the number of threads to be used (n is 8, by default) and can range from 1 to 128. The /LOG: switch logs output to the specified location.

To see the full list of options, type robocopy /? at a command prompt.

There’s even a GUI!

TSQL: Generate Missing Foreign Key Indexes

2011-09-15T08:03:00.001+08:00

It’s good practice to ensure all foreign key columns are indexed (I’ve often wondered why SQL Server Management Studio doesn’t have a built-in option to perform this). I can’t remember where I saw this code originally (despite trying to search for a reference to it), but it is a useful addition to a TSQL code snippet toolbox:

--
-- Create non-clustered indexes for all foreign keys in the current database
--
DECLARE @sql varchar(4000)

CREATE TABLE #sqlfkindexes
(
   id int not null identity(1,1),
   sql varchar(4000) not null
)

INSERT INTO #sqlfkindexes
SELECT 'CREATE NONCLUSTERED INDEX [NC_' + tablename + '_' + columnname +'] ON [' + 
       schemaname + '].[' + tablename + '] ([' + columnname + ']);' as [sql]
FROM 
(    
    -- All foreign key columns
    SELECT        
       o.name AS tablename, 
       col.name AS columnName, 
       sch.name AS schemaname
    FROM
       sys.foreign_key_columns fkc        
       INNER JOIN sys.objects o ON fkc.parent_object_id = o.object_id        
       INNER JOIN sys.columns col ON col.object_id = o.object_id and fkc.parent_column_id = col.column_id    
       INNER JOIN sys.schemas sch ON sch.schema_id = o.schema_id
      
    EXCEPT  
    
    -- All the already indexed columns
    SELECT 
       o.name AS tablename,
       col.name AS columnName, 
       sch.name AS schemaname
    FROM 
       sys.index_columns ixc       
       INNER JOIN sys.objects o ON ixc.object_Id = o.object_id         
       INNER JOIN sys.columns col ON col.object_id = o.object_id and ixc.column_id = col.column_id
       INNER JOIN sys.schemas sch ON sch.schema_id = o.schema_id
) FK
ORDER BY  
   schemaname,
   tablename,  
   columnname

DECLARE @count int
SELECT @count = COUNT(*) FROM #sqlfkindexes
WHILE @count > 0
BEGIN
      SELECT @sql = [sql] from #sqlfkindexes where [id] = @count
      SET @count = @count - 1
      PRINT @sql
      -- Uncomment to execute directly
      -- EXEC sp_executesql @sql
END

drop table #sqlfkindexes
GO

As is, this script prints out the missing index definitions but does not run them into the database. Simply copy and paste into another SSMS query window and run. (You could also uncomment the execute line, but I prefer to check them before running in).

LINQPad: Async Functions Interactive Tutorial

2011-08-27T13:20:00.001+08:00

Joe Albahari has recently released an interactive tutorial on C# asynchronous functions. It's shipped as a LINQPad sample library. Download the latest LINQPad beta, then go to 'Samples' on the bottom left, click 'Download more samples' and choose the async functions tutorial.

Visual Studio Lightswitch

2011-08-16T08:55:00.001+08:00

The latest issue of MSDN magazine has a short but interesting article “Advanced Programming Made Easy With Visual Studio Lightswitch” from Beth Massi. You can download the development sample from here.

T-SQL: Generating Random Numbers, Random Sampling and Random ‘Goodness’

2011-08-07T17:20:00.001+08:00

Note: I will use the word ‘random’ throughout to mean ‘pseudo-random’. If you want truly random numbers then you need to sample a physical source of noise, i.e. you need one of these devices: http://www.idquantique.com/true-random-number-generator/products-overview.html !

If you are working with large volumes of data, it’s a common requirement to select a smaller, random sample of that data. There are several techniques to do this using TSQL; we will examine several, and perform a statistical evaluation of the distribution of the samples that each technique produces.

There are two common scenarios. Consider a data warehouse that contains hundreds (or thousands) of millions of rows. Working with the entire dataset to perform statistical analysis or data mining can be very time-consuming. Instead, analysts often select a smaller, random sample of records to build a model or draw conclusions, and then test these predictions against the full population.

Alternatively, analysts could use a BI tool (such as OLAP or data mining) to study the entire dataset and identify patterns that might represent a business opportunity. By testing against small random subsets of data, analysts can verify that their hypotheses hold true.

If you search the internet for “TSQL Random Sampling” most paths lead back to this article “Random Sampling in T-SQL”, written by Brian Connolly back in 2004. As the code no longer appears to be available in its entirety, I’ve rewritten the code but followed some of the same techniques used in that article.

There are several techniques that can be used to implement random sampling with TSQL:

Add a column to the table, fill it with values from the Rand() function, and select an ordered subset based on that column.
The same as the first technique, except that the Rand() function is seeded.
The same as the first technique, except that the Rand() function is anchored to a row.
Use the NEWID() GUID function to select an ordered subset.
Use CHECKSUM(NEWID()) as a source of random values.
Use TABLESAMPLE introduced in SQL Server 2008.

Before going any further, we can quickly rule out limiting result sets using TABLESAMPLE:

You can use TABLESAMPLE to quickly return a sample from a large table when either of the following conditions is true:

The sample does not have to be a truly random sample at the level of individual rows.
Rows on individual pages of the table are not correlated with other rows on the same page.

In other words, despite its name, if you want a truly random sample don’t use the built-in TABLESAMPLE!

Some considerations: Rand() is relatively expensive because it requires the use of INSERTs, cursors, or single-row UPDATEs. Seeded Rand() and NewID() are easier to use, but are they sufficiently random? What we want to determine is the quality of the statistical random samples they produce. There are many statistical tests to determine the randomness of a pseudorandom number generator, which we expect to have a uniform distribution. One of the most commonly used is the Chi-square Test:

Discrete uniform distribution

In this case, N observations are divided among n cells (or buckets). A simple application is to test the hypothesis that, in the general population, values would occur in each cell with equal frequency. The "theoretical frequency" for any cell (under the null hypothesis of a discrete uniform distribution) is thus calculated as

Calculating the Chi-Square test-statistic

The value of the test-statistic is

where

Χ² = Pearson's cumulative test statistic, which asymptotically approaches a χ² distribution.
O_i = the observed frequency;
E_i = the expected (theoretical) frequency, asserted by the null hypothesis;
n = the number of buckets that the sample is divided into.

The Chi-squared test divides the row range into a number of equally spaced "buckets" and ensures that on average the number of items in each bucket falls within the expected count (which for a uniform distribution is simply the number of rows divided by the number of buckets). For example, a random 1 percent sample of a RandomPopulation table with 100,000 rows will return 1000 row ids that are scattered between 1 and 100,000. Divide the row space into 40 "buckets" from 1-2500, 2501-5000 ... 97501-100000. A random 1 percent sample from RandomPopulation should place roughly 25 row ids into each bucket (some buckets will have more and some will have less). The Chi-square test measures this deviation to determine how much non-randomness is present.

First Attempt Using Rand()

So you have a problem where you need to select some random sampled data in TSQL, and therefore need a source of ‘random’ numbers. Your first attempt at generating random numbers might be something along the lines of:

   -- Won't work: returns same value for all rows!
   SELECT TOP 1000 
   RAND() AS rnd
   FROM master.sys.all_columns AS ac1
   CROSS JOIN master.sys.all_columns AS ac2

But you will quickly discover that a major drawback to using Rand() without some form of ‘row anchor’ is that it is only evaluated once per query. So the previous query returns the same random number repeated for each row.

The RandomPopulation table

Following the original implementation, we will use a similar table to implement and compare these techniques:

   create table dbo.RandomPopulation
   ( 
      rowid int not null PRIMARY KEY,

      pure_random float null,
      seeded_random float null,
      seeded_random_anchored float null,
      newid_random float null,
      newid_guid UNIQUEIDENTIFIER null,

      pure_random_order int null,
      seeded_random_order int null,
      seeded_random_anchored_order int null,
      newid_random_order int  null,
      newid_guid_order int null
   )

This table will contain the population to be sampled. The first group of columns hold the actual random values generated, while the second group hold rowid values when ordered by their respective random value column.

If there are 100,000 rows in the table, rowid will have values between 1 and 100,000 inclusive. The row with the smallest pure_random number will have a pure_random_order value of 1, and similarly the row with the largest pure_random number will have a pure_random_order value of 100,000. We use queries like the following to return a sample of RandomPopulation with @samplecount rows into a temporary table:

   select rowid
   into #tpure
   from RandomPopulation
   where pure_random_order < @rowsample

The other random value columns are processed similarly.

Method 1: Fill a column using rand() in a while loop

Since the only way to assign a different random number to every row in a table is to make a separate assignment for each row, one solution is to loop over the rows, updating the table one row at a time:

   -- 1. Pure random value
   set @row = 1
   while (@row <= @rowcount)
   begin
      insert into dbo.RandomPopulation (rowid, pure_random)
      values (@row, rand())
      set @row = @row + 1
   end

Once all rows have been assigned a pure_random value, the pure_random_order column can be calculated:

   update dbo.RandomPopulation
   set pure_random_order = x.row_order
   from RandomPopulation 
   inner join (select row_number() over(order by pure_random) as row_order, rowid 
               from dbo.RandomPopulation) x on  x.rowid = dbo.RandomPopulation.rowid

This query pattern is likewise repeated for the other random value/order column pairs.

Method 2 and 4: rand() seeded with Newid(), and pure Newid()

In order for rand() to be evaluated for each row, we need to seed it with a row-dependent value. TSQL’s NewID() function is evaluated once per row, and the uniqueidentifier values (128 bit GUIDs) that it returns can be ordered. Both of these methods are shown below:

   -- 2. Rand seeded with guid  
   UPDATE dbo.RandomPopulation 
   SET seeded_random = rand(checksum(newid()))

   -- 4. newid() value
   UPDATE dbo.RandomPopulation 
   SET newid_guid = newid()

Method 3: rand() seeded with a row anchor

As mentioned earlier, if rand() is seeded with a row-dependent value, it's evaluated not once per query, but rather once for each row. In Brian’s original article, he uses the following formula to seed rand() with a row-dependent value:

   -- 3. rand seeded and anchored to row: DO NOT USE!!
   UPDATE dbo.RandomPopulation 
   SET seeded_random_anchored = rand((rowid % 1000000) * (datepart(ms, getdate())+1))

This formula does not work at all well. You do not even need to perform a chi-square test on the data ; it is visibly predictable!

Instead, I used the following formula:

   -- 3. rand seeded and anchored to row (2): also, DO NOT USE!!
   UPDATE dbo.RandomPopulation
   SET seeded_random_anchored = rand((rowid % 97) * (datepart(nanosecond, sysdatetime())/100 + 1))

The seed chosen for rand() is a function of the rowid and also the current nanoseconds. This is the one I’ve included in the results, BUT both versions perform very poorly, and should not be used in practice. Here’s what Brian had to say:

The use of seeded random is problematic. Rand has been designed (and tested) to give a stream of statistically independent, uniformly distributed numbers on successive calls after it's seeded. However, the "seeded random" technique used earlier essentially takes the first random number from a different seed for each row. So while Rand(X), Rand(), Rand() ... Rand() would be expected to yield a valid stream of random numbers, there's no reason to expect Rand(N1), Rand(N2) ... Rand(NNN) to be random. For this reason, and also because the tests that follow demonstrate how bad it can be, I don't recommend you use this technique.

Although using a time based function to seed rand() might seem convenient, in addition to the above reasons for not doing so, there is also the issue that the ‘randomness’ is affected by the speed of your CPU!

Method 5: checksum(newid) as a float value

This method transforms the 128bit GUID returned from Newid() into a 32bit integer value using Checksum() and then into a floating point value between 0 and 1.

   -- 5. checksum(newid()) value
   UPDATE dbo.RandomPopulation 
   SET newid_random = CAST(CHECKSUM(NEWID()) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

The expression CAST(CHECKSUM(NEWID()) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1. The idea behind testing this is to check whether the restriction of the GUID values to 32 bits still renders them sufficiently random.

Note: This technique can be used directly to select a random sample of individual rows. In this example from MSDN, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table:

   SELECT * FROM Sales.SalesOrderDetail
   WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

The SalesOrderID column is included in the CHECKSUM expression so that NEWID() evaluates once per row to achieve sampling on a per-row basis. The expression CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) evaluates to a random float value between 0 and 1.

-------------------------------

Sidebar: Picking a single random row

Picking a single random row using an ORDER BY clause like this:

   SELECT TOP 1 * FROM table ORDER BY CHECKSUM(NEWID())

performs a table scan (because the random value associated with each row obviously needs to be calculated before the rows can be ordered), which can be slow for large tables. Using an indexed integer column (such as that commonly used for a primary key), and using:

   SELECT TOP 1 * FROM table 
   WHERE rowid >= RAND(CHECKSUM(NEWID())) * (SELECT MAX(rowid) FROM table)

works in constant time, provided the rowid column is indexed. Note: this assumes that rowid is uniformly distributed in the range 0..MAX(rowid). If your dataset has some other distribution, your results will be skewed (i.e. some rows will be picked more often than others).

-------------------------------

Running the Tests

I have followed Brian’s original TSQL implementation to calculate the bucket frequencies and the resulting chi-square statistic. Please refer to the original article or the attached downloadable code for more details. The tests are wrapped in the following stored procedure (available in the download):

create procedure dbo.test_random_selection
(
   @runs        int,        -- Number of times to run the tests
   @rowcount    int,        -- Size of the Random population to be sampled
   @sampleprop  float,      -- Proportion of the Random population to be sampled
   @bucketcount int         -- Number of buckets to be used for chi-square analysis
)

Test Results

All tests were run against SQL Server 2008 R2 SP1 Enterprise Edition (x64) (and also without SP1; the results were similar) on an Intel Quad core i7 based PC running Windows 7 x64 Ultimate.

-------------------

Test1: test_random_selection(50, 100000, 0.01, 40): 50 runs, 100,000 RandomPopulation rows, a 1 percent sample, and 40 buckets.

Each 2,500 (= 100,000 / 40) interval range bucket should contain approximately 25 rowids. There are 39 degrees of freedom (1 less than the number of buckets). We can say that the row sample for each technique is independent and uniformly distributed at the 90% confidence level if the Chi-square statistic for each technique is Chi-Sq[.90, 39]. Consulting a standard Chi-square table gives a value of 50.660 for the Chi-Sq[.90, 39] threshold level of this statistic (and the lower Chi-Sq[.10, 39] = 28.196). As long as the Chi-square value for each technique is lower than 50.66, there is no reason to suspect that the sample is not random (Note: we expect that some samples will exceed the threshold value, because we are working with a confidence level of 90%. A few instances of crossing this value does not necessarily mean that the samples are non-random).

The results from this test are shown here:

	Rand()	Rand(Seeded)	Rand(anchored)	Checksum(Newid())	Newid
Avg	38.12	38.90	23.26	38.38	38.67
Std	10.18	8.73	2.92	9.17	9.40
Min	19.64	23.64	18.44	21.08	18.20
Max	68.68	60.64	26.88	60.68	57.60
# >= 50.66	6	5	0	4	6

The Rand() seeded with a row anchor technique is showing very predictable behaviour. In fact, it is producing an unusual number of equal values!. The averages, standard deviations, max and min values for the other techniques are very similar to each other in shape and bounds.

Out of interest, I ran the tests again using the rand anchored formula from the original article, and obtained a similar result to Brian:

Neither of the ‘rand() seeded with a row anchor’ methods produce anything like sufficiently random sample, and consequently should not be used in practice.

Test 2: test_random_selection(160, 1000000, 0.01, 100): 160 runs, 1,000,000 RandomPopulation rows, a 1 percent sample, and 100 buckets.

Consulting a standard Chi-square table gives a value of 117.407 for the Chi-Sq[.90, 99] threshold level of this statistic (and the lower Chi-Sq[.10, 99] = 81.449). I have omitted the seeded anchored results in the larger graph to improve the viewable scale (but have included with the same results below, reduced in size)

	Rand()	Rand(Seeded)	Checksum(Newid())	Newid()
Avg	99.17	99.06	95.75	97.20
Std	14.27	15.14	13.38	14.7
Min	69.27	69.72	63.92	70.41
Max	138.11	145.07	131.39	138.57
# > 117.407	16	23	7	14
# < 81.449	15	21	27	21

Conclusions

The seeded random anchored technique should never be used: the results were inconsistent and generally very poor.

The other 4 random sampling techniques produced very similar, acceptable results. They produced random samples that were sufficiently random as measured by a Chi-Square test. rand() has been designed to meet statistical standards so it is no surprise it produces random numbers whose distribution is uniform and satisfy a Chi-Square test. You should use rand() wherever possible, but bear in mind the overhead it has when used as a ‘row at a time’ operation. In testing, I found generating rand() per row was 60 times slower than the set-based operation of either using checksum(newid()) or even rand(checksum(newid())):

Filling a table column for 1 million rows:

Rand() per row : 124 seconds
Set:CHECKSUM(NEWID()) : 2 seconds
Set:rand(checksum(newid())) : 2 seconds

So, if speed is a factor, then consider using of the newid()techniques. But heed Brian’s advice:

Bear in mind, though, that because NewID() is assigned by the operating system, you should test its behaviour on the target system–perhaps using the code included in the accompanying Download as a starting point.

Notes:

Note: none of the random number generation methods discussed here are sufficient for cryptographic purposes (which require much larger number generation spaces). SQL Server 2008 introduced the CRYPT_GEN_RANDOM(length [, seed ]) function, which returns a cryptographic random number generated by the Crypto API (CAPI). The output is a hexadecimal number of the specified number of bytes.

Note: Please be aware of the following issue, when using Newid() or any other non-deterministic function in a table expression (derived table, CTE, view, inline TVF):

Refs:

Download

RandomNoTest.zip

SQL Server: Is it OK to use a uniqueidentifier (GUID) as a Primary Key?

2011-08-02T08:07:00.001+08:00

I was recently discussing this topic with a colleague and less than a day later a similar was question was asked on StackOverflow. Then a few days later I was asked a related question by another colleague.

Let’s start with the answer to the question “Is it OK to use a uniqueidentifier (GUID) as a Primary Key?”: Yes, a uniqueidentifier (GUID) column can be fine as a Primary Key, BUT it is not a particularly good choice for the clustered index. In many cases, you will be better off creating the clustered index on a column (or columns) that are likely be used in range searches, and create a non-clustered index on the GUID column.

OK, that’s the answer out of the way! Now for the reasons.

A common reason for using a GUID column as a primary key (over an int or bigint) is the ability to create and assign these in the middle application tier without making a round trip to the database, while still avoiding key clashes between distributed clients.

A primary key is a column (or set of columns) that uniquely identifies each row in a table (or entity). When a column (or a set of columns) is defined as a primary key, SQL Server checks that ALL of the columns that make up the PRIMARY KEY meet the requirement of a PRIMARY KEY constraint which is that they must not contain any NULLs. If the table contains data, a check is made to ensure that this existing data meets the uniqueness constraint. If there are any duplicate rows, the addition of the constraint will fail, as will the creation of the primary key.To enforce this for new rows, SQL Server builds a UNIQUE index. If you do not specify the index type when adding the constraint, SQL Server creates a UNIQUE CLUSTERED index. Which is almost certainly why people sometimes confuse the clustered index with the primary key.

It’s important, so it’s worth repeating: If you don't specify the index type when designating a Primary Key, SQL Server creates a UNIQUE CLUSTERED index by default.

What’s the significance of the clustering key?

It defines the lookup value used by all the non-clustered indexes (and therefore should be unique, as narrow as possible and unchanging).
It defines the table's order, so fragmentation (and index maintenance) needs to be taken into account.
It can be used to satisfy a query, either as a table scan or a range query (where the clustering key supports that range)

Notice point (1): Each index row in the non-clustered index contains the non-clustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value (Nonclustered Index Structures). So a wide clustering key adds overhead to every non-clustered index. This overhead can be significant.

What are good choices for the clustering key?

An identity column (int or bigint)
A composite key based on date and identity (in that order)
An ‘ordered’ (or pseudo sequential) GUID (such as SQL Server’s NEWSQUENTIALID(), or a modified, ordered GUID sometimes called a ‘COMB’)

A uniqueidentifier (GUID) column requires 16 bytes of storage (4 times wider than an int identity column), so the space used per row is higher (and less rows fit on a database page), and bandwidth consumed by data transfer is higher.

Column Type	Size (bytes)	Range
int	4	2³¹ - 1
bigint	8	2⁶³ - 1
uniqueidentifier (GUID)	16	2¹²⁸

If all you need is a key range larger than that supported by an int (4 bytes, 2³¹ – 1 rows) then consider using a bigint (8 bytes), which supports 2⁶³ - 1 rows (which should be sufficient for most applications) and saves 8 bytes per row compared to using a GUID.

Are there any performance considerations? Yes. In addition to the increased storage issues, a clustered index based upon a non-ordered GUID will experience fragmentation due to page splits during inserts. If you absolutely have to have a (non-ordered) GUID as a primary key with a clustered index, make sure you have a regular index rebuild as part of your database maintenance plans, and maybe even consider leaving extra space per page by decreasing the Fill Factor for the clustered index.When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth, and reducing the number of page splits. [Note: The fill-factor setting applies only when the index is created, or rebuilt.]

Ref: GUIDs as PRIMARY KEYs and/or the clustering key

Perth .NET User Group Meeting: Thurs 4th Aug, 5:30pm: Introduction to NServiceBus with Colin Scott

2011-08-01T23:20:00.001+08:00

Join us at the Perth .NET user group, August 4th 5:30pm, where Colin Scott will give an introduction to NServiceBus covering key features and concepts such as asynchronous messaging, Publish/Subscribe, message handling and versioning and just what the heck is a service anyway. NServiceBus is an open source service bus built on .NET. It supports the construction of systems that are robust, loosely coupled and aligned to your business.

TOPIC: Introduction to NServiceBus with Colin Scott
DATE: Thursday, August 4th, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Colin Scott is a developer who's been working with .NET long enough to start telling the kids of today that they don't know how good they have it! Having worked for consulting companies since 1999 he's recently decided to see how the other half live by joining the development team at Quickflix as a Senior .NET Developer/Architect. When not making the world a better place for developers everywhere he can be found on Twitter at @AbstractCode.

More details here: http://perthdotnet.org/blogs/events/archive/2011/07/17/introduction-to-nservicebus-with-colin-scott.aspx

SQL Server - Book Recommendations

2011-08-01T08:15:00.003+08:00

If you’re an experienced developer/DBA coming from a background in another RDBMS or just want to improve your SQL Server knowledge, here are some SQL Server book recommendations:

Microsoft SQL Server 2008 Internals: goes deep but does not neglect the fundamentals. It is a must read.
Professional SQL Server 2008 Internals and Troubleshooting: similar to first book, but with a slightly more performance based focus.
SQL Server 2008 Query Performance Tuning Distilled: great techniques for identifying and fixing poorly performing queries, from the metal to the application.
SQL Server MVP Deep Dives: written by some of the world's leading SQL Server experts and practitioners. [There's a short review here. Volume 2 is due out somewhere around November 2011, I believe]

If you have more of a development focus:

Pro SQL Server 2008 Relational Database Design and Implementation
Expert SQL Server 2005 Development (SQL Server 2005 but still relevant for SQL Server 2008)

There are also some very good, free electronic books from Redgate:

to list a few.

Ref: Long time Oracle developer/DBA learning SQL Server - book recommendations?

New Version of VSCommands 2010

2011-07-31T10:27:00.001+08:00

A new version of VSCommands 2010 was released earlier this month. New features include file structure, output windows highlighting (makes errors easier to spot), and friendly solution name (handy where you might be working in multiple branches of the same project).

SharePoint 2010 Database Maintenance Whitepaper

2011-07-30T10:18:00.001+08:00

Paul Randal, who teaches the SQL Server portion of the SharePoint MCM, has released an updated version of the database maintenance whitepaper for SharePoint 2007 (which was somewhat out of date). You can download it from here.

SQL Server Maintenance Solution

2011-07-22T09:32:00.001+08:00

Ola Hallengren has recently released a new version of his maintenance solution scripts. He has added the ability to log to a table, which is useful if you want to analyse how your indexes are rebuilt and reorganized over time. All you need to do is to set @LogToTable = 'Y'.

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', 

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 

@FragmentationMedium = 'INDEX_REORGANIZE', 

@FragmentationLow = NULL, 

@LogToTable = 'Y'

It can also be used with DatabaseBackup and DatabaseIntegrityCheck.

http://ola.hallengren.com/Versions.html

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

New 550MB/s SSDs

2011-07-21T10:45:00.001+08:00

If you’re thinking of buying a new shiny 550MB/s SSD, there are two things you should keep in mind:

Does your motherboard support SATA 3.0?
SATA revision 3.0 (also known as SATA 6) is NOT SATA 3

(Some motherboards specs even refer to Sata-600 and Sata-300 to try and limit the confusion)

SATA revision 2.0 (SATA 3 Gbit/s)

With a native transfer rate of 3.0 Gbit/s, and taking 8b/10b encoding into account, the maximum uncoded transfer rate is 2.4 Gbit/s, providing a peak throughput of about 300 MB/s.

SATA revision 3.0 (SATA 6 Gbit/s)

Provides a peak throughput of about 600 MB/s (Megabytes per second) including the protocol overhead (10b/8b coding with 8 bits to one byte).

Ref.

My realisation came about today through a conversation I had with Hadley Willan (aka Hadders). He’s a gold mine of tech info; I wish he would blog these gems more often…

Visual Studio LightSwitch 2011

2011-07-19T09:10:00.001+08:00

Visual Studio LightSwitch 2011 is launching July 26th. Sounds just like another Visual Studio add-in being released? It’s definitely more than that. It’s going to have a big impact on how some businesses build applications.

[NOTE: If you are planning to install LightSwitch Beta 2, and Visual Studio 2010 Express, Professional, Premium or Ultimate is installed, you must first install Visual Studio 2010 SP1]

Visual Studio 2010 Debugging Tips

2011-07-15T08:53:00.001+08:00

Not new, but several excellent Visual Studio 2010 debugging tips from Scott Guthrie, some you probably know and perhaps the odd one you might not be aware of (or have forgotten about).

Fixing Slow SQL Server Management Objects (SMO) Performance

2011-07-14T11:32:00.002+08:00

Just recently I’ve been converting a small utility that uses SQL-DMO to use SQL-SMO instead (SQL-DMO is deprecated in SQL Server 2008 R2; if you really need to use it, install the Backward Compatibility Components from the SQL Server 2008 Feature Pack from the Microsoft Download Centre). Remembering all those posts saying how slow SMO was compared to DMO, a colleague mentioned this:

SMO has the concept of Delayed Instantiation of Object Properties. The concept consists of not returning every property of a database object when the object is requested. Some properties, like the StoredProcedure's IsSystemObject property, are not returned by default when you request a Stored Procedure or a collection of Stored Procedures from the Database. It is only when you explicitly access the IsSystemObject property that SMO goes back to the database to get its value.

[…]

If you find your SMO code is running slowly, use SQL Profiler to check whether you are accessing properties that are not returned by default and creating extra roundtrips to the database. Force SMO to load those properties by default by using the Server.SetDefaultInitFields method:

            Server server = new Server();

            // Load all properties for columns
            server.SetDefaultInitFields(typeof (Column), true);

            // ...or load all properties for all objects
            server.SetDefaultInitFields(true);

To build an SMO application, you need to reference the SMO assemblies. Click ‘Add Reference’ and navigate to the folder

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

Add references to:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.SqlEnum.dll

(These are the minimum files that are required to build an SMO application)

Ref.: How to: Create a Visual C# SMO Project in Visual Studio .NET

SQL Server Code Name "Denali" CTP3 Released

2011-07-14T09:19:00.001+08:00

SQL Server Code Name "Denali" CTP3 has been released. You can download from TechNet here (I believe it requires a current subscription).

Resources:

	•	Whitepaper: SQL Server-What's New
	•	Video: SQL Server and the Data Explosion
	•	Datasheet: High Availability

SQL Server 2008 R2 Service Pack 1 Released

2011-07-13T09:28:00.001+08:00

The title says it all!: SQL Server 2008 R2 Service Pack 1

SQL Server 2008: The ultimate guide to the datetime datatypes

2011-07-11T15:31:00.001+08:00

I mentioned Tibor Karaszi’s guide a while back and he’s updated it for the datetime datatypes added in SQL Server 2008. A great reference: The ultimate guide to the datetime datatypes

Windows Azure Platform FAQs

2011-07-03T09:40:00.001+08:00

Just for my reference: Windows Azure Platform FAQs contains a nice overview and description of many of the aspects of Microsoft’s cloud offering, including SQL Azure, and a list of feature differences between SQL Server versions and SQL Azure.

Improving SQL Server Management Studio’s Tabs

2011-06-24T11:24:00.001+08:00

I saw this post by Brent Ozar, Fixing SQL Server Management Studio’s Tab Text, and immediately made the changes whilst face palming myself and going “D’oh!” at the same time.

What are the improvements? First, the status bar is moved to the top of the tabbed window space, where your eyes are normally focused. Second it removes the server name, database name and login (which are all in the status bar anyway) from each tab title so that you can actually navigate around the tabs!

It’s been a constant source of frustration over many years of using SSMS, and I feel chastised for not investigating the options before. Nice one Brent!

MVC Mini Profiler

2011-06-22T10:29:00.001+08:00

If you are working with ASP.NET MVC 3 you might be interested in this project: the mvc-mini-profiler. It includes standard profiler timing output but also comprehensive database profiling capabilities.

MVC Mini Profiler was designed by the team at Stack Overflow, and is actively being used there to monitor the Stack Exchange family of sites.

Visual Studio 2010 Web Standards Update

2011-06-21T15:32:00.001+08:00

Visual Studio 2010 Web Standards Update is a free extension available for anyone who is using Visual Studio 2010 SP1 and it provides HTML5 & CSS3 support based on current W3C specifications.

It supports:

HTML5 – Video, Audio, Input Type, Drag & Drop, WAI-ARIA, Microdata, Schema.org
Browser API – GeoLocation & Local Storage
CSS3 – 2D Transforms, 3D Transforms, Animations, Background & Borders, Basic Box Model, Basic UI, Behaviour, Colour, Flexible Box Layout, Fonts, Paged Media, Hyperlink Presentation, Line, Lists, Marquee, Media Queries, Multi Column, Namespaces, Presentation Levels, Ruby, Selectors, Speech, Syntax, Template Layout, Text & Transitions. It also supports vendor specific prefixes like –ms, -webkit & -moz.

Note: This extension is created by a bunch of folks within Microsoft in their spare time. This is NOT an official Microsoft product

Scott Hanselman blogged about it here.

You can download here: Web Standards Update for Microsoft Visual Studio 2010 SP1

Who’s Your Product?

2011-06-19T11:41:00.001+08:00

Love this quote, which sums up the model of companies like facebook, google etc.:

“If you are not paying for it, you're not the customer; you're the product being sold.”

Originally(?) appeared here and quoted many times since.

Perth .NET User Group, Thurs June 2nd: Good Things Come to Those Who ‘await’ with Joe Albahari

2011-05-30T21:41:00.001+08:00

Join us at the Perth .NET user group, June 2nd 5:30pm, where Joe Albahari will showcase the magic of C# 5's await and async keywords. You'll see how easy asynchronous programming has become with Microsoft's latest CTP, and how much you can achieve without being a guru in multithreading or monadic calculus.

We'll start with very simple examples to introduce asynchronous programming in general: what exactly is it, how does it differ from multithreading, and why do we need it? Then we'll examine the historical support for asynchronous programming in the .NET Framework and what people are doing right now. You'll see why BackgroundWorker and the event-based pattern are clumsy and often inadequate - and how the APM strikes fear, pain (and ultimately asynchrophobia) into the hearts of the even most hard-core programmers. We'll then demonstrate how the async CTP eliminates the problem almost entirely - allowing you to program as you always have.

TOPIC: Introduction to C# 5 async with Joe Albahari
DATE: Thursday, June 2nd, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

There will be plenty of practical examples - from making a rich client app responsive to writing high-concurrency apps and games. We'll also cover limitations and discuss when you would use this versus Reactive Extensions. The presentation will assume minimum background knowledge and yet will go fairly deep. We'll also look at what the compiler actually does in translating asynchronous calls, and potential extensibility points. This is a great chance to get to grips with an otherwise difficult-to-research topic.

Joe Albahari is a C# MVP and author of C# 4.0 in a Nutshell and LINQPad. He has presented at TechEd and JAOO, and is a regular speaker in the Perth .NET UG. He has an extensive free online resource on .NET multithreading and parallel programming at www.albahari.com/threading/

Mount .iso files in Windows

2011-05-30T08:09:00.002+08:00

I remember Greg Low once talking about the “Resolved: by Design” or “Resolved: as Won't Fix” replies on Connect when the issue being raised or asked for was clearly a good idea (obviously there are time constraints and sometimes things just aren’t possible); his point was that it doesn’t take too many such responses for even someone evangelical in their conscientious raising of issues to be less than enthusiastic about continuing to do so.

Why do I mention that? Well, ever wanted to mount an .iso file natively in windows? Not such a surprising thing to want to do, right? Especially as Microsoft actually provide many downloads in this format (such as the Windows 7 OS, which is what I was installing). Check out this Connect article “Mount .iso files”. It had 919 up-votes at last count!… Something for Windows 8?

I resorted to using one of the many third party installs which do the job: Virtual CloneDrive. But it really does feel like this should be part of Windows. Getting community/user feedback on your products is a great way to improve them, but only if you’re listening.

“Thank you for your comment/suggestion.
We will continue to monitor your submission and when it reaches the vote threshold from the community we will forward it to the appropriate feature team. We will also respond here to let you know it has been escalated.”

I wonder what that vote threshold is!

Free ASP.NET MVC2 Book

2011-05-29T10:47:00.001+08:00

Apparently a very good book on ASP.NET MVC 2 is "MVC 2 In Action" from Manning, and while the authors are putting the finishing touches to the MVC 3 version of the book, the entire MVC 2 version is available completely free in Word document form at: https://github.com/jeffreypalermo/mvc2inaction.

Every version of MVC relies on the previous version (except with a few breaking changes), so there is still value in reading this free resource.

Jeffrey Palermo’s original post is here.

ASP.NET Web Forms and ASP.NET MVC Video Training

2011-05-27T09:10:00.001+08:00

Microsoft has made the "Pluralsight On-Demand!" 10 part ASP.NET MVC 3 video training course available free of charge. (“Essential Videos” left-hand pane).

Similarly, there is a 9 part ASP.NET Web Forms 4 video training course also available for free.

TSQL: Finding Maximum, Minimum and Average Data Row Lengths

2011-05-26T08:55:00.001+08:00

This is probably a classic example of if you find you are doing something complicated, there’s almost certainly a better way. It’s also an example of if you think something is genuinely useful and can’t understand why it’s not been implemented already, it probably has but you just haven’t found it yet!

I wanted to get a table’s approximate minimum, maximum and average row size, so after a few attempts I came up with this TSQL snippet:

declare @schemaname varchar(200) = 'Person'
declare @tablename  varchar(200) = 'Person'

declare @columnList nvarchar(max)
declare @sql        nvarchar(max) 

set  @columnList = 
stuff( 
    (    SELECT
           ' + ISNULL(DATALENGTH(' + c.name + '),0)'
     FROM 
        sys.tables AS t
        INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
   where SCHEMA_NAME(schema_id) = @schemaname and t.name = @tablename
    for xml path('') 
    ) 
, 1, 3, '') 

SET @sql = 'SELECT ''' + @schemaname + '.' + @tablename + ''' as TableName,' + 
           ' MIN(' + @columnList + ') AS MinRowLength, ' + 
           ' MAX(' + @columnList + ') AS MaxRowLength, ' + 
           ' AVG(' + @columnList + ') AS AverageRowLength ' + 
           ' FROM [' + @schemaname + '].[' + @tablename + ']'
EXEC sp_executesql @sql

[Note: It’s not 100% accurate due to some row overheads, but it is close enough for many purposes.]

At which point, the thought “Surely there exists a built-in method to do this?” crossed my mind, and of course there is!

If you have sufficient permissions you can quickly produce a min, max and average row size for each table in a database (along with fragmentation information) using

   DBCC SHOWCONTIG WITH TABLERESULTS

Online IDE for 40+ languages

2011-05-25T16:52:00.001+08:00

It’s not often I don’t have access to the Visual Studio IDE, but I saw Jon Skeet mention this recently on SO: http://ideone.com/

What is ideone?
Ideone is something more than a pastebin; it's an online compiler and debugging tool which allows
to compile and run code online in more than 40 programming languages.

The Time has Come, the Walrus said…

2011-05-25T10:59:00.001+08:00

A big push to move to TFS and away from Visual SourceSafe (strongly resisting urge to berate VSS!)

But what’s this? No event in Perth? Guess that means stick to VSS!! :) (OK, there is a Live Meeting…)

Getting Started with Visual Studio LightSwitch

2011-05-24T09:05:00.001+08:00

Beth Massi, one of the Program Managers for LightSwitch, has posted a landing page with links for Getting started with Visual Studio LightSwitch.

The links include downloading LightSwitch and the training kit, the forums, and the official LightSwitch Developer Center.

Large Object Heap and Arrays of Double

2011-05-22T11:23:00.002+08:00

If you were asked where objects greater than or equal to 85,000 bytes are allocated in .NET, you would no doubt say on the Large Object Heap (LOH). What would you say if you were asked where an array of 1000 doubles would be allocated? Currently, as of .NET 4.0, it will be allocated on the Large Object Heap!

William Wegerson (aka OmegaMan), a C# MVP, posted this item to Connect: Large Object Heap (LOH) does not behave as expected for Double array placement that describes and reproduces the behaviour:

byte[] arrayLessthan85K = new byte[84987]; // Note: 12 byte object overhead 84987 + 12 = 84999
Console.WriteLine("byteArrayLessthan85K: {0}", GC.GetGeneration(arrayLessthan85K)); // Returns 0

byte[] array85K = new byte[85000];
Console.WriteLine("byteArray85K: {0}", GC.GetGeneration(array85K)); // Returns 2

double[] array999Double = new double[999];
Console.WriteLine("array999Double: {0}", GC.GetGeneration(array999Double));   // Returns 0
           
double[] array1000double = new double[1000];
Console.WriteLine("array1000double: {0}", GC.GetGeneration(array1000double)); // Returns 2

By looking at the garbage collection generation on object creation (GC.GetGeneration), we can identify if objects reside the LOH or not. If immediately created in generation 2 then that suggests we are in the LOH.

The reason why double arrays with 1000 or more items are allocated on the LOH is performance, due to the fact that the LOH is aligned on 8 byte boundaries. This allows faster access to large arrays and the trade-off point was determined to be 1 thousand doubles.

According to Claudio Caldato, CLR Performance and GC Program Manager, “there’s no benefit to applying this heuristic on 64-bit architectures because doubles are already aligned on an 8-byte boundary”. Subsequent changes have been made to this heuristic that should appear in a future release of the .NET Framework.

As a side note, the expected behaviour is seen if you use Array.CreateInstance() :

// As noticed by @Romout in the comments to that post, the same behaviour is not seen when using Array.CreateInstance()
double[] array1000doubleCreateInstance = (double[])Array.CreateInstance(typeof(double), 1000); // Returns 0
Console.WriteLine("With array-create: " + GC.GetGeneration(array1000doubleCreateInstance));

// Indeed, the expected tipping point into the LOH occurs when using Array.CreateInstance
// (85000 / 8) = 10625, need 12 bytes for object overhead, nearest is 16 (2*8) bytes so effectively 10623 doubles
double[] array1000doubleCreateInstance2 = (double[])Array.CreateInstance(typeof(double), 10623); // Returns 0
Console.WriteLine("With array-create: " + GC.GetGeneration(array1000doubleCreateInstance2));

double[] array1000doubleCreateInstance3 = (double[])Array.CreateInstance(typeof(double), 10624); // Returns 2
Console.WriteLine("With array-create: " + GC.GetGeneration(array1000doubleCreateInstance3));

Newline in Summary XML

2011-05-20T13:01:00.001+08:00

You probably know this already, but just in case you don’t! If you want line breaks in your popup tooltip descriptions in Visual Studio, you add the <para> element to your XML summary comments e.g.:

/// <summary> 
/// Main comment 
/// <para>Line 1</para> 
/// <para>Line 2</para> 
/// </summary> 
public bool SomeProperty { get; set; }

and it appears like this:

SQL Server Compact Toolbox

2011-05-14T12:07:00.001+08:00

If you are using SQL Server Compact Edition (CE), and have not seen this already, the SQL Server Compact Toolbox is a Visual Studio 2010 Pro or higher add-in (for SQL Server CE 3.5/4.0) and standalone app (for 4.0), that adds scripting, import, export, migrate, rename, run script, manage replication and more to your SQL Server Compact Data Connections in VS Server Explorer.

Written by Erik Ejlskov Jensen who’s aptly named blog, Everything SQL Server Compact, contains a wealth of tips, tricks and techniques relating to SQL Server Compact.

The toolbox adds several features to Server Explorer:

Scripting:

Script tables, including data, both DDL and DML
Script entire schema, optionally with data, from SQL Server Compact and SQL Server 2005/2008 databases
Import to SQL Server Compact from a SQL Server 2005/2008 database or a CSV file
Migrate from SQL Server Compact to SQL Server and SQL Azure
Migrate from SQL Server to SQL Server Compact
Create database diff scripts, compare with a SQL Server Compact or even a SQL Server database

Query editing:

Basic, free form query execution
Editor with syntax colouring
Parse SQL scripts
Display graphical estimated and actual execution plan
Check query duration

Other features:

Rename tables
Generate detailed DGML files for visualizing table columns and relationships (requires VS 2010 Premium or higher to view)
Generate an Entity Data Model (EDMX) in the current project for both 3.5 and 4.0 in any applicable project (WPF, WinForms, Class Library)
Remove invalid connection definitions from the Toolbox (and Server Explorer)
Create and manage SQL Server Merge Replication subscriptions
Data types node with documentation tooltips lists the 18 available data types
File version check (for version 2-4)
Upgrade version 3.x files to version 4 via the "Add version 4 connection" dialog
About dialog with detailed SQL Server Compact version information

Another of his posts, SQL Compact 3rd party tools, lists several 3rd party tools for CE, both commercial and non-commercial.

SQL Server 2008: Query Hash Statistics

2011-05-12T12:55:00.000+08:00

Bart Duncan has released a very useful addition to the DataCollector capture/reporting abilities of SQL Server 2008. Query Hash Statistics can do low-overhead query cost monitoring, utilising the query fingerprint and query plan fingerprint (aka query hash/query plan hash) features that were added in SQL Server 2008. Query fingerprints enable you to get the cumulative cost of all executions of a query even if the query is non-parameterized and has different inline literal values for each execution. Previously, the only way to get this type of query performance data was to capture a Profiler trace and run the trace through a post-processing tool.

Once installed, and sufficient data has been collected, you can access the collected information via 2 custom reports.

Perth .NET User Group Meeting: 6pm Thurs, 12th May: Introduction to the .NET Reactive Extensions (Rx) with Lee Campbell and James Miles

2011-05-08T09:06:00.001+08:00

Join us at the Perth .NET user group, Thurs May 12^th(6pm) where Lee Campbell and James Miles join up to present an Introduction to Rx, aka the .NET Reactive Extensions. Rx is a product from Erik Meijer's team at Microsoft that allows you to compose asynchronous and event based programs using observable collections and a Linq style syntax.

The presenters will guide you through the background and basics of Rx, and introduce you to the terminology that is peculiar to Rx. James and Lee will compare code written with and without Rx and show case code to demonstrate the power of Rx in the areas of resource management, fluent and familiar Linq syntax, composable nature, testability of asynchronous and concurrent queries, and the and the ability to tame side effects.

TOPIC: Introduction to the .NET Reactive Extensions (Rx) with Lee Campbell and James Miles
DATE: Thursday, May 12th, 6:00pm - 7:30pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

I’m under strict instructions to ask everyone to come armed with questions about Rx!

Please Note: this talk will start at 6pm (not our usual time of 5:30pm due to the venue’s availability)

Full details here: http://perthdotnet.org/blogs/events/archive/2011/05/07/introduction-to-the-net-reactive-extensions-rx-with-lee-campbell-and-james-miles.aspx

Reminder: Perth .NET User Group: ASP.NET MVC framework with Michael Minutillo

2011-05-04T21:55:00.001+08:00

Scott Hanselman is fond of saying that programming components are like Lego pieces and right now "the lego pieces coming out of Microsoft are the right size". One important piece of the web stack is the ASP.NET MVC framework. Since its initial release in March 2009 there has been a new version of the framework released every year and it has quickly become the platform of choice for .NET developers creating web sites. January 2011 saw the version 3.0 release which introduces a number of changes and new features. Additionally, at the MIX11 conference earlier this month Microsoft released the "MVC3 tools refresh" which make developing MVC3 applications in Visual Studio 2010 a highly productive experience.

Join us at the Perth .NET user group, Thurs May 5^th, where we will look at the new Razor View Engine, Unobtrusive Javascript, Integrated Scaffolding, better support for IoC integration. We will also touch on SQL CE 4, NuGet and Entity Framework 4.1 (Magic Unicorns Editions). Come and see the Lego pieces and be inspired by what you can build.

TOPIC: ASP.NET MVC framework with Michael Minutillo
DATE: Thursday, May 5th, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Mike Minutillo is .NET software engineer with a B.Sc. in computer science. In 2000, Mike started writing .NET software to fund his university studies and has been an active member of the .NET community ever since. Mike is a regular attendee at the Perth .NET Community of Practice where he has given presentations on new features of C#, ASP.NET MVC and Test-Driven Philosophy. In 2009 he started the Perth ALT.NET user group which meets monthly to discuss software engineering tools and practices in the .NET development space. Mike is co-author of Professional Visual Studio 2010. He maintains a technical blog at http://wolfbyte-net.blogspot.com/ and can be contacted at http://twitter.com/wolfbyte/.

There will be a door prize of a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, dotCover , RubyMine, IntelliJ IDEA, PyCharm).

When to avoid CQRS

2011-04-26T17:48:00.001+08:00

Interesting post from Udi Dahan (who was previously an exponent of CQRS):

“It looks like that CQRS has finally “made it” as a full blown “best practice”.

Please accept my apologies for my part in the overly-complex software being created because of it.

I’ve tried to do what I could to provide a balanced view on the topic with posts like Clarified CQRS and Race Conditions Don’t Exist.

It looks like that wasn’t enough, so I’ll go right out and say it:

Most people using CQRS (and Event Sourcing too) shouldn’t have done so.

Should we really go back to N-Tier? When not using CQRS (which is the majority of the time), you don’t need N-Tier either…

. . .

In Summary

So, when should you avoid CQRS?

The answer is most of the time.”

SQL Server: How to Share Data Between Stored Procedures

2011-04-09T17:40:00.001+08:00

Erland Sommarskog has an excellent SQL Server article “How to Share Data Between Stored Procedures” tackling these two questions:

How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
How can I pass a table as a parameter from one stored procedure to another?

He discusses several methods, and points out their advantages and disadvantages. [Adding here so I remember where to find it in future!]

SQL Server: Summary of Join Properties

2011-04-06T22:17:00.001+08:00

An oldie but a goldie: a nice Summary of Join Properties

SSMS Table Column List Tip

2011-04-06T18:27:00.001+08:00

On occasion, you will be at someone’s desk, looking over their shoulder when you suddenly notice something and exclaim “Hold on! How did you do that?” quickly followed by “I can’t believe I didn’t know about that!”.

I had just such an experience a few days ago. At the risk of being the last person to know this: in SQL Server Management Studio Object Explorer: open a Database, open the Tables folder, and open a Table. Left click on the Column list folder, and drag and drop to an open query window: you get a comma separated list of column names.

In the past, I had always right-clicked on the table, selected Script->Create To->New query window and then edited out the list of columns. D’oh!

Reminder: Perth .NET User Group Meeting: Thurs 7th April, 5:30pm: LightSwitch- with Andrew Coates

2011-04-04T13:37:00.001+08:00

Join us at the Perth .NET User Group Thurs, April 7th where Andrew Coates will demonstrate LightSwitch, a new product in the Visual Studio family aimed at developers who want to easily create business applications for the desktop or the cloud. LightSwitch simplifies the development process by letting you concentrate on the business logic, while LightSwitch handles the common tasks for you. In this demo-heavy session, you will see, end-to-end, how to build and deploy a data-centric business application using LightSwitch. We’ll also go beyond the basics of creating simple screens over data and demonstrate how to create screens with more advanced capabilities

TOPIC: LightSwitch with Andrew Coates
DATE: Thursday, April 7th, 5:30pm - 7:00pm
VENUE: Enex 100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Andrew Coates is a Developer Evangelist for Microsoft in Australia. Before joining Microsoft, Andrew was an independent consultant specialising in database applications using Microsoft technologies including SQL Server, Visual FoxPro and Microsoft Office. Andrew's wide range of experience includes telecommunications, SMB inventory, dispatch and accounting systems, using both web and rich client front ends. He has developed and delivered class room training and has mentored a number of developers in Visual FoxPro and SQL Server technologies. Andrew has spoken at international conferences on Geographical Information Systems, health statistics and database maintenance and is a regular presenter at TechEd both locally and internationally. An MCPD, MCTS and MCSD, Andrew also holds a Masters degree in Civil Engineering. Andrew blogs at http://blogs.msdn.com/acoat

Get Started with SQL Azure

2011-04-04T09:36:00.001+08:00

Want to get started with SQL Azure but not keen on setting up an account using your credit card details?

Well you can, by signing up for a 30 day SQL Azure pass for free with no credit card required (you get two 1GB Web Edition databases for one month). You can find the promo code at the Get Started with SQL Azure page.

Useful Codeplex Libraries

2011-03-28T08:41:00.001+08:00

I recently came across two useful codeplex libraries. The first enables you to create advanced Excel 2007/2010 spreadsheets without Office installed, EppPlus:

EPPlus is a .Net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

The second is a .NET wrapper of the ImageMagick image manipulation API, ImageMagick.NET

Quotes: When there is no…

2011-03-27T08:57:00.001+08:00

When there is no wind ... ROW! - Latin Proverb

When there is no peril in the fight there is no glory in the triumph. - Pierre Corneille

When there is no enemy within, the enemies outside cannot hurt you. - African Proverb (often attributed to Winston Churchill)

Real love is a pilgrimage. It happens when there is no strategy, but it is very rare because most people are strategists. – Anita Brookner

Perfection is finally attained not when there is no longer anything to add, but when there is no longer anything to take away - Antoine de Saint Exupéry

Laws and principles are not for the times when there is no temptation: they are for such moments as this, when body and soul rise in mutiny against their rigour; stringent are they; inviolate they shall be. - Jane Eyre in Charlotte Bronte.

When there is no vision the people perish. - Franklin Delano Roosevelt (from his first inaugural address)

It is hard to apply oneself to study when there is no money to pay for food and lodging. - Zora Neale Hurston

Microsoft Virtual Academy

2011-03-26T15:02:00.001+08:00

Microsoft (and just about everyone else) has a cloud focus these days, so it's no surprise that Microsoft Learning has also ventured there. Last week saw the official debut of the Microsoft Virtual Academy.

The Microsoft Virtual Academy is targeted at the cloud, with self-paced courses for learning more about private and public clouds, to more specific coverage of Windows Azure and SQL Azure.

MVA requires membership, but it's free. Check out the FAQ here to find out more and get started.

Visual Studio 2010 Service Pack 1 Released

2011-03-09T12:24:00.002+08:00

Visual Studio 2010 SP1 RTM has been released for MSDN subscribers! Get it here

It will be made generally available over the next day or so.

Binary Search: A Cautionary Tale!

2011-03-07T10:03:00.002+08:00

Practically every developer knows what binary search is: a simple (indeed fundamental) searching algorithm which is an example of a natural divide-and-conquer strategy. Basically: starting with an already sorted array, compare the middle element of the array with the value we want to find. If the values are the same we are done, else if the array element is larger, repeat in the remaining lower half of the array, else if the array element is smaller, repeat in the remaining upper half of the array.

Unsurprisingly, binary search is an often used interview question (although perhaps less so, due to increasing complexity and the proliferation of programming frameworks; I must admit I’ve never personally been asked about it in an interview). It is a basic technique that you can reasonably expect every reasonable candidate to know and can be implemented in just a few lines of code.

Despite binary search’s simplicity, it is easy to implement it incorrectly!

Jon Bentley, in his book Programming Pearls (a programming classic), wrote that in a course he ran for professional programmers, he asked the participants to code binary search and found that 90% failed to implement it correctly. If you asked 100 programmers to write an implementation of binary search a large number of them would be incorrect and many of those those that apparently worked would actually contain subtle flaws. Indeed, many published implementations of binary search are wrong:

“a study reported that it is correctly implemented in only five out of twenty textbooks.”

Indeed, Jon Bentley’s own implementation of binary search, published in the ‘Writing Correct Programs’ chapter of Programming Pearls, contained a subtle bug that remained undetected for over twenty years. Here is an iterative C# implementation, adapted from Bentley’s pseudocode, that contains the bug (can you spot it?):

public static int BinarySearch(int[] sortedArray, int valueToFind)
{
    int lower = 0;
    int upper = sortedArray.Length - 1;
    int m;
 
    while (lower <= upper)
    {
        m = (lower + upper) / 2;
        if (sortedArray[m] < valueToFind)
        {
            lower = m + 1;
        }
        else if (sortedArray[m] == valueToFind)
        {
            return m;
        }
        else
        {
            upper = m - 1;
        }
    }
 
    return -1;
}

…Scroll down…

The bug is in the mid-point assignment

m = (lower + upper)/2

which performed as a direct sum could lead to an integer overflow. It should be replaced by the identically equivalent safe expression

m = lower + (upper – lower)/2

This bug was highlighted in Chapter 1 of Algorithms for Interviews, IMO, a slightly misnamed book which could perhaps be better described as ‘Algorithms to make programmers think’, where the emphasis is on describing problems and getting the reader to attempt to solve them.

[Side Note: A quick check with Reflector shows that this is implemented correctly without the possible overflow flaw in .NET 4, in fact the developers have gone put of their way to make sure someone doesn’t introduce this bug by encapsulating the mid-point division in its own Method GetMedian()]

SQL Server: Optimizing tempdb Performance : Moving tempDB to a New Location

2011-03-03T07:49:00.001+08:00

The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with autogrowing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by pre-sizing tempdb. For more information, see Optimizing tempdb Performance and Capacity Planning for tempdb.

Optimizing tempdb Performance

Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload for the environment. This prevents tempdb from expanding too frequently, which can adversely affect performance. The tempdb database should be also set to autogrow, just in case of unplanned growth. But ideally, any tempDB expansions should be rare.
Put tempdb on a fast I/O subsystem. Use RAID 10 if there are sufficient directly attached disks available. Consider using directly attached Solid State Disks (SSD), such as a FusionIO drive.
Create multiple data files to maximize disk bandwidth. Using multiple data files can reduce tempdb contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create as many data files as 1/2 – 1/4 times the number of CPU cores. [Note that a quad-core CPU is considered to be four CPUs.]
Make each data file exactly the same size; this allows for optimal proportional-fill performance. Consider tuning on Trace Flag -T1116
Put the tempdb database on disks (physical spindles) separate from those used by user databases.

If you're seeing PAGELATCH (not PAGEIOLATCH) waits on tempdb, then you can mitigate these using trace flag 1118 and creating multiple tempdb data files. Paul Randal wrote a blog post debunking some myths around this trace flag and why it's still potentially required in SQL 2005 and 2008 - Misconceptions around TF 1118.

-- 1.Determine the logical file names of the tempdb database and current location on disk. 

SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO


-- 2.Change the location of each file using ALTER DATABASE.

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb.mdf', SIZE = 512MB, FILEGROWTH = 128MB);
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'D:\Logs\templog.ldf', SIZE = 64MB, FILEGROWTH = 64MB);
GO


-- 3.Stop and restart the instance of SQL Server. 
net stop "SQL Server" 
-- or 
net stop "SQL Server (namedinstance)"


-- 4.Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');

How to Write Without Writing

2011-03-02T17:52:00.001+08:00

I’ve long admired (perhaps even envied) Jeff Atwood’s ability to craft interesting, informative and thought provoking articles on his ‘Coding Horror’ blog. His recent post How to Write Without Writing is a great example why.

Over the last 6 years, I've come to believe deeply in the idea that that becoming a great programmer has very little to do with programming. Yes, it takes a modicum of technical skill and dogged persistence, absolutely. But even more than that, it takes serious communication skills:

The difference between a tolerable programmer and a great programmer is not how many programming languages they know, and it's not whether they prefer Python or Java. It's whether they can communicate their ideas. By persuading other people, they get leverage. By writing clear comments and technical specs, they let other programmers understand their code, which means other programmers can use and work with their code instead of rewriting it. Absent this, their code is worthless. [Joel Spolsky]

The ability to inform, entertain and present connected ideas is a wonderful skill.

Personally, I’ve always found writing difficult (though perhaps less so in the last 5 years).The main reason I started blogging was to improve my writing (and also to record things I find useful and tend to misplace!). If you set yourself a goal of writing a short blog post once a week, you are forcing yourself to write something. The more often you write, the more likely your writing skills will improve.

Some time ago I answered a question on StackOverflow: Should programmers be able to write clearly? I firmly believe the answer to that question is “Yes”. If you can’t communicate and express yourself clearly, how can you write correct code?

Powershell: Get SQL Server Default File Paths using SMO

2011-02-27T13:03:00.004+08:00

I recently needed to find the location of SQL Server’s default data file path in order to create multiple database data files as part of an automated production install. After looking at and discarding a few options that included reading the registry directly, SQL Server Management Objects (SMO) seemed a logical choice. Talking to one of my colleagues, Piers, whose Powershell wizardary has to be experienced to fully appreciate, we (well he!) fired up a Powershell GUI and we took a look at the methods available.

As an aside, if you are not aware of this ‘trick’ it’s worth explicitly mentioning:

In Powershell, first load the relevant assembly into memory (which in this instance is Microsoft.SqlServer.Smo):

  > [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null

Then, create an instance of the type you are interested in (the Server type):

  > $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server “servername”

and pipe the object instance default method output through Get-Member to list all the Events, Methods and Properties exposed:

  > $smoServer  gm

So having done that we found a property named DefaultFile which looked promising, but it just returned an empty string. After a bit of digging around, it transpires that it only returns a path if the current location is different to where the master DB is located, so here it is in Powershell:

function Get-SQLServerDefaultDataFilePath
(
  [string]$sqlServer = $(throw 'sqlServer is required')
)
{
   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null
   $smoServer = new-object Microsoft.SqlServer.Management.Smo.Server $sqlServer

   $str = $smoServer.DefaultFile

   # if DefaultFile property is empty, it means default path has not been changed
   if ($str)
      {$str}
   else
      {$smoServer.MasterDBPath}
}

Update: Piers pointed out that Books Online contains a useful section on programming tasks using SQL Server Management Objects (SMO)

Visual Studio Database Guide

2011-02-25T08:59:00.001+08:00

CodePlex is hosting an excellent whitepaper created by the Visual Studio ALM Rangers, containing guidance on working with Visual Studio 2010 Database projects:

Practical guidance for Visual Studio 2010 Database projects, which is focused on 5 areas:

Solution and Project Management
Source Code Control and Configuration Management
Integrating External Changes with the Project System
Build and Deployment Automation with Visual Studio Database Projects
Database Testing and Deployment Verification
This release includes common guidance, usage scenarios, hands on labs, and lessons learned from real world engagements and the community discussions.

You can download the Visual Studio Database Guide here.

SQL Sentry Plan Explorer

2011-02-24T08:33:00.002+08:00

It may not be the greatest named application out there but SQL Sentry Plan Explorer is a free, lightweight, standalone tool that improves dealing with SQL Server query plans so much, you’ll wonder why it hasn’t been incorporated into SSMS (and let’s face it, viewing plans in SSMS really sucks!).

There are several ways to open a plan: In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into Plan Explorer. Or, save an execution plan from SSMS to a .sqlplan file, then open the file from Plan Explorer.

Download SQL Sentry Plan Explorer here.

Service Pack 1 (SP1) for Windows 7 and Windows Server 2008 R2

2011-02-24T08:23:00.001+08:00

Service Pack 1 for Windows Server 2008 R2 and Windows 7 has been released. Get it here.

TSQL: Round to Nearest 15 Minute Interval

2011-02-17T17:13:00.001+08:00

A colleague asked me if I had any TSQL to hand that would round down to a 15 minute interval and also round to nearest 15 minute interval. A quick search found several formulas but several had rounding errors. Here are both without any rounding errors.

declare @adate datetime = '2010/02/15 23:59:00'
 
-- The epoch, or start of SQL Server time: '1900-01-01 00:00:00.000'
-- select cast(0 as DateTime) as Epoch
 
-- Both these formulas will only work until '5983-01-24 02:07:00.000' !!
-- select dateadd(n, 2147483647, cast(0 as DateTime))
 
 
-- Round down to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,(DATEDIFF(n, cast(0 as DateTime), @adate)/ 15) * 15, cast(0 as DateTime))
 
-- Round to nearest 15 minute interval (avoiding any rounding issues)
select dateadd(n,((DATEDIFF(n, cast(0 as DateTime), @adate) + 7)/ 15) * 15, cast(0 as DateTime))

As noted, they have the limitation of working only until 5983 AD, but I figure I won’t be around!

How to Configure SQL Server 2005 to allow Remote Connections

2011-02-12T10:44:00.001+08:00

Just so I remember where this article is (also applies to SQL Server 2008 Express):

How to configure SQL Server 2005 to allow remote connections

Mark Russinovich: “Zero Day”

2011-02-11T08:08:00.002+08:00

Microsoft Technical Fellow and all-round Windows Internals expert, Mark Russinovich, has an upcoming book release. And this time it’s not a new edition of ‘Windows Internals’!

His fiction debut, Zero Day, is set in a world completely reliant on technology (sounds familiar), and surrounds the events of cyber infrastructure attacks released on a largely unprepared world.

How to give great presentations

2011-02-06T10:17:00.001+08:00

Assuming your user group is actually holding meetings :), User Group Support Services (UGSS) have released a series of videos on “How to give great presentations” aimed at first time speakers and anyone wanting to improve their skills (I know I need to):

This video series guides you through what you need to know to give your first presentation or to improve your existing presentation skills. You’ll learn how to choose the right subject for you, how to break subjects up so that your explanations and demonstrations are clear and understandable, how to construct your slide deck so that it covers essential subjects without sending your audience to sleep, why being nervous is completely normal and what you can do to make it manageable, tips and tricks for giving great demonstrations, how to prepare your laptop so that it does not fight you while you are presenting and finally how to deliver the presentation that you have worked so hard creating.

Microsoft SQL Server Compact 4.0 Released

2011-01-31T12:26:00.001+08:00

I must have missed this going RTM 2 weeks ago. You can download here.

SQL Server 2008 ‘…/sec’ Performance Counters

2011-01-26T13:00:00.001+08:00

SQL Server contains many Dynamic Management Views (DMV) for diagnosing server activity and health, including one for examining performance counters, sys.dm_os_performance_counters. Some of the counters in "sys.dm_os_performance_counters" with names ending with ‘/sec’ (such as like "Page lookups/sec", or "Page writes/sec") are known as "per second" counters. When you first come across this DMV you might not realise that the values for these counters are cumulative and require two measurements over an interval and then calculating the difference in those values based on the time between the two measurements.

Here is a simple TSQL snippet to perform this calculation for all per second counters in sys.dm_os_performance_counters:

-- Get first sample
SELECT *,  getdate() as sampletime, 
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters1
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name]; 

-- Wait for 10 seconds
WAITFOR DELAY '00:00:10'; 

SELECT *,  getdate() as sampletime, 
ROW_NUMBER() OVER (order by [object_name], instance_name, [counter_name]) as rowid
INTO #tempCounters2
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
order by [object_name], instance_name, [counter_name]; 

-- Calculate per second values
SELECT t1.[object_name], t1.instance_name, t1.[counter_name],
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime) 
FROM #tempCounters1 t1 join #tempCounters2 t2 ON t1.rowid = t2.rowid; 

DROP TABLE #tempCounters1
DROP TABLE #tempCounters2
GO

Here is a Table valued function version which accepts an interval parameter, and is easy to filter:

IF OBJECT_ID(N'dbo.PerSecondPerformanceCounters', N'TF') IS NOT NULL
    DROP FUNCTION dbo.PerSecondPerformanceCounters;
GO 

CREATE FUNCTION PerSecondPerformanceCounters(@intervalseconds int)
RETURNS @retCounterInformation TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null
)
AS
BEGIN 

DECLARE @tempTable1 TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null,
    [sampletime] datetime not null,
    [rowid] int not null
) 

DECLARE @tempTable2 TABLE
(
    [object_name] nchar(128) not null,
    [counter_name] nchar(128) not null,
    [instance_name] nchar(128) not null,
    [cntr_value] bigint not null,
    [sampletime] datetime not null,
    [rowid] int not null
) 

-- Take first sample

INSERT INTO @tempTable1
SELECT [object_name], 
       [counter_name], 
       [instance_name],
       [cntr_value],
       getdate() as sampletime, 
       ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name]; 

-- Wait for specified time

declare @start datetime = getdate()
while (1=1)
BEGIN
  if datediff(ss, @start, getdate()) >= @intervalseconds
     BREAK;
END 

-- Take second sample
INSERT INTO @tempTable2
SELECT [object_name], 
       [counter_name], 
       [instance_name],
       [cntr_value],
       getdate() as sampletime, 
       ROW_NUMBER() OVER (order by [object_name], [counter_name], [instance_name]) as rowid
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%/sec%'
ORDER BY [object_name], [counter_name], [instance_name]; 

---- calculate per second values (ignore fractional values)
INSERT @retCounterInformation
SELECT 
t1.[object_name], 
t1.instance_name, 
t1.[counter_name], 
(t2.cntr_value - t1.cntr_value) / datediff(ss,t1.sampletime,t2.sampletime) AS cntr_value
FROM @tempTable1 t1 
join @tempTable2 t2 ON t1.rowid = t2.rowid; 

RETURN; 

END
GO

Here is how you would call it:

-- Return only per second counters with non-zero values 

select * from PerSecondPerformanceCounters(10)
where cntr_value > 0 

-- Return non-zero counter values for specified objects only

select * from PerSecondPerformanceCounters(10)
where cntr_value > 0 AND
    (object_name = 'SQLServer:Buffer Manager' OR object_name = 'SQLServer:Access Methods')

Useful SSMS Shortcuts

2011-01-19T12:51:00.001+08:00

Here are a few useful SSMS shortcuts:

Ctrl-N	Open a new tabbed query window (with same credentials as current tab)
Ctrl–K + Ctrl–C / Ctrl–K + Ctrl–U	Comment / Uncomment selected text
Ctrl-Shift-U / Ctrl-Shift-L	Change selected text to upper / lowercase (wish it just affected keywords!)
Ctrl-L	Include estimated execution plan
Ctrl-K	Include actual execution plan
Ctrl+R	Toggle Results pane
F4	Show Properties window
Ctrl+Shift+M	Specify values for template parameters

Usual Suspects

Ctrl-F, Ctrl-H : Find/Replace

F5 or Ctrl-E : Execute query

SQL Server Transaction Log size does not match the size of the data being loaded

2010-12-28T09:13:00.001+08:00

I came across this cautionary tale from the SQL Server Development Customer Advisory Team, while looking for information on virtual log files (VLF):

The ISV created an empty database, setup a backup device, set the database recovery model to FULL, and then started loading data. They also intended to backup the transaction log occasionally to keep the log a manageable size. However, at the end of the load process although the database contained 92 GB of data, the log was only 1.2 GB. One would have expected that the transaction log in the case of FULL recovery would have been approximately the same size as the data.

After confirming the database was in FULL recovery mode, they tried BULK LOGGED and got the same result!

The reason? The database had never had an initial FULL backup!

From Books Online ‘Working with Transaction Log Backups’:

‘Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. […]‘

Because there was no initial FULL database backup, the database ‘effectively’ remained in SIMPLE recovery mode; SQL Server was truncating the transaction log automatically.

So, for FULL and BULK-LOGGED recovery models, create a full database backup right after creating a database. (This initial Transaction Log backup will be ‘empty’ because the database contains no data)

SQL Server Migration Assistant supports MySQL and Access to SQL Azure

2010-12-27T08:50:00.001+08:00

Old news, but the SQL Server Migration Assistant now supports migrating from MySQL and Access to SQL Azure. The latest SQL Server Migration Assistant is available for free download here:

Comparing the .NET Timer Classes

2010-12-26T17:43:00.001+08:00

Quick comparison table taken from an excellent MSDN article: Comparing the Timer Classes in the .NET Framework Class Library

	System.Windows.Forms	System.Timers	System.Threading
Timer event runs on what thread?	UI thread	UI or worker thread	Worker thread
Instances are thread safe?	No	Yes	No
Familiar/intuitive object model?	Yes	Yes	No
Requires Windows Forms?	Yes	No	No
Metronome-quality beat?	No	Yes*	Yes*
Timer event supports state object?	No	No	Yes
Initial timer event can be scheduled?	No	No	Yes
Class supports inheritance?	Yes	Yes	No

Changes to Microsoft Certified Master: Microsoft SQL Server 2008

2010-12-11T09:13:00.001+08:00

When I first heard about the SQL Server Microsoft Certified Master (MCM) certification, it immediately struck me as something worth doing. Then I found out how much it cost! Microsoft have recently announced changes to the original program. In order to pursue the SQL Server Microsoft Certified Master certification you still need the pre-requisites MCITP: Database Developer 2008 (Exam 70-450) and MCITP: Database Administrator 2008 (Exam 70-451). If you work with SQL Server, these 2 exams are relatively easy (unlike the rest of the MCM).

The Original Program:

Cost: $18,500 for 3 continuous weeks of onsite training, based in Seattle
Three written multiple-choice exams during the training
One final six-hour lab

This has some obvious drawbacks: US$18,500 is a considerable sum of money to find in one go (even with the strong Aussie dollar), and that’s in addition to taking 3 continuous weeks away from work. Plus the course only happened in Redmond, so add travelling costs as well.

The New Program:

Certification and training are separate
A written multiple-choice knowledge exam (88-970): cost US$500, taken at Prometric testing centres
A 6-hour lab exam (88-971): cost US$2,000, taken at selected secure Prometric testing centres

Microsoft will no longer provide training for the SQL MCM. Instead, training will provided by approved third-party vendors. I’ve just visited the Prometric site but cannot find any test centres for the MCM exams in Australia!

If the MCM is something you are interested in, Microsoft (in partnership with SQLskills) has made 40 hours of free introductory training available for the MCM program, which you can download here. These videos give an indication of the breadth of material you need to know for each area (rather than everything you actually need to know). There is also an excellent reading list put together by SQLskills at Microsoft Certified Master - SQL Server Online Training - Links and Study Aids

(Ref.: Brent Ozar’s blog, Paul Randell’s blog)

DevJam 2010

2010-12-05T16:18:00.001+08:00

I really enjoy our User Group’s end of year ‘DevJam’ events (despite the fact that it involves a bit of work to organise). It is great to see so many local developers willing to give up their time to present a talk. Around 50 people attended last Thursday’s event, so it was a relief when the pizza finally arrived. A crowd of developers deprived of pizza is a riot waiting to happen ;) !

It was a great line-up of topics with a nice mixture of content, and every presenter did a great job of informing and entertaining. Every talk received a good number of audience votes, but we have to have some winners in order to give out the prizes! The favourite talks (in order) were:

Adrian McGrath - WPF: Design Time Data
Alistair Waddell - A simple approach to test your data driven app
Michael Minutillo - Dynamic UI in WinForms

Thank you to all the sponsors who help make these events possible: Microsoft (for the venue and VS2010 MSDN door prize), UGSS for the pizza and refreshments, JetBrains, Joe Albahari, RedGate, TekPub, Scooter Software, DevExpress, TechSmith. [The presenter VS2010 with MSDN prize was donated by myself from the MVP program.]

Thank you to everyone who attended DevJam and meetings throughout the year. Special thanks to Dave Gardner who fetched and delivered the beer.

[ If you are interested in running one of these events at your User Group, I put together some notes here: Running a DevJam Community Event ]

Reminder: Perth .NET User Group, Thurs 2nd Dec: DevJam Community Event

2010-12-01T23:16:00.001+08:00

Here’s the line-up of talks for our end of year DevJam community event:

Mike Bain - Using SpecFlow and WatiN
Michael Minutillo - Dynamic UI in WinForms
Piers Williams – Rx:Reactive Framework
Alistair Waddell - A simple approach to test your data driven app
Dave Gardner - NuGet: The caramel goodness of open source in your projects
Darren Helsby - Asynchronous programming in .NET 5
Sam Ritchie - Ditching BizTalk for NServiceBus
Adrian McGrath - WPF: Design Time Data

Where and when is DevJam happening?:

DATE: Thursday, Dec 2nd, 5:30pm

VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

There will be pizza and refeshments provided. Not to mention some great prizes!…

SQL Server 2008: Using Hex Values in TSQL Scripts

2010-11-25T18:32:00.001+08:00

I was updating several TSQL scripts today containing ARGB values and thinking (not for the first time) that it was a shame I couldn’t use the Hex values directly (eg 0xFFB40000) rather than converting to the harder to manage base10 integer equivalent (-4980736).

The SQL Server 2008 release updated the CONVERT() function to be able to do just that!

CONVERT(varbinary(4), '0xFFB40000', 1)

The last Style parameter governs the the conversion.

From Books Online:

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

Value

Output

0 (default)
Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1.
If the data_type is a binary type, the characters 0x are added to the left of the result.

1, 2
If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.
If the length of the converted expression is greater than the length of the data_type the result will be right truncated.
Fixed length data_types that are larger then the converted result will have zeros added to the right of the result.
If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated.
If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits.
The characters 0x will be added to the left of the converted result for style 1.

ASP.NET Code Samples Collection

2010-11-23T22:28:00.001+08:00

If you missed this in the last MSDN Flash, J. D. Meier (Developer Guidance, Patterns & Practices) has a roundup of various ASP.NET code sample on his blog: ASP.NET Code Samples Collection

The ASP.NET Code Samples Collection is a roundup and map of ASP.NET code samples from various sources including the MSDN library, www.ASP.net, Code Gallery, CodePlex, and Microsoft Support.

Determine if your .NET Application has a Memory Leak

2010-11-13T17:21:00.001+08:00

You can use Perfmon to determine if your .NET application is leaking memory (managed or unmanaged).

Run Perfmon.exe and add these counters:

Process / Private bytes
.NET CLR Memory / # bytes in all heaps
.NET CLR LocksAndThreads / # of current logical threads

Run your application for a representative length of time and exercise its functionality.

If Private bytes is increasing but # bytes in all heaps is not, then unmanaged memory is leaking. If both both are increasing, then managed memory is leaking. If the # of current logical threads is increasing beyond what you expect, then thread stacks are leaking.

If Private Bytes is periodically jumping in 1MB increments with a corresponding increase in # of current logical Threads, a thread stack leak is the likely cause.

[Tracking it down is another matter! For that, one of the commercially available tools is invaluable: Memprofiler, ANTS Memory Profiler, dotTrace]

.NET CLR Large Object Heap

2010-11-07T18:00:00.001+08:00

I was recently talking to several colleagues about the CLR garbage collector, and the question of where the size of 85,000 bytes as the threshold for objects on the Large Object Heap (LOH) comes from (instead of the seemingly more obvious value of 65,535).

This value was determined as a result of performance tuning by the CLR garbage collector team.

One of the reasons you should try to keep your object allocation sizes below this value (and off the Large Object Heap) is because unlike the Generation 0,1 and 2 area, the LOH is not compacted.

An excellent reference for the LOH is here: Large Object Heap Uncovered

There were changes made in .NET 4.0 as to how the LOH performs, but according to this connect issue there are still improvements that could be made:

Based on the example provided, we were able to allocate nearly 23 times as much memory before running out of memory on the large object heap going from version 3.5 to version 4. That's not to say we are finished addressing fragmentation issues—we will continue to pay attention as we improve in future versions. In the .NET 4 release, we heard from customers that latency was a high priority. So that is where we have spent much of our focus. [Brandon Bray: Lead program manager responsible for the garbage collector in the .NET Framework]

.NET 4.0 introduced differences between the workstation and server versions of the garbage collector:

Comparing Workstation and Server Garbage Collection

Threading and performance considerations for workstation garbage collection:

The collection occurs on the user thread that triggered the garbage collection and remains at the same priority. Because user threads typically run at normal priority, the garbage collector (which runs on a normal priority thread) must compete with other threads for CPU time.
Threads that are running native code are not suspended.

Workstation garbage collection is always used on a computer that has only one processor, regardless of the <gcServer> setting. If you specify server garbage collection, the CLR uses workstation garbage collection with concurrency disabled.

Threading and performance considerations for server garbage collection:

The collection occurs on multiple dedicated threads that are running at THREAD_PRIORITY_HIGHEST priority level.

A dedicated thread to perform garbage collection and a heap are provided for each CPU, and the heaps are collected at the same time. Each heap contains a small object heap and a large object heap, and all heaps can be accessed by user code. Objects on different heaps can refer to each other.

Because multiple garbage collection threads work together, server garbage collection is faster than workstation garbage collection on the same size heap.

Server garbage collection often has larger size segments.

Server garbage collection can be resource-intensive. For example, if you have 12 processes running on a computer that has 4 processors, there will be 48 dedicated garbage collection threads if they are all using server garbage collection. In a high memory load situation, if all the processes start doing garbage collection, the garbage collector will have 48 threads to schedule.

If you are running hundreds of instances of an application, consider using workstation garbage collection with concurrent garbage collection disabled. This will result in less context switching, which can improve performance.

Ref.: Fundamentals of Garbage Collection

Reminder: Perth .NET User Group Meeting: Thurs Nov 4th, Production .NET Debugging with Piers Williams

2010-11-02T18:13:00.001+08:00

In this talk, you will learn how to debug your application in the field with this exploration of advanced debugging techniques for managed code applications. Hang, crash, leak or other – we will go beyond Visual Studio debugging and talk about replicating and isolating problems using Perfmon, WMI, WinDbg, and automating the diagnosis with PowerShell using PowerDbg.

TOPIC: Production .NET Debugging with Piers Williams
DATE: Thursday, Nov 4th, 5:30pm - 7:00pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Piers is a .NET Architect with Beacon Technology, and a contributor to PowerDbg (http://powerdbg.codeplex.com).

There will be a door prize of a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, RubyMine, IntelliJ IDEA), and several coupons to Tekpub's high-quality, online screencasts for programmers.

If you have any problems getting in to the venue, please call me on 0422 971 731

MSMVPS Site

2010-10-22T15:23:00.001+08:00

When I was awarded as a MVP, I had to decide whether to move my blog from blogger.com to the msmvps.com site. Conversations with a few MVPs about the site’s often downtime, convinced me to leave my blog where it is. Seems like it was a good decision; I haven’t been able to access the msmvps site for several hours today.

Upgrading from SQL Server 2000 to 2008

2010-10-22T14:51:00.001+08:00

I put together these short notes a while back while helping with an upgrade.

Side-by-Side Upgrade versus In-Place Upgrade

There are two fundamental strategies for upgrading:

In-place upgrade: uses the SQL Server 2008 Setup program to directly upgrade a SQL Server 2000 instance to SQL Server 2008. The older SQL Server instance is replaced.
Side-by-side upgrade: performs operations to move all or data and other database components from SQL Server 2000 to a separate SQL Server 2008 instance.

In this case, we opted for a side-by-side upgrade, where a backup of the current database on SQL Server 2000 was restored to a clean install of SQL Server 2008 R2 and then upgraded in place.

Before upgrade

Run the Microsoft SQL Server 2008 Upgrade Advisor for all databases (including system databases). Review any deprecated features identified by the upgrade advisor (e.g. Notification Services, backup with TRUNCATE ONLY etc.) (SQL Server 2008's TSQL parser is stricter and some things that ran in 2000 might fail in 2008, but it is mostly deprecated TSQL language features anyway)
If using a SAN: Benchmark the SAN using SQLIO and capture baseline data before performing the upgrade.
Run database/application performance benchmarks
Formulate and test a rollback plan
Ensure database consistency: Run DBCC CHECKDB on databases to be upgraded to ensure that they are in a consistent state before performing the upgrade.
Back up all important files: Back up all SQL Server databases from the instance to be upgraded (including master, model and msdb), data and transaction log files, as well as any application files, script files, extract files, etc. so that you can completely restore them if necessary.)
Perform transaction log backup for all user databases and disable all the jobs including jobs for taking full backups, jobs to defrag and other maintenance plans. [It is also recommended to take an image of your database server, in case you will have to rollback.]
If the operating system of the server is Windows 2003, ensure Windows service pack 1 is installed. This needs to be done on all the nodes of a cluster in a Failover Clustered environment.
Reserve enough disk space: Estimate the disk space required to upgrade SQL Server components, user databases, and any database files that might need to be created during the upgrade process. Might need two to three times the amount of disk space during the upgrade process.
Ensure enough space for system databases: Configure system databases (master, model, msdb, and tempdb) to auto-grow during the upgrade process, and make sure that they have enough disk space for this growth.
Transfer logins: Ensure all logins are transferred to the master database on the target server before upgrading the database.
Disable all start up stored procedures: The upgrade process will usually stop and start services multiple times on the SQL Server instance being upgraded. Stored procedures set to execute on start-up might interfere with the upgrade process.
Stop replication: Stop any replication and make sure that the replication log is empty at the start of the upgrade process.
Quit all applications: Certain applications, including all services with SQL Server dependencies, might cause the upgrade process to fail if local applications are connected to the instance being upgraded.

After upgrade

Review all logs
Revisit Upgrade Advisor recommendations
Rebuild Indexes
Reconfigure Log Shipping (if this was in use)
Transfer and verify SQL Agent jobs and maintenance tasks
Verify security settings: especially if there any cross server and/or cross-domain access privileges
Recreate any full text indexes removed from the databases. Re-populate Full-Text catalogs
Update the statistics for all the databases. This step may take some time depending on the size of database.
Change compatibility level: After initially restoring SQL Server 2000/2005 databases to SQL Server 2008, the compatibility level will still be 80/90, which corresponds to SQL Server 2000/2005 respectively. To take advantage of the new features of SQL Server 2008, you must change the compatibility level to 100.
Rebuild Indexes: To optimize query performance.
Update usage counters: In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, run DBCC UPDATEUSAGE on all databases following the upgrade.

Resources

Resources for Upgrading to SQL Server 2008
SQL Server 2000 to 2008 Upgrade Whitepaper – Microsoft
Using Upgrade Advisor to Prepare for Upgrades
The Microsoft SQL Server 2008 Upgrade Advisor can be downloaded from here. The Microsoft SQL Server 2008 R2 Upgrade Advisor is part of the SQL Server 2008 R2 Feature Pack

Perth .NET User Group: December DevJam

2010-10-11T16:43:00.001+08:00

For December's meeting of the Perth .NET User Group (Thursday, 2nd December), we are running another DevJam community event, where anyone can give a short talk about anything .NET software development related.

DATE: Thursday, Dec 2nd, 5:30pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

You can talk about any development topic but it must be between 5 – 10 minutes long:

Anything you’ve done.
Any new technology you’ve investigated.
Any tips and tricks, or tools that aid development
Basically, anything .NET software development related!

How do I enter? You must register by sending an email to mitch.wheat at gmail.com, no later than 20th Nov, with the word 'DevJam’ in the subject, stating your talk title in the body. Let me know whether you will be bringing your own laptop (preferable). Presenter places are limited, so it's first come, first served.

There are prizes for the three highest, audience voted talks (with the organisers’ decision as final in case of a tie break). The first prize is a copy of Visual Studio 2010 Ultimate with MSDN subscription, worth approximately $12,000! There will several other prizes which will be announced shortly.

There will be pizza and refreshments provided. If you have any questions, please send me an email.

SQL Server 2008 Service Pack 2 Released

2010-10-07T11:57:00.001+08:00

Service Pack 2 for SQL Server 2008 includes new compatibility features with SQL Server 2008 R2, product improvements based on requests from the SQL Server community, and hotfix solutions provided in SQL Server 2008 SP1 Cumulative Updates 1 to 8.

You can download here.

MVP Re-awarded

2010-10-05T10:47:00.001+08:00

I’ve been re-awarded as a Microsoft MVP. Once again, I would like to thank everyone for their support. It’s an honour and I will try to live up to it. Congratulations to all those who were also awarded.

Reminder: Perth .NET User Group Meeting: Thurs 7th Oct, 5:30pm: So what is the Windows Server AppFabric? with Bill Chesnut

2010-10-04T21:48:00.001+08:00

Join us at the Perth .NET User Group for a talk exploring what the Windows Server AppFabric is, looking at both hosting and caching, and learn how to configure and manage WCF and WF services in AppFabric. We will examine the diagnostic and tracing functionality while building high performance and fault-tolerant applications.

TOPIC: So what is the Windows Server AppFabric
DATE: Thursday, Oct 7th, 5:30pm - 7:00pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Bill is Mentor with SolidQ Australia located in Melbourne Australia. Bill started his career in IT some 25-years ago with the US Defence as an IBM Systems Programmer. He switched to the Microsoft Windows platform 14-years ago, and has been involved with Windows development ever since. Bill has worked on numerous enterprise projects using Microsoft C/C++, Visual Basic and SQL Server. Most recently, Bill has been driving various application integration projects using BizTalk Server (2000 – 2009) to connect a variety of Microsoft Business Solutions applications with other systems. Bill is also a Microsoft Certified Training and has been actively training BizTalk developers since the release of BizTalk 2004. Bill has been awarded as a BizTalk Server MVP since 2004 and is the leader of the Melbourne .Net User Group.

There will be a door prize of a choice of license from JetBrains (one of ReSharper , TeamCity Build Agent, dotTrace Profiler, RubyMine, IntelliJ IDEA), and several 30-day coupons to Tekpub's high-quality, online screencasts for programmers.

Also note: Bill will be presenting a talk on SQL Server virtualisation at the SQL Server User Group on Weds 6th Oct. Details here.

Reminder: Agile Talks with Dave Thomas

2010-09-30T09:11:00.001+08:00

Just a quick reminder that Tonight Dave Thomas is presenting two talks this evening (Thurs 30th Sept). If you haven’t already registered, please register here.

Windows Phone 7 Deep Dive Workshop

2010-09-29T21:43:00.001+08:00

It’s a fact that we sometimes miss out in Perth when it comes to events that make the circuit over East. Well not this time!

Nick Randolph is running a free, 2-day event (14 - 15th October) on developing for Windows Phone 7: Windows Phone 7 Deep Dive Workshop visiting Perth.

As well as founding the Perth .NET User Group, Nick has been working in the mobile space for many years and is a Microsoft Mobile Development MVP. Registrations are open at the above link. Get in quick, this event will fill up fast…

Perth .NET User Group Meeting: Thurs Oct 7th: So what is the Windows Server AppFabric? - Bill Chesnut

2010-09-21T22:18:00.001+08:00

Join us at the Perth .NET User Group, where Bill will explore what is Windows Server AppFabric, looking at both hosting and caching, and learn how to configure and manage WCF and WF services in AppFabric. We will examine the diagnostic and tracing functionality while building high performance and fault-tolerant applications.

TOPIC: So what is the Windows Server AppFabric?
DATE: Thursday, Oct 7th, 5:30pm - 7:00pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

Bill is Mentor with SolidQ Australia located in Melbourne Australia. Bill started his career in IT 25-years ago with the US Defence as an IBM Systems Programmer. He switched to the Microsoft Windows platform 14-years ago, and has been involved with Windows development ever since. Bill has worked on numerous enterprise projects using Microsoft C/C++, Visual Basic and SQL Server. Most recently, Bill has been driving various application integration projects using BizTalk Server (2000 – 2009) to connect a variety of Microsoft Business Solutions applications with other systems. Bill is also a Microsoft Certified Training and has been actively training BizTalk developers since the release of BizTalk 2004. Bill has been awarded as a BizTalk Server MVP since 2004 and is the leader of the Melbourne .Net User Group.

Agile Evening, Thurs 30th Sept with Dave Thomas

2010-09-21T22:16:00.001+08:00

Dave Thomas, a popular speaker and agile proponent has kindly agreed to present in Perth. He is a founding director of the Agile Alliance and is a Managing Director for Object Mentor a leader in XP, Craftmanship and Lean and Agile in the Large. Dave is doing the opening keynote for the Agile 2010 Conference.

YOW! Night: An Evening For Software Professionals
Sponsored by Perth.NET and YOW! Developer Conference Australia (formerly known as JAOO)

In recognition of the importance of active software professionals who always seek improvement - YOW! Australia in cooperation with IBM, and your local user groups is pleased to invite you to join us for YOW! Nights. Hear great YOW! speakers and network with other software professionals.

Provisional Schedule of Events (Thurs, 30th Sept)
5.30pm - 5.45pm Doors Open
5.45pm - 6.45pm Presentation #1: Envisioning Backlogs
6.45pm - 7.00pm Quick Break
7.00pm - 8.00pm Presentation #2: Functional Programming and NoSQL Data
8.00pm - Q & A

This event requires registration as we expect places to fill up fast. Please register here (where there are more details).

When Design Goes Bad, and The ‘One table to Rule Them All!’

2010-09-08T23:34:00.001+08:00

Via an interesting article from Joe Celko on bad practices in database design and the horrors of EAV (Entity-Attribute-Value) tables, there is a link to a great read on the ultimate ‘One table to Rule Them All’! I find this tale very pertinent as I once did a short stint at a company with ‘start’ in their name, that had created a design with a single table for static data (although no where near as bad as the one described in ‘Bad CaRMa’).

Perth Agile Night, Thurs 30th Sept with Dave Thomas

2010-09-06T22:11:00.001+08:00

Dave Thomas, a popular speaker and agile proponent has kindly agreed to present 2 talks in Perth. He is a founding director of the Agile Alliance and is a Managing Director for Object Mentor a leader in XP, Craftmanship and Lean and Agile in the Large. Dave is doing the opening keynote for the Agile 2010 Conference.

YOW! Night: An Evening For Software Professionals
Sponsored by Perth.NET and YOW! Developer Conference Australia (formerly known as JAOO)

Provisional Schedule of Events (Thurs, 30th Sept)
5.30pm - 5.45pm Doors Open
5.45pm - 6.45pm Presentation #1 Envisioning Backlogs
6.45pm - 7.00pm Quick Break
7.00pm - 8.00pm Presentation #2 Functional Programming and NoSQL Data
8.00pm - Q & A

This event requires registration as we expect places to fill up fast. Please register here

Reminder: Perth .NET User Group Meeting: Thurs 2nd Sept, 5:30pm: Test Driven Development – the Agile Perspective on Testing with Dwayne Read

2010-09-01T22:23:00.001+08:00

Please Note: Venue has changed to:

Enex100 Seminar Room, Level 3, Enex100, 100 St Georges Terrace

TOPIC: Test Driven Development (TDD) – the Agile Perspective on Testing
DATE: Thursday, Sept 2nd, 5:30pm - 7:00pm
VENUE: Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
COST: Free. All welcome

In this talk, we will look at how the discipline of being Test-Driven in an agile environment creates a before/just-in-time testing regime that drives the rest of the development (requirements, design and implementation). TDD is key to both optimizing (read eliminating) other “traditional” development techniques such as requirements specifications, design documents, etc as well as enabling other agile best practices such as short-iterations, architectural spikes and refactoring. We will look at the approach and what you need (people/roles, tools, process, mind-set, etc) to support this approach.