Is it a bird, is it an airplane...?

No it's the SQL Server 2005 Service Pack 1 CTP
Check it out here

GO GO GO

Foreign keys and indexes

A very common misunderstanding is that indexes are automatically created for foreign keys. While most of the times you need them they are NOT automatically created by SQL Server and before you ask... SQL Server NEVER created them automatically. When your database grows in size this may become a very big performance hit.

Your first task is to detect the foreign keys that have missing indexes. I've created the following query for SQL Server 2005. I have to test it some more to be sure it covers everything but on my little test database it seems to work fine. This is obviously a starting point and you can extend the query quite easily. I'm not checking if the index is too wide to be considered yet, but obviously none of you guys have such wide indexes right?

I'll give it a try on a bigger database tomorrow since it's getting kind of late now ;-)


SELECT fk.Name as 'ForeignKey', OBJECT_NAME(fk.parent_object_id) as 'ChildTable',
OBJECT_NAME(fk.referenced_object_id) as 'ParentTable', c.Name as 'Column', i.Name as 'ValidIndex'
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = fk.parent_object_id AND c.column_id = ic.column_id AND ic.index_column_id = 1
LEFT OUTER JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
ORDER BY ChildTable, ParentTable

DevDays 2006 - Clarification

I noticed on Tom Mertens' blog that my words may have been a little too harsh so I felt I should give a more nuanced response. I do not mean any disrespect and I know everyone works very hard to organize such an event. I was just expressing my own personal feeling at that time.I took the SQL Server track and maybe my comment on Tom's blog explains why I personally didn't like it too much. It could be the difference between IT Pro's and Developers which I am... a Database Developer :-)

Maybe this is the real problem in Belgium, Database Developers are kind of an unknown species and we don't see many projects that actually have people that do just that. I hope more and more companies will start to understand the importance of Database Developers so the DevDays can have a separate Database Developers track :-D

4GB of VAS under WOW, does it really worth it?

Another great post by the SQL Server Memory Guru: here

Also check out this suggestion coming from Greg Low (MVP). Although it is a feature in Oracle I still think it is a great idea :-) Being able to define your variable type as the column type gets rid of the sometimes painful type mismatches between parameters/variables and column data types. Having the wrong datatype can cause very odd behavior from the Query Optimizer. It also saves you a lot of work when you change the datatype of a specific column. Go VOTE :-D

From the suggestion: DECLARE @TradingName dbo.Customers.TradingName

DevDays 2006

So... I just attended the Belgian edition of the DevDays.
To be honest I wasn't too thrilled about the sessions, most of the content was too high level to be interesting. I think the technologies presented are already 'too old' to talk about them in such a way. Most of us freaky developers read too much about it or even worked too much with them already.

I was impressed by the visual effects in Vista though. It looks like something in a science fiction movie and I got the urge to grab the windows in a virtual reality kind of way :-) The upgrade-your-memory-by-using-a-usb-stick was really 'funky' too. Apart from that the deployment options seem quite impressive. Unfortunately I took the SQL track for the rest of the day so I didn't get a chance to see some more new features.

