Synonym

Perhaps a little known feature of SQL Server 2005 but it might come in handy when you are slowly changing your database to use schema's. Synonyms allow you to define an alias for your objects and this is very helpful when you are migrating to schema's.

By using synonyms you can change your object names while retaining your 'interface' to the application and this allows for a smoother and more phased migration.

A little example with a table but remember that this also works for stored procedures, functions, etc. and it is also possible to create synonyms for remote objects (linked server).


--Create a schema
CREATE SCHEMA SchemasRock
GO
--Create a table in the default schema
CREATE TABLE dbo.myTable
(ID int)
GO
--Insert a row
INSERT INTO dbo.myTable VALUES (1)
--Move the table to the new schema
ALTER SCHEMA SchemasRock TRANSFER dbo.myTable
--Insert another row (will fail!)
INSERT INTO dbo.myTable VALUES (2)
/*
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.myTable'.
*/
--Create a synonym to support 'old' table name
CREATE SYNONYM dbo.myTable FOR SchemasRock.myTable
--Insert another row
INSERT INTO dbo.myTable VALUES (2)
--Select the rows
SELECT * FROM dbo.myTable
SELECT * FROM SchemasRock.myTable
--Clean
DROP SYNONYM [dbo].[myTable]
DROP TABLE [SchemasRock].[myTable]
DROP SCHEMA [SchemasRock]

SQL Server 2005 SP2 Cumulative Hotfix 3175

We're at build 3175 with KB936305, the hotfix is not publicly available though.
For an overview of the hotfixes released after SP2 check out KB937137, it contains the list of fixes per build.

SQL Server 2005 Books Online (May 2007)

Back to the current release of SQL Server ;-)
Get the updated Books Online here.

Fun with the new MERGE statement

I found some time to play with CTP3 and checked out the MERGE statement. What you achieve with it may not be rocket science and was perfectly possible with a couple of IF statements but using MERGE gives you much cleaner code. It looks like Microsoft is investing a lot in 'cleaner' coding in SQL Server 2008, which I can only welcome with great enthusiasm.

A simple example will show you exactly what the MERGE statement does.

CREATE TABLE StagedData
(ID int, UserName varchar(20))
GO

CREATE TABLE RealData
(ID int, UserName varchar(20))
GO

INSERT INTO StagedData VALUES (1, 'Slava') , (2, 'Paul') , (3, 'Wesley')
GO

MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

UPDATE StagedData SET UserName = 'Kimberley' WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DELETE FROM StagedData WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO

DROP TABLE StagedData
GO
DROP TABLE RealData
GO

SQL Server 2008 - A first look

I took some time to look at the ''nifty" features in SQL Server 2008. There is a long list of new features shown in this webcast, but I doesn't look like all of them are already implemented in CTP3.

Here are some of the new features that are in this build and are quite nifty:

• MERGE, a very powerful statement to combine insert/updates and even deletes in a single statement
• Multiple values INSERT (eg. INSERT INTO myTable VALUES (1, 'SQL Server 2008 Rocks'), (2, 'It really does') - the example in the BOL seems to be incorrect)
• FORCESEEK query hint
• Table-valued parameters enables you to pass table variables to stored procedures or functions
• ALTER DATABASE SET COMPATIBILITY_LEVEL isof sp_dbcmptlevel
• Policy based management, this is an awesome feature that allows you to set policies on many configuration options and even naming conventions for your objects in a very flexible way (Declarative Management Framework)
• Change Data Capture, a built-in auditing mechanism
• Some new dynamic management views (sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_os_nodes, sys.dm_os_process_memory and sys.dm_os_sys_memory)

There are probably many cool features and I'll keep you informed when I stumble upon them.

*EDIT*
A great list of new features can be found here.

Is it a bird, is it an airplane? No it's the first CTP of SQL Server Katmai... I mean SQL Server 2008!

Check it out here, the first public CTP of SQL Server 2008.

Go go go :-)

SQL Server Katmai Part II

Francois Ajenstat announced in a podcast interview - at the very first Microsoft Business Intelligence Conference - that the first CTP will probably be released in the coming month!

Get your Virtual Servers ready ;-)

By the way, you can find webcasts of this conference on the resources page.

