19 January 2012

SQL Cop Review

Static code analysis is used a lot by application programmers, but there have been surprisingly few tools for SQL development that perform a function analogous to Resharper, dotTest, or CodeRush. Wouldn't it be great to have something that can indicate where there are code-smells, lapses from best practice and so on, in your Database code? Now there is.

There are lists upon lists of good, common best practices on which most SQL Server professionals agree. For example, here’s one from Brad McGehee called the Sure DBA Checklist. Knowing what to check is just the start of the process. Now, you need to go and check it. Look through that list. It’s frighteningly large. Personally, I’m lazy. I don’t want do all that junk, but, I do want to be sure that my servers and my databases are well set up. If only someone made a piece of software that could do some of this work for me so that I can sit on my ever expanding bottom and relax with the knowledge that my systems are configured correctly. Well, we’re part way there. Here is SQLCop, a free tool from the people who created the lessthandot.com IT community. It runs a number of standard checks on your server, and on whatever database you assign it to. Its whole purpose is to identify common issues and inform you of them. I found it to be quick, accurate and informed. Let’s check it out.

Install

What install? Download the executable and run it: You’re done. The executable stands alone and runs as-is. No install required, which is really great. You can carry this around with you on a thumb drive and run it when you get to a new company or a new server. The only issue is, you do need internet access from the location where you run it. If you don’t have it, it will run, but you won’t get all the nifty information from the lessthandot blogs that comes up; but more on that later.

Using SQLCop

Firing it up will immediately bring up a login interface like you see below:

1428-image001.png

Provide it with the appropriate information to connect up to your server and database. No surprises. The software launches, and then presents you with a new window where you can start to explore the compliance checks as they relate to your system. The screen is divided into three pieces. Across the top is a toolbar that lets you change databases, ask a question at lessthandot.com, donate if you like the tool (yes, it’s shareware, support it), run a report showing all the issues it found, print the screen or get help:

1428-image002.png

1428-image004.png

On the left side of the screen are the categories of all the checks that SQLCop performs. You can click on a category to expand it out and see the checks it ran:

Then, the main part of the screen is where information is displayed, and oh what information you have at your fingertips. Just the startup screen is filled within information explaining what SQLCop is and what it does:

1428-image005.png

The interface is pretty clean and very easy to use. There are few functional surprises waiting for you. But, if you thought, like I did, that it performed the checks when you opened, you’d be wrong. I immediately ran the report and saw that there were zero issues with my copy of AdventureWorks2008R2, which couldn’t possibly be true (there are a couple of standard best practices violated by Microsoft, go figure). What I found was that when you drilled down on a particular check, then you saw the results. For example, one of the checks in Code is “Procedures without SET NO COUNT ON,” a pretty standard best practice. Expanding that out did two things, it found that I had several procedures without the setting and it loaded an article from lessthandot covering that particular topic. Here is my list of procedures (all of them are examples from my presentations, oops):

1428-image007.png

And in the main part of the screen is the article:

1428-image008.png

This is great stuff. It’s a wiki, so if they’ve got something wrong, you can go right in and start cleaning it up to help all the other users of SQLCop. It’s also an excellent way to help you understand what these checks are and why they’re being performed, and, most importantly, why you need to fix whatever it is that’s broken. By the way, it’s OK that my demo procs don’t have SET NOCOUNT ON, really.

I did see a couple of place where, when I clicked on a check, the web page would go into a re-load loop or something, but it was constantly refreshing which was a little annoying, but it only happened a couple of times, in fact, I had a hard time reproducing it, so I couldn’t even submit it as a bug.

Other than that, this thing worked really well. It would run the checks as advertised and let me know what it was running and why. Better still, it showed me how it was doing the check. For example, one check found a lot of problems in AdventureWorks2008R2, Tables/Views, “Missing Foreign Keys.” The check ran this script to identify columns with the phrase ID in them that are not part of a foreign key relationship:

1428-image009.png

Personally, I don’t agree with this check, although, it’s one approach for identifying where foreign keys should be, but aren’t. But what I love is the education I can receive from SQLCop because it’s showing me exactly how it’s making the best practices check that it’s informing me about. That is invaluable whether you like the check it’s performing or not.

