As a follow-on to my recent SQL Server Magazine BI Blog posting on 5 DW things to keep in mind for SQL Server DBAs, I wanted to share something that I find very interesting about the SQL Server Fast Track Data Warehouse. If you are not familiar with SQL Server Fast Track, it is a reference architecture that you buy from Microsoft and/or your hardware vendor: HP, Dell, Bull or IBM and it includes the complete bill of materials including hardware for server and SAN, OS, software and SQL Server 2008 or 2008 R2. The architecture guidance is based on tuning SQL Server and Windows Server 2008 specifically for retrieving large chunks of data from the SQL Server database engine by essentially accomplishing these things with aligned hardware, storage and database: contiguous large blocks of data avoiding fragmentation at all costs, avoiding indexes and aiming for table scans …
Given the sequential nature of the I/O requests, the Fast Track reference configurations configure the storage array to optimize reading of data off the underlying drives, with the assumption that the data is not randomly pulled from the drives but read from in contiguous chunks of data. If the data is not fragmented and the queries are scanning ranges, then throughput is a lot higher with this configuration.
Now, in the posting that I wrote as part of our SQL Mag series on SQL Server BI for DBAs, I made the point of stating that I wanted to just pick 5 common areas of difference from SQL Server OLTP DBA focus points to a typical SQL Server 2005 or 2008/R2 data warehouse. Fast Track data warehouse with SQL Server is a different animal. I am going to blog much more on Fast Track and Parallel Data Warehouse (complete Microsoft data warehouse SQL Server appliance) over the coming months.
I wanted to share that because it is a valid approach that creates proven great performance with the Fast Track architecture. But in most reporting and data warehouses under the size of a target Fast Track implementation (10s of TBs), you will still need clustered & non-clustered indexes, look for missing indexes and I still recommend auto-grow on databases with proper initial sizing and pre-emptive monitoring.
Fast Track, BTW, sets your SQL Server to no auto-grow (except TempDB) on user databases and gives lots of guidance on how to avoid fragmentation, which is why auto-grow and indexing is so minimized in the design.