3GB PAE AWE

I once posted some information about the 3GB, AWE and PAE settings.
Here is some more detailed information about these settings. Definitely worth reading!

Database Snapshot vs Procedure Cache

I recently stumbled upon a KB article (KB917828) that points out some situations in which your procedure cache is cleared.

Since one of our architectures for reporting involved database snapshots I was suprised to read that your whole procedure cache is cleared when you drop a database snapshot. This may not be as dramatic as it sounds but you should closely watch the impact on your environment when the cache is cleared.

Other situations are:

• A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
• A database has the AUTO_CLOSE database option set to ON. Maintenance operations are performed, such as the DBCC CHECKDB operation or a backup operation. When the operations finish, the background task tries to close and shut down the database automatically.
• You run several queries against a database that has default options. Then, the database is dropped.
• You change the database state to OFFLINE or ONLINE.
• You successfully rebuild the transaction log for a database.
• You restore a database backup.

SQL Server Katmai

It is announced to be available in 2008.
I'm ready for a beta/ctp version ;-)

More information here and here.

Datasheet here.

Visual Studio 2005 Team Edition for Database Professionals - SR1 (CTP)

For those of you who are lucky and are already working with VSDBPRO, there is a service release CTP available for download (KB936202). Make sure you read the release notes before you install.

Gert Drapers also posted some additional information on his blog.

One of the biggest enhancements, in my opinion, is the support for SQLCMD variables. This enables great flexibility in your deployment scripts.

Cumulative waitstats per session/query

We were troubleshooting a long running batch job when we suddenly realized that there is no easy way to get cumulative wait information for a specific session or query. With the rich information already available to us in SQL Server 2005 this might be a great addition to the product.

I decided to launch a suggestion on Microsoft Connect. This great initiative makes it possible for everyone to give suggestions to improve Microsoft's products.

Check out my suggestion here (and while you are there do vote ;-))

A couple of interesting KB articles

Today we were installing SQL Server 2005 on our new Windows 2003 Server (SP2) cluster when we ran into the following issue: "The file C:\Windows\Microsoft.NET\Framework\Meaningless_string\mscorlib.tlb could not be loaded", the solution to this can be found in KB918685.

Dirk G from MCS Belgium was nice enough to point us to another hotfix concerning the USERSTORE_TOKENPERM issue, KB933564 contains more information about this.

KB935897 has some information about the new "Incremental Servicing Model" for SQL Server.

So what's up with all the SP2 post fixes?

It's getting quite cumbersome to understand all the releases after SP2 for SQL Server 2005. Bob Ward from PSS decided to clarify this on his blog.

Aaron Bertrand also clarifies the differences between a GDR and a QFE here.

WinDbg and SQL Server minidumps

Today a couple of our test servers started to act very weird. Some jobs were going incredibly slow and eventually most of them crashed causing a mini dump to be generated. The error log pointed to I/O problems with latch timeouts but there were no details on which file exactly it crashed.

The SAN and NAS management is done by another team and we had to pinpoint the problem to convince them that it was really an I/O issue. So how exactly do you do this on a server with several databases and processes?

Off to my favorite developer Bregt, I knew he had good knowledge of WinDbg so he could help us pinpoint the issue. Lucky for me Bregt is a nice guy and he explained to me how I could find the cause of the crash.

Download WinDbg which is part of the debugging toolkit provided by Microsoft.

Start WinDbg (Run As Administrator in Vista) and configure the symbols as follows (CTRL-S)

The url can be found in the WinDbg help and the local symbols path is one you choose (and create yourself). Don't forget to save your workspace when it asks you to (thanks again Bregt).

After you have done this you can open a crash dump (CTRL-D), this might take a while depending on the size of the dump.

Type ~kv in the command line, this gives you the call stack with the memory addresses.

Using ALT-5 you get a memory viewer, here you can check the content of the memory for the last call. It is possible however that nothing is returned for the memory address since not everything is dumped with a minidump. In our case this was "ntdll!ZwCreateFile+0xa" and this directed us to the exact filename and it was a server side trace that was being saved to a NAS drive.

How cool is that?

I feel like digging a little deeper in WinDbg when I find some time. A colleague of us (Hans De Smaele) has written a great document on this topic.

