Four Underutilized Features of SQL Server Enterprise Edition (and One of Standard Edition)

I started thinking about this post a few months ago—we were running into database performance issues on one of our major customer facing applications, and I noticed we weren’t using partitioning or compression on a very large database. So I decided to put together a presentation on compression, and that led me into thinking what features aren’t utilized as often as they should be in SQL Server Enterprise Edition. SQL Server Enterprise Edition licenses cost a lot—at a minimum your company is paying about $28,000 (MSRP) for SQL Server Enterprise Edition—so what features might you be leaving on the table.

  1. Compression—Compression (both Row and Page level compression) was introduced in SQL Server 2008, and is great feature for a lot of workloads. In the past, our workloads were CPU constrained, and using compression on a database, just wasn’t a good idea, however in the era of underutilized powerful CPUs, compression makes a lot of sense for many workloads. Microsoft’s official recommendation is that you should row compress all of your data–I don’t necessarily agree with that sentiment, but using compression carefully, can greatly increase the performance of your read workloads. There’s a great Microsoft white paper on compression, and when and where to use it here. Additionally, compression is one of the only features that the DBA can use to increase the performance of a third party application.* (* Make sure your vendor is cool with this)

    SQL

  2. Partitioning—the origin of this blog post was a 112 GB table, that was neither partitioned nor compressed. Index rebuild jobs were running into the batch window, and the DBA asked me for assistance in job scheduling. When I dug into the jobs and saw that table, needless to say I was alarmed. Partitioning isn’t easy to use—you have to think about your data and how it should be broken down. There are some great resources around partitioning from Microsoft here and Kendra Little (b|T) here. Partitioning has the potential to make your databases easier to maintain, your queries run faster, and increasing your salary (well after the other two things happen). It’s also another way to improve the performance of a third party application, and lastly if your application doesn’t have built in archiving functions, it’s a way to archive data, if you have long data retention requirements.

     

  3. Resource Governor—this feature isn’t for everyone—it allows you to control the amount of CPU and Memory available to given resource pool. You can assign users to resource pool, and limit the CPU and memory available to that pool. Pinal Dave does a good job explaining how to configure it here. The Microsoft demos I’ve seen tend to show how you might give the CEO priority to run his reports, I don’t know about you, but everywhere I’ve worked the CEO has minions to run his reports, so he or she isn’t that concerned about it. The real-world situation where I’ve used this feature is in a multi-tenant SQL Server—I had a couple of databases that were overwhelming the rest of the server. Fortunately both of these databases accessed SQL Server as a process user—I was able to add that process use to a resource pool. Note—there is no way to directly limit the I/O utilization directly, but if you limit CPU enough, the user won’t be able to generate that many IOs. I’m looking at you VSphere database.

     

  4. Management Data Warehouse—Ok, I lied, this feature isn’t in Enterprise Edition. It’s also not for everyone—if you have more than 50 servers, it probably won’t scale that well. This was introduced in SQL Server 2008, to allow administrators to collect performance data from a group of servers, and return it to a central server. It is extendable and customizable, and Microsoft includes a large number of canned reports. It is limited to servers that are running SQL Server 2008 and above, but you should really be getting off of SQL 2005 soon anyway. The other caveat is that if the time comes that you want to stop using MDW, it leaves some remnants behind. Fortunately, Aaron Bertrand (b|t) has blogged on how to configure and remove it successfully here.

     

  5. Peer to Peer Transactional Replication—Do you like hitting yourself in the thumb with a hammer? This might be the SQL Server feature for you! Seriously, if you want to do this, enlist professional help before going down the path, it’s not simple, and can certainly be a headache.

Vendors, Don’t Be That Creepy Guy, OK?

We’ve all been out at the bar late, too late even, and seen that drunk guy (or girl) who’s desperate to go home with another guy/girl/mammal. That’s understandable it’s the end of the night, you’re drunk and the most attractive options are already gone. Sales can be very similar—it’s the end of the quarter, you need to meet your sales quota, and all of the attractive customers are all signed up or have already refused your pitch. That’s how sales works, and while it’s annoying, I get it. Here’s where I get upset—after I’ve refused the advances and acknowledged that I’m not really interested in your junk, er product, it’s not okay to keep asking questions. It’s not really any of your business what other solutions I own, or what I’m developing internally, and when you keep asking those questions after I’ve blown your off, it just starts to feel weird. Don’t make me get a restraining order. (BTW, this isn’t about any of our wonderful SQL Server community sponsors—NoSQL related)

PASS Business Analytics Conference Keynote Day 2