Another interesting thing is the creation of the Belgian SQL Server User Group (http://www.bemssug.org/). Finally some Belgian activity in the SQL Server world; the development side of SQL Server is often forgotten in Belgium so I hope these guys will have some interest in that side of the SQL Server story too.

GRANT EXECUTE

A lot of SQL Server projects are using stored procedures to control their query behavior (which I like because developers shouldn't be in control of this :p). In previous versions of SQL Server there was something that was kind of disturbing in terms of security. In order to grant execute permissions to a user you had to give permission to every single stored procedure the user needed. When you have 1000 stored procedures you wouldn't want to do this manually so there was the possibility to generate a script by using sysobjects or INFORMATION_SCHEMA.Routines but you need to keep the permissions up-to-date when new procedures were created.

SQL Server 2005 has a great solution to this: GRANT EXECUTE
You can now create a database user role that has execute permissions on all the objects in that database.

CREATE ROLE db_ICanExecute
GO

GRANT EXECUTE TO db_ICanExecute
GO

Fun with jobs and the public role and proxies

My colleague came across some interesting 'features' with SSIS in jobs.

• When you execute a job and change the data sources the information is NOT stored and the next execution resets the connection information. Using a config file is definitely the way to go :-)
• Proxy accounts with the public role assigned to it have a problem removing this role.

USE [master]
GO

CREATE CREDENTIAL [myCredentials] WITH IDENTITY = N'svcaccount',
SECRET = N'xxxxxxxxx'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'myProxy',
@credential_name=N'myCredentials', @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'myProxy',
@subsystem_id=11
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'myProxy',
@msdb_role=N'public'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'myProxy',
@name=N'public'
GO

This results in the following error message: "Msg 14523, Level 16,
State 1, Procedure sp_revoke_login_from_proxy, Line 63 "public" has not been granted permission to use proxy "myProxy"."

Also notice the @enabled parameter in the sp_add_proxy call. We can't find this in the interface but when this parameter is set to 0 the proxy is disabled. The problem is that you CAN select
disabled proxies as the account to run the job with but the job fails on execution.

Improving Data Security by Using SQL Server 2005

It's kind of old but I just stumbled upon this whitepaper about the encryption functionality in SQL Server 2005

Enjoy!

SQL Server 2005 Upgrade Advisor

If you are planning to migrate your SQL Server to SQL Server 2005 please make sure you run the SQL Server 2005 Upgrade Advisor. This free tool provided by Microsoft checks your current installation and detects the possible problems that will occur when you upgrade. The Upgrade Advisor also checks for some Best Practices on SQL Server and it's services.

It is able to check the following:

• SQL Server
• Analysis Services
• Notification Services
• Reporting Services
• Data Transformation Services

Do note that in order to check Reporting Services the Upgrade Advisor needs to be installed on the Report Server, the other services can be checked remotely.

You can choose to check your database(s), a trace file or a SQL batch. When the analysis has been completed a very clear report is created stating the current problems. These are expandable and contain more detailed information. Most of the details contain a link with even more detail on the problem and more important... how to fix it! When applicable the list of objects that are affected by this problem is available too.

This is really a great tool and I would advise you to ALWAYS run it if you are planning to upgrade a database. There are also other tools available when you want to migrate from Oracle. I think other database migration tools will be available too in the future.

SQL Server vs SAN

Recently we have been looking at some performance problems on some of our servers. I found some interesting information concerning SAN's so here is a little high level overview of things to remember.

• Format the Data drives with 64k sector size, the Log drive with at least 8k.
• Align the disks using diskpar in Windows 2003 (diskpart for SP1+) or do this at SAN level if possible (10%-20% improvement in some studies).
• Use StorPort drivers for your HBA's. SCSIPort was designed for direct attached storage while StorPort is optimized for SAN I/O) - Info
• Choose RAID 10 over RAID5. Although certain SAN's have optimized RAID5 technology that reduce the overhead RAID10 is still the preferred level.
• Spread over as many disks as possible.
• Always separate your data from your log drives. Log is sequential while data is more random I/O, mixing these might cause log latency.
• Stress test your SAN with realistic file sizes.

Clustered indexes on GUIDs

Hmm... this is actually a debate that will go on forever I suppose :-) I've created a little test script to prove a specific point but do NOT think of this as a complete negative advise for clustered indexes on GUIDs. Every DBA should know the sentence 'it all depends' by heart and actually you should *sigh* when someone asks: "What is the best solution for the database?" without knowing the data, the usage of the data and the environment it will run in.

What I wanted to test was fragmentation because of random uniqueidentifiers. When you run this script you will see amazing high figures for the fragmentation factor after just 1000 inserts! On my machine this small set of inserts generated 96,84% fragmentation and 587 fragments.

SQL Server 2005 has a new feature that may ease your fragmentation pain and that is sequential GUIDs. This generates uniqueidentifiers but based on the previous uniqueidentifier. This is great if you still want to use GUIDs but want to get rid of it's biggest strength and drawback which is it's random nature. When you change the script to use sequential GUIDs the fragmentation drops to 0.95% and 6 fragments. This is a quite spectacular drop. However, it is not always possible to use this because I know a lot of you are generating GUIDs on the clientside or middle tier making this unavailable since a sequential GUID can only be used as a default.

Fragmentation does have a lot of negative impact such as reduced scan speed and more pages are needed in memory because the data is spread over a lot of different pages. Is this ALWAYS a bad thing? Well there is something called a hotspot meaning all actions are concentrated on a single 'spot'. This may reduce performance but has been greatly reduced by the introduction of row locking. How many inserts are needed to create a hotspot? Well... it all depends :-) You can use the link from my previous post where a nice script has been provided to detect hotspots and lock contention using the new DMV's.

SET NOCOUNT ON
GO

CREATE TABLE myFragmentation
(myID uniqueidentifier)

GO

DECLARE @i int
SET @i = 0

WHILE @i < 100000
BEGIN
    INSERT INTO myFragmentation (myID) VALUES (NewID())
    SET @i = @i + 1
END
GO

SELECT * FROM
 sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID('myFragmentation'), NULL, NULL, 'DETAILED')
GO

CREATE UNIQUE CLUSTERED INDEX myCI ON myFragmentation (myID)
GO

DECLARE @i int
SET @i = 0

