1. The Clustered Index (50 minutes)
Let’s start with something simple – the Users table from Stack Overflow. You get just the clustered index of that table, and Brent gives you a few queries to execute. You’ll learn how to see the real database pages, understand why filtering isn’t necessarily expensive, but sorting data certainly is.
Demo Scripts
If you want to follow along with the demos, I’ll save you from the Herculean task of writing all those little queries down, heh:
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
/* How to Think Like the SQL Server Engine: Part 1, the Clustered Index Video, slides, scripts: https://www.brentozar.com/go/engine License: Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) More info: https://creativecommons.org/licenses/by-sa/3.0/ You are free to: * Share - copy and redistribute the material in any medium or format * Adapt - remix, transform, and build upon the material for any purpose, even commercially Under the following terms: * Attribution - You must give appropriate credit, provide a link to the license, and indicate if changes were made. * ShareAlike - If you remix, transform, or build upon the material, you must distribute your contributions under the same license as the original. */ /* Doorstop */ RAISERROR(N'Did you mean to run the whole thing?', 20, 1) WITH LOG; GO /* This class is based on the Stack Overflow public data dump. You can download any size/version of it below. I'll be using the 10GB "small" version to make my index creations go quickly: https://www.brentozar.com/go/querystack */ USE StackOverflow2010; GO /* And we're going to start with no nonclustered indexes. This stored procedure is included with the StackOverflow2010 database, but if you don't have it, you can get it from here: https://www.brentozar.com/go/dropindexes */ DropIndexes; GO /* If you want to get the exact same query plan costs and parallelism operators that I'm getting, you may also need to set your database to the latest compat level, and set your CTFP and MAXDOP settings to their defaults. If you don't know what these terms mean, just skip this section - that's why it's commented out - it's totally optional, just for the geeks in the room that want to get the exact same screenshots I'm getting. USE [master] GO ALTER DATABASE [StackOverflow2010] SET COMPATIBILITY_LEVEL = 150 GO EXEC sys.sp_configure N'cost threshold for parallelism', N'5' GO EXEC sys.sp_configure N'max degree of parallelism', N'0' GO RECONFIGURE GO USE StackOverflow2010; GO */ SET STATISTICS IO ON; GO SELECT Id FROM dbo.Users; GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01'; GO SELECT Id FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; GO SELECT * FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; GO SELECT * FROM dbo.Users WHERE LastAccessDate > '2014/07/01' ORDER BY LastAccessDate; GO 100 |
31 Comments. Leave new
Hi Brent,
http://www.brentozar.com/go/engine returns a 404.
Thanks,
Paul
Paul – sorry about that! Fixed.
This wasn’t fixed.
Working fine here – can you elaborate?
It says: Sorry because of privacy settings, this video cannot be played here.
That means you’re behind a firewall that blocks Vimeo.
I am the one who configured the firewall and allow Vimeo. I visited the Vimeo site and can watch from there. Is there anything else?
No, that’s the only thing – you may want to try it from another machine? I’ve seen folks with Chrome extensions block the video, too.
Clustered Indices, store every column in the index? So, the Clustered Index is a full copy of the table in reality (or theory)?
They’re not copies, they are the table, ordered by the clustered index key column(s).
Are they same as Index Organized Tables in Oracle?
Girish – good question, but we don’t use Oracle, so we’re not great folks to answer that question.
Yes.
This really a great video series. I am studying on sql 2012 and I’m afraid I could follow because it is intended for Mysql?
No, this is for SQL Server.
Ummm sorry I did ask stupid one! I will try it out. Stack overflow question confused me.
“Clustered index: may only be on one field…” – this could be misunderstood or my english is not to good.
Clustered index can be on one or more than one field.
Roger – in this case, think of it as “may only be on one field, or may be on more than one field.”
Hi Brent,
The link (https://www.brentozar.com/go/engine) does not work for me. Instead, it redirects to (https://www.brentozar.com/training/think-like-sql-server-engine/).
Yes, that’s the right page. What were you looking for?
Hi Brent – Jamesmc.it means that going to https://www.brentozar.com/go/engine does not lead to a download of the stackoverflow sample data as expected but instead just reloads https://www.brentozar.com/training/think-like-sql-server-engine/. Probably it’s a redirect because the URL isn’t found.
Glen – that is the correct page. Here’s the diabolically tricky part: you actually have to read the words on the page. There are what we call “links” that take you to different places, depending on what you’re looking for. It’s how the web works. Enjoy your newfound internet knowledge!
Just to be clear, I’m seeing the same problem a few months later.
Glen – what problem specifically?
Thank yout lots for this. Well done, well presented. This tutorial is very, very informative and useful; I plan to share with my mssql* db dev and dba friends and colleagues. ‘Wish I would have had it when I started out in .NET and SQL Server in 2005.
*honestly, I believe many of these concepts you break down apply to other db platforms (oracle, postgre, mysql, etc.)
I wanted to wait until I had watched every module before commenting. Overall, this training makes me sad. Sad, because I wish I had watched this 5 or so years ago instead of googling everything and reading some poorly written books.
In all seriousness, one of the best set of fundamental modules I have ever watched.
HA! Glad you liked it!
Hi Brent, what setting did you change on the server to make the query parallel by default ?
It usually goes parallel if you have Cost Threshold for Parallelsim at 5 and MAXDOP anything other than 1.
Thank you Brent it’s a great video, love the way you teach in more interactive way then what’s in the class where it makes you fall asleep.
Awww, thanks!