Press "Enter" to skip to content

Curated SQL Posts

Hypervisor-Driven Wait Stats

Published 2017-06-07 by Kevin Feasel

Paul Randal explains that delays in the hypervisor layer could be responsible for SOS_SCHEDULER_YIELD waits in SQL Server:

Specifically, I was concerned about SOS_SCHEDULER_YIELD waits. This is a special wait type that occurs when a thread is able to run for 4ms of CPU time (called the thread quantum) without needing to get suspended waiting for an unavailable resource. In a nutshell, a thread must call into the SQLOS layer every so often to see whether it has exhausted its thread quantum, and if so it must voluntarily yield the processor. When that happens, a context switch occurs, and so a wait type must be registered: SOS_SCHEDULER_YIELD. A deeper explanation of this wait type is in my waits library here.

My theory was this: if a VM is prevented from running for a few milliseconds or more, that could mean that a thread that’s executing might exhaust its thread quantum without actually getting 4ms of CPU time, and so yield the processor causing an SOS_SCHEDULER_YIELD wait to be registered. If this happened a lot, it could produce a set of wait statistics for a virtualized workload that appears to have lots of SOS_SCHEDULER_YIELDs, when in fact it’s actually a VM performance problem and the SOS_SCHEDULER_YIELD waits are really ‘fake’.

Read on for more details, and definitely check out the link.  It was an eye-opener when I learned that SOS_SCHEDULER_YIELD didn’t mean “need more/more powerful CPUs.”

Comments closed

Abusing The Uniquifier

Published 2017-06-07 by Kevin Feasel

Denis Gobo shows what happens when you run out of unique values available to the uniquifier:

You would get the following error..straight from the beast himself apparently

Msg 666, Level 16, State 2, Line 1
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID 72057594039173120. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

I will be using DBCC PAGE and DBCC IND in this blog post, if you want to learn how to use these yourself, take a look at How to use DBCC PAGE

One horror story along these lines I’ve heard was a system where the developers would insert every new row with a clustered index value of 0 and then subsequently update the row to set the column to its correct value.  This does not decrement the uniquifier, though, so eventually you hit the limit even if there are only a relatively small number of 0-valued rows.

Comments closed

Make Those Clustered Indexes Unique

Published 2017-06-07 by Kevin Feasel

Thomas Rushton shows what happens when your clustered index is not unique and you have a lot of time to kill:

The theory behind clustered indexes is that they are (usually) unique – after all, they define the logical layout of your table on disk. And if you have multiple records with the same clustering index key, then which order would they be in? If you don’t define the CI as unique, then SQL Server will add (behind the scenes) a so-called “Uniqueifier” (or maybe “uniquifier”) to fix that. Grant’s first post in the thread referenced above gives some information about how to see this Uniqu[e]ifier in the table structure itself.

Read the whole thing.

Comments closed

BDD In Spark

Published 2017-06-06 by Kevin Feasel

Aaron Colcord and Zachary Nanfelt explain how to use Cucumber to create behavior-driven development tests on Apache Spark:

Cucumber allows us to write a portion of our software in a simple, language-based approach that enables all team members to easily read the unit tests. Our focus is on detailing the results we want the system to return. Non-Technical members of the team can easily create, read, and validate the testing of the system.

Often Apache Spark is one component among many in processing data and this can encourage multiple testing frameworks. Cucumber can help us provides a consistent unit testing strategy when the project may extend past Apache Spark for data processing. Instead of mixing the different unit testing strategies between sub-projects, we create one readable agile acceptance framework. This is creating a form of ‘Automated Acceptance Testing’.

Best of all, we are able to create ‘living documentation’ produced during development. Rather than a separate Documentation process, the Unit Tests form a readable document that can be made readable to external parties. Each time the code is updated, the Documentation is updated. It is a true win-win.

It’s an interesting mix.  I’m not the biggest fan of BDD but I’m happy that this information is out there.

