Physical Database Design Considerations and a bit of freediving

Something that always amazes me is that people think SQL Server is easy. And, I’d like to (at least partially) blame Microsoft marketing for that. Over the years they’ve propagated this notion of SQL Server being a simplified database environment even “zero-administration” at one point (anyone remember that marketing nightmare?). And, while there are MANY […]

How much does that key cost? (plus sp_helpindex9)

OK, two in two days? What's wrong with me (is what Paul said) after he said – who are you and where is my wife? But, this one is a short one, specifically a follow-on to my prior post about "Disk space is cheap…" I did a bit of simple math on the internal overhead and […]

Disk space is cheap…

.  THAT'S NOT THE POINT!!! .  The simple point is that bad (or sloppy/lazy) design cannot be tuned. If you think that data type choice, nullability, keys – don't really matter – you won't scale. It is possible that you may completely fail because of this. Have you ever heard (or possibly said?), let's just […]

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

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

GUIDs as PRIMARY KEYs and/or the clustering key

(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!)     Expanding on the topic of "are you kidding me"… one of the MOST PREVALENT problems I see today is the dreaded "GUIDs as PKs" problem. However, just to be […]

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

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

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

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

The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment

OK, you’ve clicked through… Here’s the link: The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment

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

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

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