SQL Server
One of the dangers of conducting regular SQL training courses is that you get tied up in the sample AdventureWorks bicycle company database. It’s more realistic than the original Pubs (publishing not drinking) and the Northwind databases in that some of the tables have 100,000+ rows but it pales in comparison to a typical customer database. So when I am asked about large database performance, my favorite web site is http://www.SQLCAT.com . The SQL Customer Advisory Team is a great Microsoft initiative that has a team of experienced consultants that go into high profile customer sites using the latest release of SQL Server and help them to be successful. The by-product is that the consultants publish SQL Server white papers and best practices that are based on real customer solutions using the largest databases. Many times the SQL Server developers get involved too, so the product itself benefits over time. Microsoft can tap into this resource when encouraging new customers too. It’s a win-win situation for us all that use SQL Server. I was teaching a Business Intelligence class at a customer site recently in Denver, Colorado. Outside it was minus 14 degrees Fahrenheit. Inside we had some heated discussions on comparing SSIS with other ETL tools such as Informatica. SQL Server 2000 DTS was always rated as a light-weight ETL tool but SSIS has been re-written using .NET and has some great scalability features. But using my rinky-dink laptop with AdventureWorks was not a proper test. In fact performance was poor mainly because I had everything loaded on one machine including Active Directory, Sharepoint, Exchange, SQL Server, SSAS, SSRS and SSIS. Definitely a no-no with 2GB RAM. Must build some new VMs over the holidays. SQLCAT.com to the rescue! Listed is their TOP 10 SQL Server Integration Services Best Practices. For instance, it is clear that SSIS should run on a separate machine, even separate from SQL Server itself for maximum performance. Partitioning recommendations, network, CPU and Memory too and how to identify particular bottlenecks in your SSIS packages. Great stuff. http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx In the article, they state that SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second. That’s pretty impressive and constitutes an unofficial world record, faster than Informatica apparently. Unfortunately, there is no official TPC benchmark for ETL yet. Something for the wish list for 2010 me thinks. Happy Holidays! Brian
