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 […]

How can you tell if an index is REALLY a duplicate?

There’s a lot of confusion around duplicate indexes and I think a lot of it has to do with how sp_helpindex (or even SSMS) display what’s in an index. What’s actually in the index – and how it’s structured – are not always what they seem. This was the original motivation behind my rewrite of […]

Data Dude moving into lower priced VS Editions in VS 2010 – excellent!

Data Dude (Visual Studio "DBPro") has generally been an expensive tool – only in the "Team Systems" Edition of Visual Studio. But, that's all going to change in VS 2010. Here's a picture of the new editions and which components of Data Dude are where: This is a HUGE improvement (after the clarification isn't not […]

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: http://www.sqlskills.com/blogs/kimberly/post/Spring-cleaning-your-indexes-Part-I.aspx 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 […]

Things I know now…

OK, I was tagged by Ward Pond here. Ward was tagged by Jimmy May here. Jimmy was tagged by Kevin Kline here. Kevin was tagged by Chris Shaw here. Chris was tagged by Michelle Uford here. And, Michelle was tagged by Mike Walsh here. Ah… at least I know who to blame. Ah, thanks Mike! […]

Database Maintenance Best Practices Part II – Setting FILLFACTOR

When you decide to rebuild or reorganzie an index, you have an option to set something called FILLFACTOR. This is probably the MOST IMPORTANT thing to understand about index maintenance and reducing fragmentation (especially in databases that are prone to it). Unfortunately, we need to dive into some internals to really understand why this is […]

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 […]

Follow on post to “The perils of case-insensitive data (and our life in tangent-land)”

OK, so thought I’d do a follow up to the post I did a couple of days ago titled: The perils of case-insensitive data (and our life in tangent-land). The reason I’d like to followup on it is that I received some excellent comments and I want to make sure that you’re all aware of the […]

“EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention)

DDL Triggers were a new feature of SQL Server 2005 and while seemingly simple, they are very powerful. DDL Triggers allow you to trap an attempted DDL operation to audit it, prevent it, or do anything you want to validate/verify/”authorize”/etc – you write the code. And, since a trigger fires as part of the transaction, […]

Moving databases around – what are your options and across what versions?

I had a discussion earlier today (with Paul Randal) about many misconceptions that exist about upgrading databases and more importantly, about “downgrading” databases. Really, the issue is that I’ve heard people get frustrated when they find that things like backup/restore works FROM SQL 7.0/2000 TO SQL Server 2005 but not the other way around – […]

The Clustered Index Debate Continues…

Well, I've promised to blog more and I'm really going to try to do so. This morning I got the perfect question/comment (in email) to respond to and after working through a response that was taking me upwards of 3 hours (you'll learn later why I have 3 "spare" hours :)……… I figured that it was […]

Way, way, way overdue

OK, it’s been a heck of a long time since I blogged… and for that I apologize. I’m also WAY overdue in my posting my demo scripts from a TON of conferences BUT… now everything has been posted. Check out the past events page on SQLskills and you can find the demo scripts that you’re […]

Designing for Performance – opinions, tips, tricks, techniques?!

Been thinking a lot about something that was mentioned in a few of my most recent posts… Especially when I get comments like “that’s another item to add to our checklist” or “that’s a good trick to add to our arsenal” and well, I thought in this blog entry I’d ask for your tricks that fall […]

Welcome to Blogging Liz!

Hey there everyone – If you’re into Analysis Services, Integration Services, Reporting Services and BI in general – you’ll want to check out Elizabeth Vitt’s new blog. Liz has been specializing in BI since SQL Server started adding BI-centric components. She’s got a wealth of information to share and many great insights into performance tuning […]

SQL 2000 v. 2005 – Using Top 100 Percent in a View

Well, it’s been a GREAT week here in Switzerland while working with my partner Trivadis. Today, we wrapped up a two-day course on Designing for Performance (in Geneva) while on Monday/Tuesday we did a two-day course on Indexing for Performance in Zurich. The food, the wine, the cheese, the butter, yum! Oh… and the questions/comments/technical […]

DBAs definitely should NOT read this blog entry…

Ha… did that get your attention? Well, what I really hope to do is make everyone aware of what’s made the Developer Community rounds this week. On Wednesday, Microsoft announced “Data Dude” (aka the Visual Studio sku for Database Developers). This was an announcement that may be glanced over by many DBAs thinking it’s just […]

Changing Database Collation and dealing with TempDB Objects

OK, so… I don't know how many of you use different collations but if you do then you know that there are two truths: 1) They're very flexible 2) They can cause you a bit of grief (changing collations and tempdb) Flexibility As of SQL Server 2000 (or heck, maybe it was 7.0?), database collations […]

