Cumulative update package 2 for SQL Server 2008 is available. Here is what is fixed in this cumulative update

You can get information on how to obtain this cumulative update here: http://support.microsoft.com/default.aspx/kb/958186/en-us

What Are the RML Utilities?

The Microsoft SQL Server support team uses several internally written utilities to ease the work that is related to a typical customer support case.  One such utility suite is the Replay Markup Language (RML) Utilities for SQL Server.  Database developers and system administrators can use the RML Utilities to work with Microsoft SQL Server 2000, Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

You can use the RML Utilities for SQL Server to perform the following tasks:

• You can determine the application, the database, the SQL Server login, or the query that is using the most resources.
• You can determine whether the execution plan for a batch is changed when you capture the trace for the batch.  Additionally, you can use the RML Utilities for SQL Server to determine how SQL Server performs each of these execution plans.
• You can determine the queries that are running slower than before.

After you capture a trace for an instance of SQL Server, you can use the RML Utilities for SQL Server to replay the trace file against another instance of SQL Server. If you also capture the trace during the replay, you can use the RML Utilities for SQL Server to compare the new trace file to the original trace file. You can use this technique to test how SQL Server behaves after you apply changes. For example, you can use this technique to test how SQL Server behaves after you do the following:

• You install a SQL Server service pack.
• You install a SQL Server hotifx.
• You update a stored procedure or a function.
• You update an index or create an index.

The RML Utilities are very useful if you want to simulate application testing when it is impractical or impossible to test by using the real application.  For example, in a test environment, it may be difficult to generate the same user load that exists in the production environment.  You can use the RML Utilities to replay a production workload in a test environment and assess the performance impact of changes such as an upgrade to SQL Server 2008 or application of a SQL Server service pack.  Additionally you can use the RML Utilities to analyze and compare various replay workloads.  This kind of regression analysis would otherwise be a difficult process that you would have to perform manually.

Version 9.01 of the RML Utilities for Microsoft SQL Server has been released. This release of the RML Utilities provides support for SQL Server 2000, SQL Server 2005 and SQL Server 2008. Additionally this release of the RML Utilities for SQL Server contains important software updates, enhanced features and reports, and performance and scalability improvements.

To download the current web release of the RML Utilities for SQL Server visit the following Microsoft Web site:

RML Utilities for SQL Server (x86) - http://www.microsoft.com/downloads/details.aspx?FamilyID=7edfa95a-a32f-440f-a3a8-5160c8dbe926

RML Utilities for SQL Server (x64) - http://www.microsoft.com/downloads/details.aspx?FamilyID=b60cdfa3-732e-4347-9c06-2d1f1f84c342

Take a look at this code which I found a while back in a stored proc

Do you see what is wrong? It uses five select statements to accomplish something which can be done in one. I would do something like this instead.

Let’s take a look at another example.

What we want to do is display a row of counts for 4 xtypes from the sysobjects table, here is an example

s	u	p	c
19	143	74	6

Have you ever seen code like this that does that? I have!

That code will do a select 4 times against the table
A better way would be to do this

In SQL server 2005/2008 you can use the PIVOT operator, here is what the query would look like

If you can think of any other examples feel free to leave a comment

A lof of time your index will get framented over time if you do a lot of updates or insert and deletes.
We will look at an example by creating a table, fragmenting the heck out of it and then doing a reorganize and rebuild on the index

First create this table

Now insert 50000 rows

Now create this index

Now let us look at some data by using the sys.dm_db_index_physical_stats DMV. Keep this query handy, we will run it many times

(Result Set)

That is good, almost no fragmentation. Let’s change that shall we?

Okay, now you can see that the index is completely fragmented, we are also using 955 pages to store the data instead of 451
(Result Set)

There are two ways to fix fragmentation, one is to reorganize the index and the other is to rebuild the index. Reorganize is an online operation while rebuild is not unless you specify ONLINE = ON, ONLINE = ON will only work on Enterprise editions of SQL Server.
Here is how to do a reorganize

(Result Set)

As you can see after the reorganize(DBCC INDEXDEFRAG for you SQL Server 2000 folks) fragmentation levels dropped to less than 3 percent.

Just for fun let’s also rebuild(Drop and recreate the index for you SQL Server 2000 folks) the index

(Result Set)

As you can see the rebuild made fragmentation almost 0

Here are two differences between REBUILD ONLINE = ON and REBUILD ONLINE = OFF
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

Of course you will not run rebuild/reorganize manually for every index in your database, Michelle Ufford from the SQL Fool blog has a nice post with just a script which can do this automatically, you can find that here: Index Defrag Script

This post is also on our SQL Server Admin Hacks

I saw that some people are hitting our site with a search for how to create a sorted view in SQL Server 2008.

You all know that in SQL Server 2000 you can create a view and use TOP 100 PERCENT with ORDER By and it will be sorted. Since SQL server 2005 that doesn’t work anymore. I actually never understood the need for sorted views to begin with, how hard is it to do something like this

Not hard, I guess pople want the convenience of opening the view in SSMS and it is sorted ‘correctly’
There is a way to get this to work in SQL server 2005, there is a hotfix that will ‘fix’ this but you have to run in 2000 compatability mode.
The link to the fix is here: FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order

Now let’s get started with the code
Create this table

And create the view

Now do a select from the view

(result set)
1
3
4
5
2
7
9
6

Oops it is not sorted
Let’s try something else, we will use 99.99 percent

Run the select against the view

(result set)
1
2
3
4
5
6
7
9

look at that, magic! It works

Let’s try another way by using the max value of an integer

Run the select against the view

(result set)
1
2
3
4
5
6
7
9

And bingo, it also works.

Now, just because this works right now it doesn’t mean that it will work after you apply the next hotfix or service pack. Why not doing this instead

That will always work and you don’t have to deal with unexpected results down the road