Kalen Delaney

T-SQL Tuesday: What kind of Bookmark are you using?

2013-06-11T03:21:00Z

I’m glad there is no minimum length requirement for T-SQL Tuesday blog posts , because this one will be short. I was in the classroom for almost 11 hours today, and I need to be back tomorrow morning at 7:30. Way long ago, back in SQL 2000 (or was it earlier?) when a query indicated that SQL Server was going to use a nonclustered index to get row pointers, and then look up those rows in the underlying table, the plan just had a very linear look to it. The operator that indicated going from the nonclustered...(read more)

Geek City: SQL Server 2014 In-Memory OLTP (“Hekaton”) Whitepaper

2013-06-05T16:42:00Z

Tech Ed isn’t even over yet, and my new Hekaton Whitepaper is already generating questions I can’t answer yet! Here are the two questions that were in my inbox this morning: 1. The whitepaper says “There are other wait types, such as waiting for a log write to complete.” Does this mean that writing to the transaction log will be the main bottleneck of the system? Hey folks, the product isn’t even publicly available. How can I, or anyone, know what your main bottleneck will be? You do know that there...(read more)

Geek City: Document more trace flags?

2013-06-03T15:33:00Z

It’s been over 5 years since I last blogged about trace flags , so it seems a post on the topic is in order. If a trace flag is undocumented, it means it is unsupported and not guaranteed. It can go away at any time, even from one service pack to the next. If a trace flag doesn’t work the way the person who told you about said it should work, there is no help for that. It is undocumented. Trace flags change SQL Server’s behavior. They are toggles, that can be turned on or off. When they are on, they...(read more)

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)