SQLintersection Fall 2016

As we head into our 8th SQLintersection next month, I’m excited to say that it’s our most diverse, complete, and information-packed show yet! We have 3 precon days at the show and with our postcon day, there are 10 full-day workshops from which to choose. We have SQL keynotes that are relevant and timely, including Microsoft expert Bob Ward and one from SQL Sentry about productivity using their fantastic tools (especially cool now that all version of Plan Explorer are free!), plus 40 technology-focused (NOT marketing) sessions from which to choose.

You’ll learn proven problem-solving techniques and technologies you can implement immediately. Our focus is around performance monitoring, troubleshooting, designing for scale and performance, cloud, as well as new features in SQL Server 2012, 2014, and 2016. It’s time to determine your 2008 migration strategy – should you upgrade to 2016 directly? This is the place to figure that out!

If you’re interested in how we got here – check out some of Kimberly’s past posts:

  1. SQLintersection: a new year, a new conference
  2. SQLintersection’s Fall Conference – It’s all about ROI!
  3. Fall SQLintersection is coming up soon and we can’t wait!
  4. SQLintersection Conference and SQLafterDark Evening Event – what a fantastic week in VegasSQLafterDark

With minimal to no marketing filler, we’ve largely kept our conference focus on ROI and technical content (performance / troubleshooting / tales-from-the-trenches with best practices on how to fix them ) but we’ve also added even more social events so that you really get time to intersect with the conference attendees and speakers. The addition of the SQL-specific evening event SQLafterDark was wildly popular from some of our past shows and that’s returning for Fall!

 

SQLintersection: Great Speakers!

Once again, I think a great show starts with great speakers and current / useful content. All of these speakers are industry-experts that have worked in data / SQL for years (some can even boast decades) but all are still focused on consulting and working in the trenches. And, they’re good presenters! Not only will you hear useful content but you’ll do so in a way that’s digestible and applicable. Every speaker is either an MCM (Master), a SQL Server MVP, (or both), or a Microsoft employee. But, regardless of their official credentials – ALL are focused on providing the most ROI that’s possible in their session(s) and/or their workshops. Check out this phenomenal list of speakers:

  • Aaron Bertrand
  • Ben Miller
  • Bob Ward
  • David Pless
  • Erin Stellato
  • Jes Borland
  • Jonathan Kehayias
  • Justin Randall
  • Kimberly L. Tripp
  • Paul S. Randal
  • Tim Chapman
  • Tim Radney

You can read everyone’s full bio on our speaker page here.

SQLintersection: When is it all happening?

The conference officially runs from Wednesday, October 26 through Friday, October 28 with pre-conference and post-conference workshops that extend the show over a total of up to 7 full days. For the full conference, you’ll want to be there from Sunday, October 23 through Saturday, October 29.

  • Sunday, October 23 – pre-con day. There is one workshop running:
    • The T-SQL Programming Workshop with Tim Chapman
  • Monday, October 24 – pre-con day. There are three workshops running:
    • Performance Troubleshooting using Waits and Latches with Paul S. Randal
    • Getting Started with SQL Server in Azure with Jes Borland
    • SQL Server 2014 and 2016 New Features and Capabilities with David Pless and Tim Chapman
  • Tuesday, October 25 – pre-con day. There are three workshops running during the day with the first keynote of the conference on Monday evening:
    • Indexing for Performance with Kimberly L. Tripp
    • PowerShell for the DBA from 0-60 in a Day with Ben Miller
    • Finding and Fixing Performance Problems in SQL Server with Erin Stellato and Jonathan Kehayias
  • Wednesday, October 26 through Friday, October 28 is the main conference. Conference sessions will run all day in multiple tracks:
    • Check out our sessions online here
    • Be sure to check out our cross-conference events and sessions
    • Get your pop-culture trivia and techie-SQL-trivia hat on and join us for SQLafterDark on Thursday evening, October 27
  • Saturday, October 29 is our final day with three post-conference workshops running:
    • PowerShell for the DBA from 60-120 in a Day with Ben Miller
    • Common SQL Server Mistakes and How to Fix Them! with Tim Radney
    • Very Large Tables: Optimizing Performance and Availability Through Partitioning with Kimberly L. Tripp

SQLintersection: Why is it for you?

If you want practical information delivered by speakers that not-only know the technologies but are competent and consistently, highly-rated presenters – this is the show for you. You will understand the RIGHT features to troubleshoot and solve your performance and availability problems now!

Check us out: www.SQLintersection.com.

We hope to see you there!

PS – Use the discount code ‘SQLskills’ when you register and receive $50 off registration!

Spring 2017 classes in Chicago open for registration

I’ve just released our first set of classes for 2017 for registration, including a new 3-day class on PowerShell!

