More live online training from Kimberly in March!

Due to the popularity of Kimberly’s delivery of her new IEVLT: Immersion Event on Very Large Tables: Optimizing Performance and Availability through Partitioning in January, and the fact that many of you told us that you didn’t have a chance to attend, we’re running it again in March! The class will be delivered live via WebEx on March 20-22 (roughly 12-13 hours of content plus open Q&As; about the same as two full workshop days!) and the recording is available to attendees for six months following the end of the class. Note: we’re not planning any more deliveries of IEVLT until 2019.

Here are some quotes from people who attended the January class:

  • “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
  • “The best educator I’ve ever seen. She makes complex concepts “magically” easy to grasp. Incred-amazing.”
  • “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
  • “This was REALLY good. Getting to an IE is tough there are only a few a year and more importantly because there are only a few they cover a pretty broad range of information. Since I do mainly database design and query tuning, I can’t justify much beyond IE1 as I don’t do day to day DBA work. Seeing you were offering the online event focused on a specific topic – very large tables – was PERFECT. I know I really need to improve my knowledge of the options in that area. I recalled the PV/PT architecture from IE1 and knew a refresher, coupled with the new information would be perfect. The cost was BEYOND reasonable. The time frame, at only about 1/2 a day, was easy to justify and easy to manage keeping up with regular work while I did it. So this worked out to be a perfect event.” – Todd Everett, USA
  • “SLIGHTLY prefer the classroom option, but attendance to a location can be very hard to manage and therefore WebEx is a VERY, VERY good alternative. I didn’t think it would be, but I’m definitely sold.” – Kevin Urquhart, UK
  • “I’m really glad I decided to participate in this event. I learnt a lot and I already see lots of opportunities to implement some of the strategies at my workplace (after working through all the materials of course :P).” – Ernst Kruger, South Africa
  • “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
  • “Always love to sit at the feet of Kimberly. Always learn a ton.”
  • “I struggled with WebEx at first… felt a little like watching a video… BUT the moment it became interactive (which was very early on), that whole perception changed. By the end I REALLY enjoyed it as a method.”
  • “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”
  • “Kimberly, I have to tell you that you are hands down the finest educator I have ever met. Your dynamic personality, combined with your depth of knowledge, and passion to fully understand the subject matter are unmatched, in my experience. I feel truly privileged to have had the opportunity to learn from you again and again. You really DO make learning fun. Thank you!” – Mike Petri, USA
  • “I learned a lot of new topics and found this class really useful. Usually when I attend a class or an in person event I learn new things but no where near this class. Thanks for a great class.”

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work well for those in the Americas, Africa, and Europe. We also realize that this is complex, architectural content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

The modules covered will be:

  • Horizontal Partitioning Strategies
  • Partitioned Views
  • Partitioned Tables
  • Tables, Indexes, Keys, and Relationships
  • Implementing the Sliding Window Scenario
  • Key Partitioning Concerns and Considerations
  • Partitioning Techniques Combined
  • Review

The price of the class is US$895 and you can get all the details here.

The class was also announced in our newsletter this morning, with a $100 discount for those people who received that newsletter, valid for two weeks. All future live, online classes will always feature a two-week discount for newsletter subscribers.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these in 2018, on a variety of topics, so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!

SQLskills SQL101: Why does my heap have a bunch of empty pages?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Here’s a question I’m often asked (paraphrasing):

I’ve got a large heap table where the space isn’t being given up when I delete a large number of records, but then when I shrink the database the heap is reduced in size. Can you explain?

This behavior is how SQL Server works, but it’s pretty non-intuitive. When a page in an index becomes empty, it’s always deallocated, as an empty page isn’t allowed in a SQL Server index structure from SQL Server 2005 onward. However, the structure of a heap is different and as a result, the behavior is too.

Whenever a row is deleted in a heap, it’s usually the case that the page containing the row does not become empty. However, if the heap page that the row is stored on becomes empty as a result of the delete, the page cannot be deallocated from the table unless an exclusive (X) table lock is held (to remove the page from the internal free-space “tracking” mechanism that is in place for heaps). It’s not common that a table level X lock is held unless lock escalation has occurred (because you’re deleting enough rows to trigger escalation from individual row X locks to a single table X lock), or if you specifically use the TABLOCK hint on the delete statement, for instance. But, because both of these circumstances are unlikely, the empty heap page usually cannot be deallocated.