PASS has brought in the “Indiana Jones of Economics” for today’s keynote, Dr. Steven Levitt. He’s the author of books like Freakonomics and Super Freakonomics. Steven starts with a discussion about a long time IRS employee John Szilagyi—discusses first names and putting dependent SSNs on the tax form. At first the idea didn’t go over—but on April 16, 1987, 7 million children vanished off the face of earth.  Well, not really—they didn’t exist in the first place, as there was no IRS verification.  This made the IRS $2B in additional revenue. The story is here.

Incentives and Behavior

Dr. Levitt discusses that story and leads into discussion about incentives, and how incentives drive human behavior. Discussing the difficulty of aligning goals between workers, shareholders and managers. Dr. Levitt discusses how broken education tenure is—his only has to teach 50 hours at the University of Chicago. He spends at least 10 hours a week, just thinking.

Do What You are Good At

He studies small things, discussing controversy about his theory about abortion reducing crime rates 20 years later. Talking about what makes an economist good—it’s not math, it’s basic understanding of how the economy works. Talks about how he wasn’t great at math—had to take Math 101 at Harvard after scoring the lowest score ever on his high school AP math exam.

He starts talking about research into Drunk Driving—and it’s still really bad. Chances of causing fatal crash 8-10x higher than driving sober. Mentions how walking drunk is much more dangerous than driving drunk—a town in Alaska has banned drunk walking.

Economics of Crack

Discusses economists doing a survey—and lacked common sense. “How does it feel to be poor and black?” Discussing a colleague administering a survey to gang members in Cabrini Green.  Talks with gang leader—to begin understanding dynamics of gang behavior. It’s discussed here in Sudhir’s book. Discussing looking at gangs financial records. Discusses how gangs are organized very much like a franchised business. Top gang members made 500k/yr. Most drug dealers still live with their mom’s because, being a street level drug deal is a bad deal. It’s 7x more dangerous to sell crack in Chicago, than to be in a war zone

Businesses and Data

Dr. Levitt discusses how his forays into business consulting were abject failures. He starts discussing how poorly most enterprises do with data. Talking about surveying a large company on their hiring practices. Offers data analysis of all hiring, retention and performance data for this enterprise. Negative correlation with testing and employee performance. Businesses have organized themselves in a way to minimize feedback. Talking about the benefits of experimenting with data. Discusses how scared organizations can be of using data. Was fired from biggest client after telling them the truth about their data. Finishes with a audience Q&A.

PASS Business Analytics Conference Day 1 Keynote

We start with PASS President Bill Graziano who leads off with talking about the growth of data analytics over the past few years. Connect.Share.Learn. If you are reading this and don’t know, SQL Saturday Chicago is this weekend. I’ll be presenting there.

Matt Wolken from Dell/Quest takes the stage to discuss Business Intelligence. Talks about the rate of change in the BI space. Talking about how the shifting demographics of social media are influencing spending. Mentioning great increases in mobile computing. Mentions the tweet from the Hudson River. Analytics used to backwards looking, now it’s looking forward and revenue driving. Companies that implement BI and BA solutions tend to be more profitable (13%). Dell has a new social command center to monitor feedback and support.

Now, we welcome Microsoft Keynote speakers Amir Netz and Kamal Hathi to the stage. Amir, is talking about his Apple II and how he programmed extensively on it. Mentions using VisiCalc and Lotus 123. Talking about how spreadsheets evolved into OLAP. Kamal is now talking about how OLAP evolved into Hadoop, and how do business users understand usage of tools in the big data space. Yet it all comes back to excel, I suspect this will come back to Powerview and data explorer. I shall refrain from editorial content, but ZOOMIT. Excel is getting much better at dealing with external data. Compares evolution of cars from model T to BMW. Amir is talking about how BI is now at the stage of the early slide projector. PowerView is awesome.

Microsoft is doing a nice job of using sentiment analysis from Twitter in this demo, to demonstrate American Idol success predictions. The data shows that positive sentiment is key into American Idol. Explains how Twitter has changed the dynamics of how people watch TV. This means if you are showing something that should be live–SHOW IT LIVE (I’m talking about bike and F1 racing, NBC Sports Network)

Seeing a new excel plugin called geo flow, which lays out spatial data within excel, this functionality has been getting better, but this is really nice. GeoFlow has the ability to zoom in via touch, very nice, and additionally it has a replay functionality. Outstanding graphics and functionality.

And that’s a wrap…more tomorrow

SQL Saturday Boston–Presentation Resources

Slides from my SQL Server 2012 HA and DR

Slides from Stacia and I’s presentation Using PowerView and Hadoop to Unlock Hidden Markets

%d bloggers like this: