Category Archives: SQL Server Performance

Indexing Fundamentals Presentation

Indexing Fundamentals Presentation

**This Session has been rescheduled for July 10th @ Noon EST (16:00 UTC)**

On July 10th 2013 @ Noon EST (16:00 UTC) I will be giving a presentation with the Professional Association for SQL Server (PASS) DBA Fundamentals Virtual Chapter. Entrance is free as always and details can be found at

Code, Slides, and other information about the presentation will be under the Presentations section of this site afterward. Hope to see you there!

Abstract: Indexing is an integral part of SQL Performance and Architecture. In this presentation we will cover the basics of Clustered and Non-Clustered Index structure and function. Additionally, we will discuss the B-Tree structure, index density (fill factor), page splits, and performance considerations. Finally, there will be a brief discussion of index maintenance.


Tags: , , , , , ,

Email Response: SQL Server 2012 Planning and Architecture

Email Response: SQL Server 2012 Planning and Architecture

I received an email from a connection on LinkedIn seeking some advice for architecture on a new reporting system. Although I don’t know the gentleman personally, he is a SQL Server professional and I felt obliged to help out a fellow colleague in the field. The email is included below and my suggested solutions following it.

Read the rest of this entry »


Tags: , , , , , ,


Log File Full–Availability Group Database

An interesting post by Gethyn Ellis about an issue he encountered using Availability Groups. Follow the link above to read more.

I run into an interesting issue with an Availability Group database recently. I was running a data load against a database in an Always On Availability group database in SQL Server 2012.  The load was SSIS package migrating data from staging database when the package fell over with the following error

“The transaction log for database DBName is full due to ‘AVAILABILITY_REPLICA’”


Tags: , , ,

Querying the Management Data Warehouse for Disk Usage over time (Data Collector)

Querying the Management Data Warehouse for Disk Usage over time (Data Collector)

A part of every DBA’s job is to justify all those disk space requests. The phrases “I just need it” or “Trust me this database is going to grow one terabyte this year” might not convince your boss.

Many people are under the impression that disk space is cheap since they base that notion off of the shell shocker they saw on NewEgg or the weekly circular from Best Buy. If you are using consumer SATA drives from retail outlets for your production databases please do the following:
1. Update your resume
2. Apply for jobs at least 1000 miles from your current location
3. Hope the next DBA that replaces you has more common sense

We’re in the times of SANs, iSCSI, SAS drives, SSD drives and RAID architecture! You should be forking out almost the cost of your server in disk space. Unless of course losing data is an option where you work.

Now that I’ve bored you or intrigued you (or something in between) I can get to my point. There is a wonderful tool that comes with SQL Server 2008 and later called the Management Data Warehouse (MDW) also known as the Data Collector.

Read the rest of this entry »


Tags: , , , , ,

System Center for the SQL Server DBA: Wrap-Up

Might be worth investigating if you need a robust enterprise level system to monitor your database servers.


I’m going to call the series on System Center for the SQL Server DBA complete with this one last post today. First, here is the full series to date … my apologies for the lack of post naming consistency!!

System Center for the SQL Server DBA Intro

The Continuing Story of System Center for the SQL Server DBA

Monitor SQL Server from System Center Operations Manager

SQL Server management pack for SCOM

System Center for the SQL Server DBA Part 2: DPM

System Center Data Warehouse for SQL Server DBAs

What I wanted to point you all to is an update to my methodology of using SQL Server 2012 business intelligence leveraging in-memory analytical models to provide very important insights into your SQL Server environment with System Center Operations Manager. The link is here to my blog on this on SQL Server Pro Magazine.

By leveraging the SCOM data warehouse…

View original post 61 more words


Tags: ,

Five Seconds Transaction Log Shrink

This is somewhat of a quick or lazy way to truncate and shrink the transaction log because all you have to do is replace the database name and change your size requirements. It’s not the cleanest or best approach I admit, but sometimes I find myself on a development or staging server and I need some space quickly. There is no requirement on the server to preserve historic data, and since it is not highly transactional I don’t care what is on the log file to begin with. This happens often when running data aggregations in our DataMart environment where log files can grow up to 100GB+ with staging data that is not useful after the process is complete.

Read the rest of this entry »


Tags: , , , ,

SQL SERVER – NTFS File System Performance for SQL Server

NTFS File System and SQL Server Performance. Join the discussion!


Tags: , ,

%d bloggers like this: