1 00:00:00,640 --> 00:00:01,720 Hey, Cloud Gurus. 2 00:00:01,720 --> 00:00:04,313 Welcome to our lesson on compressing data. 3 00:00:05,470 --> 00:00:07,200 In this lesson, we'll be taking a look 4 00:00:07,200 --> 00:00:09,920 at our compression options. 5 00:00:09,920 --> 00:00:13,130 And then we'll talk about compression of rowstore objects 6 00:00:13,130 --> 00:00:15,780 and compression of columnstore objects 7 00:00:16,700 --> 00:00:18,723 and wrap everything up with a review. 8 00:00:21,560 --> 00:00:24,670 So let's start by looking at our compression options. 9 00:00:24,670 --> 00:00:27,230 We have different options for rowstore objects 10 00:00:27,230 --> 00:00:29,223 or columnstore objects. 11 00:00:30,410 --> 00:00:33,550 For rowstore, we have row compression. 12 00:00:33,550 --> 00:00:34,960 And this is the most basic level, 13 00:00:34,960 --> 00:00:37,190 where it makes easy compression decisions 14 00:00:37,190 --> 00:00:40,253 such as removing unused space and things like that. 15 00:00:41,130 --> 00:00:43,200 From there, we have page compression, 16 00:00:43,200 --> 00:00:46,320 which also includes row compression. But past that, 17 00:00:46,320 --> 00:00:48,150 it looks for patterns in the data 18 00:00:48,150 --> 00:00:51,090 and makes replacements with smaller values. 19 00:00:51,090 --> 00:00:52,730 This is less useful if you don't have 20 00:00:52,730 --> 00:00:54,280 lots of duplicate data, 21 00:00:54,280 --> 00:00:56,220 because there won't be those repeated patterns 22 00:00:56,220 --> 00:00:57,973 for it to look for and replace. 23 00:00:59,550 --> 00:01:03,510 Under Columnstore, by default, all columnstore objects 24 00:01:03,510 --> 00:01:05,660 have columnstore compression. 25 00:01:05,660 --> 00:01:09,300 This is always on from the get-go and is not configurable. 26 00:01:09,300 --> 00:01:11,860 But if you still want to compress it even further, 27 00:01:11,860 --> 00:01:14,403 there is columnstore archival compression. 28 00:01:15,800 --> 00:01:17,580 No matter which type of object you have 29 00:01:17,580 --> 00:01:20,070 or which level of compression you choose, 30 00:01:20,070 --> 00:01:21,090 one of the great benefits 31 00:01:21,090 --> 00:01:23,370 of Azure SQL Database in the cloud 32 00:01:23,370 --> 00:01:25,860 is that this is all rolled in. 33 00:01:25,860 --> 00:01:28,210 In the past, you may have elected not to implement it 34 00:01:28,210 --> 00:01:29,790 because the cost was so prohibitive 35 00:01:29,790 --> 00:01:31,940 to have enterprise licenses, 36 00:01:31,940 --> 00:01:34,070 but with Azure SQL Database, all of this goodness 37 00:01:34,070 --> 00:01:36,293 is available in any of the pricing tiers. 38 00:01:37,710 --> 00:01:40,870 Looking further into the compression of rowstore objects, 39 00:01:40,870 --> 00:01:43,150 we do this for storage savings. 40 00:01:43,150 --> 00:01:45,470 Of course, it reduces the size of the database 41 00:01:45,470 --> 00:01:46,713 to compress these down. 42 00:01:47,810 --> 00:01:50,430 And also, it improves I/O performance. 43 00:01:50,430 --> 00:01:52,570 The data is stored on fewer pages, 44 00:01:52,570 --> 00:01:54,380 and so there are fewer pages to retrieve 45 00:01:54,380 --> 00:01:56,180 when you're carrying out operations. 46 00:01:57,150 --> 00:02:00,820 The types of objects that this can be used on are 47 00:02:00,820 --> 00:02:03,880 heap tables; clustered indexes, 48 00:02:03,880 --> 00:02:06,170 which is the same as saying a table 49 00:02:06,170 --> 00:02:07,960 that has a clustered index, 50 00:02:07,960 --> 00:02:10,060 because it is stored as a clustered index; 51 00:02:10,900 --> 00:02:14,483 non-clustered indexes; and indexed views. 52 00:02:16,150 --> 00:02:17,910 For partitioned indexes and tables, 53 00:02:17,910 --> 00:02:19,590 you can kind of mix and match 54 00:02:19,590 --> 00:02:22,640 using a different compression type on each partition. 55 00:02:22,640 --> 00:02:24,730 The different partitions do not have to have 56 00:02:24,730 --> 00:02:26,180 the same compression setting. 57 00:02:28,020 --> 00:02:31,810 Some considerations for our rowstore object compression: 58 00:02:31,810 --> 00:02:34,610 it is available in Azure SQL Database, as I said, 59 00:02:34,610 --> 00:02:37,670 but not every edition of SQL server. 60 00:02:37,670 --> 00:02:38,960 Before you go to implement it, 61 00:02:38,960 --> 00:02:41,630 check and make sure that the version you're using 62 00:02:41,630 --> 00:02:42,883 has this capability. 63 00:02:44,590 --> 00:02:47,420 Maximum row size plus compression overhead 64 00:02:47,420 --> 00:02:50,010 can not exceed 8,060 bytes. 65 00:02:50,010 --> 00:02:51,823 So keep that limitation in mind. 66 00:02:53,440 --> 00:02:56,530 Row or page compression can be enabled or disabled, 67 00:02:56,530 --> 00:02:59,460 both online or offline, 68 00:02:59,460 --> 00:03:00,610 so it's nice to have that option 69 00:03:00,610 --> 00:03:03,060 to keep on moving with your production workload 70 00:03:03,060 --> 00:03:04,803 while enabling this compression. 71 00:03:06,860 --> 00:03:09,340 Disk space requirements when enabling or disabling 72 00:03:09,340 --> 00:03:12,653 are the same as when you're creating or rebuilding an index. 73 00:03:15,430 --> 00:03:18,470 Here's an example of enabling compression. 74 00:03:18,470 --> 00:03:21,450 We use the ALTER TABLE statement to carry this out, 75 00:03:21,450 --> 00:03:22,820 telling it whether to rebuild 76 00:03:22,820 --> 00:03:25,470 all of the partitions or specific ones. 77 00:03:25,470 --> 00:03:27,400 And then within the width clause, 78 00:03:27,400 --> 00:03:29,540 give it the type of compression to enable. 79 00:03:29,540 --> 00:03:31,373 In this case, row compression. 80 00:03:33,550 --> 00:03:36,540 Moving on to our columnstore objects, 81 00:03:36,540 --> 00:03:39,430 as we said, columnstore compression is on by default 82 00:03:39,430 --> 00:03:41,603 and it is not user configurable. 83 00:03:42,690 --> 00:03:44,890 However, if you'd like to compress further, 84 00:03:44,890 --> 00:03:47,860 there is columnstore archival compression, 85 00:03:47,860 --> 00:03:49,080 and you use this when you need 86 00:03:49,080 --> 00:03:50,800 to reduce the size of the data 87 00:03:50,800 --> 00:03:54,000 and can afford the extra CPU and time overhead 88 00:03:54,000 --> 00:03:55,803 required when retrieving the data. 89 00:03:57,600 --> 00:03:59,580 The type of objects this can be used on 90 00:03:59,580 --> 00:04:03,100 are a columnstore table or clustered index. 91 00:04:03,100 --> 00:04:04,660 And that's really saying the same thing, 92 00:04:04,660 --> 00:04:07,100 a columnstore table stored as a clustered index 93 00:04:07,100 --> 00:04:08,043 is the same thing. 94 00:04:09,010 --> 00:04:12,730 You can also use it on non-clustered columnstore indexes. 95 00:04:12,730 --> 00:04:14,920 And just like with our rowstore objects, 96 00:04:14,920 --> 00:04:17,580 this can be configured per partition. 97 00:04:17,580 --> 00:04:18,590 Just because you enable 98 00:04:18,590 --> 00:04:20,940 archival compression on 1 partition 99 00:04:20,940 --> 00:04:22,760 doesn't mean you have to on the others, 100 00:04:22,760 --> 00:04:23,773 and vice versa. 101 00:04:25,840 --> 00:04:29,030 Some considerations with this type of compression, 102 00:04:29,030 --> 00:04:31,300 indexes with columnstore archival compression 103 00:04:31,300 --> 00:04:33,300 are going to be slower. 104 00:04:33,300 --> 00:04:36,330 As we said, there is an extra CPU and time cost 105 00:04:36,330 --> 00:04:38,050 when working with the data. 106 00:04:38,050 --> 00:04:40,853 And so that is a sacrifice you make for the compression. 107 00:04:41,980 --> 00:04:44,190 So we mostly use this to reduce the size of data 108 00:04:44,190 --> 00:04:46,570 that is not accessed frequently. 109 00:04:46,570 --> 00:04:47,550 That way you're not getting hit 110 00:04:47,550 --> 00:04:49,683 with those extra costs very often. 111 00:04:51,980 --> 00:04:54,720 Very similar to how we enabled compression 112 00:04:54,720 --> 00:04:56,910 on our rowstore objects earlier, 113 00:04:56,910 --> 00:04:59,120 we use the ALTER TABLE statement again, 114 00:04:59,120 --> 00:05:01,360 specify which partitions to rebuild, 115 00:05:01,360 --> 00:05:03,390 and then give our data compression 116 00:05:03,390 --> 00:05:07,000 of columnstore_archive. 117 00:05:07,000 --> 00:05:08,350 And it's as simple as that. 118 00:05:10,860 --> 00:05:14,200 By way of review, for rowstore objects, 119 00:05:14,200 --> 00:05:16,683 you can use row or Page Compression. 120 00:05:18,160 --> 00:05:22,100 Columnstore objects have columnstore compression by default. 121 00:05:22,100 --> 00:05:23,520 But if you want to kick that up a notch, 122 00:05:23,520 --> 00:05:26,660 there is Columnstore archival compression 123 00:05:26,660 --> 00:05:28,713 to further reduce the size of the data. 124 00:05:29,700 --> 00:05:30,910 That's it for this lesson. 125 00:05:30,910 --> 00:05:31,930 Thank you for joining me. 126 00:05:31,930 --> 00:05:34,130 I hope it's been helpful for you. 127 00:05:34,130 --> 00:05:35,500 Take some time to absorb that 128 00:05:35,500 --> 00:05:38,010 and think about what compression type you might use 129 00:05:38,010 --> 00:05:39,800 in your particular scenario. 130 00:05:39,800 --> 00:05:40,633 And when you're ready, 131 00:05:40,633 --> 00:05:42,100 I'll see you in the next video.