There is a Knowledge Base article that describes this phenomenon: KB 913399. However, the KB article only references up to and including SQL Server 2005 but this behavior exists in all version and is very easy to reproduce if you want to prove it to yourself. Also, if you’re using one of the flavors of snapshot isolation then even using TABLOCK won’t allow the pages to be deallocated.

The script below will create a database, create a table with one row per heap page, show you how many pages and rows, and how full each page is. Next it’ll delete all the rows, and show you the pages are still there, with no rows, and empty. Give it a try!

CREATE DATABASE [EmptyHeapTest];
GO
USE [EmptyHeapTest];
GO

CREATE TABLE [test] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
GO

SET NOCOUNT ON;
GO
-- Insert 1000 rows, which will allocate 1000 pages
INSERT INTO [test] DEFAULT VALUES;
GO 1000

-- How many pages and how full?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, 	DEFAULT, 'DETAILED');
GO
page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 1000                 98.974549048678
-- Delete all the rows
DELETE FROM [test];
GO

-- Did all the pages get deleted?
SELECT [page_count], [record_count], [avg_page_space_used_in_percent]
FROM sys.dm_db_index_physical_stats (DB_ID (N'EmptyHeapTest'), OBJECT_ID (N'test'), 0, 	DEFAULT, 'DETAILED');
GO 
page_count           record_count         avg_page_space_used_in_percent
-------------------- -------------------- ------------------------------
1000                 0                    0

The empty pages will be reused by subsequent inserts (into the same table, of course), but if the space isn’t going to be reused following a large delete in a heap, you might consider using the TABLOCK hint to allow the empty pages to be deallocated and the space made available for other objects in the database to use.

Another alternative is to just use a clustered index instead, or if a heap is necessary, you could rebuild the heap using ALTER TABLE … REBUILD (that was added in SQL Server 2008 to support enabling compression on a heap), with the caveat that this will cause all the table’s nonclustered indexes to be rebuilt.

On the extreme end (in my opinion), you could reclaim the empty heap space using a shrink operation. Shrink won’t free up space inside pages as it moves them (with the exception of compacting LOB pages as it goes – somewhat unsuccessfully depending on which version and build you’re on – see KB 2967240), but it will remove empty pages rather than moving them. This will effectively shrink the heap after a large delete, but with the usual caveats about shrink causing index fragmentation and generally being an expensive, slow operation to perform.

So now you know why you may have a bunch of empty pages in your heaps!

Calling all user group leaders! We want to present for you in 2018!

By the end of December, we at SQLskills had presented remotely (and a few in-person) to more than 100 user groups and PASS virtual chapters around the world in 2017, and we already have 45 user group sessions scheduled in 2018!

We’d love to present remotely for your user group in 2018, anywhere in the world. It’s not feasible for us to travel to user groups or SQL Saturdays unless we’re already in that particular city, but remote presentations are easy to do and are becoming more and more popular. We haven’t had any bandwidth problems doing remote presentations in 2017 to groups as far away as South Africa, Australia, and New Zealand, plus Norway, Canada, UK, Poland, Belgium, Brazil, Czech Republic, and Ireland. This way we can spread the community love around user groups everywhere that we wouldn’t usually get to in person.

Note: we have our own Webex accounts which we generally use, or we can use your GoToMeeting or Webex, but you must use computer audio – we won’t call in by phone as the sound quality is too poor. We also will not use Skype/Lync as we’ve had too many problems with it around user group laptops and sound.

So, calling all user group leaders! If you’d like one of us (me, Kimberly, Jon, Erin, Glenn, Tim) to present remotely for you in 2018 (or maybe even multiple times), send me an email and be sure to include:

  • Details of which user group you represent (and if sending from a shared user group account, your name)
  • The usual day of the month, meeting time, and timezone of the user group
  • Which months you have available, starting in January 2018 (a list of available dates would be ideal)
  • Whether you’d like just one or multiple

And I’ll let you know who’s available with what topics so you can pick. We have around 20 topics across the team that we can present on.

What’s the catch? There is no catch. We’re just continuing our community involvement next year and we all love presenting :-)

There’s no deadline for this – send me an email at any time and we’ll see what we can do.

We’re really looking forward to engaging with you all!

Cheers

PS By all means pass the word on to any SharePoint and .Net user group leaders you know too.