SQL Server: Myths and Misconceptions

Learn how common SQL Server myths and misconceptions can lead to performance, availability, and other problems. Applicable from SQL Server 2005 onwards.
Course info
Rating
(332)
Level
Intermediate
Updated
Jun 6, 2012
Duration
3h 54m
Table of contents
Introduction
Performance Myths and Misconceptions
Transaction Log Myths and Misconceptions
Indexing Myths and Misconceptions
Locking Myths and Misconceptions
Transact-SQL Myths and Misconceptions
Backup Myths and Misconceptions
Restore Myths and Misconceptions
Corruption Myths and Misconceptions
High-Availability Myths and Misconceptions
Description
Course info
Rating
(332)
Level
Intermediate
Updated
Jun 6, 2012
Duration
3h 54m
Description

There is so much information online about SQL Server, however much of it is inaccurate to some degree. In this course, you will learn the truth behind more than 80 myths and misconceptions about SQL Server that can cause you performance and availability problems - from someone who wrote the SQL Server code. This course is perfect for developers and DBAs who have been struggling to make sense of conflicting information about SQL Server. The information in the course applies to all versions from SQL Server 2005 onwards.

About the author
About the author

Paul is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development.

More from the author
More courses by Paul Randal
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Paul Randal, from sqlskills. com. And I'm recording this course for Pluralsight. This course is about debunking SQL Server myths and misconceptions. And this is Module One, the introduction. By way of introduction, it's worth mentioning that SQL Server is a giant server system, there's multiple executables, and even just looking at the SQL Server engine itself, which is what this course is an there's an enormous number of features in the SQL Server engine. What this means is that even experts in SQL Server cannot be experts in absolutely everything. There's just too much to know. So if you're trying to find out information about SQL Server, maybe you've bought a few books about SQL Server or you're looking on line for articles and white papers and blog posts, it's possible that you might come up with some interesting information that might appear to be incorrect, might actually be incorrect, or might actually be conflicting. So imagine you've got a situation where you've got a blog that says feature X does this, and another blog that says feature X doesn't do this, who do you trust, what do you take as the actual correct information. This is actually starting to become a pretty serious problem on the internet, where there's an awful lot of conflicting information. One reason that this can happen is that absolutely anybody can write a blog post or write an article or write a white paper, and it gets picked up by a search engine and it will appear in the search engine search results. And just because the person is maybe from Microsoft or you using SQL Server MVP doesn't actually mean the information is protect. The problem you're going to have, then, is that what you're searching around some of the information you're going to get may be incorrect. Why is this the case?

Performance Myths and Misconceptions
Hi. This is Paul Randal from SQLskills. com. And I'm recording this course for Pluralsight. This course is about debunking SQL Server myths and misconceptions. And this is Module 2: Performance. Of course, everybody wants to have high performance. Nobody sets out to have low performance for their workloads but the problem with attaining high performance is that it takes a bunch of knowledge about how some of the features work, how you should go about tuning for performance. A lot of people, for instance, will walk up to a SQL Server and not know where to start looking for a performance issues. They might find a particular symptom of a problem and think that that must be the root cause, what I call knee jerk performance tuning. Some of the features that you're going to use to help attain performance, for instance, data compression, snapshot isolation, stuff like that. There's lot of misconceptions about how those work. So what we're going to cover in this module is 17 myths and misconceptions around performance, performance tuning, and how you can get better performance for your workload.

Transaction Log Myths and Misconceptions
Paul Randal: Hi! This is Paul Randal from sqlskills. com and I'm recording this course for Pluralsight. This course is by debunking SQL server myths and misconceptions and this is module 3, the transactional log. Wherever I'm teaching a, an internals or performance tuning class I always like to tell the class that in my opinion the, the log and how log and recovery works and how the transactional log behaves are probably the most misunderstood parts of any relational database management system no matter which one you're using Oracle or DB2 or SQL server. The things that confuse people is how logging actually works, what is actually being logged and when things like minimal logging occur, how things are being logged differently. Also when log backups happens, what's actually going on with the log backup, what effect does it have on the log, how do you string log backups together when you're doing restores. So in this module I'm going to present to you 12 different myths about logging and recovery and how the transactional log works.

