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.
[wpc_button]
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:
|
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 |