Kalen Delaney

Geek City: More statistics info available!

2013-04-10T01:39:00Z

I just started using a new DMV (one that’s actually an ‘F’ not a ‘V’, as in Function) that gives us more info about distribution statistics. It returns info about the last statistics update date (which is also available with a function STATS_DATE()). It also provides the number of rows sampled when the statistics were last updated. This is available through DBCC SHOW_STATISTICS, and in a blog post a couple of months ago , I showed you how to get that output into a table for your own querying. One...(read more)

Geek City: A Grammar Geek in the Cloud

2013-02-28T06:25:00Z

Those of you who know me well know that I am usually a stickler for spelling, grammar and proper word usage. I may have even lost a few friends because of what some people see as obsession. I am not infallible, and I do make typos, but I like to try to correct them if possible as soon as I discover them (or as soon as they are brought to my attention.) So now I will admit that I made a mistake in my usage of of the word ‘premise’, when talking about Cloud vs. non-Cloud databases. I was using the...(read more)

Geek City: Join With Me!

2013-02-14T22:16:00Z

I remember one of the most surprising changes in SQL Server 2000 2005 was how the graphical plans showed the use of a nonclustered index to seek, and the plan included something that looked like a JOIN to find rows in the base table. Here’s an example. Although I used SQL Server 2008R2, the graphical plan will be pretty similar to what it looked like back in SQL Server 2000 2005. My code will make a copy of a table in the AdventureWorks2008 database, and then build an index on one of the columns....(read more)

Geek City: Accessing Distribution Statistics

2013-01-18T20:08:00Z

Distribution statistics are one of the most important sources of information that the Query Optimizer uses to determine a good query plan. In this post, I’m not going to tell you everything about distribution statistics. I’m just going to show you a few tricks for getting access to the statistics. If you want a deeper understanding of what the statistics keep track of, and you don’t have any of my SQL Server Internals books handy, check out this whitepaper: Statistics Used by the Query Optimizer...(read more)

Geek City: What Triggered This Post?

2013-01-01T00:00:00Z

I’d really like to get another post up onto my much neglected blog before the end of 2012. This will also start one of my New Year’s resolutions, which is to write at least one blog post a month. I’m going to tell you about a change in SQL Server that wasn’t announced in any “What’s New” list that I ever saw, perhaps because it was just a chance in internal behavior, and nothing that required any change in user applications. Do you retest what you know is true for every new version? When I update...(read more)

Did You Know? I’m delivering my first SQL Server 2012 class this week!

2012-08-08T04:14:00Z

This is actually just a very short post to get my name back up in the list on the right side of SQLBlog, because Adam’s configuration removes people who haven’t posted in 3 months. I’ve been extremely busy trying to get my new book finished, and then get my course updated for its first delivery this week. I’ll be teaching it again in Norway at the end of the month. It’s a great class this week, and a couple of questions have inspired longer posts, which hopefully I be able to get to next week. Hopefully...(read more)

Did You Know? Turning Off Locking

2012-05-07T23:35:00Z

Back when the lock manager was rewritten for SQL Server 7, and row level locking was added to the product, the procedure sp_indexoption gave us the ability to turn OFF either row or page locks or both, for one particular index, or for a table and all the indexes on it. I must admit, I rarely found this option to be the solution to any sticky problems, although while doing some quick research prior to writing this post, I did find that Microsoft recommended turning off locking options on a couple...(read more)

Geek City: Build a Big Table with a Columnstore Index

2012-04-26T18:18:00Z

I was looking all over to find a big table to use for my columnstore examples in my SQL Server 2012 book, and the only one I found was from someone at Microsoft and was not publicly available. When I demonstrate code in my writings, I like to have the code available for anyone to use, so that was no use. Finally I realized I was just going to have to do it myself. I actually based the script on some work by the awesome Benjamin Nevarez ( blog | twitter ), but I needed to make a couple of changes...(read more)

Follow-up Answers for my Australia Classes

2012-04-04T22:56:00Z

I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney, which were organized by WardyIT . It was a great visit and there were 24 terrific students! As is sometimes (perhaps often?) the case, there were questions posed that I couldn’t answer during class, so here are a couple of follow-up answers. 1. I brought up the fact that SQLS 2012 generates a warning message when there are ‘too many’ Virtual Log Files (VLFs) in a database. (It turns out the message...(read more)

Geek City: Growing Rows with Snapshot Isolation

2011-11-20T00:43:00Z

I just finished a wonderful week in Stockholm, teaching a class for Cornerstone Education. We had 19 SQL Server enthusiasts, all eager to find out everything they could about SQL Server Internals. One questions came up on Thursday that I wasn’t sure of the answer to. I jokingly told the student who asked it to consider it a homework exercise, but then I was so interested in the answer, I try to figure it out myself Thursday evening. In this post, I’ll tell you what I did to try to answer the question....(read more)

Did You Know? Query Options Setting in SSMS

2011-10-20T00:09:00Z

Hi folks I’m back from an amazing week at PASS and an awesome reception for SQL Server MVP Deep Dives Volume 2. I’m starting to plan and write my next book on SQL Server 2012 Internals so blogging will kept to a minimum, not that I’ve had a lot of time to blog lately. However, while working on the final quiz for my Concurrency class through SSWUG , I noticed something peculiar about the Query Options | Advanced dialog in Management Studio. You have the option of choosing a default ISOLATION LEVEL...(read more)

Did You Know? I’ll be teaching my Internals Class online!

2011-09-07T20:27:00Z

In my most recent commentary for SQL Server Magazine, online here: http://www.sqlmag.com/article/training-and-certification2/sql-server-training-options-140429 , I talked about different options for making training content available online. As an addendum to that, I’d like to mention that one of my training partners (Benchmark Learning in Minneapolis) has decided to offer my complete 5-day deep dives Internals class online, in early November. We’re doing it as a split week class also, so your brain...(read more)

Geek City: Where are LOBs stored?

2011-06-26T21:27:00Z

When researching a question from one of the students in my class last week, I was reading the documentation for CREATE TABLE about storing LOB columns at http://msdn.microsoft.com/en-us/library/ms174979.aspx . For this discussion LOB columns includes text, image, ntext, xml and the MAX columns when they are over 8000 bytes and stored outside the regular data row. I knew that SQL Server gives us the capability of storing LOB columns in a separate filegroup with the TEXTIMAGE_ON clause, but I was surprised...(read more)

Geek City: What gets logged for SELECT INTO operations?

2011-03-15T19:41:00Z

Last week, I wrote about logging for index rebuild operations. I wanted to publish the result of that testing as soon as I could, because that dealt with a specific question I was trying to answer. However, I actually started out my testing by looking at the logging that was done for a different operation, and ended up generating some new questions for myself. Before I started testing the index rebuilds, I thought I would just get warmed up by observing the logging for SELECT INTO. I thought I knew...(read more)

Geek City: What gets logged for index rebuild operations?

2011-03-08T18:54:00Z

This blog post was inspired by a question from a future student. Someone who was already booked for my SQL Server Internals class in June asked for some information on a current problem he was having with transaction log writes causing excessive wait times during index rebuild operations when run in ONLINE mode. He wanted to know if switching to BULK_LOGGED recovery could help. I knew the difference between ALTER INDEX in FULL vs BULK_LOGGED recovery when doing normal OFFLINE rebuilds, but I wasn't...(read more)