SQL Server 2005 SP2 - Another hotfix

This time we are at build 3159 with KB934459.
Those darn maintenance plans sure are causing mayhem lately :-)

Detach/Attach migration

In our effort to consolidate some environments I had to migrate a reporting database from SQL Server 2000 to SQL Server 2005. One of the easiest ways of migrating is a simple detach and attach (sp_attach_db is deprecating and being replaced by CREATE DATABASE FOR ATTACH in SQL Server 2005).

After the attach some options have to be changed like the compatibility level, page verify checksum, etc. and as a last step I issued an sp_updatestats.

When I started the test run I noticed that it was taking very long so I started issuing some sp_who and sp_lock statements. The output showed that the same locks were being held for an unreasonable amount of time until the point that tempdb ran out of space. I had 8GB of tempdb space and the query only had to return 3 rows from a couple of tables that had less than 100 rows. I decided to run the query again and monitored tempdb usage with the new DMV sys.dm_db_file_space_usage, I immediately saw tempdb allocations rise at an incredible rate.

I decided to drop all the existing statistics and issued the same query again, this time it immediately returned the 3 rows without any trouble and without any unusual tempdb usage.

Very odd behavior but this shows the importance of statistics and the apparently subtle differences between SQL Server 2000 and SQL Server 2005. Dropping the statistics is easy in development/test but for production environments you might want to script the existing statistics and recreate them. For some unknown reason the update statistics was not as effective as expected after an attach from a SQL Server 2000 database.

*UPDATE*
Things like this are always a bit more complicated than they seem at first. After the import of the new set of data for today the query went back to the bad behavior. A strange effect was that the query worked well if I took a date range of a couple of months but failed with an interval of 1 day. The key here was of course the fact that because of a bad estimation the query plan was completely different in the two cases. Creating one index on a single field out of the ten tables that were involved in the query resulted in a different query plan being chosen and completely solved the problem.

Yet again a couple of important things are proven:

• The differences in the query optimization engine between SQL Server 2000 and SQL Server 2005 make it important to test every query
• Statistics are of great importance to the query optimizer so watch them carefully and make sure they are kept up-to-date
• Having proper indexes is an absolute necessity for every environment
• Any change, how small it may seem, could have great impact on your environment

Views and the smart Query Optimizer

In general I don't really like to use views. Although they have their advantages I often see it turn out bad in projects. The main reason is that people start to use complex views that were not written for their specific functionality and many tables get touched that have nothing to do with the result they are trying to retrieve.

The query optimizer is quite intelligent when it comes to resolve the query plan for a view. It is smart enough to ignore the tables you do not need to resolve your query.

CREATE VIEW SmartView AS
SELECT i.ItemTitle, i.ItemLink, u.UserName
FROM tbl_item i
JOIN tbl_user u ON i.OwnerID = u.UserID
GO

SELECT ItemTitle, UserName FROM SmartView

SELECT ItemTitle FROM SmartView

You can see SQL Server is quite intelligent when it comes to optimizing views. Unfortunately it still cannot prevent every situation of misuse. Some people have no problem of using SELECT * FROM SmartView and use only the ItemTitle field for example. When your view uses many (large) tables this might cause a lot of grieve on your database. One of the most extreme examples I have seen in real life is a SELECT from the biggest view in the project, causing millions of reads, where the result that was being used was the rowcount - which could actually be retrieved by counting the rows in one table. The key is obviously having a good DBA to review every query ;-)

Windows Server 2003 Service Pack 2

It was very close indeed :-)

Download here - fixlist here

I/O counters in Task Manager

One of my freaky habits is to check the I/O counters in the task manager. Whenever I'm doing I/O intensive stuff I like to quickly look at the MBs processed.

Whether these counters are updated or not is controlled by a registry setting (see below). You can set the value to 0 to disable it or 1 to enable it. Disabling it gives a little performance boost since Windows doesn't have to take care of the counters but on test systems I like to enable it. SQLIOSim also requires it to be enabled to get some of it's results.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\I/O System\CountOperations

By the way, Windows Server 2003 Service Pack 2 (fixlist) is getting very close. Keep an eye out for it because it contains fixes to improve performance of SQL Server 2005 under heavy load. More details will follow.