5

Is there a tool out there that can analyse SQL Server databases for potential problems?

For example:

  • a foreign key column that is not indexed
  • an index on a uniqueidentifier column that has no FILL FACTOR
  • a LastModifiedDate DATETIME column that has no UPDATE trigger to update the datetime
  • a large index with "high" fragmentation
  • a non-fragmented index that exists in multiple extents
  • a trigger that does not contain SET NOCOUNT ON (leaving it suspectible to "A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.")
  • a database, table, stored procedure, trigger, view, created with SET ANSI_NULLS OFF
  • a database or table with SET ANSI_PADDING OFF
  • a database or table created with SET CONCAT_NULL_YIELDS_NULL OFF
  • a highly fragmented index that might benefit from a lower FILLFACTOR (i.e. more padding)
  • a table with a very wide clustered index (e.g. uniqueidentifier+uniqueidentifier)
  • a table with a non-unique clustered index
  • use of text/ntext rather than varchar(max)/nvarchar(max)
  • use of varchar in columns that could likely contain localized strings and should be nvarchar (e.g. Name, FirstName, LastName, BusinessName, CountryName, City)
  • use of *=, =*, *=* rather than LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
  • trigger that returns a results set
  • any column declared as timestamp rather than rowversion
  • a nullable timestamp column
  • use of image rather than varbinary(max)
  • databases not in simple mode (or a log file more than 100x the size of the data file)

Is there an FxCop for SQL Server?

Note: The Microsoft SQL Server 2008 R2 Best Practices Analyzer doesn't fit the bill.

Community
  • 1
  • 1
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219

5 Answers5

8

There's SQLCop - free, and quite an interesting tool, too!

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Very nice. The interface could use some work, but the checks are quite extensive. – Ian Boyd Jan 19 '12 at 17:57
  • @Ian - are you hoping to run these checks locally, or as part of a automated build process such as continuous integration? – David Atkinson Jan 21 '12 at 17:28
  • Presumably i would run them interactively. Scheduled tests of servers/databases might be nice; except for the rate of false positives. – Ian Boyd Jan 21 '12 at 19:42
  • Yes, you'd have to pick and choose which checks count as issues for you. The reason I asked is that here at Red Gate we've integrated a handful of SQLCop tests to SQL Test (SSMS test runner), which allows such tests to not only be run within SSMS, but also as part of CI, as the tSQLt framework (tSQLt.org) that it uses can be run via a command line. – David Atkinson Jan 23 '12 at 22:30
  • 1
    @IanBoyd, I wrote SQLCop. You mentioned that the interface could use some work. Can you share your thoughts regarding this? – George Mastros Mar 28 '12 at 16:22
  • @GMastros The font doesn't scale with my selected (everything too small!). Some of the top-right buttons don't have a tooltip (the play button); it should be a toolbar button called "Execute script". The "expand all" checkbox should be a toolbar button, along with "Refresh". Then there's a rule or two that i wish existed, as well as bugs in some existing rules (e.g. view with an order by is confused when the order by is commented out). But my UI complaints are the small font, the anonymous buttons that should be a toolbar, and the checkbox that should also be a toolbar button. – Ian Boyd Mar 28 '12 at 17:14
  • Thank you for the feedback. It shouldn't be difficult to modify the interface. I changed the font to 10 point Verdana. I made sure all toolbar buttons have tool tip text. I'll need to find a suitable image for "Expand All" before I move it to the tool bar. All of the SQLCop checks are written in TSQL, so making them ignore comments would be extremely difficult, especially when you consider that SQLCop does not add any code to your database. What rules do you wish existed? – George Mastros Mar 28 '12 at 17:41
  • I changed a couple things, but have not posted a new version of the application yet (just to avoid confusion). – George Mastros Mar 28 '12 at 17:43
2

There is a tool called Static Code Analysis (not exactly a great name given its collision with VS-integrated FxCop) that is included with Visual Studio Premium and Ultimate that can cover at least the design-time subset of your rules. You can also add your own rules if the in-box rule set doesn't do everything you want.

Nicole Calinoiu
  • 20,843
  • 2
  • 44
  • 49
2

Check out SQL Enlight - http://www.ubitsoft.com/products/sqlenlight/sqlenlight.php

vmvadivel
  • 1,041
  • 5
  • 7
1

I'm not aware of one. It would be welcome.

I post this as an answer, because I actually went a long way to implementing monitoring many things which can be easily done in straight T-SQL - the majority of the examples you give can be done by inspecting the metadata.

After writing a large number of "system health" procedures and some organization around them, I wrote a framework for something like this myself, using metadata including extended properties. It allowed objects to be marked to be excluded from warnings using extended properties, and rules could be categorized. I included examples of some rules and their implementations in my metadata presentation. http://code.google.com/p/caderoux/source/browse/#hg%2FLeversAndTurtles This also includes a Windows Forms app which will call the system, but the system itself is entirely coded and organized in T-SQL.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
1

Take a look at SQLCop. It's the closest I've seen to FXCop.

D. Lambert
  • 1,304
  • 6
  • 12