SQL Server performance tuning Tips
Monitor SQL Server Performance and Activity
select @@connections as 'Total Login Attempts'
-- Returns the number of connections or attempted connections
select @@cpu_busy as 'CPU Busy', getdate() as 'Since'
-- Returns CPU processing time in milliseconds for SQL Server activity
select @@idle as 'Idle Time', getdate() as 'Since'
-- Returns SQL Server idle time in milliseconds
select @@io_busy as 'IO Time', getdate() as 'Since'
-- Returns I/O processing time in milliseconds
select @@pack_received as 'Packets Received'
-- Returns the number of input packets read from the network by SQL Server
select @@pack_sent as 'Packets Sent'
-- Returns the number of output packets written to the network by SQL Server
select @@packet_errors as 'Packet Errors'
-- Returns the number of network packet errors for SQL Server connections
select @@timeticks as 'Clock Ticks'
-- Returns the number of microseconds per CPU clock tick
select @@total_errors as 'Total Errors', getdate() as 'Since'
-- Returns the number of disk read/write errors encountered by SQL Server
select @@total_read as 'Reads', getdate() as 'Since'
-- Returns the number of disk reads by SQL Server
select @@total_write as 'Writes', getdate() as 'Since'
-- Returns the number of disk writes by SQL Server
select * from fn_virtualfilestats(null,null)
-- Returns input/output statistics for data and log files
-- Returns the number of connections or attempted connections
select @@cpu_busy as 'CPU Busy', getdate() as 'Since'
-- Returns CPU processing time in milliseconds for SQL Server activity
select @@idle as 'Idle Time', getdate() as 'Since'
-- Returns SQL Server idle time in milliseconds
select @@io_busy as 'IO Time', getdate() as 'Since'
-- Returns I/O processing time in milliseconds
select @@pack_received as 'Packets Received'
-- Returns the number of input packets read from the network by SQL Server
select @@pack_sent as 'Packets Sent'
-- Returns the number of output packets written to the network by SQL Server
select @@packet_errors as 'Packet Errors'
-- Returns the number of network packet errors for SQL Server connections
select @@timeticks as 'Clock Ticks'
-- Returns the number of microseconds per CPU clock tick
select @@total_errors as 'Total Errors', getdate() as 'Since'
-- Returns the number of disk read/write errors encountered by SQL Server
select @@total_read as 'Reads', getdate() as 'Since'
-- Returns the number of disk reads by SQL Server
select @@total_write as 'Writes', getdate() as 'Since'
-- Returns the number of disk writes by SQL Server
select * from fn_virtualfilestats(null,null)
-- Returns input/output statistics for data and log files
Rebuilding all indexes on a table and specifying options
If you are planning rebuild index to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index. But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)
-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);GO
UPDATE STATISTICS (Production.Product)
GO
(OR)
Alternatively you can use below options
-- By default STATISTICS_NORECOMPUTE = OFF
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF);
-- For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product] REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)
Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx
-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);GO
UPDATE STATISTICS (Production.Product)
GO
(OR)
Alternatively you can use below options
-- By default STATISTICS_NORECOMPUTE = OFF
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF);
-- For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product] REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)
Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx
How to remove Key Lookup on your query plan
Key Lookup was Introduced in SQL Server 2005 Service Pack 2, the Key Lookup operator is a bookmark lookup on a table with a clustered index. The Argument column(Predicate) contains the name of the clustered index and the clustering key used to look up the row in the clustered index. Key Lookup is always accompanied by a Nested Loops operator. Query performance can be improved by adding a covering index on nonclustered index.
When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.
use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]
--Find list of index currently on table [Sales.SalesOrderDetail]
index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID
When you found a key lookup for a query execution plan window. The easiest way to remove key lookup from the execution plan is to add covering index for the specific column into that non-clustered in the table.
use AdventureWorks
go
sp_helpindex [Sales.SalesOrderDetail]
--Find list of index currently on table [Sales.SalesOrderDetail]
index_name index_description index_keys
AK_SalesOrderDetail_rowguid nonclustered, unique located on PRIMARY rowguid
IX_SalesOrderDetail_ProductID nonclustered located on PRIMARY ProductID
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID clustered, unique, primary key located on PRIMARY SalesOrderID, SalesOrderDetailID
-- Run the this two queries with display estimated execution plan
Select ProductID
From Sales.SalesOrderDetail
Where ProductID = 776
go
select ProductID, OrderQty
from Sales.SalesOrderDetail
where ProductID =776
go
As you can see Key Lookup operator for second query. How would you simply remove from the query plan.? Add an QrderQty column into INCLUDE on non clustered Index idx_SalesOrderDetail.
As you can see the Key Lookup has been removed from display execution plan
Source : Craig Freedman thoughts from his blog
Bookmark lookup is not a cheap operation. Assuming (as is commonly the case) that there is no correlation between the non-clustered and clustered index keys, each bookmark lookup performs a random I/O into the clustered index. Random I/Os are very expensive. When comparing various plan alternatives including scans, seeks, and seeks with bookmark lookups, the optimizer must decide whether it is cheaper to perform more sequential I/Os and touch more rows using an index scan or a seek with a less selective predicate that covers all required columns or to perform fewer random I/Os and touch fewer rows using a seek with a more selective predicate and a bookmark lookup.Partitioning existing table SQL2008R2
Create partioning for existing table with more than 1120962253 rows today. It was amazing to see the query performance.
Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different driveNCINDEX_FG5
--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])
--Step 3. CREATE CLUSTERED INDEX 1
Make sure to create a files groups ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4] for partioned table
Also you can create Separate file group for Non-Clustered Index on different driveNCINDEX_FG5
USE [TestDB]
GO
GO
--Step 1. Creating a Partition Function
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
CREATE PARTITION FUNCTION PFSvrId_Left (numeric (10,0))
AS RANGE LEFT FOR VALUES (399, 499, 699, 799);
--The result for this RANGE LEFT assignment is:
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}
--{min … 399}, {400 … 499}, {500 … 699}, {799 … max}
--Step 2. Creating a Partition Scheme
CREATE PARTITION SCHEME SvrIdScheme
AS PARTITION PFSvrId_Left
TO ([PARTITION_FG1], [PARTITION_FG2],[PARTITION_FG3],[PARTITION_FG4],[PRIMARY])
--Step 3. CREATE CLUSTERED INDEX 1
-- Now create a Partitioned using clustered index based Scheme
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)
CREATE CLUSTERED INDEX [idx_LoadID] ON [dbo].[MyTable]
(
[M_ID] ASC
) ON SvrIdScheme(s_id)
-- Step 4. CREATE NONCLUSTERED INDEX 2 on Separate file group
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO
--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
( [t_id] ASC, [s_id] ASC
)ON [NCINDEX_FG5]
GO
--– Check for new partitions
SELECT partition_id, object_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('MyTable')
GO
How to detect a Torn Page
SQL Server maintains suspect page information in a msdb database new system table : suspect_pages.When the database engine reads a database page containing a CHECKSUM or TORN PAGE (error 824), the page is considered suspect and Page ID number is recorded in the suspect_pages table.
SELECT db_name(database_id) DatabaseName , file_id , page_id , last_update_date
FROM msdb.dbo.suspect_pages
WHERE event_type=3
Torn pages can also be detected by reviewing the output from a DBCC CHECKDB command.
SELECT db_name(database_id) DatabaseName , file_id , page_id , last_update_date
FROM msdb.dbo.suspect_pages
WHERE event_type=3
Torn pages can also be detected by reviewing the output from a DBCC CHECKDB command.
How to check AUTO_CREATE_STATISTICS is enabled for database
use master
go
SELECT name AS 'Name',
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
-- Also you can check list of statistics created on your database
use Mydb
go
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
When updating statistics with UPDATE STATISTICS or sp_updatestats, Microsoft recommend keeping
AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.
go
SELECT name AS 'Name',
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
-- Also you can check list of statistics created on your database
use Mydb
go
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
When updating statistics with UPDATE STATISTICS or sp_updatestats, Microsoft recommend keeping
AUTO_UPDATE_STATISTICS set to ON so that the query optimizer continues to routinely update statistics.
Find Page Life Expectancy (PLE) value for default SQL Server instance
SELECT cntr_value AS [Page Life Expectancy], @@servername AS Server
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND [object_name] = 'SQLServer:Buffer Manager'
Note: If you get cntr_value is less than 300 then, that means
An average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure or missing indexes, or a cache flush.
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND [object_name] = 'SQLServer:Buffer Manager'
Note: If you get cntr_value is less than 300 then, that means
An average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure or missing indexes, or a cache flush.
Advanced Troubleshooting with Extended Events
Paul S.Randal has written wonderful article on Technet about Troubleshooting SQL Server using extended events in SQL Server 2008.
- Why troubleshooting is necessary
- Troubleshooting tools in SQL Server
- Overview and architecture of Extended Events
- Using Extended Events
Set Max memory on SQL Server
Always set max server memory option to maximum physical memory of the server
exec sp_configure 'min server memory (MB)', 500
go
-- Error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'min server memory (MB)' does not exist, or it may be an advanced option.
-- Solution
exec sp_configure 'show advanced options', 1
go
RECONFIGURE
go
exec sp_configure 'min server memory (MB)' ,500
go
exec sp_configure 'max server memory (MB)' , 'your server physical memory'
go
exec sp_configure 'min server memory (MB)', 500
go
-- Error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'min server memory (MB)' does not exist, or it may be an advanced option.
-- Solution
exec sp_configure 'show advanced options', 1
go
RECONFIGURE
go
exec sp_configure 'min server memory (MB)' ,500
go
exec sp_configure 'max server memory (MB)' , 'your server physical memory'
go
Fill Factor option for when Create Index or Rebuild Index
When creating a index or rebuilt, The FILL-FACTOR value determines the percentage of space on each leaf-level page to be filled with data. It is important to set the fill-factor value for each index. In practice a fill-factor value is set to 80 means that 20 percent of each level-level page will be left empty.
USE AdventureWorks2008;
GO
CREATE NONCLUSTERED INDEX IDX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON);
GO
How to find the Index with Fragmentation issue
SELECT db.name AS databaseName , ps.OBJECT_ID AS objectID
, ps.index_id AS indexID, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0
AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1)
, ps.index_id AS indexID, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0
AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1)
How to Enable the Lock Pages in Memory Option on Windows Server
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not normally required on 64-bit operating systems. You will need to enable this right on 64-bit operating systems only when using Large Page Memory support or to configure SQL Server such that the Buffer Pool memory does not get paged out. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.
To enable the lock pages in memory option
--------------------------------------------------------------------------------
1.On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5.In the pane, double-click Lock pages in memory.
6.In the Local Security Policy Setting dialog box, click Add.
7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
To enable the lock pages in memory option
--------------------------------------------------------------------------------
1.On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5.In the pane, double-click Lock pages in memory.
6.In the Local Security Policy Setting dialog box, click Add.
7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.