Most of what a SQL Server system does lives in its T-SQL, in the stored procedures, views, functions and triggers that hold the business rules. On a system somebody else built, that’s usually the part that never got written down.
So every change feels like a risk. One procedure has become slow and no one can say why, two others do almost the same job in slightly different ways, and the version upgrade you need is waiting on code that people would sooner not touch.
You don’t have to untangle it on your own. CDE Systems has worked in SQL Server since 1999, mostly on systems other people built. We read the code, report on what it does, fix and tune what needs it, and write it all down. It stays your code throughout.
The calls we get most
- Procedures that have slowed down as the data has grown
- Code that ran for years and broke after an upgrade
- Hundreds of procedures, with no way of telling which are still called
- Business rules buried in triggers that fire in an order nobody planned
- Errors that reach the application with no handling behind them
- A change that’s needed, and a procedure no one wants to open
Code like this has usually had several authors over the years, each working to a deadline, and some of it was first generated by a wizard and edited by hand ever since. It grew that way. Nobody set out to make it hard to follow.
Why it’s worth sorting now
Undocumented code gets riskier with age. Each change depends on what one person can remember, and that person may well be you. Each upgrade is a gamble too. If you’re moving off SQL Server 2016 or earlier, the upgrade itself is rarely the hard part. The T-SQL on top of it is. See SQL Server migration and upgrades.
What we do with it
We analyse it and report back. We work through the code, comment it, and report on every object. You’ll know what runs when and what calls what, which procedures can no longer be reached, where transactions are left open, and where the risks are. On a system somebody else built this is usually the first job, and it’s what makes everything after it possible to price.
We tune it, if the code is what’s slowing things down. The usual suspects:
- Cursors and row-by-row processing, where a set-based statement would do the work in a fraction of the time
- Scalar functions called on every row, quietly forcing everything around them to run serially
- Non-SARGable predicates, where a function wrapped around an indexed column means the index never gets used
- Implicit conversions, usually a varchar parameter meeting an nvarchar column
- Parameter sniffing, which is why the same procedure can be fast for one customer and unusable for another
- SELECT * in production code, pulling columns the application never uses across the network
- NOLOCK added everywhere as a speed fix, which can quietly return wrong results
- Missing SET NOCOUNT ON, and transactions held open longer than they need to be
- Dynamic SQL built by joining strings together, which is a performance problem and an injection risk in the same line
For the wider picture, including indexes and server settings, see SQL Server performance tuning.
We make it upgrade-safe. Old-style outer joins, deprecated data types, system procedures Microsoft has removed, SET ROWCOUNT used for updates and deletes, and compatibility-level behaviour that changes execution plans without a line of code changing.
We tidy it. Naming is made consistent, copied blocks are merged, dead procedures come out, and error handling goes in throughout. The aim is that the next person to open it can follow it, whether that’s us, you or someone else entirely.
What you’re left with
A written record of every object in the database, including tables, views, stored procedures, functions, triggers and jobs, and how they depend on each other.
Once that exists, the system no longer rests on one person’s memory, and any competent developer can pick it up, including someone on your own team. Some clients ask for the documentation on its own, with no other work attached.
What if we only want it formatted?
Then a formatting tool will do it in seconds for very little, and you should use one. What a formatter won’t tell you is which of the procedures are still called, which two do the same job slightly differently, or why the one that matters takes ninety seconds. That’s the part we do.
Will you want to rewrite it all?
No. If something needs fixing we fix it, and we leave the rest alone. Anything bigger is scoped and priced first, so you can decide whether it’s worth doing. Nothing goes live without your say-so.
Commercial Catering Service & Maintenance (field service)
Aire Valley Catering
Field service apps that work with no signal
Aire Valley Catering’s engineers work in commercial kitchens across the UK, often with no usable signal, and every job still has to be recorded. We built four production…
View case study
Transport and logistics
ACC Worldwide
A faster SQL Server system, noticed on day one
ACC Worldwide’s staff used one business system all day, every day, and it was slow. We worked on the SQL Server environment behind it. The speed-up was immediate,…
View case study
Travel and leisure (adventure travel and expeditions)
Jagged Globe
Keeping bookings, customer records and the website in step
Jagged Globe runs on an internal database and a booking website that have to agree every day, and sometimes they didn’t. We traced and fixed the transfers between…
View case study
Send us the database
Tell us what the system does and what’s worrying you about the code. We’ll come back with what’s in it, what’s broken, what’s slow, and what it would take to put right.
Not sure this is the right page? Start with SQL Server support and consultancy, or a SQL Server health check for £495 (+VAT)