Sometimes when there is an object that’s been identified as having an issue, you can click on the object and you’ll see the SQL code for that object. For example, here’s one of my test procs missing it’s SET NOCOUNT:

1428-image010.png

The Checks

1428-image012.png

The whole reason SQLCop exists is to perform checks on your server and your database. These are pretty standard best practices that it’s looking at and shouldn’t be very controversial or an issue for most people. Here is most of the list:

You can see that with everything expanded, the checks are green for those that passed and red for those that failed (assuming you’re not red/green color blind). I really like the majority of these checks. It’s all the simple things that you should be doing with your code and structures. Get this right, then you can concentrate on the more difficult aspects of setting up and maintaining your system. There are a few of the checks that I don’t agree with. I already mentioned that I think looking at columns with ID in them isn’t necessarily a good way to validate foreign key use, but I see the utility of it in some circumstances. I also don’t agree that all foreign keys should have indexes, so this check could be an issue for some people. But those are really nits and personal preference. Overall this is an excellent set of best practice checks that are run for me automatically. Great.

Once I’ve run all the checks, which I did do by using the “Expand all” check box on the screen, I can run a report that shows everything that failed or was tagged as requiring attention on my system. Here’s part of that report:

1428-image013.png

The way that this is laid out works well for me. You can just hand it to your boss or a system admin and tell him, fix this stuff.

Summary

I like this piece of software and the checks it performs. Are there a couple I’d ignore? Yeah, but the vast majority are excellent and worthy. This will make your life easier. It’s a fast way to validate compliance with standard checks that will help the performance and stability of your systems. It acted a little twitchy at times, but overall it was quick, stable, and above all, helpful. I recommend trying it out on your systems. I think you’ll be happy with the results (although it might generate some work for you in the near term).

It is likely that you’ll find some of these checks against your database so useful that you’re going to want to automate them in order to make them a part of your testing in development. If so then you’ll be pleased that SQLCop has teamed up with SQL Test so that they can be incorporated directly into your SQL Test regimen and automatically be a part of your development testing cycle.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 14771 times – thanks for reading.

Tags: , , , , , ,

  • Rate
    [Total: 21    Average: 4.2/5]
  • Share

Grant Fritchey

View all articles by Grant Fritchey

  • George

    SQL Cop checks
    Grant, thank you for the review of SQLCop. One thing I wished you had mentioned was that we (at lessthandot.com) are willing to add additional checks to SQLCop. If there are any checks that you (or anyone else) thinks would be a worthy addition, please post it here: http://forum.lessthandot.com/viewforum.php?f=145

  • Grant Fritchey

    Additional Checks
    I didn’t know that. That’s great to know.

  • Anonymous

    Checks
    Why in the world enabling CLR is a smell or lapse in best practice???

  • Grant Fritchey

    CLR
    Personally, I don’t think it is, but a lot of DBAs do. Also some companies consider it a security risk. I understand why they have it, but I don’t agree with it.

  • Anonymous

    Re: CLR
    I’d be happy to be alerted if CLR has been enabled as our company policy dictates that we have it switched off in production servers as a security risk.

  • Anonymous

    CLR smells
    1. SQL Server DBAs should take example from every other vendor that has code running inside the engine (MySQL has C extensions…).
    2. SQL Server DBAs should know they are still running and will be running in SQL Server 2012:
    – to be deprecated but not yet, extended stored procedures
    – stored procedures based on cursors and C/C++ code
    3. .NET CLR is by those standards, a perfume!

  • Anonymous

    SQL Cop
    Thanks! Good stuff as always!

  • K0mmineni

    Generate reports from SqlCop
    Thanks for this article. I would like to know more details about how to generate reports from SqlCop.

  • dalehhirt

    A lot of Production Checks
    I would like to point out that a lot of these checks are more for a production-instance of SQL Server, and not necessarily a database or dev-level coding issue.

    I like the idea of it, but running an unknown executable against my production servers is not a good idea. I have found the T-SQL for these tests and are running just the ones that I need in my testing environment.