Microsoft has released a poster for download, where you can see all the system views for SQL Server 2008 and the relationships between them.
Microsoft also released the system views poster for SQL Server 2005 a couple of years ago.
You need a huge printer/plotter to print these…
Below is my real life story telling how easy it can be to find index candidates if you use your tools.
This weekend we deployed a new version of a medium/large solution to the production environment for one of our customers. Normally this is not a big issue, as we are always prepared to roll back to the previous version – the one currently in production. The deployment and configuration processes are well-prepared and highly automated to minimize faults and late-night-bug-hunting-frenzy.
After the deployment is complete we smoke test the setup to make sure that it is configured correctly and all is sound.
This weekend after the successful deployment the smoke test failed miserably. We received TimeOutExceptions all over. It was coming from the user repository service.
We decided to investigate a little before the decision of aborting the deployment of the new version and therefore rolling back to the previous version.
The steps to resolve the issue is what I want to share with you.
A quick look in the source repository showed that almost no changes to the user repository service – none that could cause TimeOutExceptions.
With the SQL Server Profiler it was easy to spot the cause of the exception.
This simple select statement was isolated and copied into the Management Studio to view the Actual Execution Plan. The execution plan did show much, but by execution the below statement which instructs the SQL Server not to execute the Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.
SET SHOWPLAN_XML ON
The SQL Server will include loads in information and one of these are suggested indexes which you will not see with the graphical execution plan in Management Studio. Look for the MissingIndex elements:
<missingindexes>
<missingindexgroup Impact="96.6222">
<missingindex Database="[DOIPEI]" Schema="[dbo]" Table="[OrganisationalUnit_Stack]">
<columngroup Usage="EQUALITY">
<column Name="[org_pkid]" ColumnId="2" />
</columngroup>
</missingindex>
</missingindexgroup>
</missingindexes>
By creating this single index on [DOIPEI].[dbo]. [OrganisationalUnit_Stack]. [org_pkid] the TimeOutExceptions disappeared, as the query no longer took minutes but milliseconds.
We were able to continue and successfully deploy the new version of the solution with only minimal disruption and a delay of only half an hour. Why the index where missing is still a mystery.
It makes all the difference in the world if you know the capabilities of your tools at hand.
I just found this list of free SQL Server tools that I want to share with you.
I use some of these already, but will check out the others too.
The latest version of NUnit Framework (version 2.4 and forward) comes with a new constrained-based assert model, which allows you to write complex assertions relatively easy with a new syntax. The old classical syntax for assertions with static methods still works and according to the NUnit blog there are no plans to deprecate the classical assertion syntax.
Assert.AreEqual(5, 5.0);
Assert.AreSame(person, person);
Assert.Greater(7, 5);
Assert.IsInstanceOfType(typeof(ArrayList), new ArrayList());
Assert.IsAssignableFrom(typeof(ICollection), new List());
StringAssert.StartsWith("Anders", "And");
CollectionAssert.AreEquivalent(col1, col2);
FileAssert.AreEqual(stream1, stream2);
The new constrained-based assert model uses a single method on the Assert class for all assertions:
Assert.That("Anders", new EqualConstraint("Anders"));
The second parameter is the type of assertions – here an equal constraint that works on all types of data – primitives, collections, streams etc.
NUnit comes with a number of constraints covering most scenarios, but also allows you to extend the model by developing custom constraint by realizing the IConstraint interface.
public interface IConstraint
{
bool Matches(object actual);
void WriteMessageTo(MessageWriter writer);
void WriteDescriptionTo(MessageWriter writer);
void WriteActualValueTo(MessageWriter writer);
}
If the syntax of constrained-based assert model seams a bit to complex and not very reader friendly, the NUnit team has implemented a range of syntax helper classes like so:
Assert.That("Anders", Is.EqualTo("Anders"));
Assert.That("Anders", Is.Not.EqualTo("Anja"));
Assert.That("Hello World!", Text.StartsWith("HELLO").IgnoreCase);
Assert.That("make me happy", Text.Contains("make"));
Assert.That(person, Is.SameAs(person));
Assert.That(null, Is.Null);
Assert.That(new object(), Is.Not.Null);
Assert.That("", Is.Empty);
Assert.That(new ArrayList(), Is.Empty);
Assert.That(myCol, Is.Unique);
Assert.That(7, Is.GreaterThan(5));
Assert.That(2.0d + 2.0d, Is.EqualTo(4.0d).Within(.000005d));
Assert.That(myPerson, Is.InstanceOfType(typeof(Person)));
Assert.That(new EqualConstraint(), Is.AssignableFrom(typeof(IConstraint)));
Assert.That(new int[] { 4, 5, 6 }, Is.All.GreaterThan(0));
Assert.That(new string[] { "abc", "bac", "cab" }, Has.All.Length(3));
Assert.That(new int[] { 4, 5 }, Is.SubsetOf(new int[] { 4, 5, 6 }));
The change of syntax separates NUnit from other unit test frameworks and therefore separates from the common approach of creating unit tests.
I like the new syntax – It is intuitive and easy to comprehend. All change is not, but it is simple rules of evolution. The notion of constraints is also found in NMock.