How to improve the performance of SQL Server tables?

Managing and optimizing a SQL Server database is no small task. Table performance is key to query speed and database health. But how do we write tables in SQL Server and why is this important? And what tips and tricks can we use to improve table performance? In this article we will look at the answers to these questions.

Understanding the performance of SQL Server tables

The performance of the tables directly affects the speed and efficiency of data retrieval. This affects how fast applications run and serve users. If the performance of tables is poor, users may experience slow load times or timeouts, which degrades their experience.

But what can cause poor board performance? There are many possible reasons, such as:

• Poorly structured queries: if you don't use the SQL language effectively, you may retrieve too much data unnecessarily or run the same query too many times.
• Lack of proper indexing: indexes help to find data quickly in tables. If there are no indexes or they are inadequate, the database will have to look through the whole table for each query.
• Large amounts of data without partitioning: if a table gets too big, it is harder to manage and slower to retrieve. With partitioning, you can divide the table into smaller chunks that are easier to manage and faster to retrieve.
• Outdated statistics: SQL Server uses statistics to estimate how much data a query will return. If these statistics are not up-to-date, the query optimizer may make bad decisions.
• Lack of regular maintenance: like any well-functioning machine, the SQL Server database needs regular maintenance. This may include updating statistics, rebuilding indexes and checking for integrity issues.

How to write tables in SQL Server

One of the basic tasks when working with SQL Server tables is writing tables. This helps to better understand the structure and properties of the data. The sp_help command can be used to get information about the structure of a table, including column names, data types and constraints.

For example, if you want to know what a table called Customers looks like, you could write:

sp_help Customers

This gives us back, among other things, this information:

column_name	Type	Length	Nullable
CustomerID	int	4	no
Name	nvarchar	50	no
Address	nvarchar	100	yes
Phone	nvarchar	20	yes

From this we can see that the Customers table consists of four columns: CustomerID (integer), Name (text), Address (text) and Phone (text). The CustomerID and Name columns cannot be empty (Nullable = no), while the Address and Phone columns can be (Nullable = yes).

Improving the performance of SQL Server tables

Now we know how to write tables in SQL Server. But how can we improve their performance? Let's look at some strategies!

Optimisation of queries

Writing efficient SQL queries is an art. Simple changes can make a big difference. For example:

• Avoid using SELECT * if you only need certain columns. This unnecessarily loads the database and slows down the query. Instead, specify exactly which columns you want to see.
• Use the WHERE clause to retrieve only the data you really need. This reduces the amount of data transferred and gives faster results.
• Use indexes to filter or sort data more efficiently. If you know which column you want to search or sort by, you should create an index for that column.

For example, if we want to know which customer has at least 10 Orders, we can write:

SELECT Name
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    GROUP BY CustomerID
    HAVING COUNT(*) >= 10
)

In this query, we return only what we are interested in: the name of the customer (Name). And with the WHERE clause, we only check which CustomerID is listed at least 10 times in the Orders table. If we have indexes on the CustomerID columns of both tables, this should go quickly.

Indexing

Indexes help you to quickly find data in tables. If there are no indexes, or if they are inappropriate, the database has to look through the whole table for each query. This can be slow and resource intensive.

However, it does matter how we index. Too many or poorly chosen indices can also hurt performance. Indexes not only help reading, they also make writing more difficult. If a table is frequently updated or new data is added, the indexes should be updated as well. This takes time and resources.

So indexation requires a balance. You should not create too many or too few indexes, but only as many as you really need. How do we decide which column to index? Some considerations to take into account:

• Index the columns by which you often search or sort. For example, if you have a lot of queries that use the Name column of the Customers table in the WHERE or ORDER BY clause, you might want to create an index for them.
• Index the columns that are unique or primary keys. For example, if the CustomerID column in the Customers table is unique and a primary key, you should create an index for it. This will help you to quickly find customers by ID.
• Do not index columns that are very diverse or take very few different values. For example, if a Gender column in the Customers table can only take two possible values (M or F), it is not worth creating an index for it. This would not help much in speeding up queries, but it would degrade write performance.

In SQL Server, you can create multiple indexes. The most common are:

• Clustered index: this index defines how the table is physically stored according to a given column. A table can have only one clustered index. If no clustered index is specified, the table is stored as a heap (i.e. randomly).
• Nonclustered index: this index does not affect the physical storage of the table, but creates a separate data structure that references the rows of the table. A table can have more than one nonclustered index.

For example, if you want the CustomerID column of the Customers table to be a clustered index (i.e. the table should be sorted by this column), you could write:

CREATE CLUSTERED INDEX idx_CustomerID ON Customers (CustomerID)

And if you want the Name column of the Customers table to be a nonclustered index (i.e. a separate data structure to help you find the names), you can write this:

CREATE NONCLUSTERED INDEX idx_Name ON Customers (Name)

Partitioning

For large boards, you may want to partition them. Partitioning means dividing a table into smaller pieces so that each piece (partition) remains at the same logical level. Partitions can be stored in different physical locations.

The benefits of partitioning include:

