Michael J. Swart

Top Eight Queries to Improve DB Design.

There is a disease that most television networks suffer from. The disease was more prevalent in the eighties and nineties than it is today, but every sit-com that lasts longer than two seasons seems destined to suffer from it: The cheesy clip show. The writers of such sitcoms probably thought “Whew! I need a break.” That thought was probably quickly followed by. “I betcha the viewers would like to re-watch Alex P. Keaton attempting to handle that feisty kangaroo”.

Well this article is like that. It’s a time where I link to old articles of mine (and some from the SQLServerPedia wiki!) and pass it off as something novel. Thanks for bearing with me and Happy Holidays. Without further ado:

The Eight Queries

These queries can be run right now! And can help identify areas for improvement in DB design. These queries are focused on DB design (so queries that identify top wait types or top queries by resource usage are skipped).

In no particular order:

1. Find Missing SQL Dependencies If a view, function or stored procedure refers to a nonexistent object (be it a table, view etc…) This query can help you identify those.
2. Finding your Missing Keys If a column with a name like ‘%id’ does not point to a table, you just might need a foreign key.
3. Indexing Foreign Keys? To minimize the amount of maintenance time required to maintain data integrity required by foreign keys. (The query needs a database with data in it. The query is useless against empty databases).
4. Which identity column is running out of room? Find columns which might be in danger of running out of room. (Run against a database with data in it).
5. Find Missing Indexes This is courtesy SQLServerPedia. The Microsoft documentation for the missing index views is here. But the SQLServerPedia article pulls it all together. (Must be run against an active database).
6. Find Queries in the Plan Cache That Are Missing an Index Another query from SQLServerPedia. Be careful with this one. It can use a lot of resources.
7. Find Indexes Not In Use Again, from SSP, use against an active database.
8. Find Tables Without Primary Keys Looking for heaps of trouble? This last query is also from SSP. It doesn’t need to be an active DB. Any old development DB will do.

What? Still here?

You still want more meta information about your databases. Be careful what you wish for. I recommend looking at what Jason Strate has done. That’ll keep you busy.