It’s the second tuesday of the month and you know what that means! T-SQL Tuesday time! If you don’t know what it means, T-SQL Tuesday is a monthly blog party of the SQL Server community – started by Adam Machanic (blog|twitter) – where everyone is invited to write about a common topic. If you count my old blog posts as well, this is my 10th contribution to T-SQL Tuesday!
This month’s T-SQL Tuesday is hosted by Andy Yun (blog | twitter) and its subject is about defaults:
This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!
I’d like to focus my contribution around the defaults of SSIS (what else?). When I create a new package, I’d like to change the following defaults:
- the ProtectionLevel of the package. The default is EncryptSensitiveWithUserKey. This is the most horrible default they could have chosen. Because the user key is used, it means that no one else except the original author can decrypt the sensitive bits of the package. In other words, everyone must enter the passwords at every connection manager that doesn’t use Windows Authentication. You can still run the package though, if you can live with a few extra warnings. The better option would have been to chose DontSaveSensitive (which is my preferred choice). It doesn’t save any sensitive data at all, forcing you to use config files (pre-SSIS2012) or parameters (since SSIS 2012). It even gets more annoying in the project deployment model, because projects suddenly have a protection level as well and the packages must have the same protection level. So, when you create a new project using the project deployment model, immediately change the protection level to DontSaveSensitive.
- the DefaultBufferMaxRows and the DefaultBufferSize of the data flow, if any is present. The first property sets how many rows can go into a buffer. The default is 10,000 rows, which is pretty small, unless you have gigantic wide rows. Crank it up to at least 30,000 or more. Chances are your package suddenly runs 50% faster or better. The second one specifies the default buffer size, which is 10MB. SSIS estimates the maximum length of a row by taking the maximum sizes of all columns (so choose your data types wisely). Multiply that with the DefaultBufferMaxRows property and you get a good idea of how big your buffer is about to be. If you have wide columns, better choose a bigger buffer size. I usually go to about 30MB per buffer, since we’re 2015 anyway and memory is cheap.
But it’s not only packages that need changing, but also the defaults of the SSIS Catalog. With SSIS 2012, a centralized storage and management repository for SSIS is added to the database engine, with the SSISDB database as the back-end. There are definitely some defaults that need changing:
- the recovery model of the SSISDB database. This is by default set to Full. Change it to Simple, right now. Chances are you are not taking backups of the SSISDB and certainly not transaction log backups. This means the log of the SSISDB database keeps growing until your hard drive fills up. And it can grow quite quickly, as the SSIS Catalog logs a lot of operations of the SSIS packages.
- speaking of logging, you should change the retention period of the logs as well. By default it’s 365 days, which is quite long for some SSIS logging. I usually keep the SSIS logs around for 50 days. You can change this in the Catalog properties by right-clicking SSISDB in the Integration Services Catalogs node.
- while you are at it, also change the maximum number of versions per project. Do you really need 10 versions? I keep at most 5.
If you have a busy server, you might want the SSIS clean-up job to only run in the weekend.
All right, that’s all I had in mind for SSIS & defaults. Thanks again Andy for hosting!