Microsoft SQL Server Performance Guide
For optimum performance, it is recommended that each table has 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 specifies how data is physically stored on the hard drive. A clustered index is crucial for database performance as it increases query speed and reduces fragmentation. Every table should have a clustered index, preferably a primary clustered index. A primary clustered index is based on the primary key of the table. The primary key is a column or a combination of columns that uniquely identifies each record in the table. A primary clustered index ensures that data in the table is sorted by the primary key, making it easier to search, insert, update, and delete data. It also prevents duplicate values in the primary key column, enhancing data integrity.
General tips
SQL Server Service Permissions
Administrator rights are required to configure the permissions.
To improve the performance and stability of the SQL Server, you need to make some settings in the local security policy of the computer on which the SQL Server is running. These settings allow the user running the SQL server (usually "network service"/"network service") to have certain rights that normally only administrators have. The following steps show how you can change these settings:
- Open the "Local Security Policy" program.
- Click on "Local Policies" and open "User Rights Assignment".
- Search for the "Lock pages in memory" policy and double-click on it
- Click on "Add User or Group" and enter the name of the user who runs the SQL server (usually "Network Service").
- Click on "OK" and close the window.
- Search for the "Perform volume maintenance tasks" policy, double-click on it and repeat steps 4 and 5.
- Restart the computer for the changes to take effect.
With these settings, you can 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 it creates or extends files. This can significantly improve the performance and stability of the SQL server.
Table indexes
Clustered Index
A clustered index is a special type of index that determines the order of the data records in a table. It determines how the data is physically stored on the hard disk. A clustered index is important for the performance of a database as 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 primary key of the table. 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 according to the primary key, which makes it easier to search, insert, update and delete data.
Less is more
When optimizing databases, 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 up additional time and resources. It is therefore advisable to create only the necessary indices and to check regularly whether they are still efficient.
You can use the script "Query missing indices" from the section "Script collection" to identify missing indices in the database.
Recovery model
The default recovery model for the database should be set to "Full", which means that the transaction log is constantly growing. It is strongly recommended to perform regular transaction log backups, usually every 15 to 30 minutes, to prevent the continuous growth of the transaction log and to create a full backup daily.
The "Full" restore model enables data to be restored since the last backup and at any time using the transaction logs.
However, if you do not need the transaction log or are dependent on a simple restore for certain reasons, you can alternatively change the Recovery model to "Simple ". Please note that this is associated with the risk of data loss, as only data contained in the last backup can be safely restored.
Fully qualified names in views & procedures
Another performance tip for SQL Server is to fully qualify the database objects by placing the owner (e.g. dbo) in front of the table name. This avoids the SQL Server having to perform name resolution for each object, which can increase execution time. It can also lead to unexpected results if several objects with the same name exist in different schemas. The use of 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.
Note that these scripts may not be suitable for direct use in a production environment and may cause unexpected problems. It is strongly recommended to test them first in a protected environment before applying them to your data.
Query missing indexes
The script only provides suggestions for optimizing the database. It is important to evaluate the results critically and not to 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 statements by cumulative CPU within last 1 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
-- change hour time frame
order by total_worker_time desc
TOP 50 by I/O
-- Top 50 statements 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 run using the script (set variable >>@ViewOnly<< to 0 to execute the actions):
-- 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 view proposed actions, set to 0 to Execute
proposed actions:
SET @ViewOnly=1
-- Decide on the maximum fragmentation to allow for.
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 the 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 the cursor.
OPEN tables
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of 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 deallocate the cursor.
CLOSE tables
DEALLOCATE tables
-- Declare the cursor for the list of indexes to be defragged.
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
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag, @IdxName
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ViewOnly=1)
BEGIN
PRINT 'WOULD be executing ALTER INDEX ' + RTRIM(@IdxName)
+ ' ON ' + RTRIM(@tablename) + ' REORGANIZE WITH
( LOB_COMPACTION = ON ) - Fragmentation currently ' +
RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'
END
ELSE
BEGIN
PRINT 'Now executing ALTER INDEX ' + RTRIM(@IdxName) + ' ON ' +
RTRIM(@tablename) + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) -
Fragmentation currently ' + 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 deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table.
DROP TABLE #fraglist
GO