Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds an MCSE Data Platform certification, MCITP certifications in SQL Server 2008 for administration and development, and CompTIA Security+. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.

PSSDiag Configuration Manager – A Companion for SQLNexus

Have you ever called Microsoft Customer Support for help with a SQL Server performance issue? The engineer will typically send you a self-extracting exe file with instructions to unzip the file on the server and run the pssdiag.cmd file to collect data while you reproduce your issue. Afterwards, you’re supposed…

Read more

0 comments, 133 reads

Posted in Everyday SQL on 21 July 2015

Use Trace Flag 902 to Recover from a Cumulative Update Failure

Recently, I ran into a critical error while I was helping a customer troubleshoot an issue in SQL Server. That may not sound like a big deal, but we were installing Cumulative Update 6 for SQL Server 2012 SP2 to fix our initial problem when we encountered the following error.

Read more

0 comments, 1,524 reads

Posted in Everyday SQL on 7 July 2015

Exams 70-457 & 70-458 - Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012

This past week I finally completed the 70-458 exam to upgrade my certification to MSCA: SQL Server 2012. I never thought it would take me over a year to pass two upgrade exams, but sometimes life just gets in the way. I will say this is probably the hardest SQL…

Read more

0 comments, 161 reads

Posted in Everyday SQL on 24 June 2015

Use PsPing When Working in Azure IaaS

Recently I’ve been learning more about how Azure functions and how it can help my customers. One of the best ways for me to learn about Azure was to build out my own environment using VMs, or Infrastructure as a Service (IaaS). All of that was easy; however, once the…

Read more

0 comments, 162 reads

Posted in Everyday SQL on 28 April 2015

SQL Nexus Might Just Save Your Bacon

The year is 2015, and I’m still surprised by how many people have never heard of SQL Nexus; although it has been available since approximately 2008. SQL Nexus is a tool that that is designed to collect and analyze data to help you troubleshoot SQL Server performance issues. There are…

Read more

0 comments, 354 reads

Posted in Everyday SQL on 10 March 2015

SQL Cruise Caribbean 2015 Recap

UPDATED: Don't just take my word. Read the feedback from others.

I wish I could just re-post…

Read more

0 comments, 198 reads

Posted in Everyday SQL on 24 February 2015

Get Out There and Challenge Yourself

It’s been almost five months since I posted my last article, and so much has changed since then. I have a new job, a new house, a new address, and of course a new mortgage.

I had been working as a DBA for Wells Fargo Securities for nearly 15 years…

Read more

0 comments, 6,146 reads

Posted in Everyday SQL on 3 February 2015

PowerPoint Slides Available for Download

I promised that I would post the slide decks for my presentations, and now I have finally followed through on that promise. I have added a new Resources page that will have downloadable content available from blog articles and presentations.

Read more

0 comments, 248 reads

Posted in Everyday SQL on 2 February 2015

Monitor the Number of Deleted Rows in a Clustered Columnstore Index

In some of my previous posts, I have talked about how to create Columnstore indexes. Now I’d like to discuss one maintenance detail that you need to keep an eye on. I’m talking specifically about the number of “deleted rows” in a clustered Columnstore index.

One of the great…

Read more

0 comments, 677 reads

Posted in Everyday SQL on 21 August 2014

How to Edit Read-Only Non-clustered Columnstore Data

As I've discussed in some of my previous posts, creating a non-clustered Columnstore index will make the index as well as the base table read-only. Which means you can’t insert, update, or delete any data until your drop the index. This may seem like a huge issue, but in…

Read more

0 comments, 6,850 reads

Posted in Everyday SQL on 29 July 2014

Columnstore Table Analyzer

As I’ve discussed in some of my previous posts, there are quite a few data types that cannot be part of a Columstore index. While there are fewer restrictions in SQL Server 2014, they still exist. I find myself constantly looking back at Books Online trying to make sure…

Read more

0 comments, 297 reads

Posted in Everyday SQL on 8 July 2014

Columnstore Memory Grant Issue

In a previous post about non-clustered columnstore indexes, I mentioned the creation of an index is a very memory intensive operation. Sometimes the memory grant needed exceeds what is currently available on your server. So what do you do about it?

SQL Server requires a minimal amount of memory in…

Read more

0 comments, 517 reads

Posted in Everyday SQL on 3 June 2014

Comparison of Columnstore Compression

SQL Server 2012 introduced non-clustered columnstore indexes, and SQL Server 2014 gave us clustered columnstore indexes. Both share the same technology for performance boosts, and they both share the same algorithms for compression. However, the compression will depend on the data you are storing.

SQL Server uses a mechanism of…

Read more

0 comments, 600 reads

Posted in Everyday SQL on 20 May 2014

What is a Non-Clustered Columnstore Index?

First introduced in SQL Server 2012, the Columnstore index is a new in-memory feature that allows for the creation of indexes that are stored in a column-wise fashion. It is targeted for data warehouses and can improve query performance by 10 to 100x. A columnstore index stores data in a…

Read more

0 comments, 2,350 reads

Posted in Everyday SQL on 29 April 2014

The system_health Extended Event Session

When I first started poking around in SQL Server 2012, I noticed an extended event session called “system_health” was created by default. It took me a few months before I really dug into the session details to see what it was capturing. But once I did, I was pretty amazed.

Read more

0 comments, 1,771 reads

Posted in Everyday SQL on 8 April 2014

How Long is that SQL Command Going to Take?

Have you ever needed to restore a large database while someone is standing over your shoulder asking “How long is that going to take"? If that hasn't happened to you yet, then it’s only a matter of time.

Let’s throw out all the reasons why you need to do the…

Read more

0 comments, 1,744 reads

Posted in Everyday SQL on 4 March 2014

My Experience Aboard SQL Cruise 2014

Where do I begin? First let me say, WOW what an experience!

How it All Began
When I first heard about SQL Cruise way back in 2012, I thought the idea of hosting training sessions aboard a cruise ship was a swell idea. However, talking my wife into going with…

Read more

0 comments, 593 reads

Posted in Everyday SQL on 11 February 2014

Collecting Historical IO File Statistics

In a previous post, Collecting Historical Wait Statistics, I discussed how you can easily collect historical wait stats by using the DMV sys.dm_os_wait_stats. Well today, I'd like to cover the same concept, but this time collect historical IO file stats from the DMV, sys.dm_io_virtual_files_stats. However, I wanted…

Read more

0 comments, 595 reads

Posted in Everyday SQL on 17 December 2013

Collecting Historical Wait Statistics

As a DBA, I'm sure you've heard many times to always check the sys.dm_os_wait_stats DMV to help diagnose performance issues on your server. The DMV returns information about specific resources SQL Server had to wait for while processing queries. The counters in the DMV are cumulative since the last time…

Read more

0 comments, 1,657 reads

Posted in Everyday SQL on 11 December 2013

In-Memory OLTP and the Identity Column

Over the past month I've been playing around with the new In-Memory OLTP (code name: "Hekaton") features within SQL Server 2014 CTP2. My organization is all about low latency applications, and this is one feature of SQL Server that I need to get familiar with ASAP.

To do this, I…

Read more

0 comments, 658 reads

Posted in Everyday SQL on 12 November 2013

Older posts