…brew your own Business Intelligence

Posts Tagged ‘T-SQL’

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 […]

Read more

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 […]

Read more

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 […]

Read more

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 […]

Read more

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 […]

Read more

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 […]

Read more