Working as a freelance and consultant in Paris, I am specialized in relational database, mainly MS SQL Server (MVP SQL Server (Most Valuable Professional), MCDBA 2000, MCITP 2005, MCSA 2012 certification) and non-relational solutions. I offer my services and expertise for modeling, administration and tuning, of both server and SQL code. I am the author of several books in French and in English.
Services
- Performance and best-practice audits;
- Data architecture, modeling, and migrations;
- Training for developers and administrators;
- Application maintenance (TMA) and remote administration of your SQL servers.
Optimization: how I work
Short version
- DBMS like SQL Server are demanding tools that require a certain level of knowledge;
- it is important to model and develop correctly to achieve good performance;
- you need to know the engine, run load tests, in short take the subject seriously;
- I specialize in audit, diagnostics, optimization, and training around SQL Server performance issues.
- I favor coaching: DBA support, developer coaching.
- I deliver structured audits, and I can help with follow-up during implementation;
- I provide targeted training with my own course materials, from basic to advanced. I also offer optimization training for developers and diagnostics and troubleshooting training.
Long version
For about 25 years I have been auditing and improving the performance of MS SQL Server systems.
Even if Microsoft does not always say it, RDBMS (Relational Database Management Systems) like SQL Server are demanding tools. To get the best out of them, you need a solid level of knowledge, not only about the engine itself, but also about SQL. It is a specific language, declarative and set-based, and it requires a way of thinking different from object-oriented languages. Unfortunately, these skills are often missing and this leads to suboptimal use of SQL Server.
Let us summarize the pattern most often encountered: a company develops an application that stores its data on SQL Server. The development phase goes smoothly on a server used only by developers and holding a limited dataset. The program is tested, a demo is organized to show management that everything works and response times are good, and the program goes into production without load testing. At first, everything goes well, but performance degrades as data volume and access concurrency increase, and becomes critical at the worst time: during peak activity periods. We then look for solutions, add RAM, split tables into partitions, add processors, without knowing exactly where the problem comes from and without being able to predict whether this will fix the situation. These solutions bring some improvements, but at a rather disappointing scale, and the problem persists.
An RDBMS like SQL Server is not a dumb warehouse that you optimize by dedicating a more powerful machine. This brute-force approach is never a good first step. True SQL engine optimization uses the brain and focuses on improving structures, code, and indexing.
In addition, the difficulty of accessing the RDBMS and the SQL language leads many development teams to use abstraction layers like ORMs (object-relational mapping frameworks). Even Microsoft recommends using Entity Framework to access SQL Server. That makes sense for basic needs with reasonable volumes, but it causes problems where fast processing of large volumes is essential. A code generator like EF is currently unable to take full advantage of SQL language subtleties to produce optimized queries, and it is also very difficult for it to handle set-based operations, such as updating data across a set of rows in a single statement.
I know these issues both from the SQL Server side and from the client development side. I did development work for several years, and I still develop sometimes for clients, currently in a .NET and EF environment. I can see how EF manipulation leads us to ask purely .NET and object-oriented questions while forgetting SQL Server requirements.
I specialize in audit, diagnostics, optimization, and training around SQL Server performance issues. My way of working - at least the one I favor - is based on coaching: supporting the DBA and coaching developers. I rely on two strengths: precise and well-argued identification of performance issues, and guided implementation of strategies and best practices to improve things, taking seriously the needs of both parties: DBAs and developers. When clients have installed third-party vendor software and are unhappy with performance, I produce a precise and argued audit of the real issues, to demonstrate the source of the problem and enable constructive dialogue with the vendor.
I developed my own training materials for courses delivered in training centers, so I can first establish a solid theoretical base, then direct my coaching toward a practical approach I favor, preferably as a long-running accompaniment, so that recommendations are implemented and adjusted as needed.