Cost Effective SQL Server Development

Why SQL Work Needs Specialists

SQL is not like the rest of your codebase. It is declarative and set based, and the habits that make a good C# or JavaScript developer often make a poor query author. A developer who writes a loop where a join belongs will produce something that passes tests on a thousand rows and falls over at a million. We wrote about this at length in SQL Is Different, because it is the single most common cause of the performance problems we get called in to fix.

The symptom is usually the same. The application was fine at launch, the data grew, and now specific screens take twenty seconds. Adding servers does not fix it, because the problem is an execution plan, a missing index, or a query shape that cannot use the indexes you already have. Our page on how to make SQL fast walks through how we find out which one it is.

Do a billion rows mean you need NoSQL?

Usually not, and this is the decision that costs the most to get wrong. Ask anything generic how to handle a billion records and you get the average answer: an ordinary database will crawl at that size, so move to NoSQL. That answer is reasoning from how a typical database with a billion rows behaves. Yours is not typical, and speed depends on how the data is used, not how much of it there is.

One system we built keeps about 1.5 billion rows in its main table on ordinary SQL Server. It is fast, for two reasons that have nothing to do with the row count:

• The queries people run all day are indexed for exactly those queries. Users are almost always asking a narrow question, and a narrow question against a correctly indexed table does not care how many rows sit behind it.
• The heavy work runs in the background, one at a time. Reports that genuinely have to cross large amounts of data are queued rather than run while someone waits. The user asks for the report, we build it, and they get an email a few minutes later telling them it is ready. Nobody sits watching a spinner, and the interactive side of the system stays quick.

Splitting a system that way, into what has to be instant and what can wait a few minutes, is usually cheaper than changing database technology, and it keeps something you lose when you move to NoSQL. NoSQL gets its speed by shaping data around a known set of queries, which leaves the data in silos. That is fine until somebody needs an answer nobody planned for, and on admin and reporting work that is most weeks. With everything still in one relational database, a new question is a new query rather than a new project.

Sometimes NoSQL genuinely is the right call, and we will tell you when it is. The point is that it is a decision about your access patterns, and it should be made by someone who has built systems at that size rather than by a rule of thumb about row counts.

Why the expensive reports run one at a time

This is the part that surprises people. If a hundred users ask for an expensive report at once, the worst thing you can do is run a hundred reports at once.

The condition that decides it is whether the data a report reads fits in memory. While it does, running several at once is fine. Once each report is scanning more than the server can hold, running them together means every report evicts the pages the others just read, so the same data gets fetched from disk over and over, read-ahead stops helping because the access pattern is no longer sequential, and the disks spend their time seeking between competing scans. Nobody gets their report, and the server struggles for everyone else at the same time.

Run them one after another and each report gets the whole buffer pool and read-ahead working for it, on one set of data at a time. The queue finishes sooner than the same reports would have running in parallel, the first person gets their answer far sooner than they would have, and the interactive side of the system, the part where people are actually watching, is never affected.

That is not a tuning trick. Once the working set is larger than memory, serializing wins every time, and more hardware does not change the rule; it only moves the point at which it starts to apply. It is the kind of thing that becomes obvious the first time you run a system carrying real data, and a generic recommendation will never mention it. Which is rather the point: the person choosing your technical approach should have been here before.

What we do

• New database design. Schema, indexing strategy, and stored procedures built for the queries you will actually run.
• Performance work. Query plan analysis, index tuning, and rewriting the queries that are costing you the most.
• Applications on top of SQL Server. .NET and ASP.NET line-of-business systems where the database is the centre of gravity.
• Integration and reporting. Pulling data from several systems into something people can actually report against.
• Migration. Moving off older SQL Server versions, or from Access, MySQL, or spreadsheets that outgrew themselves.

Work we have done

SQL Server sits underneath most of our portfolio. Credit Suisse ran on ASP.NET, SQL Server, and IIS, and the write-up of that work is published on this site. Pickatime handles online scheduling across education, health, and business on .NET, Java, and SQL Server. PDF-It, US-Imaging, and Realty-Northwest are all .NET and SQL Server applications. CondosAndCondos pulls South Florida real estate data from several sources into one SQL Server database. Different industries, same discipline underneath.

How the code gets written

AI writes the implementation and our developers direct it, which on database work mostly means overruling it. A model will produce a query that is correct and unusably slow, or an index that helps the query in front of it and hurts four others, because it is reasoning about the statement rather than the workload. Deciding what the whole system needs is still the part that takes someone who has done it before.

Databases are also where a breach actually hurts, so nothing goes out without being tested against an AI attacker first, and we re-run those audits when a materially better model is released. Why we changed the release process.

How the work runs

Performance work starts with measurement, not opinion. We look at what the server is actually waiting on before touching anything, because the slow query people complain about is often not the one consuming the machine. You get told what we found and what it will cost to fix, and you can decide to stop there.

For build work we deliver incrementally, so a schema that will not survive your real data gets caught in week three rather than after launch. You talk directly to the development lead. We have been doing this since 1998, and SQL Server has been in the stack for most of it.