Building High Performance Stored Procedures

[NOTE: Update 12 Nov 2014. The most recent bug (KB article KB2965069) is THANKFULLY very unlikely for most environments (mentioned below). As a result, I’m glad to say that we can use OPTION (RECOMPILE) as a much easier (and safer) solution. So, while I’ll still leave solution 3 as an option if you run into troubles with […]

Fall SQLintersection is coming up soon and we can’t wait!

In just a couple of weeks (9-14 November 2014), we’ll be at our twice-yearly conference, SQLintersection. We’re really looking forward to it as our conference is different than many others… our sessions are hand-picked and combined to create daily “tracks” with themes. Often, I’ll even ask a speaker if they could talk about x or y or z […]

Understanding LOB data (2008/2008R2 & 2012)

I know it’s been a while since I last blogged here on SQLskills. It’s been a busy few months with Immersion Events, conferences and even a bit of personal travel in there. And, we’ve also been blogging very regularly (weekly) at SQLServerPro here: Kimberly & Paul: SQL Server Questions Answered. And, this is what’s brought […]

(OLD): sp_helpindex rewrites

I realize that I’ve created quite a few posts (and rewrites) around sp_helpindex and recently I’ve been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it’s not so obvious where to get it and/or what to do to install it. To make it easier… I’m just going to do a […]

Lots of learning options – which is best and what’s coming up soon!

For someone that's been in the working world for many years (I wish I really were only 29 ;-), I know the struggle that everyone goes through in keeping up with technology. I admit, it's hard to stay current and it takes time and effort to do so. I attend online webcasts, I read whitepapers, […]

(OLD): A new and improved sp_helpindex (jokingly sp_helpindex8)

NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! OK, I first blogged about re-writing sp_helpindex here as sp_helpindex2 (April 2008). Shortly thereafter (Aug 2008), a reader found a bug and when I went digging, so did I (a couple of others). I did […]

Clearing the cache – are there other options?

OK, I've had a bit of a tangent going on optimizing stored procedures and its all revolved around adhoc plan caching and potentially wasted cache due to the single-use plans created (see the most recent posts titled:Plan cache and optimizing for adhoc workloads and Plan cache, adhoc workloads and clearing the single-use plan cache bloat). To […]

Plan cache, adhoc workloads and clearing the single-use plan cache bloat

In my post describing the new SQL Server 2008 configuration option [optimize for adhoc workloads] titled: Plan cache and optimizing for adhoc workloads, I asked for you to send me your plan cache numbers. In addition to the comments on the post, I received a lot of feedback in email (and lots of comments – […]

Public classes coming up in Dublin (including prior course testimonials)

Paul and I enjoyed Dublin so much in 2008 and 2009 that we're going back for two weeks this time – and teaching three classes while we're there! We'll be working with our fabulous friends (Carmel, Sandra, and Bob) at Prodata and the event will be hosted by the nice folks at Microsoft Ireland. We'll […]

Plan cache and optimizing for adhoc workloads

I mentioned that servers receiving a lot of dynamic constructs (typically those generated by client SQL apps/ORMs/LINQ, etc.) can start to consume too much plan cache and have problems with “single-use plans” in my last post titled: Statement execution and why you should use stored procedures. I also mentioned that SQL Server 2008 has an […]

Microsoft eLearning Resources – BI and 2008 R2

In addition to the SQL Server 2008 Database Infrastructure and Scalability content that Paul and I delivered for SQL Server 2008, and the content that our colleague Bob Beachemin (blog) delivered (Clinic 10164: Essential SQL Server 2008 for Developers), our colleague Stacia Misner (blog|twitter) also delivered content for SQL Server 2008 titled and available as: Clinic […]

Microsoft eLearning Resources – Developer Content – 2008 & 2008 R2

In addition to the SQL Server 2008 Database Infrastructure and Scalability content that Paul and I delivered for SQL Server 2008, our colleague Bob Beachemin (blog) also delivered complementary content under the Developer track. This course is officially titled and available as: Clinic 10164: Essential SQL Server 2008 for Developers. Additionally, this content has been updated […]

Microsoft eLearning Resources – Clinic 10259 (SQL 2008 DBIS)

When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft […]

SQL Connections Fall 2010 – Call for Abstracts

Last week we completed another SQL Server Magazine/SQLConnections conference and already, it's time to start planning for the Fall show. Once again, Paul and I will be managing the SQL side of the conference and we'd like to invite you to submit abstracts for sessions at the conference. The Fall 2010 SQL Connections conference will […]

Filtered indexes and filtered stats might become seriously out-of-date

Time flies when you're having fun, eh? This week Paul and I are in Dublin, Ireland delivering a deep technical "SQL Immersion Event" on Internals, Performance Tuning and Maintenance with our partner ProData (and having a blast – we have a really fun and very interactive group). And, today's an even more interesting day to be in […]

Column order doesn’t matter… generally, but – IT DEPENDS!

OK, for years I've been saying that SQL Server doesn't care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. In both the fixed-width portion of the row as well […]

Looking for security vulnerabilities in database code

I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters (fyi – using QUOTENAME can be a […]

The Tipping Point Query Answers

OK, I'll definitely take a beating from all of you for having gone so long between my survey posts and now. I won't even go into the details but between some crazy work schedules, multiple sinus problems and even migraines… well, I've been a bit behind. Let's just say that April/May were rough at best. […]

Companion content for Chapter 6 (Index Internals) of SQL Server 2008 Internals

Recently, the SQL Server 2008 Internals title was released (and only in the past few days have people actually received their copies)! In fact, I still haven't seen the book in person… soon though! Anyway, Kalen, Paul, Conor, Adam and I worked to create a comprehensive resource on SQL Server 2008 internals and to supplement the written […]

RunAsRadio Interview about Developers and DBAs – whose job is it?

A couple of weeks ago I wrote a blog post titled Whose job is it anyway? It's an interesting debate and something I've been hearing more and more – that SQL Server is a "set it and forget it" technology – a black box where you just don't need to know how it works to do […]

Spring cleaning your indexes – Part II

I started the series here: and I want to continue with Part II today by clarifying some great questions/comments that have come up on the series. In Part III, I'll give you a few more ways to get rid of (or consolidate) indexes. And, I think there's still a bit more that Paul and I […]

Spring cleaning your indexes – Part I

First and foremost, happy spring! I truly hope we’re on the path to summer (although who would know it here – we’re in Florida for SQL Connections and the weather is a bit chilly and it’s been raining off/on today – I hope this is short term (no, I don’t want to look at the […]

Database Maintenance Best Practices Part III – Transaction Log Maintenance

This is a tough topic. It's a big topic and more than any other – I think there are a lot of misunderstandings about what the log is for, why it's so critical and ESPECIALLY when/why it gets extrememly large. Simply put, it gets large when it's not managed correctly. OK, there are times when […]

Database Maintenance Best Practices Part I – clarifying ambiguous recommendations for Sharepoint

OK, it seems as though there's A LOT of confusion about what steps are required for proper database maintenance. And, it seems as though some recommendations are being given as "quick fixes" without any real recommendation for root cause analysis. I'm not saying that the generalizations are horribly wrong but in many cases they're just […]

Tipping Point Queries – More Questions To Really Test You!

OK, so this is interesting. I've got a few answers to my last survey (Tipping Point Query #1) and well, there's a good mix of answers (and, yes, some are correct! ;)). Be sure to go back and review that last post so that you can evaluate it and these two tipping point questions completely. […]

Why aren’t those nonclustered indexes being used?

Along the same lines of improving database design and getting better performance on SQL Server (which [IMO] DOES take an experienced SQL Server database developer – but, we'll talk more about "whose job this really is" in many more posts and probably even a RunAs – which Richard and I just setup to record on […]

Connections is 3 weeks away and counting!

It's an exciting year for us for DevConnections! SQL Server 2008 has now been out for a few months and an SP is coming up soon. This is the sign that some customers wait for to migrate over to the new release feeling that an SP indicates a higher level of stability. But, this is […]

Foreign Keys can reference UNIQUE indexes (without constraints)

Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez – who, unfortunately, does not have a blog or anything…yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index – one without a PRIMARY KEY or […]

Getting the most from the system you have now!

Given the general state of the economy…many companies are looking to cut back. Going back over what we've done and "optimizing" things -> budgets, expenses, etc. is the norm right now. And, scaling back is not always a bad thing – unless the wrong things are cut. Unless the wrong things are used to motivate […]

Understanding backups and log-related Trace Flags in SQL Server 2000/2005 and 2008

Paul and I started discussing a comment that came up regarding the many issues surrounding logging & recovery. It's one of our favorite topics and in fact was the title to an article that Paul recently wrote for TechNet here: And, as a sidenote, depending on how much you already know about the transaction […]

Conference craziness comes to a close… (and DVD information)

Well, this has been a great "conference season" but I'm also glad that it's over for a bit. In the past 4 weeks, Paul and I have been to Vienna (check out Paul's post here), Barcelona (for TechED EMEA ITPro), Las Vegas (for SQL Connections) and finally to Seattle (for PASS). We live in Redmond […]

Virtualization with SQL Server

This is something that both Paul and I want to research more but, there have been a lot of questions about this recently. So, I thought I'd pull together a quick post of some important resources if you're interested in virtualization. 1) You should know what the support implications are – Bob Ward (Microsoft PSS […]

Transaction Log VLFs – too many or too few?

(Be sure to join our community to get our monthly newsletter with exclusive content, advance notice of classes with discount codes, and other SQL Server goodies!) Way back in June 2005, I blogged about ‘8 Steps to better transaction log throughput‘. I did this blog post after seeing (again and again) overly fragmented transaction logs… Transaction […]

SQL Server 2008 Central Management Servers – have you seen these?

There's a new feature of SQL Server 2008 that during beta was called a Configuration Server… In RTM it was renamed to a Central Management Server. It's not overly obvious and without knowing how cool it can be – you might skip right by it. And, if you're using SQL Server 2008 to manage 2000, […]

RunAs Radio Interview Posted – “Kim Tripp Indexes Everything”

Well… I think I had had too much tea that morning ;-). But, as always, chatting with Richard and Greg was great. Here’s the specific show link: Oh, and just for the record, I didn’t come up with that title. But, I do hope that all your [high-priority and important] queries are indexed! Enjoy!kt

SQL2008HOLsVPC setup instructions and a note for the August 2008 release

OK, I know this post doesn't really apply to most folks but I did want to have the setup instructions on my blog so that I could refer people to it. For the August release we updated all 17 labs and all of the demos for SQL Server 2008 RTM; however, we did use a […]

Budgets, training, where is your money best spent?

I’ve tackled this type of question before but it’s a very challenging thing to decide. Budgets are tight and training is always hard to quantify. However, working more efficiently and effectively – when properly trained – means smoother operations, less downtime, less data loss, less stress. So, if you’re looking for some ideas on training […]

(OLD): Updates (fixes) to sp_helpindex2

NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and […]

Auto update statistics and auto create statistics – should you leave them on and/or turn them on??

YES!!! OK, well, I guess I should be more specific because as in most things in SQL Server, the real answer is “it depends”. And for these two options, it depends mostly on your SQL Server version. Since SQL Server 7.0, the way that auto update works, has changed (much so for the better!!). So, […]

Installing Data Dude CTP16 with SQL 2008 and Visual Studio

OK, so SQL Server 2008 came out *BEFORE* Visual Studio SP1 but VSSP1 was required (this was a bit annoying and created a lot of confusion/emails/newsgroup posts, etc.) but luckily VSSP1 came out only a couple of days after SQL Server 2008 RTM’ed. As for Data Dude, well, it’s not RTM’ing until later this year […]

SQL Server 2008 fails to send database mail, Sparse columns with filtered indexes, Upcoming Events, and – as usual – catching up (i.e. cookies)!

OK, so, I don't blog very often. I don't know what it is… I think it's that I feel like I always need to blog huge posts and the thought of writing my huge post makes me not want to blog… so, sometimes my time-between-blogging (TBB :) is long. I'm going to turn over a […]

TechEd has completed!

I started this post while Paul and I were in the TechEd Bloggers Lounge… which, from the number of folks “stopping by” must be more of an online thing :). We did have lots of folks visit with us in the DAT “Green” area but in the bloggers lounge, I thought I’d blog (maybe that’s […]

Indexes in SQL Server 2005/2008 – Part 2 – Internals

OK, I first posted on some of the limitations to indexes in SQL Server 2005 and 2008 in part one here. Now, I want to dive into index internals for a post (or two). And, I often get the question “who is the best audience for your blog – or, for this post” and well, […]

Time for TechEd ITPro and, it’s hot as hell here in Orlando………

Memorial Day weekend we were in Chicago to celebrate my Father’s life. We did a “Celebration of Life” memorial and we had a few drinks (celebratory Meyers, Tonic and lime – which was my Father’s favorite drink), we (7 of us) gave a few heartfelt speeches, and a few friends wrote a song (and passed […]

Indexes in SQL Server 2005/2008 – Best Practices, Part 1

In my blog post on my new sp_helpindex proc (sp_helpindex2), I mentioned that the indexes in my sample were not necessarily a recommended set of indexes – just a test set of indexes. So… in this post, I thought I'd start a series on indexes, limitations and best practices/uses… Especially, why/how to best choose when […]

(OLD): sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

  IMPORTANT NOTE: Always check the sp_helpindex rewrites category for the USE THIS post! OK – so this has been frustrating me for many months… when you create indexes with included columns (which was a new feature of SQL Server 2005), they’re not shown by sp_helpindex or by DBCC SHOW_STATISTICS. I understand this not showing for […]

TechNet Radio Interviews are ready for download

A couple of weeks ago, Paul and I recorded two interviews with TechNet Radio… both are ready for download and in multiple formats!  Our specific interviews can be downloaded from the following links/formats:  SQL 2008 Part 1 of 2: Security and Availability WMA | MP3 High | MP3 Low  SQL 2008 Part 2 of 2: Management, Troubleshooting […]

SQL Server Magazine Connections Fall 2008 Call For Abstracts

(Cross-posted on Paul and Kimberly’s blogs) With the Spring SQL Server Connections show coming up next month, it’s time to start planning for the Fall show. Once again, Kimberly and I will be co-Chairs of the Conference and we’d like to invite you to submit abstracts for the Fall 2008 SQL Connections conference, to be […]

Statistics, query plans, and are you reading Conor’s blog?

I know that Paul and I recommended that you subscribe to Conor's blog… but have you? He's posted some great details on Partitioning (Part 1 and Part 2) as well as statistics and it always reminds me of how much I can learn from other people's perspectives! And, just to dove-tail on some of his statistics […]

SQL Server 2005 and 2008 – Compression

OK, I still have a way to go in learning about data compression in SQL Server 2008 but one thing that I do know is that nothing is free. So, the trade-off will be performance (i.e. CPU) v. space. And, that’s not really a new trade-off wrt to compression. Sometimes that trade-off has other benefits […]

SQL Server 2008 – Transparent Data Encryption

Does it sound too good to be true: transparent data encryption? Well, it kind of is and kind of isn’t. Let me explain. Transparent means that the application developer doesn’t do anything (and if you have a third party app in which you can’t do anything anyway, then this is even more important). Once enabled, the data […]

SQL Server Magazine Connections Spring 2008 – Call for presentations

With the November 5-9th SQL Connections conference in Las Vegas around the corner, Shirley Brothers, the Connections Conference Manager, would like to start planning the Spring show. And, for the Spring show forward, Paul and I will be the co-chairs of the SQL Connections conference. We would like to invite you to submit abstracts for […]

November in Spain -> TechEd ITForum 2007

      OK, so after SQL Connections in Las Vegas, Paul and I head off to Barcelona for the second week of TechEd’s two week event (week one for developers and week two for IT professionals). November’s definitely a busy month. So, if you’re in the US – we hope to see you at […]

November in Las Vegas -> SQL Server Magazine Connections Fall Show

    OK, it’s about that time again – the Fall conference season – is here! Building on our co-presented Database Maintenance workshop at SQL Connections in Orlando, Paul and I are doing a *ton* of stuff at SQL Connections this Fall. The conference is back at the Mandalay Bay hotel and officially runs from […]

SQL Server 2008 offers partition-level lock escalation excellent, but…

OK, let me start by saying that I absolutely love when a feature improves in granularity options. Better granularity in locks means that contention is reduced and concurrency improved. And even though the overhead to manage smaller locks (and typically more of them) is usually higher – the improved concurrency benefits often significantly outweight the […]

SQL Server 2008 July CTP

Yesterday, Microsoft released the July Community Technical Preview (CTP) for SQL Server 2008. The release makes available several performance and feature enhancements designed to support mission-critical platform and enable pervasive insight across the enterprise. (ok, those last two sentences weren’t mine :) Check out for all of the latest info. You can download the SQL […]

Opinions wanted – are you considering skipping 2005?

I had a customer ask me about the debate of skipping SQL Server 2005 entirely and just moving straight to SQL Server 2008… I haven’t thought much about this as most of my direct customers have moved already BUT, I know there are a few out there that have not yet moved. So, I definitely […]

Exciting news from TechEd 2007 – SQL Server 2008’s CTP3 is ready for public download!

Well, I’m not sure where May went but I do know that part of it went to my 2 weeks in Hyderabad, India at Microsoft IDC (the India Development Center) and what was left was spent getting ready for upcoming events such as TechEd (and some personal events too :). And, the first big hurdle […]

8 Steps to better Transaction Log throughput

(Be sure to join our community to get our monthly newsletter with exclusive content, advance notice of classes with discount codes, and other SQL Server goodies!) [Edit 2019 by Paul: Although this is an old post, it’s still relevant. I’ve updated the post to reflect current numbers and guidance. Also check out these more recent […]