I will do anything to reduce the IO load on large databases, which is why I am a great fan of compression in SQL Server 2008. However, another way of reducing IO is to ask SQL Server to format new data extents without writing zeroes out to all pages. If your database takes a new extent (by default it is 10% of the file size, which typically isn't good) SQL Server will want to write binary zeroes to all the pages to ensure that some old data isn't hidden in your database. SQL Server does not need the binary zeroes, it is done to ensure data security. Someone else may have deleted a file and perhaps they don’t not want you seeing the data.

Very often, the data security is not an issue and you would like to avoid the writing of binary zeroes. This can be achieved (on NTFS drives) by ensuring that the account running SQL Server has SE_MANAGE_VOLUME_NAME privilege. You can grant this privilege in User Rights Assignment on Windows 2003, XP or above. To do this, grant the SQL Service account Perform volume maintenance tasks local security rights. Alternatively, if the SQL service account has administrator privileges, it will automatically have this privilege. That's all you need to do to avoid having SQL Server write all those binary zeros. By the way, SQL Server always needs to write binary zeroes to the log files, we are only avoiding binary zeroes on the data files.

If you don't want to grant this privilege to the SQL service account, and you want to avoid the IO load during peak times, I suggest that you extend (manually or automatically) your datasets during an off peak times (i.e. before SQL does it for you).

One of the most noticeable tasks affected by binary zeros is database restores. If you watch the restore progress, it will typically not move off 0% until it has written out the entire data files with binary zeroes. After writing every data page, it will then write the backup files over the data pages, during which time, you will see the progress percent increase. So writing binary zeroes can approximately double full database restore times.

Hope that helps you reduce IO load and increase SQL Server performance.

For real-time SQL demonstrations, including Perfmon, which is writing over 300 records every second, see http://RichardLees.com.au/Sites/Demonstrations

Last week I was spotted by SproutDaily.com Some of the best photographers in Sydney take pics around Manly Beach and post them out to everyone registered at SproutDaily.com.

Just can't do anything in Manly without everyone knowing.

BI solutions need to be continually importing/updating data from external source systems. I believe that a good BI solution will have automatically correcting load and process jobs. What I mean by that is if a package fails (such as a power or system failure) the application will automatically correct itself next time it runs. In the old days, we used to create "restartable" jobs, where, after a failure, the operator would manually intervene to restart the job with specific parameters. I think there is little excuse nowadays to have applications that require manual intervention after a failure. They should be designed in such a way that they “know” where they are up to and continue, or reprocess.

For example, my Permon application (Perfmon Dashboard) has a job that runs every 180 seconds to transform and load the new records, and incrementally process the OLAP cube. Every now and again the machine is restarted for some reason and the Perfmon update process could be at any point when it is stopped. However, the job will automatically run from the right place, next time it runs. It will know whether there is 3 minutes or 10 minutes of data to process, by using high water marks in the database. In this way, I have no operational overhead in running this application. It just looks after itself. It is the same for the weblogs BI solution on Weblogs Dashboard. If your BI solution requires manual intervention after a failure or an error, get someone else in, like myself, to fix it up to run automatically without manual intervention.

Now having said that, it is often useful to be able to pass parameters to an SSIS package. (I just needed to mention automatically correcting jobs above, as some developers make packages restartable,to help with manual correction, when it should be automatic.) For example, you might want to have the ability to ask the SSIS package to conditionally run one task. This can be achieved quite easily by assigning the Disable property on the task (container etc) using Expressions on the task to set it to a parameter. To pass the parameter it is simply a matter of adding /SET "Package.Variables[DisableBackup]";False to the cmd for the job in SQL Agent.