I am out at DevConnections this week and so far I have been impressed with the improvements and new features that are coming in the 2008 product line. I will post recaps of the sessions I attended as time allows...
- Data Warehousing in SQL Server 2008 by Christian Kleinerman – this session focused primarily on the new features and performance enhancements that were made in the SQL engine.
ETL Enhancements:
1. The new MERGE statement – this powerful new statement combines DML statements into one to allow for “UPserts” and “Deletes” between a specified source and destination.
MERGE tblDestination t
USING tblSources s
ON t.ID=s.ID
WHEN MATCHED
UPDATE SET t.Name=s.Name
WHEN NOT MATCHED
INSERT VALUES(s.Name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
2. Logging enhancements were made to allow for minimal logging. This level of logging keeps only what is needed to successfully rollback. According to there test significant performance gains where seen with this change.
3. Insert over DML – you know have the ability to do an insert off a DML statement with the OUTPUT clause.
INSERT INTO tbl
SELECT FROM (
<<MERGE STATEMENT WITH OUTPUT>>
) CHANGES
WHERE Action=’UPDATE’
4. New tabled-valued functions were attended to track and query changed data.
5. The data flow thread allocation was changed from static to dynamic assignment for operators. This improves parallelism performance and adds thread starvation prevention.
6. A new ETL lookup is being added to SSIS. The new lookup has an unrestricted cache size and the ability to persist cache.
7. Data Compression to shrink table sizes and improve query performance is being added. The compression can be configured at the table or index level and they have seen performance gains anywhere between 1.5x and 7x improvements. During this topic they Mr. Kleinerman discussed the tradeoff between memory and CPU usage when using compression. He also briefly covered how compression was implemented (I will go through later).
8. Enhancements were made to the designer to build ‘best practices’ into the tools suite.
Query and Analysis:
1. Partitioned Table Parallelism was improved through dynamic thread allocation.
2. SQL Server 2008 can support multiple bitmap filters to improve star join query processing.
3. GROUPING SETS where added to the sql toolbox
GROUP BY GROUPING SET (date, (state, city))
4. Changes were made to the MDX query processing for subspace computation. Since cubes are sparsely populated changes were made to minimize what is processed during computation.
5. The Reporting Services engine was completely rewritten for 2008. I won’t go into all the changes but there were a ton of them discussed (i.e. Enhanced visualizations, Word Rendering, etc).
Admin
1. Backups can not be run with compression
2. The entire AS backup storage system has been replaced
3. The Resource Governor basically allows workload to be differentiated and per-request limits to be set ( I plan on elaborating on this is a separate post).