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.

Myth Busters

Euan started a nice category on his blog... Myth Busters (love that show btw).
Instead of trying to sink a boat with a self constructed shark Euan will try to explain some SQL Server Myths (and probably bust a couple - 1 down... many to go)

Here

SSIS Performance Tips

Not that I am an experienced SSIS developer but I'll try to give some general guidelines to optimize the performance of bulk import operations.

• Try to get your flat files sorted by the clustered index of the destination table
• Use the 'Fast Parse' option; there are some limitations for date (time) and integer data
• For OLEDB Connections use the Fast Load setting
• Use a SQL Server Destination if possible (can be up to 25% faster!)
• Use BULK_LOGGED recovery model for your SQL Server destinations
• The MaxConcurrentExecutables package setting defines how many tasks can run concurrently (default number of logical cpus + 2)
• Change the EngineThreads property of a task (defaults to 5 but could support more on multi-processor servers - testing is the key)
• Run parallel import steps if possible
• Use the right isolation level for your package and container
• Import into a heap from multiple files in parallel and then recreate the indexes (clustered first then the non-clustered indexes)

Long term memory

Today I convinced a colleague to start blogging by telling him that I consider it my long-term memory.

He is the tool guru aka Mr. Concept (and a bit .NET guru too :p)

http://www.bloglines.com/blog/General1

Treeview AfterSelect and changing focus

When you want to set focus to another control in the AfterSelect method of a treeview there seems to be a problem with the behavior. Focus is set to the control but is then returned to the Treeview.

Forcing the Focus asynchronously seems to solve this little issue.

How?

Add a delegate:

private delegate bool _methodInvoker();

Now in the AfterSelect handler add the following code:

myControl.BeginInvoke(new _methodInvoker(myControl.Focus));

Kept me busy quite some time :-(

Missing Indexes Feature

Especially for my favorite colleague Tom :-)

SQL Server hold information about missing indexes in a couple of dmv's.

sys.dm_db_missing_index_group_stats: holds information about the possible performance improvement when implementing a group of indexes
sys.dm_db_missing_index_groups: holds information about possible groups of indexes
sys.dm_db_missing_index_details: holds details about the missing indexes
sys.dm_db_missing_index_columns: holds the list of columns that could use indexes

This is a great feature but as always it has some limitations.
From the BOL:

• It is not intended to fine tune an indexing configuration.
• It cannot gather statistics for more than 500 missing index groups.
• It does not specify an order for columns to be used in an index.
• For queries involving only inequality predicates, it returns less accurate cost information.
• It reports only include columns for some queries, so index key columns must
  be manually selected.
• It returns only raw information about columns on which
  indexes might be missing.
• It can return different costs for the same missing
  index group that appears multiple times in XML Showplans.

Remember that it is not the holy grail but yet again a nice addition that guides you in the right direction. Performance remains the responsibility of the DBA that has to make the right decision for the specific workload and configuration available. Proper modeling and indexing remains a key factor in high performance applications that squeeze out the last drop of hardware-power.

Microsoft Minded?

I guess I'm pretty Microsoft minded and with good reason so far.
But some people really go well beyond being 'MS minded' :)

Here