PCIE SSD Update – Optimizing SQL Backup Performance

In my last blog entry (http://blogs.msdn.com/b/microsoftbob/archive/2010/11/06/the-potential-of-ssd-for-millions-of-iops-on-increasingly-larger-databases.aspx), I shared the throughput numbers backing up from 2 Fusion-IO devices to a 4-spindle 15K SAS Raid-10 device.  I also shared the numbers of backing up to NUL device, and was surprised they weren’t better especially given the advertised throughput of 700 MB/Sec for a standard SLC Fusion-IO IoDrive. …


The potential of SSD for millions of IOPS on increasingly larger databases

NOTE: There is an update to this article at http://blogs.msdn.com/b/microsoftbob/archive/2010/11/08/pcie-ssd-update-optimizing-sql-backup-performance.aspx regarding the throughput I was able to achieve on my own configuration with the Fusion-io backup process.  Through some parameter changes on the backup command, was able to double the throughput of the backup to Raid-10 and more than triple speed to the Null device….


Back again, Dealing with Storage-aligned Index oddities

I’m back after being on leave for past several weeks in case you wonder why I haven’t posted.  To quote Joy Gresham from the move ShadowLands “I wasn’t dead, I was just in America (fill-in-the-blank)” This was one of my favorite movies that had so many wonderful quotes (http://www.imdb.com/title/tt0108101/quotes and http://www.moviequotes.com/repository.cgi?pg=3&tt=96601)- Some of you may…


Indexes and SSDs and Random versus Sequential Access

If you’re one of the dozen or so people who follow my blog, you’re aware that I’m using SSDs for my doctoral research project involving simulations to find solutions to complex data correlation problems.  I finally have started to get down to the level of really looking at various query plans.  What I’ve found is…


SSIS – Chunking number of inserts with a For Each container

My main customer utilizes SSIS for some SQL and Oracle integration requirements.  One challenge they’ve had is timeouts from the Oracle side.  We’ve tried everything to eliminate the issue in terms of extending timeout values within SSIS, but the problem appears to be that for very large data transfers into Oracle, Oracle simply gets tired…


Using Checksum to create random data sets

Here’s a quick tip if you don’t want to try to use random and figure out seed, etc.  If you’ve got a lot of columns in a table, it is pretty likely that the checksum for all those data values are going to be random across your primary keys. I recently needed to create a…


Managing Index Fragmentation and Compression

For today, I recycle bits from a couple of excellent authors in the SQL community.  The first script is for optimizing compression.  I “borrowed” this from Paul Nielson, author of the SQL Bible.  You can visit his SQL Server bible site at http://www.sqlserverbible.com/.   It includes a link for sample scripts, which is where I pulled…


Using the SQL Scripting via the GUI for Admin Changes

If you’re like me, you like GUIs even for SQL Server, they make it easier to do things quickly without having to remember a lot of syntax.  But GUIs within Management Studio ultimately have to fire off TSQL to perform the changes.  And not knowing exactly what it is generating, short of running a profiler…


I love SSD

Been working with 2 PCIE Fusion cards testing out performance for my SQL Server simulation database.  I also have a raid set of 3 Intel X25-E SSDs.  This is a technology to take seriously.  The folks at Fusion have been most helpful.  I will be putting together more results over next couple of weeks, but…