Capturing spinlock statistics for a period of time

This is a quick post to blog a script that allows spinlock statistics to be captured for a defined period of time (as I need to reference it in my next post). Enjoy!

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##TempSpinlockStats1')
	DROP TABLE [##TempSpinlockStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##TempSpinlockStats2')
	DROP TABLE [##TempSpinlockStats2];
GO

-- Baseline
SELECT * INTO [##TempSpinlockStats1]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Now wait...
WAITFOR DELAY '00:00:05';
GO

-- Capture updated stats
SELECT * INTO [##TempSpinlockStats2]
FROM sys.dm_os_spinlock_stats
WHERE [collisions] > 0
ORDER BY [name];
GO

-- Diff them
SELECT
		'***' AS [New],
		[ts2].[name] AS [Spinlock],
		[ts2].[collisions] AS [DiffCollisions],
		[ts2].[spins] AS [DiffSpins],
		[ts2].[spins_per_collision] AS [SpinsPerCollision],
		[ts2].[sleep_time] AS [DiffSleepTime],
		[ts2].[backoffs] AS [DiffBackoffs]
	FROM [##TempSpinlockStats2] [ts2]
	LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
		ON [ts2].[name] = [ts1].[name]
	WHERE [ts1].[name] IS NULL
UNION
SELECT
		'' AS [New],
		[ts2].[name] AS [Spinlock],
		[ts2].[collisions] - [ts1].[collisions] AS [DiffCollisions],
		[ts2].[spins] - [ts1].[spins] AS [DiffSpins],
		CASE ([ts2].[spins] - [ts1].[spins]) WHEN 0 THEN 0
			ELSE ([ts2].[spins] - [ts1].[spins]) /
				([ts2].[collisions] - [ts1].[collisions]) END
				AS [SpinsPerCollision],
		[ts2].[sleep_time] - [ts1].[sleep_time] AS [DiffSleepTime],
		[ts2].[backoffs] - [ts1].[backoffs] AS [DiffBackoffs]
	FROM [##TempSpinlockStats2] [ts2]
	LEFT OUTER JOIN [##TempSpinlockStats1] [ts1]
		ON [ts2].[name] = [ts1].[name]
	WHERE [ts1].[name] IS NOT NULL
	AND [ts2].[collisions] - [ts1].[collisions] > 0
ORDER BY [New] DESC, [Spinlock] ASC;
GO

Example output (trimmed to fit here):

New  Spinlock                DiffCollisions   DiffSpins   SpinsPerCollision DiffSleepTime   DiffBackoffs
---- ----------------------  ---------------- ----------- ----------------- --------------- ------------
     ALLOC_CACHES_HASH       999              257750      258               0               5
     BLOCKER_ENUM            103              27250       264               0               2
     CMED_HASH_SET           286              71500       250               0               0
     COMPPLAN_SKELETON       148              37000       250               0               0
     DBTABLE                 14               3500        250               0               0
     FGCB_PRP_FILL           4                1000        250               0               0
     FREE_SPACE_CACHE_ENTRY  983              255250      259               0               7
     LOGCACHE_ACCESS         3353             314628      93                0               2241
     LOGFLUSHQ               797              206250      258               0               10
     OPT_IDX_STATS           147              36750       250               0               0
     RESQUEUE                23               5750        250               0               0
     SECURITY_CACHE          106              26500       250               0               0
     SOS_CACHESTORE          235              60750       258               0               3
     SOS_OBJECT_STORE        55               13750       250               0               0
     SOS_SCHEDULER           219              54750       250               0               0
     SOS_SUSPEND_QUEUE       72               18000       250               0               0
     SOS_TASK                69               18750       271               0               2
     SQL_MGR                 394              98500       250               0               0
     XDES                    52               13000       250               0               0
     XDESMGR                 840              341500      406               0               16
     XTS_MGR                 165              42750       259               0               2

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 :-)

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!