TechNet Webcast Series for the ITPro – Series Links

Hey there everyone – The series has completed and I know that many of you struggled to get access to the surveys… Microsoft has asked me to post links to the surveys…so, for completeness, I decided to create this blog entry to have links for every session, every blog link (resources, demo scripts, etc.) and […]

TechNet Webcast Series for the ITPro – Part 11 of 11

Well… 11 of 11 has completed. Friday was our last chat – until next time ;). It was a summary event where I took a slightly different spin on things focusing on grouping technologies by the amount of effort that’s needed to implement them. Simply put, we looked at the technologies in order of what […]

TechNet Webcast Series for the ITPro – Part 9 of 11

In part 9 of our webcast series titled: Implementing Database Mirroring, we covered the steps from setup to failover to monitoring. There were lots of great questions and I think we could easily go back and do a couple more hours on database mirroring, failover combinations – including manual failover and client application questions. Having said […]

TechNet Webcast Series for the ITPro – Part 10 of 11 – errata

In the last few minutes of the webcast (part 10), I goofed up one line of code and didn’t realize it until today. As my very last demo (and there were at least 10 different scenarios/concepts/demos yesterday) in my webcast, I decided to show a Database Snapshot on a Mirror database. It was the second […]

TechNet Webcast Series for the ITPro – Part 10 of 11

OK – today's session was quite fun… lots of demos and quite a few "tie-ins" where I tried to bring together many things that we've touched on in our series. And – that's really the point of the series – creating a reliable, robust, scalable and available environment takes MANY different features. You really need […]

Chatting with Greg Low from SQLDownUnder.com

On Wednesday I had the pleasure of chatting with Greg Low – a fellow RD and MVP who is also excited to be focused and working in the SQL Server space. He’s been doing a few podcasts on database technologies and we were finally able to hook up today. He caught me off guard with […]

TechNet Webcast Series for the ITPro – Part 5 of 11

Well, Friday brought another flood of great questions from everyone as we moved our way through many of the new 2005 tools. The one thing that I really wanted to stress was that *many* SQL Server 2005 tools (SQLCMD, SSMS and SQL Profiler) offer important features that can be leveraged today, even if your primary […]

TechNet Webcast Series for the ITPro – Part 4 of 11

And another one bites the dust! Wow – what a great group today… soooooo many questions! For those of you that weren’t there – the lecture was 80 minutes and the additional Q&A went on for another 45 minutes. So – as a result, there were *a lot* of additional resources needed. Let me get started […]

TechNet Webcast Series for the ITPro – Part 1 of 11

