Farber Consulting Group | Custom Software Development NY | Software Development Company | Sql Consulting

Database Performance Tuning

Written and reviewed by Doron Farber, founder of The Farber Consulting Group. 30+ years building custom software, optimizing databases, and integrating AI into existing systems. Former Director of IT at a NYC architecture and engineering firm.

  • Slow reports and application timeouts can delay daily work. Our database performance tuning service finds the database problems behind these delays and measures the effect of each change.

  • We review SQL Server queries, indexes, and server settings before recommending work. You receive an explanation of the problem, the proposed changes, and the results we will measure.

  • For iA American Warranty Group, we tuned a database with more than 800 stored procedures and functions. Queries ran 45% to 80% faster, depending on the procedure.

  • For Avis Car Rental, we tuned the MySQL database before developing its contract manager software. The database work came first, followed by application development.

Call 732-536-4765 or Contact Us to schedule a free consultation. Tell us which reports or screens are slow and when the delays occur.

If your applications works slow, then it is most likely could be that the back end your application as the database is not optimized and probably required some indexes to be created or removed and some stored procedures to be re-written.

SQL Server performance tuning for daily business work

Our team has worked with databases since 1992. We tune SQL Server and MySQL databases and help companies move data from MS Access when their requirements change. A report can run well with one set of inputs and take much longer with another. We examine the queries and workload behind the delay before recommending database or application changes.

What a database performance tuning engagement actually involves

We identify the work that consumes resources, test changes, and compare the results. These six steps connect the technical findings to the reports and applications your staff use.

Find the queries that use the most resources

SQL Server keeps a record of what it's been running. That record tells you which queries burn the most CPU, which do the most reads, and which get called the most times. Those 3 lists almost never agree. A query that runs 4 seconds and fires 900 times an hour costs you more than one that runs 40 seconds once a night. So we rank the real offenders first, because guessing at this stage is how tuning budgets get spent on the wrong thing.

Read actual execution plans

An execution plan shows how SQL Server processes a query. An actual plan also includes measurements from execution, which help us compare expected and actual row counts. Large differences can point to problems with estimates, data distribution, or query design. Our guide to SQL Server execution plans explains this analysis in more detail.

Check statistics and indexes

Statistics describe the data and help SQL Server choose an execution plan. Indexes can reduce the work needed to find rows, but they also require storage and maintenance. We check for outdated statistics, missing indexes, and indexes that duplicate existing ones. We test proposed changes against both reports and the operations that add or update data.

Rewrite the stored procedures and functions that deserve it

This is usually where the big numbers come from. Scalar functions called once per row. Cursors doing what a set-based query does in one pass. Parameter sniffing that hands one customer a fast plan and everybody else a slow one. Rewriting a single procedure often beats every server setting combined.

Blocking, contention, and the Monday morning crash

When a database is fast at 2pm and unusable at 9am, the cause is usually concurrency. Long transactions holding locks, a nightly job that didn't finish, an isolation level nobody chose on purpose. Some of these fixes take no code at all, which is where we look first: codeless options for database optimization.

Measure performance before and after

Logical reads, duration, CPU time. Same query, same data, same server, captured before we touch anything and again after. Without that baseline, nobody can tell whether the work paid for itself. Including us.

This is just the short list of services we offer. If you need something that isn’t found above, be sure to ask during a consultation.

A SQL Server project with 45% to 80% faster queries

iA American Warranty Group had more than 800 stored procedures and functions, some nested 10 levels deep. Applications called those functions directly, so the work had to preserve the existing application interfaces. Three DBAs worked on the project over 4 months. One report fell from approximately 1.19 billion logical reads to 48 million, reducing the database work needed for each run. That report's runtime fell from 6 minutes 6 seconds to 2 minutes 17 seconds. Across the project, the recorded performance improvement was 45% to 80%, depending on the procedure, without application code changes. The database performance tuning case study explains our analysis, changes, and testing. These figures describe that engagement; the result for another database depends on its code, data, and workload.

Remote DBA services for ongoing support

A SQL Server database administrator can provide ongoing support or help your existing team with specific work. Our remote DBA services cover SQL Server, MySQL, and Oracle. Support includes backup checks, maintenance, and investigation of performance problems, with around-the-clock availability. We work with databases larger than 1 TB and provide support according to the agreed scope.

MS Access upsizing and MySQL consulting

MS Access upsizing moves data to SQL Server or MySQL when the existing setup no longer meets business requirements. We transfer the data, check table relationships, and add indexes where needed. The existing Access forms and reports may remain in use if the new connection supports them. We assess compatibility and required changes before planning the move. Our MySQL consulting includes query tuning, configuration, security, and backup and recovery planning. We assess the installed version and workload before recommending changes.

Data migration and database design

We migrate data between platforms, including MS Access to SQL Server and Visual FoxPro to SQL Server or MySQL. We also support database version upgrades and check transferred records and relationships. Our database design work uses the xCase modeling tool to examine tables and their relationships. This helps us identify duplicated data and structural problems that affect queries or future changes.

Data cleansing

Duplicate records and inconsistent fields make data harder to use. We clean records while preserving useful information from the source data.

  • Find and merge duplicate records.
  • Convert dates of birth to a consistent format.
  • Separate combined email fields where required.
  • Apply duplicate checks during imports and exports.

Database maintenance

Database maintenance supports reliable operation after tuning work. We select tasks and schedules according to the workload and recovery requirements.

  • Full and differential backups.
  • Removal of old backup files according to retention requirements.
  • Index rebuilds or reorganization where testing shows a benefit.
  • Data and index compression where appropriate.
  • Report creation.
  • Ongoing duplicate checks and cleanup.

FAQ - Database Performance Tuning

How long does database performance tuning take?

The scope depends on the cause of the delay and the testing required. We assess the database before estimating the work and cost.

Do we need to rewrite the application?

Some problems can be resolved through indexes, statistics, database code, or server settings. We assess whether application changes are necessary and explain the reason before recommending them.

Can you tune MySQL as well as SQL Server?

We provide tuning services for both platforms. We also support Oracle through our remote DBA services.

What if database design causes the problem?

We explain which problems tuning can address and which require design changes. You can then compare the scope and cost of each option.

Discuss your database with a SQL Server consultant

Our SQL Server consulting starts with the problem your staff experience. Database performance tuning gives us measurements to guide the work and check the result. Call **732-536-4765** or schedule a free consultation. We are based in NJ USA, and serve clients across the US, the UK, and France.

Got questions about unleashing the full potential of your project?
We’ve got the answers!

Contact Us

Search

Schedule a free consultation Free consultation