SQL Server performance and administration
When a business system slows down, the cause is usually in the database. We find the queries, indexes and maintenance jobs responsible, fix them, and keep the database healthy afterwards.
What an index changes
Many slow screens come down to the database reading far more rows than it needs. Each dot here is one row.
Without the right index
The database reads every row in the table to find the three it needs. The more data there is, the slower it gets.
With it
It goes straight to the three rows. The time taken barely changes as the table grows.
Why systems slow down
A database that performed well with a year of data can struggle with ten. Tables grow, queries that once scanned a few thousand rows now scan millions, and indexes designed for the original usage no longer match how the system is used. Nothing has broken; the system has simply outgrown its tuning.
Because the decline is gradual, people adapt. They start a report and go for coffee. They learn not to run certain screens on a Monday morning. By the time it is raised as a problem, the cost in lost time is already considerable.
Measurement before opinion
SQL Server records a great deal about its own behaviour: which queries run, how often, how long they take and what they wait for. We start from that evidence. It means the review is quick, needs no changes to your system and produces findings you can check.
It also keeps the work honest. Every recommendation comes with a measured impact, and after each change we measure again. If a fix does not deliver, you will see that too.
A few changes do most of the work
In most systems a handful of queries account for the bulk of the load. Fixing those, typically by adding or correcting an index or rewriting the query, often makes the whole system feel different. We deal with those first and then stop to ask whether further work is worth it.
Some findings point beyond tuning: a table design that cannot scale, business logic in triggers that makes every write slow, or reporting run against the live database. Those are raised as options with costs, and can be folded into a wider modernisation plan.
Administration afterwards
Tuning is not permanent. Data keeps growing and usage keeps changing. Ongoing administration, either on its own or as part of a maintenance arrangement, covers backups and restore tests, integrity checks, index upkeep, capacity planning and version upgrades, so that performance problems are caught early.
Talk to us
Describe the system and what you need. You will hear back from someone who can answer technical questions.
Discuss your database 0800 433 7990What we look at
- Slow and expensive queries
- The statements that consume the most time and resources, measured from the server's own statistics over a representative period.
- Indexes
- Missing indexes that force full scans, and unused or duplicate ones that slow every write.
- Blocking and deadlocks
- Where transactions wait on each other, and which code paths cause it.
- Maintenance jobs
- Backups, integrity checks, index and statistics maintenance: whether they run, how long they take and whether they clash with working hours.
- Growth and capacity
- Which tables are growing, what can be archived, and whether the server is the right size for the load.
- Version and support status
- Whether the SQL Server version still receives security fixes, and the route to one that does.
How a performance review runs
Measure
With read-only access we collect the server's own performance data, so findings rest on what happens in production.
Report
You get a ranked list of problems, each with its measured impact, the proposed fix and the risk of making it.
Fix the top few
A small number of changes usually account for most of the gain. Each is tested on a copy of the database first.
Confirm
We measure again after each change, so the improvement is demonstrated.
Keep watch
Ongoing administration keeps the gains from eroding as data and usage grow.
A good fit when
- Screens or reports that used to be quick now take many seconds.
- Overnight jobs are overrunning into the working day.
- Users see timeouts or errors at busy times.
- The database is on a SQL Server version that is out of support.
- Hosting costs keep rising because the answer to slowness has been a bigger server.
Probably not for you if
- The slowness is in the network or on users' own machines; we will tell you if the evidence points that way.
- You need a full-time, on-site database administrator.
“We have found CodeFirst extremely professional. Their SQL Server technical experts are of the highest standard and I would definitely recommend them.”
Questions we are asked
Do you need full access to our production database?
No. A performance review needs read access to the server's performance statistics, not to your business data. Changes are scripted, reviewed with you and applied through your normal release process.
Can you help if we did not write the application?
Yes. Many fixes are made in the database alone: indexes, statistics and maintenance. Where the application's own queries need changing, we do that too if you hold the source code.
Is a bigger server the answer?
Occasionally, but it is the most expensive fix and the benefit rarely lasts. A missing index can make a query hundreds of times slower than it needs to be, and no hardware upgrade makes up for that.
Can you upgrade our SQL Server version?
Yes. We assess compatibility, rehearse the upgrade on a copy and plan the cut-over. See our SQL Server page for the detail.
Do you work with very large databases?
Yes. We look after databases holding hundreds of millions of rows, where maintenance windows and index strategy need real care.
Tell us about your system
Say what it does, what it is built on and what is worrying you. We will reply with what we would look at first and whether we are the right people to help.