Microsoft SQL Server Performance Guide
General Tips
SQL Server Service Permissions
Administrator rights are required to configure permissions.
To improve the performance and stability of SQL Server, it may be helpful to grant additional permissions to the service account. This typically refers to the user under which SQL Server runs, such as “Network Service.” The following steps show how to adjust these settings in the Local Security Policy:
- Open the “Local Security Policy” tool.
- Click “Local Policies” and open “User Rights Assignment.”
- Locate the “Lock pages in memory” policy and double-click it.
- Click “Add User or Group” and enter the name of the user running the SQL Server (usually “Network Service”).
- Click “OK” and close the window.
- Locate the policy “Perform volume maintenance tasks,” double-click it, and repeat steps 4 and 5.
- Restart the computer for the changes to take effect.
These settings help prevent unnecessary delays in memory management and file extensions, among other things. This can significantly improve the performance and stability of the SQL Server.
Table Indexes
Clustered Index
For optimal performance, every table should have a clustered index whenever possible, preferably a primary clustered index.
A clustered index determines the physical order of the data records in a table. This can speed up queries and reduce fragmentation. A primary clustered index based on the primary key is particularly useful because it allows search, insert, update, and delete operations to be performed more efficiently.
Less Is More
When optimizing a database, a balanced number of indexes is important. While indexes speed up queries, they simultaneously slow down INSERT, UPDATE, and DELETE operations because they must be updated with every change. Therefore, create only the necessary indexes and regularly check whether they are still useful.
You can identify missing indexes in the database using the Query Missing Indexes script from the Script Collection.
Recovery Model
In many cases, the database’s recovery model should be set to “Full.” In this mode, the transaction log grows continuously, which is why regular transaction log backups are required, typically every 15 to 30 minutes. Additionally, a full backup should be created daily.
The "Full" recovery model allows you to restore data from the last backup as well as at any point in time, e.g., before a crash, using the transaction logs.
If you do not need a transaction log or rely on simple recovery, you can alternatively use the "Simple" recovery model. However, this carries an increased risk of data loss because only data from the last backup can be reliably restored.
Fully Qualified Names in Views and Procedures
Another performance tip is to fully qualify database objects by placing the schema, for example dbo, before the object name. This reduces the effort required for name resolution. It also helps avoid confusion when multiple objects with the same name exist in different schemas.
Script Collection
Here are some useful SQL scripts that you can use for various purposes.
Note that these scripts may not be suitable for direct use in a production environment and could cause unexpected problems. It is strongly recommended that you test them in a protected environment first before applying them to your data.
Query for Missing Indexes
The script only provides suggestions for optimizing the database. It is important to critically evaluate the results 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 over 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 indexes, you can use a maintenance task such as index defrag.sql. To do this, set the variable @ViewOnly to 0 if you want the script to apply the changes.
-- 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
SET @ViewOnly=1
-- Decide what the maximum allowed fragmentation should be.
SET @maxfrag = 30.0
-- Declare a 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 a 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
-- Run 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 the cursor.
CLOSE tables
DEALLOCATE tables
-- Declare a 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 the cursor.
OPEN indexes
-- Iterate through the 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 executing 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 the cursor.
CLOSE indexes
DEALLOCATE indexes
-- Drop the temporary table.
DROP TABLE #fraglist
GO