Recently I’ve been tasked with testing storage for a hardware purchase for a new, large SQL Server implementation. I’ve not gotten everything “down pat” as far as SQL Server IO methods, but I figured I’d take on the challenge of getting better. SQLIO: The go-to tool There are plenty of SQLIO resources on the Internet, but Jonathan Kehayias has a good post that uses SQLIO well and also references other posts.
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 have the privilege of speaking at SQL Saturday #331 in Denver this upcoming weekend (Saturday, September 20, 2014) about managing statistics in SQL Server.
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.
After conversation with some people whom I consider smarter than I am, I decided that my magic number would be 3.
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.
I’ve uploaded my deck and scripts for my upcoming presentation. It’s in the “Presentation” page.
http://swasheck.wordpress.com/presentations/
In order to create the sample “Stack Exchange” databases, you can use the Gist here .
** Update 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. So I downloaded her presentation demo and tried to get a sense for what she was doing.
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.
There have been a few blog posts about the new DMVs that are being introduced in SQL Server 2014 CTP1, but Bob Beauchamin has what seems to be the most definitive list of new Extended Events in the CTP. There’s no real information out there, though, on how to use them. That’s probably because it may be a bit too early, but I like taking things apart so I figured I’d check it out.