Comments closed

S3 And HDFS Data Migration

Published 2017-06-06 by Kevin Feasel

Ilya Yalovyy looks at S3DistCp, which allows you efficiently to migrate data back and forth between HDFS and S3:

Raw files often land in S3 or HDFS in an uncompressed text format. This format is suboptimal both for the cost of storage and for running analytics on that data. S3DistCp can help you efficiently store data and compress files on the fly with the --outputCodec option:

$ s3-dist-cp --src s3://my-tables/incoming/hourly_table_filtered --dest s3://my-tables/incoming/hourly_table_gz --outputCodec=gz

The current version of S3DistCp supports the codecs gzip, gz, lzo, lzop, and snappy, and the keywords none and keep (the default). These keywords have the following meaning:

  • “none” – Save files uncompressed. If the files are compressed, then S3DistCp decompresses them.

  • “keep” – Don’t change the compression of the files but copy them as-is.

This is an important article if you’ve got a Hadoop cluster running on EC2 nodes.

Comments closed

Memory Grants Affecting Columnstore Load

Published 2017-06-06 by Kevin Feasel

Denzil Ribeiro explains how memory grant pressure can determine whether a columnstore bulk insert skips the deltastore or not:

We found that only at the beginning of the run, there was contention on memory grants (RESOURCE_SEMAPHORE waits), for a short period of time. After that and later into the process, we could see some latch contention on regular data pages, which we didn’t expect as each thread was supposed to insert into its own row group. You would also see this same data by querying sys.dm_exec_requests live, if you caught it within the first minute of execution, as displayed below.

This is useful in case you run into the issue, but also useful as a case study on effective troubleshooting.

Comments closed

Hash Tables In Powershell

Published 2017-06-06 by Kevin Feasel

Adam Bertram explains what hash tables are and why they’re useful:

Notice that each of the keys is unique. This is required in a hash table. It’s not possible to add two keys with the same name. Below I’ve defined the SomeKey2 key twice.

We’ve just talked about creating a hash table and adding key/value pairs at creation time. It’s also possible to add keys to an already created hash table in a number of different ways, either through dot notation, enclosing the key in brackets, or by using the Add() method. Each approach has the same effect.

Hash tables are quite useful in Powershell for storing key-value pairs, like if you’re building a dictionary of configuration settings.

Comments closed

Watch Those Aggregates

Published 2017-06-06 by Kevin Feasel

Ronald Dameron warns us about graphs in the Azure portal:

I would expect to see the CPU spike with the same value no matter what time range I selected. But, to see the spike that fired the alert, I had to to “Edit” the chart and select different time ranges to see the differences. It wasn’t until I selected a narrow custom time range that the CPU graph would display the CPU spike that corresponded to the alert firing. The alert fires if the CPU percentage exceeds 80% over 15 minutes. So, if you “know” something happened, try different time ranges but especially the custom range to find what you are looking for.

For your highbrow reference of the quarter, FA Hayek on John Maynard Keynes’s Treatise of Money:  “Mr. Keynes’s aggregates conceal the most fundamental mechanisms of change.”

This isn’t an Azure-specific problem; it’s something we have to think about whenever we aggregate data.

Comments closed

Misleading Perfmon Counters

Published 2017-06-06 by Kevin Feasel

Kendra Little describes five Performance Monitor counters whose metrics aren’t quite as straightforward as you’d think:

SQLServer: Locks – Average Wait Time (ms); Lock Wait Time (ms);  Lock Waits/sec

The idea: Alert if you have high lock waits

The problem: These counters update when the lock wait ends, not while it’s ongoing. Let’s say you have a query that’s blocked for five minutes. You’ll only see this in the perf monitor when it becomes un-blocked.

These counters can cause confusion and make you look for blocking queries at the wrong times.

Kendra provides better solutions for each of the five perfmon counters, so read the whole thing.

Comments closed