WHILE @i < 1000
BEGIN
    INSERT INTO myFragmentation (myID) VALUES (NewID())
    SET @i = @i + 1
END

SELECT * FROM
sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID('myFragmentation'), 1, NULL, 'DETAILED')

DROP TABLE myFragmentation

Index information using DMV's

Wow, this is a GREAT post by the Customer Advisory Team.
http://blogs.msdn.com/sqlcat/archive/2006/02/13/531339.aspx

SSIS Variables in Execute SQL Task

When trying to use an input variable in my Execute SQL Task I was getting the following error:"Parameter name is unrecognized." and the package obviously failed.

After some playing around I noticed that it had something to do with the difference in variable mapping declaration based on your connection type.

Based on your connection type you have to choose the correct name in your Parameter Mapping window.

Here is a little overview (credits go to Kirk Haselden):
OLEDB takes ? in the query and a number as name (0, 1, ...)
ODBC takes ? in the query and a number as name (1, 2, ...)
ADO takes ? in the query and a @Variable as name
ADO.Net takes @Variable in both the query and the name

Also make sure to choose the correct datatype for the parameter!

Transaction Log behavior

Recently I've been asked to investigate the migration possibilities of our Reporting Services environment to SQL Server 2005. Up until now everything has been really smooth to migrate and no real issues have come out of the process.

Yesterday I noticed that our BULK INSERT tasks were taking a lot longer as opposed to the SQL Server 2000 runtime. I am testing this on the same server with the exact same import files and database settings so there has to be a reason why it is taking twice as long as before. Off to investigate!

While checking all possible bottlenecks I came across a big difference and this was the Transaction Log size. While it is sized at 2GB and remains that size on the 2000 instance (Simple Recovery) it grows to 12GB on the 2005 instance.

Adding an explicit TABLOCK to the BULK INSERT statement seems to help. I admit we should have included this in the first place :-) Minimally logged operations do have some prerequisites and this is one of them. Others are that the table is not being replicated, simple or bulk logged recovery model and there are some index restrictions you can check in the books online.

However, this does not explain the difference between the two versions as those rules apply to both versions.

More to follow!

Non-unique clustered index rebuild in SQL Server 2005

One of the big disadvantages of SQL Server 2000 non-unique clustered indexes was the fact that SQL Server generated a new uniqueifier whenever you would rebuild your index. Although the general guideline remains to create narrow, unique and ever-increasing clustered indexes SQL Server 2005 has a nice improvement over SQL Server 2000.

SQL Server 2005 no longer changes the uniqueifier when you rebuild it which is great news! You now have more control over when you want to rebuild your non-clustered indexes if your table has a non-unique clustered index. This is true for ALTER INDEX, DBCC DBREINDEX as well as CREATE INDEX WITH DROP_EXISTING.

Here is a little script to show this behavior.
I check the STATS_DATE because whenever your indexes are being rebuilt your statistics will be updated with a fullscan.

CREATE TABLE myUniqueifierTest

(myID int,

myChar char(200)

)

GO

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('X', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Y', 200))

INSERT INTO myUniqueifierTest (myID, myChar)

VALUES (1, REPLICATE('Z', 200))

GO

CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID)

GO

CREATE NONCLUSTERED INDEX myNCI ON myUniqueifierTest (myChar)

GO

WAITFOR DELAY '00:00:03'

ALTER INDEX myCI ON myUniqueifierTest

REBUILD

GO

--DBCC DBREINDEX(myUniqueifierTest, myCI)

--CREATE CLUSTERED INDEX myCI ON myUniqueifierTest (myID) WITH DROP_EXISTING

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 1 )

SELECT STATS_DATE ( OBJECT_ID('myUniqueifierTest'), 2 )

GO

DROP TABLE myUniqueifierTest

SSWUG Radio

Chuck Boyce (http://chuckboyce.blogspot.com/) has a daily session called "The Where Clause". He makes an overview of the interesting blog posts of the day and yesterday he mentioned mine :-)

Check it out: http://www.sswug.org/sswugradio/the_where_clause_02feb2006.mp3

Thanks Chuck.

Equijoin and search predicates in SQL Server 2005

In older versions of SQL Server a common optimization technique for equijoins with a criteria on one of the fields of the equijoin was to add the criteria for both tables.

eg.

SELECT p.myID FROM tblParent p
JOIN tblChild c ON p.myID = c.myID
WHERE c.myID > 3 AND p.myID > 3

Because we are talking about an equijoin one can conclude that the myID field should be > 3 for both tables if it was requested for one of the tables.

SQL Server 2005 however is a bit smarter than older versions and comes up with a correct query plan all by itself.

When executing the following query in SQL Server 2005 you will see in the query plan that SQL Server takes into account the equijoin with the correct criteria for both tables:

SELECT p.myID FROM tblParent p
JOIN tblChild c ON p.myID = c.myID
WHERE c.myID > 3

Notice how the the Seek Predicates for tblParent contains myID > 3 too.

Another little optimization that makes your life a little easier. The best part is that optimizations like this are for free, meaning no changes have to be made to your existing queries to benefit from this. There are a couple more optimizations like these, for example: statement level recompilation for stored procedures, caching of plans that use dynamic objects (like table variables), various tempdb optimizations, ...

As a side note do remember that the recommendations for tempdb in SQL Server 2000 are still valid for SQL Server 2005. For those of you that don't know them or have forgotten them:

• Avoid autogrowth
• Use as many files as there are CPU's (take into account the processor affinity setting)
• Equally size the files
  
  

Slow mass deletes

My favorite Lead DBA Patrick asked me to find out how we could delete millions of rows from a table in the fastest way possible. You guessed it... this too is linked to the archiving project :-)

