Don’t forget “Instant File Initialization”

If you ever run into scenarios where a) your database files are not properly sized and b) you populate the database with gigabytes of data, performance will be degraded/reduced because you will need to wait for SQL to zero out the file while expanding it.

To mitigate this issue you have two choices. Either you size your files properly (which most DBAs and system owners don’t), or you enable “Instant File Initialization”.

I’ve used this on large implementations and it works great.

Documented here and here.

Connect BIDS 2008 R2 to a TFS 2010 server

 

• install Visual Studio 2008 R2.
• install VS 2008 Team Explorer
• install VS 2008 Service Pack 1, or reinstall.
• install the Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010

You might need to assign Port and a location to your TFS server, like this:

Useful code: Get the physical size for each table

-- Create the temp table for further querying CREATE TABLE #temp     (       rec_id INT IDENTITY(1, 1) ,       table_name VARCHAR(128) ,       nbr_of_rows INT ,       data_space DECIMAL(15, 2) ,       index_space DECIMAL(15, 2) ,       total_size DECIMAL(15, 2) ,       percent_of_db DECIMAL(15, 12) ,       db_size DECIMAL(15, 2)     ) -- Get all tables, names, and sizes EXEC sp_msforeachtable @command1 = "insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",     @command2 = "update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)" -- Set the total_size and total database size fields UPDATE  #temp SET     total_size = ( data_space + index_space ) ,         db_size = ( SELECT  SUM(data_space + index_space)                     FROM    #temp                   ) -- Set the percent of the total database size UPDATE  #temp SET     percent_of_db = ( total_size / db_size ) * 100 -- Get the data SELECT  * FROM    #temp ORDER BY total_size DESC -- Comment out the following line if you want to do further querying DROP TABLE #temp

SSIS: The Balanced Data Distributor

This little friend is worth looking at:

“Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. “

SQLCAT has described it further here.

Download here.

SSIS: deploy to file or MSDB?

This is a pretty good read.

Useful code: Finding records out of sequence

This piece of code might come in handy one day

http://www.mssqltips.com/sqlservertip/1664/using-cross-join-queries-to-find-records-out-of-sequence/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20111023

Useful code: Padding

On a recent project of mine, we wanted to present certain natural keys padded with zeros. So instead of having:

1234
12345
123456

..we now have

00001234
00012345
00123456

We used this simple code: