Now the pages are linked together!
Note that this is an OFFLINE rebuild, which is the default. What happened is that the offline ALTER TABLE … REBUILD operation uses the part of the underlying functionality for an offline ALTER INDEX … REBUILD operation that builds the leaf level of the index. As that functionality builds a doubly-linked list of pages, the newly rebuilt heap initially has a doubly-linked list of pages! This doesn’t happen for an ONLINE rebuild of the heap, which uses a totally different mechanism.
Although the pages appear doubly-linked, that’s just an artifact of the mechanism used to build the new heap – the linkages aren’t used or maintained.
In other words, you get zero benefit from this. Click through to see Paul’s test script.
Recently we had to restore our 5TB prod database to rebuild an AG node. Here’s some things we learned along the way to hopefully help you speed up your restores.
Backup your VLDB to multiple files. We found 12 to be the sweet spot in our setup. Make sure you’re going to 10gig NICs on both ends of the transfer.
In terms of folder directories on that thing we learned to go wide and small. Let me explain. Our setup is this:
\\datadomain\sql\sqlbu\\\Full or Tlog
Click through for more tips. This is independent of optimizing your restore scripts themselves.
Using VS Code Debug
First, we are going to use VS Code debug option to run PowerShell Out-Of-The-Box. This way we can be use debug to execute and step thru the PowerShell script code.
Open the folder were the scripts are going to be stored. The first time using the Debug it will ask to create the “launch.json” which is needed in order to execute and debug the script. Accept the default values as you can change them later if needed.
Read on; it’s a whole new world…
ReadyRoll automatically generates and adds migration scripts to our project in Visual Studio. This means almost all the manual work of writing (or generating with compare tools) migration scripts is done by ReadyRoll. ReadyRoll can also organise these scripts, using semantic versioning, into a logical folder structure within our project.
This post is a continuation in his database deployment automation series; this post talks mostly about what ReadyRoll is and how to install it.
Over the past couple of years, we have seen the introduction and increasing usage of NVM Express (NVMe) PCIe SSDs based on existing NAND flash technology. These typically have latencies in the 50-100 microsecond range. They also use the newer, much more efficient NVMe protocol and the PCIe interface, giving much better performance than older SAS/SATA SSDs using the old AHCI protocol.
Currently, Hewlett Packard Enterprise (HPE) is selling 8GB NVDIMM modules for their HPE Proliant DL360 Gen9 servers and HPE Proliant DL380 Gen9 servers. These modules have 8GB of DRAM which is backed by 8GB of flash for $899.00, which is pretty expensive per gigabyte. These two-socket servers have 24 memory slots that each support up to 128GB traditional DDR4 DIMMs. Any slots you use for NVDIMM modules won’t be available for regular memory usage. You can use a maximum of 16 memory slots for NVDIMM usage, which gives you a maximum capacity of 128GB. You must use Intel Xeon E5-2600 v4 series processors in order to get official NVDIMM support. Micron is scheduled to release larger capacity 16GB NVDIMMs in October of 2016.
Read the whole thing.
Hooray. Now you have to type less.
Partitioned views don’t need a scheme or a function, there’s no fancy syntax to swap data in or out, and there’s far less complexity in figuring out RIGHT vs LEFT boundaries, and leaving empty partitions, etc. and so forth. You’re welcome.
A lot gets made out of partition level statistics and maintenance being available to table partitioning. That stuff is pretty much automatic for partitioned views, because you have no choice. It’s separate tables all the way down.
Partitioned views, AKA SQL Server 2000 partitioning. I think my favorite use case for them today is to serve as a combination of hot data in a memory-optimized table and cold data on disk.
I fixed an issue that would cause a continual loop to occur and hang the script indefinitely. With this fix, my tests are returning results in just seconds now!
Corrected various typos and details in the results
If you had xp_Cmdshell active before the script, it used to turn it off at the end for compliance. Now the script checks and leaves it active if you had it active. It will still notify you of the results though
This is one of my favorite third-party scripts for configuring a database.