THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Erin Welker

SQL Server and Business Intelligence

My Top 10 list for SQL Server 2008

Long gone are the days when you can reasonably expect to know all aspects of SQL Server at a deep level.  When a new version starts to emerge, I try to put some scope around the features that I will dive more deeply into.  I'm publishing my list here so that like-minded SQL Server enthusiasts may become aware of a feature or two that was lost in that one-page Powerpoint slide you see in most of the presentations on SQL Server 2008.

Note:  I put together this list during the summer as an roadmap for personal testing with the CTP releases.  In December 2007, a whitepaper, "An Introduction to New Data Warehouse Scalability Features in SQL Server 2008", was released that includes a short writeup on each of these features, as well as some additional improvements to SSAS, SSIS and SSRS.  If you are interested in the types of features I mention below, I highly recommend you check out this whitepaper. 

My focus is on Business Intelligence and performance (particularly in terms of relational data warehouse performance).  With that said and in no particular order, here is my list of top 10 features  in SQL Server 2008, and why.  I plan to post more detail about several of these in the future:

  1. MERGE command - this is a new TSQL command that will allow you to combine an Insert with an Update command, sometimes referred to as an UPSERT.  This is particularly useful in loading a data warehouse.  You will no longer have to test for a row's existence in order to take one of two paths (INSERT or UPDATE, or even DELETE).
  2. Star Join - this could have a tremendous impact on queries in a relational data warehouse.  Data warehouse queries are characteristically performance hogs. Since a large percentage of data is usually selected, the query optimizer often cannot take advantage of indexes like it can with more selective OLTP queries.
  3. Change Data Capture (CDC) - this feature can be used in SQL Server 2008 data sources to automatically track changes in data that require a row to be re-sent to a data warehouse.  This makes ETL from a SQL Server 2008 data source far more efficient and straight-forward.
  4. INSERT INTO - no structural changes have been made to the statement, but minimal logging can be implement, much like BULK INSERT or SELECT..INTO, under the right conditions.
  5. Lookups in SSIS - though SSIS lookups were incredibly enhanced from DTS (where they were virtually unusable), the performance of this task has been tuned to improve performance and minimize resource utilization.  In lieu of blogging about this later, I'll refer to a blog post by Jamie Thomson who explains this feature in detail and far better than I could.
  6. Data compression - this is potentially huge!  First of all, compression is an option, so if if the CPU hit is an issue you can choose not to implement compression.  Data compression means more data on fewer pages, which has a domino effect on performance (more pages in memory, better memory utilization, and improved page life expectancy).
  7. Partitioning enhancements - there's an issue in SQL Server 2005 partitioning parallelism that affects queries on a few number of partitions but greater than 1.  If only one partition is queried, intra-partition parallelism is implemented effectively.  If greater than one, a single thread is used to process each partition, which results in under-parallelism with queries on just a few partitions.  This has been addressed in SQL Server 2008 (future post).
  8. Resource Governor - if you haven't seen the demo on this, you should.  You can implement resource governing rules that affect inflight queries.  One scenario I see for this is in environments where ETL occurs simultaneously with data warehouse queries, such as in a real-time environment.
  9. Backup compression - this will dramatically affect backup times for large data warehouses. This capability is currently available through third party vendors but some DBAs are currently unable to leverage these solutions due to company standards or budget constraints.
  10. Partition-aligned indexed views (IVs) - this allows for the use of IVs on fact tables.  Indexed views on partitioned tables is virtually unusable in SQL Server 2005 because of the requirement to drop and recreate dependent IVs whenever a fact table partition SWITCH is made.  I'll explain why I think this is such a big deal in a future post.
Published Sunday, January 20, 2008 12:21 PM by ErinW

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Hi Erin,

I realize you are focused on performance / DW, but you forgot about DMF.  This is going to be more powerful for administrator types than it seems to be getting credit for right now... and it can help you have a great impact on performance (e.g. in one set of clicks, making sure all servers across your enterprise have AWE enabled).  :-)

January 23, 2008 12:20 PM
 

ErinW said:

Thanks, Aaron - I thought I might receive one or two "you left this out" comments. :-)

I am glad you bring up DMF (I understand they've renamed this to "Policy-based Framework"?)  The ex, multi-server DBA in me finds the DMF feature to be beyond awesome!  However, this is a personal list of features that are highly relevant to what I (and others like me) do currently and I had to scope it down.    

I will take this as an opportunity to point out other whitepapers at http://www.microsoft.com/sql/2008/learning/whitepapers.mspx that focus on other SQL Server user roles that may be more relevant to readers.

Thanks again, Aaron!

January 23, 2008 12:59 PM
 

Denis Gobo said:

Erin,

I would add Spatial (I think it is going to be huge) and the new date datatypes. I know I will save a bunch of GBs by using date instead of datetime

>>Long gone are the days when you can reasonably expect to know all aspects of SQL Server at a deep level.

with every new version you learn more but know less of the total product, hey at least you won't be bored  ;-)

January 24, 2008 7:23 AM
 

anonymous said:

Agreed on the additional date datatypes.  DATETIME2 will be helpful as we load DB2 timestamps.

March 10, 2010 3:06 PM
 

David said:

Hi Erin, i know only sql server 2005 but this My Top 10 list for SQL Server 2008 is really good and can get more tips and info about the latest version. Thanks for sharing this info with us... Expecting more from you in this blog!!

http://godwinsblog.cdtech.in/2010/10/windows-nt-user-or-group-domainuser-not.html

December 28, 2010 6:43 AM
 

sdfffffff said:

sdfffff

June 15, 2011 5:33 AM
 

SUresh said:

Could u please give me the difference betweeen SQL Server 2005 and 2008 in the format of Word or PDF document...thnx

June 15, 2011 5:35 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement