After just finishing up my first ever SQL Risk Assessment Program (SQLRAP), I felt it would be a good time to do a little critiquing on it. Most people don’t even know what a SQLRAP is, so let me start by explaining that first. No, I am not referring to an actual SQL Rap like this. However, that did give me a good laugh this morning. Rather, it is the Risk Assessment Program offered from Microsoft through their premier support. There is a whole variety of RAPs for all different flavors of MS products. It is a package deal where you get an assessment application/program, training and knowledge transfer. What happens is a Microsoft SQL expert comes on-site and shows the application they have developed to pull perfmon counters, ask questions and do a general baseline and best practices check on a SQL server. At the end, a bunch of nifty reports and a PowerPoint
presentation are given to everyone (whether interested or not) to show the overall risk and issues that need to be followed up on. At this point, you get a license for 18 months that can only be used to gather stats on a single instance in your environment.
What did I take away from this three day engagement?
A few things I guess. I wasn’t really sure what the SQLRAP engagement was going to entail, so I tried to keep an open mind about it. The MS SQL expert we had on-site was good. He knew his stuff and was not here to try and sell us on anything else (which is really what I expected). He did a great job with explaining things to make sure everyone understood. The RAP was performed on our newly (rushed) installed SQL2008 R2 cluster. Come to find out the cluster install and configuration was pretty much flawless, which
is awesome. The first new item had to deal with the server power options mode and how to set it for high performance rather than power saving which ‘can’ increase performance. Is this a game changer, probably not and in fact, based on our current run performance, he estimated no change at all. The other new recommendation is for snapshot isolation to be enabled. Our SQL rep did a great job explaining the different kinds of locks and how dirty reads occur with our development team. It was good to have the developers in the room to take it all in. Since this application was developed long ago, many coding practices have evolved and many new techniques are not well known.
The other issues that were found were all known. Things like missing recent security patches, some security best practices that were not followed, compatibility level not set to 100 (I claimed fault on this one since I was trying to get out the door to Tahoe the day I did the migration and just missed the step), no documented Disaster Recovery plans, SLA’s, OLA’s and a few high waits for CXPACKET and ASYNC_NETWORK_IO. All of which we can fix with some fine tuning of problematic queries (hopefully!!!) and some balancing the MaxDOP setting we currently have set at 0 for a 16 proc server.
Really, there were only a handful of issues that needed to be addressed. Overall, our SQLRAP was below the customer average for issues normally experience in these engagements with Microsoft which was exciting for us. I think it was a good experience, but without knowing the cost, I can’t say that it was completely worth it yet. Most of the issues that came up were either already documented by our team almost a year ago. I guess the biggest benefit was that Microsoft validated to our senior management what
we have been saying for the last 10 months. The only difference is that it is now coming from the mouth of the Microsoft experts which seems to hold a lot more value to some; however, this is a whole other issue. I can only hope that this will add more creditability to us in the future as experts in SQL Server.
What I didn’t like about it?
I didn’t like the fact that if you want to use the tool for other instance, you can’t just purchase licenses for more, rather you have to create a whole new engagement, bring an MS rep on-site for 3+ days, just to go over all the same stuff you did for the first one. Seems like a waste of time and money for the customer. If you really want to get an overview of best practices on your server, there are plenty of SQL help sites and white papers out there to review. I think it would be more beneficial to bring someone on-site to help with performance tuning instead. This engagement barely scraped the surface of tuning the SQL code and focused more on the server/instance configurations. If there is a next time, it think it would be beneficial to lay out the ground work with MS first by saying “here are our current known issues, now tell us something we don’t know” and hope that it will make them dig a little deeper and maybe use the resources in a more efficient manner while on-site.
All in all, this process is only worthwhile if you take the recommendations and do something with them. If no recommended changes are implemented, then having a baseline really won’t do any good if no changes are made to try and improve that baseline performance.