The Curious Case of… extent scan fragmentation

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

There was a Twitter #sqlhelp question this week about whether to be concerned about extent scan fragmentation or logical scan fragmentation when using DBCC SHOWCONTIG.

The real answer is to stop using DBCC SHOWCONTIG as it was deprecated with the release of SQL Server 2005, and use sys.dm_db_index_physical_stats, the replacement DMV I designed, instead.

The new DMV doesn’t report extent scan fragmentation because it doesn’t have to. If you think about it, extent scan fragmentation is a measure of how many times a key-order scan of the leaf level of the index moves from an extent to another extent which isn’t the next physically-contiguous extent. Logical fragmentation is a measure of how many times a key-order scan of the leaf level of the index moves from a page to another page which isn’t the next physically-contiguous page. So extent scan fragmentation is actually part of logical fragmentation, which is why the new DMV doesn’t report it.

Back to the original question: use logical scan fragmentation if you’re still using the old DBCC command, but plan to move to the DMV.

And there’s another reason: extent scan fragmentation in DBCC SHOWCONTIG is documented as not being accurate if the index is on a filegroup with multiple files. It’s been that way since it was originally written for SQL Server 7.0 and we decided not to fix it in SQL Server 2000 or later.

Try one of our new candidate screening tests at Kandio!

We’re excited to announce that we have a new partnership!

We’ve been involved in a lot of recruiting processes for our clients and realized we could save everyone time and money by pre-screening candidates. To that end, we’ve teamed up with Kandio to publish exclusive candidate and skill assessments for SQL Server and other database products.

We encourage you to try out our first two screening assessments for SQL Server for free – test your own SQL skills – and you’ll get the results by email in 24 hours to see if you’d make it to the job interview! :-)

Edit: 4/30/2020 The trial period for these tests is over now.

MS SQL Server Database Administrator Assessment (description here)

  • Edit: 4/30/2020 link and password removed
MS SQL Server Database Developer Assessment (description here)
  • Edit: 4/30/2020 link and password removed

3D printing to help healthcare workers

You may not know that Tim and I are both into 3D printing in a big way. You’ll definitely know that there’s a big shortage of PPE (Personal Protective Equipment) right now across the world. Because of this, many people around the world with 3D printers are helping out by printing masks, parts for face shields, and other things.

Both Tim and I started printing mask straps over the weekend. This is a plastic strap with hooks to attach to the ear loops of face masks. Why? Because wearing a face mask (and maybe one that’s too small because it’s all that’s available) can really hurt the ears of someone who has to wear them through a whole, long shift. The mask strap is a much easier and more comfortable way of alleviating that pressure than something like a paper clip.

I’m printing mask straps that are for use in non-sterile environments and Tim is using material that can be sterilized in an autoclave. We’ve donated about a thousand so far between us.

If you have a 3D printer, you can help too. The STL file is on Thingiverse here and has been approved for use by the US National Institute of Health (see here). Arrange a few of them in your slicer so you can print multiple on each run, and off you go. There may be an organized 3D printing group in your area that you can get with, or contact your local healthcare providers/grocery stores/vets/senior living facilities and so on to see if they’re interested.

It’s only a little thing, but Tim and I are both happy to be able to do *something* to help during this horrible time. I’m only posting this here to help make others with 3D printers aware that they can contribute and know you can request these (send me or Tim email).

Here’s a photo showing my two printers (each run takes a few hours to print):

Plus photos of an obstetrics nurse (friend of Tim) who’s already wearing a strap Tim printed over the weekend, so you can see how it works:

Stay safe and healthy out there!