Microsoft SQL Server Performance Guide

General Tips

SQL Server Service Permissions

note

Administrator rights are required to configure permissions.

To improve the performance and stability of SQL Server, you must make some settings in the local security policy of the computer running SQL Server. These settings allow the user running SQL Server (usually "Network Service") to obtain certain rights that normally only administrators have. The following steps show how you can change these settings:

1. Open the "Local Security Policy" program.
2. Click on "Local Policies" and open "User Rights Assignment."
3. Search for the policy "Lock pages in memory" and double-click on it.
4. Click "Add User or Group" and enter the name of the user running the SQL Server (usually "Network Service").
5. Click "OK" and close the window.
6. Find the "Perform volume maintenance tasks" policy, double-click it, and repeat steps 4 and 5.
7. Restart the computer for the changes to take effect.

These settings prevent the SQL Server from having to free up disk space when it needs more memory and from having to wait a long time when creating or expanding files. This can significantly improve the performance and stability of the SQL Server.

Table indexes

Clustered index

For optimal performance, it is recommended that each table have a clustered index, preferably a primary clustered index.

A clustered index is a special type of index that determines the order of records in a table. It determines how the data is physically stored on the hard disk. A clustered index is important for database performance because it increases query speed and reduces fragmentation. Each table should have a clustered index, preferably a primary clustered index. A primary clustered index is a clustered index based on the table's primary key. The primary key is a column or combination of columns that uniquely identifies each record in the table. A primary clustered index guarantees that the data in the table is sorted by the primary key, which makes it easier to search, insert, update, and delete data.

Less is more

When optimizing a database, it is advisable to use a balanced number of indexes. Although indexes speed up queries, they also slow down data changes. Each index must be updated with every INSERT, UPDATE, or DELETE, which takes additional time and resources. It is therefore recommended to create only the necessary indexes and to check regularly whether they are still efficient.
You can use the script "Query missing indexes" from the section "Script collection" to identify missing indexes in the database.

Recovery model

The default recovery model for the database should be set to "Full," which means that the transaction log grows continuously. It is strongly recommended to perform regular transaction log backups, typically every 15 to 30 minutes, to prevent the transaction log from growing continuously, and to perform a full backup daily.

tip

The "Full" recovery model allows you to recover data since the last backup and at any point in time using the transaction logs.

warning

However, if you do not need the transaction log or require simple recovery for specific reasons, you can alternatively switch the recovery model to "Simple". Please note that this involves the risk of data loss, as only data included in the last backup can be reliably recovered.

Fully qualified names in views & procedures

Another performance tip for SQL Server is to fully qualify database objects by placing the owner (e.g., dbo) before the table name. This avoids SQL Server having to perform name resolution for each object, which can increase execution time. It can also lead to unexpected results if multiple objects with the same name exist in different schemas. Using fully qualified names ensures that the correct object is always addressed.

Script collection

Here are some useful SQL scripts that you can use for various purposes.

warning

Please note that these scripts may not be suitable for direct use in a production environment and may cause unexpected problems. It is strongly recommended that you test them in a protected environment first before applying them to your data.

Query missing indexes

warning

The script only provides suggestions for optimizing the database. It is important to evaluate the results critically and not accept them blindly. The relevance of the index and the appropriateness of the includes depend on various factors that the script cannot take into account.

-- Missing indexes
select 
 mig.index_group_handle, mid.index_handle, 
 CONVERT (decimal (28,1), 
 migs.avg_total_user_cost * migs.avg_user_impact *
 (migs.user_seeks + migs.user_scans)
 ) as improvement_measure, 
 'CREATE INDEX missing_index_' + CONVERT (varchar,
 mig.index_group_handle) + '_' + CONVERT (varchar,
 mid.index_handle) 
 + ' ON ' + mid.statement 
 + ' (' + ISNULL (mid.equality_columns,'') 
 + case when mid.equality_columns is not null and
 mid.inequality_columns IS NOT NULL then ',' else '' end 
 + ISNULL (mid.inequality_columns, '')
 + ')' 
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') as
 create_index_statement, 
 migs.*, mid.database_id, mid.[object_id]
from sys.dm_db_missing_index_groups mig
inner join sys.dm_db_missing_index_group_stats migs on
 migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details mid on
 mig.index_handle = mid.index_handle
where CONVERT (decimal (28,1), migs.avg_total_user_cost *
 migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
order by migs.avg_total_user_cost * migs.avg_user_impact *
 (migs.user_seeks + migs.user_scans) desc

TOP 20 queries by CPU

-- Top 20 queries by cumulative CPU load within the last hour
select last_execution_time, total_worker_time as [Total CPU
 Time], execution_count,
total_worker_time/execution_count as [Avg CPU Time],
text, qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
where DATEDIFF(hour, last_execution_time, getdate()) < 1 
-- The time frame in hours can be changed here
order by total_worker_time desc

TOP 50 by I/O

-- Top 50 queries by I/O
select top 50
(qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count as average_io, substring
 (qt.text,qs.statement_start_offset/2, (case when
 qs.statement_end_offset = -1 then len(convert(nvarchar(max),
 qt.text)) * 2 else qs.statement_end_offset end -
 qs.statement_start_offset)/2) as query_text, qt.dbid,qt.objectid
 from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text
 (qs.sql_handle) as qt
order by average_io desc

Index defragmentation

To optimize the indexes, a maintenance task (index defrag.sql) should be performed using the script (set variable >>@ViewOnly<< to 0 to start):

-- Specify your database name
USE DatabaseName
GO
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR(128)
DECLARE @execstr VARCHAR(255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @IdxName varchar(128)
DECLARE @ViewOnly bit
-- Set to 1 to display suggested actions, set to 0 to execute them
 proposed actions:
SET @ViewOnly=1
-- Decide how much fragmentation should be allowed at most.
SET @maxfrag = 30.0
-- Declare cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create temporary table.
if exists (select name from tempdb.dbo.sysobjects where name like
 '#fraglist%')
drop table #fraglist
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal)
-- Open cursor.
OPEN tables
-- Iterate through all tables in the database.
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Perform Showcontig on all indexes of the table.
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and release cursor.
CLOSE tables
DEALLOCATE tables
-- Declare cursor for the list of indexes to be defragmented.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open cursor.
OPEN indexes
-- Iterate through indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'Would execute ALTER INDEX ' + RTRIM(@IdxName)
 + ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH
 ( LOB_COMPACTION = ON ) - Current fragmentation ' +
 RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
END
ELSE
BEGIN
PRINT 'Now execute ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' +
 RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) -
 Current fragmentation ' + RTRIM(CONVERT(VARCHAR(15),@frag)) +
 '%'
SELECT @execstr = 'ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' +
 RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC (@execstr)
END
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
END
-- Close and release cursor.
CLOSE indexes
DEALLOCATE indexes
-- Delete temporary table.
DROP TABLE #fraglist
GO