Category Archives: Best Practices

Two Presentations This Weekend at #SQLSaturday349 in Salt Lake City

I have the privilege of being selected to fill two slots this weekend at #SQLSaturday349 in Salt Lake City.  The venue is simultaneously hosting the Big Mountain Data event.

My talks are:

To the Cloud, Infinity, & Beyond: Top 10 Lessons Learned at MSIT

&

Columnstore Indexes in SQL Server 2014: Flipping the DW /faster Bit

The latter is one of my staples.  The former is a new presentation, a preview of my forthcoming delivery for PASS Summit14.

Register, see the schedule, or see the event home page on the SQL Saturday site.  I’ll look forward to seeing you here:

Spencer Fox Eccles Business Building
1768 Campus Center Drive
Salt Lake City, UT 84112

Kudos to Pat Wright (blog | @sqlasylum) & crew for their crazy efforts (pardon the pun) in coordinating the event.

TJay Belt (@tjaybelt), Andrea Alred (@RoyalSQL), & Ben Miller (@DBADuck), keep your peepers peeled—I’m on the way!

AdventureWorks 2014 Sample Databases Are Now Available

 

Where in the World is AdventureWorks?

Recently, SQL Community feedback from twitter prompted me to look in vain for SQL Server 2014 versions of the AdventureWorks sample databases we’ve all grown to know & love.

I searched Codeplex, then used the bing & even the google in an effort to locate them, yet all I could find were samples on different sites highlighting specific technologies, an incomplete collection inconsistent with the experience we users had learned to expect.  I began pinging internally & learned that an update to AdventureWorks wasn’t even on the road map

Fortunately, SQL Marketing manager Luis Daniel Soto Maldonado (t) lent a sympathetic ear & got the update ball rolling; his direct report Darmodi Komo recently announced the release of the shiny new sample databases for OLTP, DW, Tabular, and Multidimensional models to supplement the extant In-Memory OLTP sample DB. 

What Success Looks Like

In my correspondence with the team, here’s how I defined success:

1. Sample AdventureWorks DBs hosted on Codeplex showcasing SQL Server 2014’s latest-&-greatest features, including: 

  • In-Memory OLTP (aka Hekaton)
  • Clustered Columnstore
  • Online Operations
  • Resource Governor IO

2. Where it makes sense to do so, consolidate the DBs (e.g., showcasing Columnstore likely involves a separate DW DB)

3. Documentation to support experimenting with these features

As Microsoft Senior SDE Bonnie Feinberg (b) stated, “I think it would be great to see an AdventureWorks for SQL 2014.  It would be super helpful for third-party book authors and trainers.  It also provides a common way to share examples in blog posts and forum discussions, for example.” 

Exactly.  We’ve established a rich & robust tradition of sample databases on Codeplex.  This is what our community & our customers expect.  The prompt response achieves what we all aim to do, i.e., manifests the Service Design Engineering mantra of “delighting the customer”.  Kudos to Luis’s team in SQL Server Marketing & Kevin Liu’s team in SQL Server Engineering for doing so.

Download AdventureWorks 2014

Download your copies of SQL Server 2014 AdventureWorks sample databases here.

Third Party Applications and Other Acts of Violence Against Your SQL Server

I just got finished reading a great blog post from my buddy, Thomas LaRock ( t | b ), in which he describes a useful personal policy he used to track changes made to his SQL Servers when installing third-party products. Note that I’m talking about line-of-business applications here – your inventory management systems and help desk ticketing apps. I’m not talking about monitoring and tuning applications since they, by their very nature, need a different sort of access to your back-end server resources….(read more)

Top 10 DBA Mistakes on SQL Server. (Now with Prizes for Participation!)

Microsoft SQL Server is easier to administrate than any other relational database on the market. But “easier than everyone else” doesn’t mean it’s easy. And it doesn’t mean that database administration on SQL Server is problem free. And since SQL Server is constantly growing from small, home-grown applications, many IT professionals end up encountering issues that others had tackled and solved years ago. Why not learn from those who first blazed the trails of database administration, so that we don’t make the same mistakes over and over again. In fact, wouldn’t you like to learn about those mistakes before they ever happen? Watch the video to learn more!…(read more)

SQL Server 2014 Columnstore Indexes: The Big Deck

The History

Though Columnstore indexes were introduced in SQL Server 2012; they’re still largely unknown.  In 2012, some adoption blockers remained; yet Columnstore was nonetheless a game changer for many apps.  In SQL Server 2014, potential blockers have been largely removed & Columnstore is going to profoundly change the way we interact with our data.