Hey there everyone – Well there was lots of excitement around our first session…so much so that apparently a Live Meeting server went down and caused MANY of you to get booted-out or even blocked-from attending (figures, right!)…. Ugh (talk about the irony here – a series on high availability that isn’t available because a […]

MSDN Webcast Series Wrap-up Resources

Hey there everyone! I know I still owe you a few Q&A entries (for sessions 7, 8 and 9) but I wanted to get this blog entry out there so that you can play a bit with some of the resources. This series was targeted at developers but really helps to "bridge the gap" between […]

Understanding TempDB, table variables v. temp tables and Improving throughput for TempDB

This is a much needed and much overdue blog entry… In 8 Steps to Better Transaction Log throughput, I mentioned a customer that was helped by TWO typical optimization problems I see. In that blog entry, I said I would write two blog entries – that one on transaction log optimization and another on common […]

MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 5 of 10

Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development New Features in Indexing and Index Maintenance Best Practices, Part 5 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Part 5 can be replayed by clicking here. Q: Where can we get the demo […]

MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 4 of 10

Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development Indexing Best Practices, Part 4 of 10Presented by Kimberly L. Tripp, SQLskills.com For a list of the non-technical Resources related to this webcast, please review the following blog entry:MSDN Webcast Q&A for Resources: Indexing Best Practices, Part 4 of 10  Technical Questions […]

MSDN Webcast Q&A for Resources: Indexing Best Practices, Part 4 of 10

Effectively Designing a Scalable and Reliable Database A Primer to Proper SQL Server Development Indexing Best Practices, Part 4 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? The webcast will be available for viewing within 24 hours. All “on-demand” webcasts can be found here. Q: Where […]

MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 3 of 10

MSDN Webcast: A Primer to Proper SQL Server Development Designing Tables that Scale, Best Practices in Data Types and Initial Table Structures, Part 3 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Here’s the specific replay link. Q: Where can we get the demo scripts? The […]

Getting ready for Part 4, “Best Practices in Indexing” in the MSDN Webcast Series on Effectively Designing a Scalable and Reliable Database

Two days isn't enough time! In fact, that's really true. There are so many interesting (well, I think they're interesting) aspects to Indexing that we could in fact do a whole 10-part Series on Indexing and it's likely that we'd still have other things to look at and talk about. So, in preparation for part […]

MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 2 of 10

MSDN Webcast Q&A: A Primer to Proper SQL Server Development Creating a Reliable and Automated Backup Strategy, Part 2 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Here’s the specific replay link. Q: Where can we get the demo scripts? The demo scripts are in this zip […]

MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 1 of 10

MSDN Webcast Q&A: A Primer to Proper SQL Server Development Creating a Recoverable Database, Part 1 of 10 Presented by Kimberly L. Tripp, SQLskills.com Q: Can I view a recording of this webcast? Here’s the specific Replay Link: http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032278585&Culture=en-US Q: Where can we get the demo scripts? The demo scripts are in this zip (20050805 […]

MSDN Webcast Series to start tomorrow!

Wow, I've been horribly bad at blogging these days and I owe you a few entries as well… But, good news, I have a new MSDN Webcast series that will help to answer a lot of best practices in database design strategies that can help you achieve better scalability, availability and reliability. All of my […]

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!)   NOTE/UPDATE: Be sure to read this post as well: Transaction Log VLFs too many or too few? after reading this one. Not only can you have too many small […]

An interview with theServerside.net has been posted!

Many of you in the .NET Community are already familar with theServerSide.NET but many of you in the SQL Server community are not…well, here’s your chance to bridge the gap over to .NET and hear a bunch of great .NET interviews! My interview is more SQL-centric but there are lots of great ones on Development […]

Ever-increasing clustering key – the Clustered Index Debate……….again!

As I'm preparing for my Tech*Ed session on Indexing Best Practices in SQL Server 2005, I'm reminded that there are a ton of best practices that really apply to both SQL Server 2000 as well as SQL Server 2005. When it comes to indexing, there are many dependencies on the storage structures. These dependencies are […]

You learn something new everyday!

I think there are numerous reasons for why I love technology but at the top of the list: learning. It’s amazing to me that not a day goes by where I don’t dig deeper into something or clarify it further. Even learning something trivial, like a new keystroke, can make our work easier to do […]

Clarifying LEFT and RIGHT in the defintion of a PARTITION FUNCTION in SQL Server 2005

NOTE: Generally, I recommend RIGHT-based partition function so that you don't have to deal with datetime timetick issues at all. However, this post can really help you if you still want to use LEFT-based partition functions. Enjoy!!!  When creating partitioned tables in SQL Server 2005, a partition function requires a LEFT or RIGHT designation. In general, […]

When did SQL Server stop putting indexes on Foreign Key columns?

The title is a common question I've received in the past and I thought I'd take a few minutes to explain a bit about keys and indexes… This is by no means a lot of detail regarding relational theory, etc. but there are a few things that we should quickly review to make sure that […]

SQL Server 2005 – Management Studio Scripting Option and SQLCMD (loving it!)

One of my favorite features of Management Studio is the ability to “script” a step rather than execute it. Within SQL Management Studio, each dialog has 4 different options from which to choose (these are including the typical OK/Cancel) but also add a couple more at the top of the dialog:   At the top […]

MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation, Deletes and the “Sliding Window” Scenario and it’s the LAST one!

People always ask me how I keep up to date with SQL Server and how I could possibly know so many intricate details about SQL Server… Well, I test/play/figure it out until it’s obvious (painfully sometimes and sometimes it’s not obvious). So, this blog entry is based on a specific question that made me want […]

MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part III

Q: How do I interpret DBCC SHOWCONTIG WITH ALL_INDEXES on index id = 255 on a table with multiple text columns. I’m looking at a copy of last night’s production after running Maintenance Plan Optimizations. Scan density on table and all indexes is 99 – 100%. Logical Fragmentation is 0% for everything except text column […]

MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part II

Q: Can I automate DBCC DBREINDEX to rebuild all tables on all databases on a server? Actually, yes and no… With stored procedures you’ll have a hard time changing database context without having to use dynamic string execution…. And so this can add a bit of frustration. BUT – it’s your lucky day (and really, […]

MSDN Webcast Q&A: Index Defrag Best Practices – Fragmentation Questions, Part I

Q: Is there another way to determine fragmentation? I don’t want to use DBCC SHOWCONTIG as it locks objects. Well, this is a good question and unfortunately, there really isn’t a way to generate information such as Scan Density or Average Page Density without running DBCC SHOWCONTIG. However, if you decide that you’re only interested […]

MSDN Webcast Q&A: Index Defrag Best Practices – Index Usage Questions

Before I launch into the Q&A from this area there are a few other resources with Index Usage Q&A that you should also check out:             Review the Q&A from the June 11 Webcast here.             Review the Q&A page on SQLskills here.   Q: How does SQL server decide what index to use when […]

MSDN Webcast Q&A – almost ready….

NOTE: These have NOT yet been completed and there are still a few questions that need to be answered. I will wrap these up shortly and also break them down into separate blog entries so they’re easier to wade through. I will also post the pdf, the webcast link (when it’s available), the demo scripts […]

A Simple Start – Table Creation Best Practices

** Well, this started out as an easy “row size” write-up but I ended up adding a bunch of tangents in it and well, there’s apparently more coming because as I got started I realized there’s way more to this topic than I even barely touch on here… fyi :) ** Maybe not something that […]

Database Design considerations – where’s the silver bullet?

Well, the person then asked if we could sitdown… I said sure… We sat down at a table and they proceeded to rummage through their bag, pull out a bunch of paper (which turned out to be a single, very, large piece of plotter paper which was folded up) and proceeded to unfold it and […]

Stored Procedure Parameters giving you grief in a multi-purpose procedure?

Well… the performance ramifications are… not good! Without seeing more of the proc I’d have to suggest that you avoid this like the plague! I think I can convince you with this simple example which shows why it’s better to have multiple procedures and limit the flexibility a bit OR use DSE = dynamic string […]

MSDN Webcast SQL Index Tuning Q&A: Index Management/Maintenance Questions

Index Management/Maintenance Questions: Q: Is there a tool or method for monitoring a database for awhile to identify indexes that are rarely or never used – that should be considered for removing? I think I mentioned this one late in the presentation but it certainly warrants an answer. Unfortunately, the answer is both yes and […]

MSDN Webcast SQL Index Tuning Q&A: Index Creation Questions

Index Creation Questions: Q: In an OLTP server do clustered indexes create a negative performance impact to the constant inserts and updates? The wrong clustered index can for sure… In most environments, having the right clustered index (an index on an ever-increasing key) will be better than a heap (a table without a clustered index) […]

MSDN Webcast SQL Index Tuning Q&A: Tools

Tools Questions: Q: Isn’t there some issue with profiler where it does not recognize DBName but only the DBID? Well, this is a good one – and a frustrating one as well. It is true that Profiler doesn’t always generate a data column value for every type of event. So – if you setup filters […]

MSDN Webcast SQL Index Tuning Q&A: Related Resources

Related Resources: Q: Can I view a recording of this webcast? It should be available on www.microsoft.com/usa/webcasts/ondemand shortly.   Q: Where can we get the powerpoint file? The powerpoint will be available on the MSDN webcast resource page. I will post the exact link here and on SQLskills.com as soon as the final link is […]

Kicking users out of a database for…

All sorts of purposes: maintenance, rebuilding the database objects, dropping/recreating the database in test environments, changing a state option like readonly (did you know that no users can be using the database when you make this change to RO)…. Anyway, lots of reasons and this has always been the case. In previous releases we used […]

Profiler – STOP that scrolling!!!

Have you ever been watching a profiler trace only to find what you’re looking for, select it and then all of a sudden get “auto-scrolled” down to the end… UGH – that’s the default! But – Profiler does have an icon for stopping that behavior. (and since I generally (and personally) HATE most icons cause […]

For Keyboard/keystroke geeks only

OK, so I can’t tell you how fast I type… I have no clue! But – I do know that people get made at me on IM chats because they (well, most of them) cannot keep up (especially my Father!!!)… And the key reason for why??! I tend to stay on the keyboard instead of constantly […]

Random info about documentation and the Books Online

First, do you have the latest version of the SQL Server Books Online? Make sure you do: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Second, do you ever need the info when you’re not at your desk? Make sure you know where they are: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Text in Row table option – a good thing???

In a discussion with Scott about the “text in row” option for LOB (Large Object Data types: text, ntext, and image) data he questioned whether or not it was really a good idea to set… Well, I have mixed feelings about it really. In general I don’t recommend it unless EVERY query tends to want the […]