SQL Server Section

Your database is like a machine. Much like your car, your database needs maintenance to ensure it runs smoothly. The indexes slowly become fragmented as you insert, delete, and update data within your tables. As indexes become fragmented, the performance of your queries begins to suffer. With the Enterprise Edition of SQL Server, indexes can be de-fragmented automatically, but with other versions, you must keep on top of things. Of course, you should include an automated housekeeping job to ensure this is done to some sort of cadence. How do you determine which indexes are fragmented? As with many parts…

How big? It isn’t very often that you need to understand which tables take up most space within your database. Normally, knowing how much space the entire database consumes is enough. However, SQL Server is expensive if you use a cloud provider. Even the smallest database is expensive. Knowing which tables take up the most space can be crucial in those instances. For example, suppose you store infrequently accessed data in the database, such as historical information. In that case, you might decide that this can be moved from the database to something more appropriate, such as Table Storage. Determining…

Background You’ll find yourself logged into a web or application server and need to test that you can access the SQL server at which you’ve unsuccessfully tried to point your app. Luckily, a trick built into Windows allows you to save the day without having to install anything on your box – and it works on both client and server operating systems just as well. The mythical UDL file The guys at Microsoft have baked a SQL Connectivity tool right into Windows by creating Microsoft Universal Data Link files (*.UDL) – something designed for another purpose. These were designed to save SQL…

Background There are many ways to join tables and views in an SQL server. This diagram is a great quick reference.

How SQL access data Whenever SQL Server executes a query, it builds an execution plan to find the data and return it to the requesting process. I have another post that explains what the plans do. One major part of the process, is to examine the Indexes it has over the data within the tables or views the query is running over. Performance Problems In tables with small numbers of rows (100s or 1000s), modern computers are so fast that often you don’t see that there is a problem. However, as the size of those tables grows, suddenly, out of…

SQL Files A typical SQL Server Database comprises two physical files on the disks: an ‘mdf’ file and a ‘ldf’ file. The ‘mdf’ file contains the database scheme, tables, stored procedures, etc., and the ‘ldf’ contains the log file. It is best to put these files on different disks for optimal performance. I like to keep things simple in production environments and create and maintain separate customer databases on separate servers. Each server is provisioned with two disks, so the data file goes on one and the log on another. In test environments, I usually do not do this. A…

Size does matter As you use a database, the log files increase in size. When you back up your database, SQL frees the space in the log file – it doesn’t shrink it – and the process starts again, filling the log file. In typical production environments, you run backups, but in test environments, you may never run backups, so the log files will keep growing until you do something about it. Regardless of where you run your database, the larger the database, the more it costs. This is very much the case in cloud environments; the larger the database,…

A true story Several years ago, before we moved our Production and Development systems over to Kubernetes running on Azure, we used a well-known hosting provider in the UK. Initially, we used Virtual Windows Machines on Windows Hyper-V Server, but the performance was never good enough. Even though we paid for a guaranteed level of service, there were times of the day when our application tanked. Our CPU, memory, disk, and network were little more than idling along, but still, the performance was terrible. An hour or two later, the application was snappy once more. Traffic hadn’t changed, we hadn’t…

The Code SQL Database Diagrams are invaluable in understanding the schema. I use them all of the time. Occasionally, I create the database tables and relationships from the diagram designer – try it – it’s great! Of course, you cannot use database diagrams unless you have things set up correctly. At some point, diagrams stopped working on my databases. I don’t really understand why. However, the fix is simple: – ALTER AUTHORIZATION ON DATABASE::[DATABASENAMEHERE] TO sa;

Background As our customer base has grown, so has the size of those customers and, with them, the amount of data we hold within our databases. We have a solution that keeps an eye on the performance of the database servers, and we monitor it closely to ensure we can see potential problems before they cause downtime or poor performance in our applications. A few months ago, we noticed that one of our SQL servers was beginning to show signs of high CPU usage. It was regularly hitting over 80% CPU usage. We like to keep ‘some in reserve’ on…