Categories
Uncategorized

Analyzing SQL Server Object Dependencies with NodeXL

One time I was tasked with developing a handful of custom reports for a legacy application.  The data model was an absolute nightmare – overflowing with technical debt and being held together with duct tape and views…tons and tons of views.  In this post, I’ll show you how NodeXL can be used to help navigate […]

Categories
Uncategorized

How to Calculate the Median in T-SQL, MDX, and not DAX

Building from my last post, Performance Metrics: Average, Mean, Median, and Outliers, where I covered the topic of when to use Median instead of Average, in this post I’m going (to try) to show you how to calculate the Median of a set of values in T-SQL, MDX, and DAX. Calculate Median in MDX Let’s start […]

Categories
Uncategorized

SQL Profiler EventClass & EventSubClass Column Descriptions for SSAS 2012

Lately, I’ve been spending quite a bit of time troubleshooting some performance issues related to PowerView reports running against a SQL Server Analysis Services 2012 Tabular data source.  One of the first steps in the troubleshooting process involved turning up a SQL Profiler trace on the SSAS Tabular instance and having the users reproduce the […]

Categories
Uncategorized

TSQL Script: Check for Missing DEFAULT Constraints

Here’s a quick script I put together recently while polishing up a data model I’ve been working on.  This script will list every table containing a column named EffectiveDate that is missing a default constraint. The scenario that prompted its creation was for an EffectiveDate column that exists in a lot of the tables in […]

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

Static Code Analysis and SARG-ability

This morning while coding a stored procedure for a SQL Server 2012 SSDT project in Visual Studio, I noticed an interesting little blue squiggly under one of the column references in the WHERE-clause of the statement I was working on.  Curiously, I hovered the mouse over it and was pleasantly surprised to see the following […]