Our classes in April/May will be in Chicago, IL:

  • IEPTO1: Immersion Event on Performance Tuning and Optimization – Part 1
    • April 24-28 (US$200 discount for registering in 2016)
  • IESSIS1: Immersion Event on Learning SQL Server Integration Services
    • April 24-28 (US$200 discount for registering in 2016)
  • IE0: Immersion Event for Junior/Accidental DBAs
    • April 24-26 (US$120 discount for registering in 2016)
  • IEPTO2: Immersion Event on Performance Tuning and Optimization – Part 2
    • May 1-5 (US$200 discount for registering in 2016)
  • IEBI: Immersion Event on Business Intelligence
    • May 1-5 (US$200 discount for registering in 2016)
  • IEHADR: Immersion Event on High Availability and Disaster Recovery
    • May 8-12 (US$200 discount for registering in 2016)
  • IEPDS: Immersion Event on Practical Data Science
    • May 8-12 (US$200 discount for registering in 2016)
  • ** NEW **IEPS: Immersion Event on PowerShell for SQL Server DBAs
    • May 8-10 (US$120 discount for registering in 2016)

We’ll likely have some US classes in the second half of 2017 (details in a month or two), and there will be NO classes in Europe in 2017.

Note that we will be retiring our IEHADR class after 2017! This is your last chance to see us teach this material in person.

As you can see, we’re offering discounts off the early-bird price for all our 2017 classes if you register before the end of this year. The regular early-bird prices will apply from January 1st, 2017. If you’ve previously attended an Immersion Event, there’s a larger discount that’s always available to you whenever you register – details on the class pages.

You can get all the logistical, registration, and curriculum details by drilling down from our main schedule page.

We hope to see you there!

When heap data pages become linked…

The pages at each level of an index are linked together in a doubly-linked list (using the m_nextPage and m_prevPage fields in their page headers) to allow ascending-order and descending-order scans, based on the index key(s).

Data pages in a heap are NOT linked together, as there’s no ordering in a heap.

However, there is a special case when the data pages in a heap will become linked together in a doubly-linked list…

Here’s a script that sets up a heap and fills four data pages:

USE [master];
GO
DROP DATABASE [HeapTest];
GO
CREATE DATABASE [HeapTest];
GO
USE [HeapTest];
GO

CREATE TABLE [Test] ([c1] INT IDENTITY, [c2] VARCHAR (4000) DEFAULT REPLICATE ('Paul', 250));
GO

SET NOCOUNT ON;
GO

INSERT INTO [Test] DEFAULT VALUES;
GO 28

We can see the pages in the index using the undocumented DMV sys.dm_db_database_page_allocations that was added in SQL Server 2012:

SELECT
    [allocated_page_file_id] AS [FileID],
    [allocated_page_page_id] AS [PageID],
    [next_page_file_id] AS [NextFileID],
    [next_page_page_id] AS [NextPageID],
    [previous_page_file_id] AS [PrevFileID],
    [previous_page_page_id] AS [PrevPageID]
FROM
    sys.dm_db_database_page_allocations (
        DB_ID (N'HeapTest'),    -- database ID
        OBJECT_ID (N'Test'),    -- object ID
        0,                      -- index ID
        NULL,                   -- partition ID
        'DETAILED')             -- scanning mode, DETAILED required for my WHERE clause
WHERE [page_type] = 1; -- Just data pages
GO
FileID PageID      NextFileID NextPageID  PrevFileID PrevPageID
------ ----------- ---------- ----------- ---------- ----------
1      247         NULL       NULL        NULL       NULL
1      289         NULL       NULL        NULL       NULL
1      290         NULL       NULL        NULL       NULL
1      291         NULL       NULL        NULL       NULL

Now I’ll rebuild the heap, using functionality that was added in SQL Server 2008 to allow data compression to be enabled for a heap:

ALTER TABLE [Test] REBUILD;
GO

And now running the DMV query again, gives:

FileID PageID      NextFileID NextPageID  PrevFileID PrevPageID
------ ----------- ---------- ----------- ---------- -----------
1      296         1          297         NULL       NULL
1      297         1          298         1          296
1      298         1          299         1          297
1      299         NULL       NULL        1          298

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.

To prove it, I’ll update one of the rows to make it longer than there is space on its page, so it’ll be moved to a new page as a forwarded record:

UPDATE [Test] SET c2 = REPLICATE ('Long', 1000) WHERE c1 = 1;
GO

And running the DMV again gives:

FileID PageID      NextFileID NextPageID  PrevFileID PrevPageID
------ ----------- ---------- ----------- ---------- -----------
1      288         NULL       NULL        NULL       NULL
1      296         1          297         NULL       NULL
1      297         1          298         1          296
1      298         1          299         1          297
1      299         NULL       NULL        1          298

The new page, (1:288), was added to the heap but was not linked to any of the pages, and the existing pages were not updated to link to it.

Bottom line: there’s usually a special case exception to every ‘rule’ in SQL Server :-)