Newsletter April 2021

After my first newsletter three months ago, here's a next one with hopefully some interesting topics from my practice.

If you don't want to receive the newsletter, let me know.

Best regards,

Paul van Oordt

Choosing a software application, data layer considerations

All recommendations for SQL Server development also apply when you are selecting a software package, for example an ERP or warehouse management system. Data layer changes are notoriously difficult to implement. This not only affects the developer of the package, but once you have committed yourself to a vendor, it affects you as well. How well the software uses correct SQL Server techniques greatly affects the data quality, performance, scalability and extensibility of your system.

Of particular importance, first of all, are an adequate normalized data model with the right data types, nullability and foreign keys, as well as thorough indexing and an efficient way of accessing the data. Are queries set-based, written with expertise, and do they take advantage of recent SQL Server capabilities? Are stored procedures used or is the code ad hoc? How is good concurrency achieved? Are there any long term locks? Is nolock used, which can lead to incorrect results (this is quite common, also within well-known systems), or is the much better optimistic locking with read committed snapshot isolation used? Can you even see all this or is the code encrypted? And can you tune queries yourself and thus limit (cloud) costs, for example by creating indexes? If not, is the supplier going to do it for you?

It is logical that you primarily look at the functionality of a system, but the underlying database is also very important and largely determines the quality of the data - your data. For example, it may seem like a minor thing in the context of a package selection, but a database with or without trusted foreign keys makes a world of difference if you ever want to export the data and process it yourself. My advice: Don't be surprised later and take a critical look at the underlying database!

Index (de-)fragmentation, a developers responsibility

When performance problems occur, index fragmentation is often the first thing assumed to be the cause. Fragmentation of an index means that the physical order of the index pages on disk differs from the logical order. When during the execution of a query data is read from a spinning disk, this delays an ordered scan of the index. But not all indexes get fragmented and fragmentation isn't always a problem, at least not with abundant memory and/or data on SSD.

I can't just blindly recommend weekly or daily defragmentation of all indexes. (And I've seen it done every hour!) It may temporarily degrade performance significantly. Moreover, the capacity with which this is done is not free, certainly not in the cloud. Even if the performance improves after defragmentation, that doesn't say much. An index rebuild implies a statistics update and can therefore lead to a better query plan. Only performing a statistics update may then be better.

It is my standpoint that creating the correct indexes is the responsibility of the developer. Only the developer knows which data is accessed. When creating an index, ideally, you also think about its management. Does this index fragment? Is that a problem? If yes, what is the defragmentation regime and fill factor? If needed the index can be added to a job that performs defragmentation of a selection of indexes. This also allows defragmentation of indexes that suffer from low page-fullness (sometimes called internal fragmentation) due to updates or deletes.

Always be careful with rebuild, especially with large indexes. This can block the execution and even compilation of queries for a considerable time. SQL Server Enterprise Edition has the option to do a rebuild online so that these problems do not occur. Otherwise, choose to reorganize the index.

What if the developers haven't taken care of this? Then a DBA will have to do it. Query tuning can clarify whether fragmentation is a problem and also provide insight into the many other factors that play a role in performance.

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