design-1700169368929-768x512 SQL Server Section

Check for SQL Index Fragmentation

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…

Read More
design-1700169330915-768x512 SQL Server Section

SQL Disk Space Usage

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…

Read More
design-1700169232893-768x512 SQL Server Section

Test SQL Connection without SSMS

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…

Read More
image-82 SQL Server Section

SQL Joins

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

Read More
design-1700166318548-768x512 SQL Server Section

SQL Suggested Indexes

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…

Read More
sql-768x511 SQL Server Section

Determine where SQL files are

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…

Read More
savings-2789137_640 SQL Server Section

Shrink an SQL database

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,…

Read More
network-8198758_640 SQL Server Section

Fix a SQL 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…

Read More
533233247_b6baa30fdb_b-768x710 SQL Server Section

Enable SQL Database Diagrams

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;

Read More
blur-1853262_640 SQL Server Section

Update SQL Statistics on things

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…

Read More
1 2 3 4 5