Click here to monitor SSC
  • Av rating:
  • Total votes: 19
  • Total comments: 8
Grant Fritchey

SQL Cop Review

19 January 2012

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:

SQLCop Login Screen

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:

SQLCop Toolbar

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:

SQLCop Instructions

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):

List of Procedures

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

SQLCop Wiki Article

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:

Finding missing foreign keys

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:

Test missing SET NOCOUNT

The Checks

Showing passed / failed tests

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:

The SQLCop Report

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.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 19 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: SQL Cop checks
Posted by: George (view profile)
Posted on: Friday, January 20, 2012 at 2:50 PM
Message: 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

Subject: Additional Checks
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, January 24, 2012 at 1:52 PM
Message: I didn't know that. That's great to know.

Subject: Checks
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 01, 2012 at 2:17 PM
Message: Why in the world enabling CLR is a smell or lapse in best practice???

Subject: CLR
Posted by: Grant Fritchey (view profile)
Posted on: Thursday, February 02, 2012 at 5:59 AM
Message: 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.

Subject: Re: CLR
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 02, 2012 at 10:09 AM
Message: 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.

Subject: CLR smells
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 02, 2012 at 2:02 PM
Message: 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!

Subject: SQL Cop
Posted by: Anonymous (not signed in)
Posted on: Friday, February 10, 2012 at 7:46 AM
Message: Thanks! Good stuff as always!

Subject: Generate reports from SqlCop
Posted by: K0mmineni (view profile)
Posted on: Thursday, February 27, 2014 at 2:56 AM
Message: Thanks for this article. I would like to know more details about how to generate reports from SqlCop.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.