Designing a Database for Optimal Performance

Practice Labs Module
1 hour 10 minutes

The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: Optimize indexing strategies, Design scalable database solutions, Resolve performance problems by using plan guides, Design a table and...

Join over 3 million cybersecurity professionals advancing their career
Sign up with

Already have an account? Sign In »



The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Optimize indexing strategies
  2. Design scalable database solutions
  3. Resolve performance problems by using plan guides
  4. Design a table and index compression strategy
  5. Design a table and index partitioning strategy

Exercise 1 - Optimize indexing strategies

Indexes speed up the querying process by providing swift access to rows in the data tables, similar to the way a book’s index helps you find information quickly within that book. If you create an index on the primary key and then search for a row of data based on one of the primary key values SQL Server first finds that value in the index and then uses the index to quickly locate the entire row of data.

Table-valued function

Inline Table Valued Function

An inline table valued function is essentially a parameterized view. This means that you should have a good idea of at least one of the columns that will be indexed (the parameter). Unlike a stored procedure, the Inline TVF is not susceptible in the same manner of generating a bad plan and sticking with it. This is because the Inline TVF will still expand into the outer query which is using it and optimize the underlying query. However, don’t be blinded by this point. You will still need to ensure that the underlying query is optimized for the filters, joins and returned columns as described.

Multi-Statement Table-Valued Function

Unfortunately, the Multi-Statement Table-Valued Functions are not quite as easy to optimize. Similar to a stored procedure, all of the statements contained in the TVF will need to be optimized independently. However, the table returned will be returned as a Table Variable, with all of the underlying problems (and the bonuses) that this confers.


A view is really just a stored query. As such, it is going to be expanded by the optimizer (providing it is not an indexed view, which we will discuss momentarily) and combined with any other portion of the query which is calling it. As such, there will be times when portions of the view are dropped out of the execution plan or rearranged. However, if you think enough of the query to store it as a view, we will probably want to optimize it just as we would any other query. Look at the join conditions, the filters, the columns returned, the characteristics of the columns used, the frequency of the updates, etc.

Clustered and non-clustered

?lustered indexes

A clustered index stores the actual data rows at theintermediate level or leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order.

Nonclustered indexes

Unlike a clustered indexed, the intermediate nodes or the leaf nodes of a non-clustered index contain only the key pair values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves.

Exercise 2 - Design scalable database solutions

Scale up vs. scale out

When the database that you are currently using begins to hit the limits of operation due to resource constraints, it is time to make more resources available. When doing so, you have the choice of scaling up, meaning that you are buying a bigger single server (or multiple servers if involved in high availability) that will handle the whole load, or scaling out, meaning that you are purchasing many smaller boxes that will share the load. However, this decision might not be quite as simple as it sounds. Scaling out could mean significant changes to the underlying architecture and the application. I think that this is the main reason that most companies choose to instead scale up. It’s simple, you just throw more hardware at the problem, and you’re done. There is no cost to re-architect the application or database and no cost associated with investigating the right solution for scaling out as well as the particular options within those options. We will look in a little more detail at some of the options for scaling out in SQL Server below. However, some methods that aren’t mentioned include using a Service Oriented Architecture (SOA) with Service Broker, and using a data dependent routing mechanism within the application to route the inquiries and updates to the appropriate server (with no underlying relationship between the various servers).

Federated databases

In SQL server, Federated Databases are essentially a large number of servers that collectively, as a single “federation”, are used to process the load (another term for this is a database “shard“). Although each server in the federation is managed independently, it is presented to the applications as a single database server. In these systems, the data is horizontally partitioned. This type of architecture is generally associated with distributed computing and uses distributed partitioned views.

Partitioned views

In some cases it might happen that your hardware is upgraded, your stored procedures are optimised and the application also is scaled up but still you face performance problems. It’s time to implement Distributed Partitioned View which is done by horizontally partitioning large tables over multiple servers so that the load is distributed across two or more servers involved in the partitioning.

Exercise 3 - Resolve performance problems by using plan guides

Plan guides can be used to help optimize queries that are already in production, or that you might not otherwise be able to adjust (such as from a third party vendor or tool). With these guides you can specify optimization hints (which replace any stated optimization hints in the original query) or even a give execution plan.

Note that plan guides should not be overused. It is generally better to use the hint within your code or application if possible. As such, their use should probably be limited to the “critical” queries that are in need of optimization.

Object plan guides

An OBJECT plan guide matches queries that execute in the context of Transact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

Exercise 4 - Design a table and index compression strategy

Row vs. page level

SQL Server 2008 provides two types of compression, Database Backup Compression and Data Compression. In the example below, we will perform data compression. SQL Server 2008 supports Row Level and Page Level Compression for tables and indexes.

Exercise 5 - Design a table and index partitioning strategy


Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.