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)
  • IESSIS2: Immersion Event on Advanced SQL Server Integration Services
    • 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 :-)

Last chance to see us in Europe until late 2018!

Due to scheduling issues, we’re not presenting any classes in Europe in 2017 or early 2018, so your last chance to come to one of our classes in Europe is in Dublin next month.

Kimberly and I will be teaching our signature IEPTO-1 (formerly IE1) Immersion Event on Performance Tuning and Optimization, in partnership with our great friends Bob and Carmel Duffy of Prodata.

The class will be October 3-7, and is €2,795. You can get all the details on the class page here.

We hope to see you there!

New course: Improving Storage Subsystem Performance

Glenn’s latest Pluralsight course has been published – SQL Server: Improving Storage Subsystem Performance – and is just over two hours long. It’s based on Glenn’s very popular user group/conference sessions and workshops, plus extensive work with SQLskills consulting clients and in his previous roles.

The modules are:

  • Introduction
  • Measuring and Analyzing Storage Subsystem Performance
  • Testing and Benchmarking Storage Subsystems
  • Understanding Storage Types Suitable for SQL Server Workloads
  • Understanding the Effects of RAID Levels on SQL Server Workloads
  • Improving Storage Subsystem Performance

Check it out here.

We now have more than 145 hours of SQLskills online training available (see all our 48 courses here), all for as little as $29/month through Pluralsight (including more than 5,000 other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.

Enjoy!

New course: Consolidation Tactics and Best Practices

Tim’s first ever Pluralsight course has been published – SQL Server: Consolidation Tactics and Best Practices – and is just over two hours long. It consolidates (ha!) all of Tim’s knowledge and experience from managing major consolidation projects over the years, as well as client experience from the SQLskills team.

The modules are:

  • Introduction
  • General Considerations
  • Consolidation Candidates
  • Large Single Instance
  • Multiple Instance
  • Virtualization
  • Migration Strategies

Check it out here.

We now have more than 145 hours of SQLskills online training available (see all our courses here), all for as little as $29/month through Pluralsight (including more than 4,500 other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.

Enjoy!

New course: Replacing Profiler with Extended Events

Erin’s new course is called SQL Server: Replacing Profiler with Extended Events and is just under 2.5 hours long. It’s based on her very popular user group/conference session and will help you move painlessly from using Profiler to using Extended Events – as Extended Events are the only way to monitor all features added in SQL Server 2012 and later.

The modules are:

  • Introduction
  • Transitioning from Profiler’s UI to Extended Events
  • Leveraging the Extended Events UI
  • Understanding Target Options for Extended Events
  • Avoiding Performance Issues with Extended Events

Check it out here.

We now have more than 140 hours of SQLskills online training available (see all our courses here), all for as little as $29/month through Pluralsight (including more than 4,500 other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.

Enjoy!

You can upgrade from any version 2005+ to any other version

There’s a persistent myth that you cannot upgrade a database to a version more that is more then two versions newer.

It’s not true.

You can backup+restore (safest way) or detach+attach (not a safe way) from SQL Server 2005 (or higher) to any other newer version. You just can’t do an in-place upgrade to the latest version from SQL Server 2005.

I didn’t know this until a few weeks ago, and it’s not common knowledge, hence this short blog post.

Here’s the proof, restoring a SQL Server 2005 database directly to SQL Server 2016, and I tried it successfully on SQL Server 2014 and SQL Server 2012 as well.

SELECT @@VERSION;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\SalesDB2005.BAK';
GO
Version
--------------------------------------------------------------
Microsoft SQL Server 2016 (RC3) - 13.0.1400.361 (X64) <snip>

(1 row(s) affected)

<snip> DatabaseName   DatabaseVersion DatabaseCreationDate    <snip>
--------------------------------------------------------------------
       SalesDB        611             2008-08-06 12:47:41.000 
(1 row(s) affected)
RESTORE DATABASE [SalesDB] FROM DISK = N'C:\SQLskills\SalesDB2005.BAK'
WITH MOVE N'SalesDBData' TO N'C:\SQLskills\SalesDBData.mdf',
MOVE N'SalesDBLog' TO N'C:\SQLskills\SalesDBLog.ldf',
REPLACE;
GO
Processed 24480 pages for database 'SalesDB', file 'SalesDBData' on file 1.
Processed 2 pages for database 'SalesDB', file 'SalesDBLog' on file 1.
Converting database 'SalesDB' from version 611 to the current version 852.
Database 'SalesDB' running the upgrade step from version 611 to version 621.
Database 'SalesDB' running the upgrade step from version 621 to version 622.
Database 'SalesDB' running the upgrade step from version 622 to version 625.
Database 'SalesDB' running the upgrade step from version 625 to version 626.
Database 'SalesDB' running the upgrade step from version 626 to version 627.
Database 'SalesDB' running the upgrade step from version 627 to version 628.
Database 'SalesDB' running the upgrade step from version 628 to version 629.

<snip>

Database 'SalesDB' running the upgrade step from version 845 to version 846.
Database 'SalesDB' running the upgrade step from version 846 to version 847.
Database 'SalesDB' running the upgrade step from version 847 to version 848.
Database 'SalesDB' running the upgrade step from version 848 to version 849.
Database 'SalesDB' running the upgrade step from version 849 to version 850.
Database 'SalesDB' running the upgrade step from version 850 to version 851.
Database 'SalesDB' running the upgrade step from version 851 to version 852.
RESTORE DATABASE successfully processed 24482 pages in 0.142 seconds (1346.892 MB/sec).

Very cool! This is going to make upgrading some of our clients a lot easier.

Also, remember that upgrading is a one-way operation. You absolutely cannot take the database and attach or restore it to an older version of SQL Server.

Some Microsoft links around this:

Enjoy!

Announcing the comprehensive SQL Server Wait Types and Latch Classes Library

It’s finally ready!

For the last two years, I’ve been working on-and-off on a new community resource. It was postponed during 2015 while I mentored 50+ people, but this year I’ve had a bunch of time to work on it.

I present to the community a comprehensive library of all wait types and latch classes that have existed since SQL Server 2005 (yes, it includes 2016 waits and latches).

The idea is that over time, this website will have the following information about all wait types and latch classes:

  • What they mean
  • When they were added
  • How they map into Extended Events (complete for all entries already)
  • Troubleshooting information
  • Example call stacks of where they occur inside SQL Server
  • Email link for feedback and questions

As of today, I have complete information for more than 225 common wait types (897 entries in the library) and 26 common latch classes (185 entries in the library), and I’m adding more information constantly.

If there’s one that I haven’t done yet, and you’re really interested in it, click the email link on the page to let me know. Also, if you’ve seen something be a bottleneck that I haven’t, let me know so I can update the relevant page too.

I’m doing this because there is so little information about waits and latches available online and I know that people are constantly searching for information about them. This is very much a labor of love for me as wait statistics are now my favorite area to teach.

Waiting until it’s complete isn’t feasible, so it’s open and available for use now!

Check it out at https://www.SQLskills.com/help/waits and https://www.SQLskills.com/help/latches, or jump to some examples PAGELATCH_EX, SOS_SCHEDULER_YIELD, WRITELOG, CXPACKET.

Enjoy!

Reconciling set-based operations with row-by-row iterative processing

Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators.

The crux of the discussion is: if SQL Server is passing single rows around, how is that set-based operations?

I explained it in two different ways…

SQL Server Example

This explanation compares two ways of doing the following logical operation using SQL Server: update all the rows in the Products table where ProductType = 1 and set the Price field to be 10% higher.

The cursor based way (row-by-agonizing-row, or RBAR) would be something like the following:

DECLARE @ProductID   INT;
DECLARE @Price       FLOAT;

DECLARE [MyUpdate] CURSOR FAST_FORWARD FOR
SELECT [ProductID], [Price]
FROM [Products]
WHERE [ProductType] = 1;

OPEN [MyUpdate];

FETCH NEXT FROM [MyUpdate] INTO @ProductID, @Price;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE [Products]
    SET [Price] = @Price * 1.1
    WHERE [ProductID] = @ProductID;

    FETCH NEXT FROM [MyUpdate] INTO @ProductID, @Price;
END

CLOSE [MyUpdate];
DEALLOCATE [MyUpdate];

This method has to set up a scan over the Products table based on the ProductType, and then runs a separate UPDATE transaction for each row returned from the scan, incurring all the overhead of setting up the UPDATE query, starting the transaction, seeking to the correct row based on the ProductID, updating it, and tearing down the transaction and query framework again each time.

The set-based way of doing it would be:

UPDATE [Products]
SET [Price] = [Price] * 1.1
WHERE [ProductType] = 1;

This will have one scan based on the ProductType, which will update rows matching the ProductType, but the query, transaction, and scan are only set up once, and then all the rows are processed, one-at-a-time inside SQL Server.

The difference is that in the set-based way, all the iteration is done inside SQL Server, in the most efficient way it can, rather than manually iterating outside of SQL Server using the cursor.

Non-Technical Example

This explanation involves a similar problem but not involving SQL Server. Imagine you need to acquire twelve 4′ x 8′ plywood sheets from your local home improvement store.

You could drive to and from the store twelve times, and each time you need to go into the store, purchase the sheet, and wait for a staff member to become available to load the sheet into your pickup truck, then drive home and unload the sheet.

Or you could drive to the store once and purchase all twelve sheets in one go, with maybe four staff members making three trips each out to your pickup, carrying one sheet each time. Or even just one staff member making twelve trips out to your pickup.

Which method is more efficient? Multiple trips to the store or one trip to the store, no matter how many staff members are available to carry the sheets out?

Summary

No-one in their right mind is going to make twelve trips to the home improvement store when one will suffice. Just like no developer should be writing cursor/RBAR code to perform an operation that SQL Server can do in a set-based manner (when possible).

Set-based operations don’t mean that SQL Server processes the whole set at once – that’s clearly not possible as most sets have more rows than your server has processors (so all the rows in the set simply *can’t* be processed at the same time, even if all processors were running the same code at the same time) – but that it can process the set very, very efficiently by only constructing the processing framework (i.e. query plan with operators, scans, etc.) for the operation once and then iterating over the set of rows inside this framework.

PS Check out the technical comment from Conor Cunningham below (Architect on the SQL Server team, and my counterpart on the Query Optimizer when I was a Dev Lead in the Storage Engine for SQL Server 2005)