Indexing Myths and Misconceptions
Hi. This is Paul Randal, from SQLskills. com, and I'm recording this course for Pluralsight. This course is about debunking SQL Server myths and misconceptions, and this is Module 4, Indexing. If you think about performance in SQL Server, there isn't a /faster switch that you can use to make things go faster in SQL Server. But, having the correct non-clustered indexing strategy, having the right clustered index, choosing the right cluster keys is almost a /faster switch, if you think about it. It's probably the nearest thing that SQL Server has to a -- a /faster switch. The problem is, though, how do you choose the right indexes? How do you choose the right index keys? It's very easy to choose the wrong indexes, in which case performance is going to suffer. Once you've got your indexes, you have to make sure that they're healthy and actually being used. That means you're going to be performing index maintenance. So in this module, what I'm going to do is present to you seven myths around indexes, index keys, and index maintenance.

Locking Myths and Misconceptions
( Silence ) Paul Randal: Hi. This is Paul Randal from SQLskills. com. And I'm recording this course for Pluralsight. This course is about debunking SQL Server myths and misconceptions. And this is Module Five, Locking. Locking always occurs in SQL Server, to some degree, and the problem is there's an awful lot of confusion with how locking works, under the covers, when locks are going to be held, how long locks are going to be held for, what does lock escalation mean, how can blocking occur. So there's all kinds of misconceptions about even what kind of operations can cause blocking and when that can be possible. So what I'm going to do in this module is I'm going to present to you six myths around locking, lock escalation and blocking.

Transact-SQL Myths and Misconceptions
Hi, this is Paul Randal from sqlskills. com and I'm recording this course for Pluralsight. This course is about debunking sqlserver myths and misconceptions and this is Module 6, Transact sql. As far as using transact sql is concerned it's just like coding in any other language the issues you have are there's obviously a learning curve where you have to learn transact sql and how to actually string the elements of the language together. There are some things however that the language references don't tell you. So what we're going to talk about in this module is five myths around transact sql usage and it's more about how some of these operations work or don't work under the covers and things that books online don't actually tell you this.

Backup Myths and Misconceptions
Paul Randal: Hi. This Paul Randal from sqlskills. com and I'm recording this course for Pluralsight. This course is about Debunking SQL Server Myths and Misconceptions and this is Model 7; Backup. Backups are really critical for making sure you've got your data properly protected. You can't just rely on having a redundant copy of your data using high availability technologies. It's almost like what they say with security which is defense in depth. So you're going to have high availability technologies giving you a redundant copy of the database and you're going to have to have backups. So it's really critical that you understand how backups work and how to put together a proper backup strategy. To that end, in this module I'm going to show you seven myths around using backups and backup strategy.

Restore Myths and Misconceptions
Hi, this is Paul Randal from sqlskills. com and I'm recording this course for Pluralsight. This course is about debunking sqlserver myths and misconceptions and this is Module 8, Restore. Most of the time when you're doing a restore it's a time critical operation, for instance you're involved in some kind of disaster recovery. You want the restore operations to complete as fast as possible and you want them to complete successfully. Other times you might be doing a restore could be you're prepping def environment, you're prepping a test environment, you might be initiating a data base mirroring partnership, you might be initiating log shipping but any time that you're doing any kind of restore if you make a mistake and have to go back and start again there's an awful lot of time involved and with disaster recovery that time could actually translate into lost revenue for your company. So in this module I'm going to talk about five myths around restore operations.

Corruption Myths and Misconceptions
Hi, this is Paul Randal from SQLskills. com, and I'm recording this course for Pluralsight. This course is about debunking SQL Server myths and misconceptions, and this is Module 9, Corruption. If I'm ever teaching a class that includes a module on database corruption, I ask people to stick up their hands and say who's encountered corruption at some point so far in their career. For the people who haven't yet put their hands up I say they've been lucky, because I always say that in a room full of DBAs you're all going to see corruption at some point in your careers. Dealing with corruption is a lot easier, however, if you understand a bit about how corruption can and cannot occur, you've used CHECKDB before and so you know what corruption looks like, and you've practiced recovering from corruption maybe using repair or maybe using backups. In this module then I'm going to talk about 11 myths about dealing with database corruption and some of the features around recovering from database corruption. ( Pause )

High-Availability Myths and Misconceptions
Paul Randal: Hi! This is Paul Randal from sqlskills. com and I'm recording this course for Pluralsight. This course is by Debunking SQL Server myths and misconceptions and this is module 10, High Availability. Any time you're designing a high availability strategy, the success or failure of your strategy is going to depend on whether you've picked the right technologies based on the requirements that you have. You also have to know how to configure these technologies correctly and also some of the side effects that some of these technologies might have and some of the potentially unwanted behaviors that these technologies might have as well making sure that you understand all these different things is going to mean that your high availability strategies going to work and isn't going to have an effect for instance on the performance of your workload. To that end in this module I'm going to talk about eight myths around high availability features in SQL Server.