In the Star Wars universe, the nerf was an adaptable, hairy, slow, grumpy animal that was important for its meat. In SQL Server, the Statistic is a flexible, hairy, (sometimes) slow, grumpy blob that is important in query compilation. The similarities don’t end there, though. Both nerfs and statistics can get unwieldy if not managed properly.
I recently wanted to explore potential candidates for data compression in our environment. I found a few ways to go about doing this, most notably this TechNet article that steps through strategy and planning. This was helpful, but I wanted to come up with a repeatable process. Since we have partitioned tables, I also wanted to examine candidates for compression at the partition level for each index.
As users and business activities generate data that is stored in SQL Server, the possibility for data skew increases. For the purposes of this conversation, data skew can be generally characterized as significant variance in frequencies of values in a column. Admittedly, the significance of the variance depends on factors such as data size and/or the informed subjectivity of the analyst. To illustrate, I’ll use some data from a recent SQL Saturday (#297) presentation I did on backup throughput analysis.
It’s been a while, primarily because I’ve been looking into a different blogging platform with actual Markdown support, and on a different host. It’s definitely better looking and undoubtedly easier to use from a publishing perspective. I may still end up self-hosting and just buying a domain name. However, I digress …
We’ve been having some issues with blocking on a pre-prod implementation recently. That’s not really a huge cause for alarm since blocking comes with the territory, but this has been excessive and was having some noticeable performance impact. I wanted to put together a query that would help us find the blocking chain (this was before we put the environment in the list of servers monitored by SQLSentry). Recursive CTEs really came in handy. The first iteration looked something like this:
I’ve noticed some range and scale issues so I took out the explicit schema creation (for now). For right now it just selects into a table, dropping the table if it already exists.
Back in October I had the opportunity to attend Jonathan Kehayias’ “SQL Server Archaeology” presentation. I’d been trying to wrap my brain around Extended Events for a while, but his presentation got me motivated to dive into the system_health Event Session.
I had the good fortune of attending Kimberly Tripp’s data skew presentation at the PASS Summit 2013 in Charlotte, NC back in October. In this presentation she revealed some code that she’d developed to help analyze skew in your data and make suggestions for filtered stats. This inspired me to take a look at statistics in some of my servers.
The other day my team was asked by a developer to see if his trigger was firing. One person has been working on a Profiler Trace but I thought this would be a good opportunity to raise awareness regarding Extended Events within my team. So while I’m waiting for a good change window to be able to monitor the backup throughput of my VLDB with my backup_restore_throughput XE Session I figured I’d work something up for them.
So I created some simple test tables and triggers
This sure has taken me a while to produce. I’ve been working on it for about a month now. However, I went to the 2013 PASS Summit and have been trying to work through implementing many of the things that I learned there in our environment. Just about the time I got a handle on that, I went on call and the rest has been a blur. Also, sorry the screencaps are so small. I’ll do better next time.