I’ve been working with Columnstore Indexes since Denali alpha bits were available.  As SQL CAT Customer Lab PM, I hosted over a half-dozen customers in my lab proving out our builds, finding & entering bugs, & working directly with the product group & our customers to fix them. 

The Why

Why Columnstore?  If we’re looking for a subset of columns from one or a few rows,  given the right indexes, SQL Server has long been able to do a superlative job of providing an answer.  But if we’re asking a question which by design needs to hit lots of rows—reporting, aggregations, grouping, scans, DW workloads, etc., SQL Server has never had a good mechanism—until Columnstore.  Columnstore was a competitive necessity—our Sybase & Oracle customers needed a solution to satisfy what was heretofore a significant feature & performance deficit in SQL Server.  Our leadership & product team stepped up & provided a superb response.

The Presentation

I’ve delivered my Columnstore presentation over 20 times to audiences internal & external, small & large, remote & in-person, including the 2013 PASS Summit, two major Microsoft conferences (TechReady 17 & TechReady 18), & several PASS user groups (BI Virtual chapter, IndyPASS, Olympia, PNWSQL, Salt Lake City, Utah County, Denver, & Northern Colorado).

The deck has evolved significantly & includes a broad overview, architecture, best practices, & an amalgam of exciting success stories.  The purpose is to educate you & convince you that Columnstore is a compelling feature, to encourage you to experiment, & to help you determine whether Columnstore could justify upgrading to SQL Server 2014.

The Table of Contents

Here’s my deck’s ToC:

  • Overview
  • Architecture
  • SQL Server 2012 vs. new! improved! 2014
  • Building Columnstore Indexes
  • DDL
  • Resource Governor
  • Data Loading
  • Table Partitioning
  • Scenarios & Successes
    • Motricity
    • MSIT Sonar
    • DevCon Security
    • Windows Watson
    • MSIT Problem Management
  • Room for Improvement
  • Learnings & Best Practices
  • More Info

The Demos

I’ve included several demos, all of which are exceedingly simple & include step-by-step walkthroughs.

  • Conventional Indexes vs. Columnstore Perf
  • DDL
  • Resource Governor
  • Table Partitioning

Let me know if you have any questions.  In the meantime, enjoy!

Join Me on March 14 in Silicon Valley for a Full-Day of SQL Server Configuration Tuning Lessons

The secret ingredient in this full day seminar is using the benchmarking and load testing tools to visually see the performance impact of the various configuration choices that Kevin illustrates throughout the course….(read more)

SQL Rally Pre-Con: Data Warehouse Modeling – Making the Right Choices

As you may have already learned from my old post or Adam’s or Kalen’s posts, there will be two SQL Rally in North Europe.

In the Stockholm SQL Rally, with my friend Thomas Kejser, I’ll be delivering a pre-con on Data Warehouse Modeling:

Data warehouses play a central role in any BI solution. It’s the back end upon which everything in years to come will be created. For this reason, it must be rock solid and yet flexible at the same time. To develop such a data warehouse, you must have a clear idea of its architecture, a thorough understanding of the concepts of Measures and Dimensions, and a proven engineered way to build it so that quality and stability can go hand-in-hand with cost reduction and scalability. In this workshop, Thomas Kejser and Davide Mauri will share all the information they learned since they started working with data warehouses, giving you the guidance and tips you need to start your BI project in the best way possible―avoiding errors, making implementation effective and efficient, paving the way for a winning Agile approach, and helping you define how your team should work so that your BI solution will stand the test of time.

You’ll learn:

  • Data warehouse architecture and justification
  • Agile methodology
  • Dimensional modeling, including Kimball vs. Inmon, SCD1/SCD2/SCD3, Junk and Degenerate Dimensions, and Huge Dimensions
  • Best practices, naming conventions, and lessons learned
  • Loading the data warehouse, including loading Dimensions, loading Facts (Full Load, Incremental Load, Partitioned Load)
  • Data warehouses and Big Data (Hadoop)
  • Unit testing
  • Tracking historical changes and managing large sizes

With all the Self-Service BI hype, Data Warehouse is become more and more central every day, since if everyone will be able to analyze data using self-service tools, it’s better for him/her to rely on correct, uniform and coherent data. Already 50 people registered from the workshop and seats are limited so don’t miss this unique opportunity to attend to this workshop that is really a unique combination of years and years of experience!

http://www.sqlpass.org/sqlrally/2013/nordic/Agenda/PreconferenceSeminars.aspx

See you there!