EXEC dbo.LongTermMemory__Archive

We are in the process of gradually upgrading our servers to CU4 now (build 3200) and here is the next announcement.

I know many people think they are messing about with SQL Server 2005 and that it has more bugs than other products but this is not the case.
Releasing this many hotfixes is all part of a new strategy of the "incremental servicing model" which is explained here.

So do not panic, updates like this will be released every 2 months.

Many UNIX people will probably say "boring" when they see PowerShell. UNIX has always supported great flexibility in scripting, of course they did not have a great GUI like Windows does. But as server management is getting more and more complex because of the large environments and many flavors of server software it was time to start thinking without the GUI because it does tend to work a bit slower as compared to a script enabled server and some things are just impossible using only a GUI. With scripts you can check for certain conditions and take actions automatically and appropriately based on these conditions.

The answer to this is PowerShell, a command line shell and scripting environment which will enable you to manage almost everything. One of its most powerful features is the ability to extend it with .NET components as well as being able to use the out of the box .NET Framework objects.

For Exchange 2007 they have first written the PowerShell layer and based the GUI entirely on these scripts. The GUI will also support a Script This Action (sound like we were ahead in SQL Server) which will create the PowerShell script for you.

At the customer we have something we call DCS (Data Conversion Scripts), basically it means going from one version of the database to the next version. We had a great .NET console application combined with some batch files which does this for us. Although this worked great we felt this was getting too complex to manage because we do parallel development and support different version at the same time. A new system was required using a simpler concept which my colleague killspid had proven was possible.

We started out with a batch file which used sql scripts with SQLCMD to perform the DCS task. Although this worked fine the lead DBA had a new requirement which needed more logic and was hard to do in a batch file or T-SQL. We decided to rewrite the DCS in PowerShell and I am very glad we did. Using a PowerShell script, SQLCMD and an XML config file we now support all the requirements and we have much more control than we did in the previous versions. The support for variables in SQLCMD has also proven itself very useful so do not underestimate the power of SQLCMD either.

I must admit the syntax and way of working is a bit peculiar at first because you have to lose the .NET mindset and get into the scripting mindset. If you are used to creating administrative scripts you will probably have less of a problem.

Tip: if you want to get rid of the output SQLCMD gives you in PowerShell add >$null behind it.
eg. sqlcmd -SmyServer -dmyDatabase -E -b > $null

Before SQL Server 2008 the most common way to pass data from one procedure to another would be by creating a temporary table, fill it with the required data and then call another procedure that uses that temporary table. Although this is not a disaster it does have some flaws like not being strongly typed. The weak typing of temp tables in this scenario make it a perfect spot for issues. There are other solutions using XML but these tend to be more complex than necessary for the 'simple' thing you are trying to do... pass structured data around.

SQL Server 2008 now supports something called Table Valued Parameters (or TVP) which can help you in these situations. TVP's make it possible to use a "table" as a parameter for a procedure. A couple of limitations apply, TVP's can only be READONLY in the procedure that define them as a parameter and they can only be used as an input parameter. Apart from this the same rules apply to TVP's as to table variables for example no DDL can be executed against a TVP and no statistics are kept for TVP's.

A little example will make it very clear.

--Create test table
CREATE TABLE myUsers
(ID int, UserName varchar(50), UserRole tinyint);
GO

--Create the required type -> strongly typed (not temp table)
CREATE TYPE UserRoleType AS TABLE
( UserRole tinyint );
GO

--Create procedure that takes the type as a parameter (READONLY is required)
CREATE PROCEDURE GetUsersInRole
        @UserRoleType UserRoleType READONLY
AS
SELECT UserName
FROM myUsers u
INNER JOIN @UserRoleType ut ON u.UserRole = ut.UserRole
GO

--Insert some test data (multiple inserts in one go, another new feature)
INSERT INTO myUsers
VALUES  (1, 'Wesley', 1),
                (2, 'Tom', 2),
                (3, 'Patrick', 2),
                (4, 'Jan', 3),
                (5, 'Bregt', 3)

--Throw in a new dmv to look at the type and check dependencies if you like
--SELECT * FROM sys.table_types
--SELECT * FROM sys.dm_sql_referenced_entities ('dbo.GetUsersInRole', 'OBJECT')
GO

--Lookup action
DECLARE @UserRoleType
AS UserRoleType;

--Lets use another new features (initialize var on declare!)
DECLARE @Admin tinyint = 1
DECLARE @PowerUser tinyint = 2
DECLARE @User tinyint = 3

--Add parameter values to the table valued parameter
--INSERT INTO @UserRoleType VALUES (1), (2)
INSERT INTO @UserRoleType VALUES (@Admin), (@PowerUser)

--Call stored procedure with specific type (remember the post is about table valued parameters)
EXEC GetUsersInRole @UserRoleType;
GO

--Clean up
DROP PROCEDURE GetUsersInRole
DROP TYPE UserRoleType
DROP TABLE myUsers

 

It is out there!  We are at the nice round build 3200.
Same as with CU3 there is also no download available.

http://support.microsoft.com/default.aspx/kb/941450

Windows Server 2008 offers a lot of new features that are very visible like Internet Information Server 7.0, a whole new virtualization model, Server Manager and many many more.  But being a SQL Server person I am interested in other things that have been improved.  These not so visible features are mostly I/O related since databases are a synonym for I/O.  Also note that many (if not all) of these changes also apply to Windows Vista.

I/O Completion Port improvement

Before Windows Server 2008 a thread that issued an async I/O also executed the I/O completion task causing a context switch which is expensive.  The I/O completion is now deferred until the thread pulls the I/O off the completion port preventing this context switch.  
 
I/O prioritization

This is completely new to the OS, not only do your processes have a priority but also the I/O's that are triggered by these processes have their priority.  This priority is based on the thread priority but can be set on the I/O itself too.

I/O Performance improvements

Using the GetQueuedCompletionStatusEx API call enables Windows to retrieve multiple completion port entries in one call. 

Prior to Windows Server 2008 the Memory Manager and I/O system limited every I/O request internally to 64KB,  larger requests were divided into multiple 64KB parts.  This limit has been removed so every request can now be issued as a whole, meaning less transitions to kernel-mode to send the I/O to your storage device.

NUMA improvements

When working with SQL Server 2005 and later you will most likely encounter more and more NUMA enabled machines.  In Windows Server 2008 more memory allocations procedures have been updated to be NUMA aware and I/O interrupts direct their completion to the node that initiated the I/O.  An addition to the NUMA APIs also allows applications to specify the preferred node.

SMB2

According to the people who designed SMB2 this should help performance of large file copies over the network with factor 30 to 40.  This will not really help your SQL Server performance but it will certainly get your backups to other servers faster.

For more information about kernel changes in Windows Server 2008 check out this great webcast by Mark Russinovich.