Technical Debt

Overview

Every database that has been alive for a decade has accumulated things nobody would write today: NOLOCK sprinkled everywhere because of one blocking incident in 2014, cursors where a set based statement would do, SELECT * inside procedures, the old *= join syntax nobody has been able to use since compatibility level 90.

None of it is broken. All of it costs something, every time somebody has to work in that code.

Technical Debt reads the T-SQL in one database, applies thirty two rules, and gives every finding a score. Then it does the thing that makes the report useful outside a development team: it multiplies the score by a rate and shows a currency figure.

That figure is a conversation starter, not an invoice. It is the score converted at two numbers you set yourself. Its value is that it turns the code is a bit tatty into a number that gets a sprint allocated.

The Technical Debt report
Three panes: worst objects, worst kinds of debt, and every finding.

Where to find it

A database level page. Expand the database → Technical Debt.

It requires a registration code. Without one the page says so rather than running.

The page title reads Technical Debt for <database name>.

Requirements

  • A registration code.
  • Permission to read the object definitions in the database.

Nothing on this page changes anything. It reads code and scores it.

Running a scan

The page opens on a welcome panel rather than results, because a scan is work and it should be something you asked for.

Button What it does
Analyze Scans the whole database against every enabled rule.
Analyze Object Opens a picker so you can scan a single object.
Stop Analyzing Ends a scan in progress. Whatever has been found so far stays on screen.

The scan runs in the background with a progress bar, and it names the rule it is working on as it goes. The grids fill in as it runs, so a database with a lot of debt starts showing results long before the scan finishes.

Analyze Object is the one to use during a code review. Scanning one procedure takes a moment, and after a scan of a single object a Refresh button appears to re-run it, which is the loop you want while somebody is actually fixing the code.

Reading the three panes

Pane What it ranks
Top objects The objects carrying the most debt, worst first. Where is the mess?
Top debt types The rules that fired most, by score. What kind of mess is it?
All Technical Debt Every finding, worst first.

Each pane carries both the score and the cost.

Read the two ranking panes together. They answer different questions and the answers suggest different work.

  • One object at the top of the objects pane is a rewrite. One procedure, one piece of work, and the score drops by that much.
  • One rule at the top of the types pane is a sweep. NOLOCK in ninety procedures is one decision and ninety small edits, and Schema Search will find every one of them.

Double-click a debt type to filter the full list to just that type, which is how you turn the second answer into a work list. A Clear Filter button appears next to the heading.

Double-click an object, or a row in the full list, to open that object with all of its findings together. Double-clicking the URL column of a finding opens the page explaining that particular rule.

The score and the cost

Each rule has a multiplier reflecting how much it costs to live with, so the score is weighted rather than a count. NOLOCK scores fifteen times a keyword casing problem, because it is fifteen times more likely to cause somebody a bad afternoon.

The cost is a straight conversion:

cost = score x hourly rate / points per hour

Both numbers live in Settings, under Technical Debt, and default to 20 points per hour and 50 per hour in your currency. Change them to match what fixing this code actually costs where you work, and the figure becomes meaningful for your organisation rather than a default.

The score is comparable; the currency is a translation of it. Two scans of the same database a month apart are directly comparable. A scan of your database against somebody else’s is not, unless they are the same size.

The rules

Thirty two of them, in four rough groups.

Things that hurt at runtime

NOLOCK hint used, cursors used, = NULL where IS NULL was meant, WITH RECOMPILE, SELECT * inside procedures, EXEC (query) string execution, missing SET NOCOUNT ON, and the old *= join syntax.

Things that will stop working

Deprecated DBCC DBREINDEX, DBCC INDEXDEFRAG, DBCC SHOWCONTIG, DBCC PINTABLE, DBCC UNPINTABLE, and the deprecated procedures sp_dbcmptlevel, sp_helpdevice, sp_addtype, sp_droptype, sp_attach_db, sp_attach_single_file_db, sp_change_users_login and sp_depends.

Things that make the code hard to read

Procedure or function too long, lines too long, GOTO used in procedures, keyword casing, and missing table references on the columns in SELECT, WHERE, GROUP BY and ORDER BY clauses.

Things that will bite somebody later

Tables missing their schema in the FROM clause, spaces in object names, and spaces in column names.

Every rule can be turned off. In Settings → Technical Debt, pick a rule and disable it. A rule that does not match your house style is noise, and noise makes the score useless. Turning off keyword casing on a codebase that has never cared about it is a reasonable first move.

How to read the report

  1. Run it once, and write the score down. The number only means something as a baseline.
  2. Turn off the rules you do not agree with, then run it again. That is your real baseline.
  3. Read the top debt type. It is usually one thing, in a lot of places, and it is the cheapest thing to fix.
  4. Read the top object. One procedure carrying a tenth of the total is a rewrite worth scheduling.
  5. Re-run after the work and show the difference. That is the argument for doing it again next quarter.

Common patterns

NOLOCK at the top of the type list. The most common finding in the product. It is not a performance hint; it is a correctness trade that allows dirty reads, missed rows and duplicated rows. Read committed snapshot isolation is what most people who use NOLOCK everywhere actually want.

Cursors scoring heavily in a handful of procedures. Usually the same author, usually the same shape, usually convertible to a set based statement. Worth checking against CPU by Query to see whether they are also expensive at run time.

Missing schema names everywhere. Every unqualified reference costs a name resolution and hurts plan reuse. A cheap sweep with a real benefit.

Missing SET NOCOUNT ON. Trivial to fix, and it genuinely reduces network round trips from chatty procedures.

Deprecated DBCC commands. Old index maintenance scripts. Deprecated Features will show whether they are still being executed rather than just still existing.

A very high score on one enormous procedure. Long procedures score against several rules at once. Splitting one is often the largest single drop available.

Where the data comes from

  • The object definitions in the database, read directly and matched against the rule set.

The rules are carried with Database Health Monitor rather than read from the instance, so a product update can add rules to an existing database.

Nothing is stored by this page. The scan is not saved: closing the page and coming back means running it again, which is why the object level scan and its Refresh button exist for the edit-and-recheck loop.

Report Why you would go there
Schema Search Finding every occurrence of a pattern once you have decided to sweep it.
Deprecated Features Which deprecated things are actually being executed, not just present.
CPU by Query Whether the worst code is also the most expensive at run time.
Plan Cache and One Time Use Queries The plan reuse cost of unqualified names and string execution.
Structure Change Log Who has been changing the code, and when.
Blocking Tree The contention NOLOCK was probably added to work around.

Frequently asked questions

The page asks for a registration code. Technical Debt is a registered feature.

Is the currency figure real? It is your score converted at two numbers you set. Set them to something true for your organisation and it becomes a reasonable estimate; leave them at the defaults and it is a relative measure.

Can I turn off a rule I disagree with? Yes. Settings → Technical Debt, select the rule, disable it. Disabled rules are skipped and score nothing.

Why does the score change between runs with no code changes? It should not, with the same rules enabled. If it has, either a rule was enabled or disabled, or the scan was stopped early last time.

Does it change my code? No. It reads and scores.

Can I scan just one procedure? Yes, Analyze Object. After it finishes, a Refresh button re-runs the same object, which is the loop to use while fixing something.

Are the results saved? No. Each scan is fresh.

Is NOLOCK really that bad? It scores heavily because it is the finding most likely to have been added without anybody understanding the trade. It permits dirty reads, rows read twice and rows missed entirely, and none of those produce an error. It is a correctness decision wearing a performance hint’s clothes.