Hi Guys, in this post, we will be discussing my top 5 tips on how to keep your SQL Database performing. The points that are discussed in this article aren’t the only tips you should know and implement on your database. They are just my top picks from the list of many dos that are available.
The main objective of our database is to safely store and retrieve our data. Keeping our data safe is highly important and another important factor is the speed at which the data is been retrieved. This brings us to our first point.
5 free tips on how to keep SQL Server Database performing
In Summary, an index is a data structure that improves the data retrieval operation speed on a database. Indexes are used to quickly locate data without having to search every row in a table when accessed.
To understand how indexes work, think of searching for a particular topic in a huge book without consulting the table of contents or the index at the back of the book. To find what you’re looking for, you would have to scan through the entire book. This would be time consuming and inefficient. But if you had consulted the index in the book, it would tell you exactly which page to look on to find what you’re looking for. According to Microsoft’s here are the 12 index types that are available in SQL Server:
|Hash||With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.|
|memory-optimized Nonclustered||For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns|
|Clustered||A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.|
|Nonclustered||A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.|
|Unique||A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.|
Uniqueness can be a property of both clustered and nonclustered indexes.
|Columnstore||An in-memory columnstore index stores and manages data by using column-based data storage and column-based query processing.|
Columnstore indexes work well for data warehousing workloads that primarily perform bulk loads and read-only queries. Use the columnstore index to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.
|Index with included columns||A nonclustered index that is extended to include nonkey columns in addition to the key columns.|
|Index on computed columns||An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.|
|Filtered||An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.|
|Spatial||A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.|
|XML||A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.|
|Full-text||A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.|
The example below demonstrates how to create a columnstore index in SQL Server. The same general approach goes for other index types.
Drop unused indexes
While having indexes on our database tables is good, having too much can cause performance issues. Each time an index is created it takes up disk space, so having multiple indexes especially unused indexes will take up much-needed space, it also increases the workload for INSERT, UPDATE and DELETE statements as they have to keep the indexes updated as well as the base table. Below is a query that you can execute to view statistics on your least used indexes.
SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC GO
Partitioning is the process of dividing very large tables into smaller manageable segments on a partitioning column. This will in no doubt keep your database performing. An example of this is splitting up the data on a table into monthly segments using the date column of the table as a partition column.
Let’s say a table has 1 year’s worth of data (from January 1, 2019, to December 31, 2019), if we were to partition the data on a monthly bases, we would have at least 12 segments of data, one for every month of the year. Base on the column we portioned the data on (data column), the data will fall in a partition range that corresponds to the date for that particular row. A few benefits or using partitions are:
- subsets of Data can be transferred or accessed quickly and more efficiently. e.g. Select * from table where date = ‘FEB 14, 2019’. Seeing that our table is partitioned on a monthly partition column, the query above will only check the partition that meets the date criteria specified in the where clause.
- Maintenance of partition can be done more quickly compared to doing maintenance on an entire table.
Keep Data and Log Files Separate
When designing or installing a new database it is always important to remember to keep log files (.ldf) and data files (.mdf) on separate drives. By doing this we reduce contention for that device, which will in turn result in poor performance. This is recommended for production environments, especially if the database has a high I/O. By placing the files on separate drives, I/O activities are allowed to occur at the same time for both data and log files.
Enable Query Store
As of SQL Server 2016, SQL Server now has a new feature that helps to monitor query performance called Query Store. Once turned on, you can think of Query Store as being the NSA(collecting info) of the database, capturing and keeping a history of executed queries, runtime execution, execution plans, etc. Below is a list of common scenarios where the SQL Server Query Store features can be helpful if enabled on your database.
- Find the most expensive queries for CPU, I/O, Memory etc.
- Get the full history of query executions
- Get information about query regressions (a new execution plan generated by the query engine is worse than the older one). Quickly find performance regression and fixing it by forcing the previous query plan whose performances are much better than a newly generated plan
- Determine how many times a query was executed in the given range of time
Enabling Query store is pretty easy. Please see the Animated GIF below on how to do so.
Even though we listed 5 free tips on how to keep SQL Server Database performing, there are many more tips and tricks that you can implement to make your database perform optimally.