Microsoft SQL Server 2012 Admin (70-462) Tutorials
Improvements in 2012 / Scalability and Performance Enhancements
Subtitles of the Movie (Microsoft SQL Server 2012 Admin (70-462) / Improvements in 2012 / Scalability and Performance Enhancements)
Scalability and Performance Enhancements in SQL Server 2012 were a major focus for Microsoft and for one simply good reason. As data storage continued to grow and data delivery performance becomes more and more critical. And a better way to think about this is, if your data solution doesn't allow people's data to expand and still deliver satisfactory performance. As far as getting back to that data, using that data to relate on a transactional basis with our customers. Then it's simply not a solution we can afford, regardless of how inexpensive it is, regardless of how easy it is to use. So with that in mind Microsoft really focused on Scalability and Performance once again and it looks like they've hit a home run. There are Three Main Enhancements you are going to hear a lot about, about SQL Server 2012 in the areas of Scalability and Performance. The first one is called a Columnstore Indexes. The second one is Increased Partition Support and the third one Online Index Maintenance. These three things are going to cause some really neat things to happen. First of all let's take a look at Columnstore Indexes. This is an In-memory Index that's built directly in the Relational Engine. Now this is fundamentally changing the way index data is stored and managed in SQL Server. In the past they used the B-Tree Format and I even did some cute little animations in some of the earlier SQL Courses about how the B-Tree Format works. And once you've, you know determined a direction in the B-Tree you keep lopping off 50 percent of what's left and you just kind of narrow it down to your result. Well in a Columnstore Index, we're not using B-Tree anymore, each column is stored in a separate disk page instead of a row. So basically what is happening here, the storage difference for this index is very similar to what we do with a Pivot Table in Excel. We kind of flip it and the column values become row values and so we're storing all of our column values together in rows and then our individual rows break out a little bit differently. Now here's the deal and this is astounding, by doing this you can realize an average of 10 to 100 times better query performance using a Columnstore Index. Now there are documented things out there on the Internet and out in some of the forums where people are reporting 400 percent increases in query performance by using this Columnstore Index. This is something you need to nerd out on a little bit, read about, play with, we'll go into a little more detail later on in the course but Columnstore Indexes are going to be big. Partition Support has been increased and of course Partition is where we can basically separate a table into multiple pieces, it helps us with locking and all sorts of things. Previous SQL Server versions let's you do up a 1,000 partition per table by default and in SQL Server 2012 they've upped that just a little bit as you see here. You can, it now supports 15,000 partitions per table by default. So we can split our tables into lots of little pieces and update and manipulate to our hearts content. Now the Online Index Maintenance, this is not a really huge one but it is if you are a person who uses these functionalities like your databases. Now in the past, Online Indexing was possible sometimes. It depended on what kind of data, you couldn't rebuild an index online if it included columns that were large strings. In SQL Server 2012 you can if you've got varchar max, nvarchar max which is the, the unicode version of nvarchar and varbinary max. Those data type columns can now be created, rebuilt and dropped as an online operation. Now this is huge because when you have data that's changing very rapidly, your Index Functionality is changing, your Index Statistics are changing. And in the old days you had to actually stop, take it offline and rebuild it, then bring it back. No, not any more, now with these three, you can rebuild this Index online. Now is this going to be a big deal for a lot of people? Maybe not, if you're not using varchar max, nvarchar max and varbinary max, it's not going to be a big deal to you. If you are using these, it's a real big deal. Okay. So Online Index Maintenance is another one, it's going to get a lot of attention out there. So Scalability and Performance, Microsoft continues to push and push hard and they are really making some great strides here.
Tutorial Information
| Course: | Microsoft SQL Server 2012 Admin (70-462) |
| Author: | Mark Long |
| SKU: | 34342 |
| ISBN: | 978-1-61866-048-0 |
| Release Date: | 2012-06-18 |
| Duration: | 9 hrs / 99 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