I was inspired by a blogpost from Kimberly Tripp where she tested mass deletes extensively with all different kinds of indexing.

The conclusion was that deletes from tables with non-clustered indexes was a lot slower as opposed to tables with only a clustered index.

When I started testing the delete options I suddenly noticed that there was a lot of locking activity on TempDB. Freaky as I can get I needed to find out why this was happening.

So off to investigate!

I've created a small table with the following script:

SET NOCOUNT ON
GO
CREATE TABLE TestNCDelete
(myID int IDENTITY(1,1),
 myChar char(500),
 myVarChar varchar(500)
)

GO

DECLARE @i int
SET @i = 0

WHILE @i < 10000
BEGIN
    INSERT INTO TestNCDelete (myChar, myVarChar)
    VALUES (' ', REPLICATE('A', 500))

    SET @i = @i + 1

END

CREATE UNIQUE CLUSTERED INDEX CI_myID ON TestNCDelete (myID)
GO
CREATE INDEX IX_myChar ON TestNCDelete (myChar)
GO
CREATE INDEX IX_myVarChar ON TestNCDelete (myVarChar)
GO


--DROP TABLE TestNCDelete

Let's delete everything from this table:

BEGIN TRAN
    DELETE FROM TestNCDelete WITH (TABLOCKX)

--ROLLBACK

Check the active locks with sp_lock and there you go... lots and lots of extent locks on TempDB.

Now why is this happening?
It is actually very simple to find out... the query plan says it all!

After the Clustered Index Delete you will see a Table Spool/Eager Spool action for every non-clustered index. The description of this action is quite clear: "Stores the data from the input in a temporary table in order to optimize rewinds". This is followed by a Sort, an Index Delete, a Sequence and finally the delete is final.

You can imagine that these spools, sorts, ... can be quite intrusive when we are talking about 40 to 100 million rows.

Another mystery solved!

Fun with RAW Destination files

As I said before one of the hot projects is Archiving. Because it is the first time a functional archiving process will take place we are talking about a lot of data and a small maintenance window. All this makes performance a key factor.

How do you achieve high performance with SSIS exports?
Use the Raw File Destination.

We have tested a couple of parallel exports to Flat File and repeated this action to the Raw File Destination. The export process went from 31 minutes to 26 minutes and the file size decreased to an incredible 2/3 of the size the Flat File Destination took. Now this may not look like a big gain but all this was as easy as changing the destination type. Another problem is that we are reaching our max read performance. I am convinced that there are more tuning options available and will probably be exploring these tomorrow.

The import process is blazing fast but I will be tuning this even more tomorrow too... let's see what we learn from that. Stay tuned!

Fun with Lookups in SSIS

One of the current hot projects here is archiving (the database grows with +1GB/day). For one of the solutions we need two files from the same table based on a staging table. This is now done in 2 steps so I tried to figure out a way to read the table only once.

I noticed that the Lookup Transformation has an option to redirect a row when an error occurs. This enables us to separate the output to 2 separate files based on the lookup table.

I started testing and I was thrilled by the performance of these transformations. So off to do the 'real' work, a lookup table with 40.000.000 records (holding just a guid) to export a table with 120.000.000 records. Unfortunately I hit the first 32-bit limitation :-( Because of the massive amount of data SSIS is having trouble reading all the records in memory. I get up to about 38.000.000 rows and then the process stalls. After quite some time I get the following error:
0xC0047031 DTS_E_THREADFAILEDCREATE
The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

SSIS cannot use AWE memory so it depends entirely on the VAS memory meaning only 2GB (or 3 with /3GB enabled) on a 32-bit environment. If we were running a 64-bit environment all my worries would be over :-(

I could skip the cache or cache less data but then it becomes quite slow and the 'double' export runs better in that case.

Who knows... maybe tomorrow I will find a solution.