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 is a SQL Server MVP with over 20 years’ experience in IT including time spent in support and development. Grant has worked with SQL Server since version 6.0 back in 1995. He has developed in VB, VB.Net, C# and Java. Grant has authored books for Apress and Simple-Talk, and joined Red Gate as a Product Evangelist in January 2011. Find Grant on Twitter @GFritchey or on his blog.

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 1, 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 2, 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 2, 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 2, 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.

 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

The Evolution of SQL Server BI
 It is sometimes hard to keep up with Microsoft's direction in Business Intelligence. Over the years,... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... 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...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... 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.