Categories
Uncategorized

Column­-Stores For Wide and Sparse Data

Over the weekend I read a paper titled Column­-Stores For Wide and Sparse Data by Daniel J. Abadi.  Below is a quick summary of the points made by Abadi followed by some thoughts / application for SQL Server’s ColumnStore index. Wide Tables & Column “Elimination” I’ve already discussed (in a previous post) why ColumnStores are […]

Categories
Uncategorized

70-463: Determine whether you need a star or snowflake schema…

If you’re a Kimball purist like myself, the title of this post should give you pause.  I came across this phrase when reviewing the SkillsMeasured section for the 70-463 exam.  At the time, the only scenarios I could come up with that would justify using a snowflake schema were the following: Short on Disk Space? […]

Categories
Uncategorized

Sequence vs Identity – Performance Comparison

Recently, as part of an effort to support a few database design choices, I spent a bit of time researching the performance characteristics of the new sequence objects available with SQL Server 2012. The main objective was to gain an understanding of how they stacked up performance-wise with the IDENTITY property…which is the predominant design pattern […]

Categories
Uncategorized

Generating a Database Clean Up Script

Below is a script I recently used on a project to dynamically generate and execute the statements needed to clear all the data out of a database.  It is heavily borrowed from a blog post by Nikola Ilijev.  I just added the parts pertaining to the dropping and re-creating of the ColumnStore index objects and […]

Categories
Uncategorized

SQL Server 2012 Certification Path

Regardless of your opinion on Microsoft certifications, when you work for a Microsoft partner company, you are typically encouraged to get certified in your area of expertise and stay current.  So now that SQL 2012 has been out for a few months, implementations are picking up speed, official exams have been released from beta, and […]

Categories
Uncategorized

Filter Enhancements in PerformancePoint Services 2013

On a previous project, there was quite a bit of PerformancePoint dashboard work.  A few of the dashboards contained a large multi-select nested filter with a ton of items to choose from.  Once the dashboards were published, some of the users began voicing their frustrations with having to dig through this filter to find all the previously […]

Categories
Uncategorized

Performance Tuning with OPTIMIZE FOR UNKNOWN

Today I ran into a performance/scalability problem with a group of stored procs I’d written earlier this week.  All the procs passed basic unit-testing, but once I increased the load from processing 100 records to 100,000 records…you know, to make sure it scales and stuff…the test went from < 3 seconds to over 35 minutes!!  […]

Categories
Uncategorized

ColumnStores vs. RowStores: I/O Performance and Compression

One of the major benefits associated with ColumnStore  indexes is increased I/O performance.  Below are two of the more common explanations for this bump in I/O performance… ColumnStores only retrieve data (from storage) necessary to satisfy the query This notion is inherent in the column-oriented architecture…data pages in column-oriented storage only contain data from a single […]

Categories
Uncategorized

ColumnStores vs. RowStores: How Different Are They Really?

For the vast majority of SQL Server professionals, it wasn’t until earlier this year, with the release of SQL Server 2012, that concept of “ColumnStore” (as opposed to RowStore) popped up on the radar.  And now, as  part of the research for a current project, I’m trying to find details on Microsoft’s implementation of the ColumnStore index…but since […]

Categories
Uncategorized

Diving Into the Deep End with ColumnStore Indexes

Currently, I’m working with a client to develop a custom database application that will be used for tracking and reporting on account receivables.  We have estimated between 2-3 billion transactions per year and need to maintain 3 years of history…so it will be a fairly large database.  But more interesting that the sheer size of […]