★New★ a newsletter

Dear reader,

Somewhere in the past we've probably worked together to develop, manage, or improve a SQL Server application. Maybe it's been quite some time since we had contact, and I never asked for permission to use your email address in this way.

A few times a year I will send a newsletter in which I tell something from my practice about the proper use of SQL Server and of course about my services. If you don't want to receive the newsletter, let me know.

Best regards,

Paul van Oordt

Lessons from 200+ applications

In the past 20 years I have worked with over 200 SQL Server applications. These are some of the lessons I would like to share with you.

Inadequate code is the main performance killer

And this can only be solved permanently and scalably by improving the code. There are lots of ways to get it wrong. A detailed explanation can be found on my site: what to know about SQL Server performance and recommendations for SQL Server development. Most important take away: Learn to think set-based and be aware of what is possible in SQL Server.

Due to a lack of knowledge, inefficient query techniques are often used. I often also see a sub-optimal division of labor in which the application performs tasks that are easier and more scalable performed by SQL Server. It also happens that functionality is built that is already available as standard, for example with regard to concurrency (transaction and locking), redundancy (indexed views) or transferring data to other systems (transactional replication).

Performance is a developer responsibility

Admins take care of the platform and monitor its performance. But they are not best equipped to tune a system. They cannot change code and that is often the main cause of performance problems. The DBA can add indexes, but he or she will do it no better than SQL Server can do it automatically. And that's not good enough. It is the developer who knows how often and how much data is retrieved or modified, how that will change over time, and who can define a scalable way to do that. Performance and scalability are essential, if often forgotten, parts of development.

Keep it simple

I've seen many systems that are unnecessarily complex. Scaling out, redundancy or enabling completely different technologies (SQL Server Analysis Services, NoSQL, FileMaker), it is often done with performance in mind, but without first fully exhausting the capabilities of SQL Server itself. And the following applies to denormalization: Use the entire arsenal of indexes, indexed views and persisted computed columns. And further: Don't.

The same goes for premature scaling out. Sometimes scaling out to another platform is necessary, but keeping the data up to date should not be underestimated. Especially if the data is also transformed into a data warehouse. Replication, read-only servers, data warehouses, Analysis Services, it's great technology, but only use it if you really need to, when the capabilities of a single SQL Server performing all tasks are exhausted.

Ensure adequate availability and disaster recovery

I regularly come across applications that are inadequate in terms of recovery point or recovery time. Any database that contains non-reproducible data, deserves a configuration and backup routine for point in time restore. In addition, a basic availability group is usually required for high availability and can be configured without additional SQL Server license costs. Otherwise, at least an emergency procedure should be prepared, tested and documented, for example by the setting up an empty SQL Server (no license required) on which all server level objects such as logins and jobs have already been created.

From problem solving to avoiding problems

Over the years I have analyzed and improved hundreds of SQL Server implementations. I keep doing that. And at the same time I'm going to focus more on preventing those problems. An impeccable data model and state-of-the-art set-based queries are needed to create a high-performing and scalable system. Are you developing an application with a SQL Server data layer? Then I can help you set up such a system. Don't become one of those future customers with a large and artfully written application... on an imperfect data model... using the wrong query techniques. Do it now, while there still are only advantages.

I like to share my knowledge. Take a look at the recommendations for SQL Server development. I wonder how many of those rules you're breaking right now. If you are looking for help and advice in applying these principles, please contact me. I only do short assignments. I will work with you, put you on the right track, advise, generously share my knowledge and leave. And I remain available for more quick questions or short assignments. Both for advice and for problem solving.

January 2021; the newsletter appears a couple of times per year; subscribe / unsubscribe