• Improved performance: if a query only applies to a specific partition, you don't need to look through the whole table. For example, if we partition a table called Orders by date (e.g. monthly), if we only want to query orders for a particular month, we only need to query that partition.
• Improved manageability: if a partition becomes too large, it can be moved or archived to another location more easily. For example, if we partition a table called Orders by date (e.g. monthly), then when we no longer need the orders from an old month, we can move or delete that partition.
• Improve availability: if a partition is damaged or lost, you don't have to restore or rebuild the whole board. For example, if you partition a table called Orders by date (e.g. monthly), if a partition of a particular month is damaged or lost, you only need to restore or rebuild that partition, not the whole table.

Partitioning in SQL Server consists of several steps. First, you need to define a partition function that determines which partition a particular row will be placed in. For example, if you want to partition a table called Orders by date (e.g. monthly), you could write this:

CREATE PARTITION FUNCTION pf_Orders (date)
AS RANGE RIGHT FOR VALUES
('2023-01-01', '2023-02-01', '2023-03-01', ...)

This function tells you which partition to place an order in based on its date. RANGE RIGHT means that the boundary values (e.g. '2023-01-01') belong in the right partition. The FOR VALUES section lists the boundary values.

Second, you need to define a partition schema that determines where each partition should be stored. For example, if you want to partition a table called Orders by date (e.g. monthly) and you want to store all partitions on the same disk, you could write this:

CREATE PARTITION SCHEME ps_Orders
AS PARTITION pf_Orders
ALL TO ([PRIMARY])

This schema tells us to partition the table based on the previously created pf_Orders function, and to store each partition in the file system named [PRIMARY].

Third, we need to specify which table should be partitioned and by which column. For example, if we want to partition a table called Orders by date (e.g. monthly), we could write:

CREATE TABLE Orders (
OrderID int PRIMARY KEY,
CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID),
OrderDate date,
TotalAmount decimal(18,2)
)
ON ps_Orders (OrderDate)

This command tells us to create a table called Orders with four columns: OrderID (integer), CustomerID (integer), OrderDate (date) and TotalAmount (decimal). OrderID should be the primary key, CustomerID should be a foreign key that references the CustomerID column in the Customers table. The table should be partitioned based on the previously created ps_Orders schema, according to the OrderDate column.

This completes the partitioning. Now we can use the table as if it were not partitioned, but we can enjoy the benefits of partitioning.

Use of statistics

Statistics help the SQL Server query optimizer to make informed decisions about how to run queries. Statistics contain information such as the set of values, distribution and frequency of columns.

If these statistics are up to date, the query optimiser can better estimate how much data a query will return and which method to choose to retrieve the data. For example, it can decide whether or not to use an index.

SQL Server automatically creates and updates statistics for indexed columns and some non-indexed columns. However, sometimes you may want to manually update or create statistics for certain columns.

For example, if we want to update the statistics for the OrderDate column of the Orders table (which we have partitioned), we could write:

UPDATE STATISTICS Orders (OrderDate)

This command updates the statistics for the OrderDate column on all partitions.

And if you want to create a new statistic for the TotalAmount column of the Orders table (which is not indexed), you can write:

CREATE STATISTICS st_TotalAmount ON Orders (TotalAmount)

This command creates a new statistic for the TotalAmount column.

Using statistics can help you make better use of indexes and partitioning.

Regular maintenance

Like any well-functioning machine, the SQL Server database needs regular maintenance. This may include the following tasks:

• Updating statistics: as mentioned above, this helps the query optimiser to make better decisions.
• Rebuilding or reordering indexes: as we use indexes, they may become fragmented, i.e. not optimally ordered. This can degrade query performance. Rebuilding or reordering indexes can help to bring them back to an optimal state.
• Checking for integrity problems: there may be corruption or errors in the database, for example due to disk failure or power failure. These integrity problems should be checked and repaired regularly.

SQL Server has several built-in commands and tools to perform maintenance tasks. For example:

• sp_updatestats: this command updates all statistics in the database.
• ALTER INDEX: this command allows you to rebuild or reorder indexes on a table or the whole database.
• DBCC CHECKDB: this command checks and repairs the integrity of the database.

Analysis of SQL Server tables

Parsing SQL Server tables is as important as maintaining their performance. Analysis can help you get to know your data better and discover potential problems or opportunities.

In SQL Server there are several tools for parsing tables. Some examples:

• sp_spaceused: this command shows how much space a table takes up and how much space it uses.
• sp_columns: this command shows detailed information about the columns in the table, such as data types, lengths and constraints.
• sp_indexinfo: this command shows detailed information about the indexes in the table, such as index type, size and partitions.

For example, if you want to know how much space the Orders table takes up, you could write:

sp_spaceused Orders

This gives us back, among other things, this information:

name	rows	reserved	data	index_size	unused
Orders	1000	160 KB	112 KB	40 KB	8 KB

This shows that the Orders table contains 1000 rows and takes up a total of 160 KB of space. Of this, 112 KB is for data, 40 KB for indexes and 8 KB is unused.

Summary

Optimizing the performance and analysis of SQL Server tables does not have to be a daunting task. By understanding the importance of table performance, knowing how to describe it in SQL Server, and applying remediation strategies, you can ensure that your database runs smoothly and efficiently.

We hope you found this article useful. If you have any further questions or comments, don't hesitate to let us know in the comments!