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 MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.

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, 125 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,411 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, 131 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, 144 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, 224 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, 941 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,186 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,225 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, 253 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, 297 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,346 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, 364 reads

Posted in Everyday SQL on 12 November 2013

TSQL Tuesday #47 - Your Best SQL Server SWAG

The host for T-SQL Tuesday #47 is Kendal Van Dyke (blog|twitter), and his topic of choice is about the best SQL Server SWAG we ever received at a conference; specifically, the “good stuff”.
I’ve been doing a lot of work with SQL Server over the years,…

Read more

0 comments, 321 reads

Posted in Everyday SQL on 8 October 2013

One Year Later

Wow!  It’s been one year since I launched my blog, and my how things have changed.

Accomplishments Over the Past Year
I’ve had a chance to interact with a lot of people relating to many of the posts on my blog, and even run into a few people that said…

Read more

0 comments, 222 reads

Posted in Everyday SQL on 24 September 2013

The Case of the NULL Query_Plan

As a DBA, we're often asked to troubleshoot performance issues for stored procedures.  One of the most common tools at our disposal is the query execution plan cached in memory by SQL Server. Once we have the query plan, we can dissect what SQL Server is doing and hopefully find…

Read more

0 comments, 1,523 reads

Posted in Everyday SQL on 3 September 2013

How to Tell If Your Users are Connecting to the Availability Group Listener

You've spent a lot of time planning and building out a new SQL Server 2012 environment complete with Availability Group Listeners, but how can you be sure the end users are connecting to the listener and not directly to the SQL Server instance?

So why would we care about this? …

Read more

0 comments, 920 reads

Posted in Everyday SQL on 27 August 2013

PASS Summit 2013 - You Ain't From Around Here Are Ya?

I know what y'all are thinkin', what's Charlotte got to do with SQL Server?  Just hear me out.  There's a lot more to Charlotte than NASCAR, fried chicken, and rednecks. I assume most of the 5000 attendees have never been to Charlotte, and probably don't know much about the area.  To…

Read more

0 comments, 276 reads

Posted in Everyday SQL on 20 August 2013

Are You the Primary Replica?

In my recent adventures with AlwaysOn Availability Groups, I noticed a gap in identifying whether or not a database on the current server is the primary or secondary replica.  The gap being Microsoft did not provide a DMO to return this information.  The good news is the documentation for the…

Read more

0 comments, 348 reads

Posted in Everyday SQL on 23 July 2013

Setup an Availability Group with Multiple Subnets in VMware Workstation


Before we get started, I want to make it clear this is NOT how you would normally configure all these items in a production environment.  This is meant for a lab or demo area to play with Availability Groups over multiple subnets.

I use VMware a lot for demos at…

Read more

0 comments, 517 reads

Posted in Everyday SQL on 16 July 2013

VMware vSphere Storage Performance - Thick vs Thin Provisioning

Industry experts will tell you that virtualization of your environment is not done to improve performance, it's done to make it cheaper and easier to manage.  The task of most VM administrators is to cram as many VMs into a farm as possible.  One of the ways is to accomplish…

Read more

0 comments, 843 reads

Posted in Everyday SQL on 25 June 2013

Older posts