Best Practices and Tips for Designing SQL Server Databases for Database Administrators

According to consultant Koen Verbeeck, knowing what to expect from SQL Server is probably the biggest step to running it successfully in enterprise applications. It’s not just about how SQL Server works, he said. Successful deployments also come from knowing your company’s business goals and how best to use SQL Server to achieve them.

For this process to work, it would be an understatement to say that Verbeeck considers SQL Server database design to be important. “We can talk for hours and days, even weeks, about designing databases,” he said during a recent SQL Server development and design best practices webinar.

Each database technology has its pros and cons, said Verbeeck, senior business intelligence consultant at Brussels Airport Co., which operates the main airport in the Belgian capital. He encouraged database administrators (DBAs) and developers to experiment and explore different designs for their SQL Server systems. “You really have to assess your specific database needs and find out what’s best for you. “

Verbeeck said users should ask themselves the following questions: What am I doing with my database today? What will I do with it next week, next year and in five years? When it comes to database design, thinking long term is a good idea, he advised, before breaking down a list of design tips.

Tables and indexes in SQL Server databases

From Verbeeck’s perspective, the smaller or narrower the database tables, the better. Avoiding the use of blob and nvarchar columns in SQL Server database design is key to keeping tables narrow, he said. Verbeeck recommended against using nvarchar data types in particular, as they require twice as much space in a table as varchar columns.

Even though data storage is less expensive than before, reading larger columns takes longer, Verbeeck pointed out. Conversely, if a query has less data to read, it will perform the task much faster.

If you’re using clustered indexes, however, it doesn’t matter what type of columns your SQL Server table contains, Verbeeck added. “Have a clustered index – it will help you in the long run,” he said.

A clustered index sorts the rows in a table based on their key values; when you put one in a table, the index essentially becomes the table, according to Verbeeck. He noted, however, that data mover tables do not always benefit from clustered indexes, as they can slow down the process.

While Verbeeck vouches for clustered indexes overall, he stressed that DBAs and developers should also consider other SQL Server indexing options before committing to a particular design.

For example, nonclustered indexes that include pointers to rows containing particular key values ​​are typically used to speed up SELECT queries. But Verbeeck said they may slow down other queries, especially data manipulation language, or DML, instructions. Nonclustered indexes also create additional copies of the data, which take up more space in a database. This tends to result in unused indexes, which DBAs should monitor as part of the database monitoring process, advised Verbeeck.

More ways to improve database performance

Columnstore indexes are especially useful for querying data warehouses because they help speed up crawls of large tables, Verbeeck said. When data is stored in SQL Server in columns instead of the traditional row-based format, DBAs can control which of these columns are read or checked by the system during query execution, which speeds up the whole process, he explained.

Verbeeck added that columnstore indexes can also be used with In-Memory OLTP, an in-memory engine built into SQL Server to speed up the performance of transactional databases.

A so-called coverage index is a clustered or nonclustered index that, according to Verbeeck, is designed to include – or cover – all of the columns required by a particular query. Therefore, coverage indexes generally do not need to roll back to a database table to retrieve additional columns.

When one needs to get back to the table, this step is called key finder, which can slow down queries and should be avoided if possible, Verbeeck said. One way to do this, he suggested, is to use the INCLUDE commands to cover additional columns. Plus, by including keywords in specific columns, you can narrow down your search as part of a query.

Although a good SQL Server database design is necessary, it will not solve all the problems that arise. A workaround recommended by Verbeeck is to add more memory. Like storage, memory isn’t as expensive as it used to be, and it’s a lot cheaper than bringing in an external database expert to help you improve your design, Verbeeck said. He likened the memory boost to a band-aid – in many cases it’s a temporary fix, but it’ll save you time to find a more solid solution to the problem.

Give your databases space to grow

In the online seminar, which was sponsored by database tool vendor Idera and published on MSSQLTips.com, Verbeeck also recommended checking the auto-grow settings in SQL Server to make sure they match the need for space additional file or log when transactions are executed. When a new database is created, he said, the default settings applied to it may not match the needs of your application.

You really need to assess your specific database needs and find out what works best for you.

Koen VerbeeckSenior BI Consultant at Brussels Airport Co.

Verbeeck also recommended allowing databases to “grow in peace”. He advised planning all the data you think you need and then adding an extra year of storage space. This gives you more room and space to expand in case your system accumulates or produces more data than expected. However, creating a larger database will take longer, he said.

Another valuable SQL Server resource that can be used to store temporary tables, indexes, and other valuable SQL Server resources is the tempdb system database. stored procedures and other database objects, but Verbeeck said it shouldn’t be treated as the perfect solution to all SQL Server database design problems. He quoted Brent Ozar, a SQL Server consultant and trainer who, half-jokingly refers to tempdb as “SQL Server public toilet”.

Tempdb requires monitoring, said Verbeeck, and not just for storing temporary objects and files; if a system does not have enough memory, tempdb will automatically compensate for it, which can remove valuable resources from other programs and slow SQL Server execution.

“Don’t turn it into a bottleneck,” he warned, adding that pre-allocating space for tempdb – and making sure it’s big enough to accommodate your workloads – is best. way to avoid performance issues.

Recovery Mode Options for SQL Server

Verbeeck also encouraged database administrators and SQL Server developers to explore the recovery options available as part of the database design process. There are three basic recovery modes for saving transactions and restoring them when needed: full recovery, bulk saved, and simple.

Full recovery is a default setting that can be turned off, if desired. In full recovery mode, everything that is done in a database is logged. It supports point-in-time recovery, which can be useful if you need rollbacks for specific transactions, Verbeeck said. But it can also slow down SQL Server systems, he added, because everything is logged, your inserts and queries will run slower than normal.

For users who do not need a full recovery, bulk recovery is an add-on model in which database operations are logged more minimally; transactions can be recovered until the end of a previous database backup, but not until a specific time. In simple recovery mode, no log backup is taken and existing transaction log files are recycled so that the storage space can be used again.

Finally, Verbeeck said that null or unknown values ​​in databases can create problems with comparison and Boolean logic. “Dummies are evil,” he joked. Humor aside, he strongly advised against using them in SQL Server systems. Although you can declare a column as NOT NULL, Verbeeck said putting a “dummy value” instead is a better approach.

Maria H. Underwood