Performance Tuning, Monitoring and Troubleshooting
* As part of performance tuning we have to analyze and work with
* Physical I/O and Logical I/O
* CPU usage
* Memory usage
* Database Design
* Application's db programming methods
Query Architecture
* Once the query is submitted to Database Engine for first time it performs the following tasks.
* Parsing (Compiling)
* Resolving (Verifying syntax, table, col names etc)
* Optimizing (Generating execution plan)
* Executing (Executing query)
* For next time if the query was executed with same case and same no of characters i.e with no extra spaces then the query is executed by taking existing plan.
* To display cached plans
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
st.text AS SQLBatch,qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
* To remove plans from cache memory
DBCC FREEPROCCACHE
Execution Plan
* Step by step process followed by SS to execute a query is called execution plan.
* It is prepared by Query Optimizer using STATISTICS.
* Query optimizer prepares execution plan and stores in Procedurec Cache.
* Execution plans are different for
* Different case statements
* Different size statements (spaces.)
* To view graphical execution plan
* select the query --> press ctrl+M/L
* To view xml execution plan
* set showplan_xml on/off
* Execute the query
* To view text based execution plan
* set showplan_text on/off
* Execute the query.
Statistics
* Consists of meta data of the table or index.
* If statistics are out of date, query optimizer may prepare poor plan.
* We have to update statistics weekly with maintenance plan.
USE master
GO
-- Enable Auto Update of Statistics
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON;
GO
-- Update Statistics for whole database
EXEC sp_updatestats
GO
-- Get List of All the Statistics of Employee table
sp_helpstats 'HumanResources.Employee', 'ALL'
GO
-- Get List of statistics of AK_Employee_NationalIDNumber index
DBCC SHOW_STATISTICS ("HumanResources.Employee",AK_Employee_NationalIDNumber)
-- Update Statistics for single table
UPDATE STATISTICS HumanResources.Employee
GO
-- Update Statistics for single index on single table
UPDATE STATISTICS HumanResources.Employee AK_Employee_NationalIDNumber
GO
Index
* It is another database objects which can be used
* To reduce searching process
* To enforce uniqueness
* By default SS search for the rows by following the process called table scan.
* If the table consists of huge data then table scan provides less performance.
* Index is created in tree-like structure which consists of root,node and leaf level.
* At leaf level, index pages are present by default.
* We can place max 250 indexes per table.
* Indexes are automatically placed if we place
* Primary key (clustered)
* Unique (unique non clustered index)
* We can place indexes as follows
create [unique][clustered/nonclustered] index <indexName> on <tname>/<viewName>(col1,col2,....)
[include(.....)]
Types
-------
* Clustered
* NonClustered
1. Clustered Index-----------------------
* It physically sorts the rows in the table.
* A table can have only ONE clustered index.
* Both data and index pages are merged and stored at third level (Leaf level).
* We can place on columns which are used to search a range of rows,
Ex:
Create table prods(pid int,pname varchar(40), qty int)
insert prods values(4,'Books',50),(2,'Pens',400)
select * from prods (run the query by pressing ctrl+L)
create clustered index pid_indx on prods(pid)
select * from prods -- check the rows are sorted in asc order to pid
FAQ:- Difference between P.K and Clustered Index?
* Primary key enforce uniqueness and allows to eshtablish relationship. But by default clustered index cannot.
select * from prods where pid=2 -- press ctrl+L to check execution plan
insert prods values(3,'Pencils',500) -- Check this row is inserted as second record.
Note: A table without clustered index is called HEAP where the rows and pages of the table are not present in any order.
NonClustered Index-----------------------
* It cannot sort the rows physically.
* We can place max 249 nonclustered indexes on table.
* Both data and index pages are stored seperately.
* It locates rows either from heap (Table scan) or from clustered index.
* Always we have to place first clustered index then nonclustered.
* If the table is heap the index page consists of
IndexKeyColvalues rowreference
* If the table consists of clustered index then index page consists of
IndexKeyColValues Clusteredindexkeycolvalues
* Nonclustered indexes are rebuilded when
* Clustered index is created/droped/modified
Ex: Create nonclustered index on pname column of prods table.
create index indx1 on prods(pname)
select * from prods where pname='Books' -- check execution plan
* To disp indexes present on a table
sp_helpindex <tname>
* To drop index
drop index prods.pid_indx
* To disp space used by the index
sp_spaceused prods
Using Included Columns in NonClustered Index--------------------------------------------------------
* We can maintain regularly used columns in nonclustered index so that no need that SQL Server should take data from heap or clustered index.
* If the no of rows are more it provides better performance.
Ex:
--step1
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO
--step2
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';
GO
Index Management
FillFactor------------
* Percentage of space used in leaf level index pages.
* By default it is 100%.
* To reduce page splits when the data is manipulated in the base table we can set proper FillFactor.
* It allows online index processing
* While the index rebuilding process is going on users can work with the table.
Page Split------------
* Due to regular changes in the table if the index pages are full to allocate memory for the index key columns SS takes remaining rows into new page. This process is called Page split.
* Page split increases size of index and the index pages order changes.
* This situation where unused free space is available and the index pages are not in the order of key column values is called fragmentation.
* To find fragmentation level we can use
dbcc showcontig
or
We can use sys.dm_db_index_physical_stats DMF as follows
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'),
OBJECT_ID('Production.Product'), NULL, NULL, NULL)
AS a JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id =b.index_id;
* To control fragmentation we can either reorganize the index or rebuild the index.
1. Reorganizing Index * It is the process of arranging the index pages according to the order of index key column values.
* If the fragmentation level is more than 5 to 8% and less than 28to 30% then we can reorganize the indexes.
* It cannot reduce the index size as well as statistics are not updated.
syn:
ALTER INDEX <indexName>/<All> on <tname> REORGANIZE
2. Index Rebuilding * It is the process of deleting and creating fresh index.
* It reduces the size of index and updates statistics
* If the fragmentation level is more than 30% then we can rebuild indexes.
syn:
ALTER INDEX <indexName>/<ALL> on <tname> REBUILD
Note:
If we have mentioned ONLINE INDEX PROCESSING option then rebuilding takes space in TEMPDB.
To check consistancy of a database we can use DBCC CHECKDB('dbName') it disp if any corrupted pages are present, use space in tempdb.
Transactions and Locks
----------------------------
* A transaction is single unit of work which may consists of one or more commands.
* Transactions works with ACID properties
* Automicity
* Consistancy
* Isolation
* Durability
* SQL Server supports 2 types of transactions
* Implicit
* Explicit
* By default SS supports implicit transaction where for every insert, update and delete 3 records are stored in T.Log file
Begin tran
insert/update/delete
commit tran
* To implement business logic i.e. according to the required if we want to commit or rollback the changes we can use explicit transactions.
Begin Tran
----
commit/rollback tran
* Any transaction which consists of manipulations places locks on the tables.
* By default when we make a db as current db automatically Shared Lock is placed.
* While working with insert,update,delete by default SS places Exclusive lock.
* Type of locks placed on objects depends on isolation levels.
Isolation Levels
-------------------
* It is a transaction property.
* Types of locks placed by SS on the resource depends on isolation levels.
* SS supports 5 isolation levels
* Read Committed (Default)
* Read Uncommitted
* Repeatable Reads
* Snapshot
* Serializable
* To check the isolation level
dbcc useroptions
* To set the isolation level
SET TRANSACTION ISOLATION LEVEL <requiredisolationlevel>
* To handle the concurrency related problems SS places locks
* SS supports 2 types of concurrencies
* Optimistic Concurrency
* Uses Shared Locks
* More concurrency
* Pessimistic Concurrency
* Uses Exclusive Locks
* Low concurrency
Ex: Open new query window
--user1
use Test
go
begin tran
update emp set sal=5000
Take new query -->
--user2
use Test
go
select * from emp (--query runs continuesly till user1 session releases lock)
Take new query
--user3
set transaction isolation level read uncommitted
select * from emp
--Take new query
sp_lock -- To view locks information
or
select * from sys.dm_tran_locks
--check blocking using
sp_who/sp_who2
-- To check locks placed by a particular session
sp_lock <spid>
sp_lock 56
* As part of performance tuning we have to analyze and work with
* Physical I/O and Logical I/O
* CPU usage
* Memory usage
* Database Design
* Application's db programming methods
Query Architecture
* Once the query is submitted to Database Engine for first time it performs the following tasks.
* Parsing (Compiling)
* Resolving (Verifying syntax, table, col names etc)
* Optimizing (Generating execution plan)
* Executing (Executing query)
* For next time if the query was executed with same case and same no of characters i.e with no extra spaces then the query is executed by taking existing plan.
* To display cached plans
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
st.text AS SQLBatch,qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;
GO
* To remove plans from cache memory
DBCC FREEPROCCACHE
Execution Plan
* Step by step process followed by SS to execute a query is called execution plan.
* It is prepared by Query Optimizer using STATISTICS.
* Query optimizer prepares execution plan and stores in Procedurec Cache.
* Execution plans are different for
* Different case statements
* Different size statements (spaces.)
* To view graphical execution plan
* select the query --> press ctrl+M/L
* To view xml execution plan
* set showplan_xml on/off
* Execute the query
* To view text based execution plan
* set showplan_text on/off
* Execute the query.
Statistics
* Consists of meta data of the table or index.
* If statistics are out of date, query optimizer may prepare poor plan.
* We have to update statistics weekly with maintenance plan.
USE master
GO
-- Enable Auto Update of Statistics
ALTER DATABASE AdventureWorks SET AUTO_UPDATE_STATISTICS ON;
GO
-- Update Statistics for whole database
EXEC sp_updatestats
GO
-- Get List of All the Statistics of Employee table
sp_helpstats 'HumanResources.Employee', 'ALL'
GO
-- Get List of statistics of AK_Employee_NationalIDNumber index
DBCC SHOW_STATISTICS ("HumanResources.Employee",AK_Employee_NationalIDNumber)
-- Update Statistics for single table
UPDATE STATISTICS HumanResources.Employee
GO
-- Update Statistics for single index on single table
UPDATE STATISTICS HumanResources.Employee AK_Employee_NationalIDNumber
GO
Index
* It is another database objects which can be used
* To reduce searching process
* To enforce uniqueness
* By default SS search for the rows by following the process called table scan.
* If the table consists of huge data then table scan provides less performance.
* Index is created in tree-like structure which consists of root,node and leaf level.
* At leaf level, index pages are present by default.
* We can place max 250 indexes per table.
* Indexes are automatically placed if we place
* Primary key (clustered)
* Unique (unique non clustered index)
* We can place indexes as follows
create [unique][clustered/nonclustered] index <indexName> on <tname>/<viewName>(col1,col2,....)
[include(.....)]
Types
-------
* Clustered
* NonClustered
1. Clustered Index-----------------------
* It physically sorts the rows in the table.
* A table can have only ONE clustered index.
* Both data and index pages are merged and stored at third level (Leaf level).
* We can place on columns which are used to search a range of rows,
Ex:
Create table prods(pid int,pname varchar(40), qty int)
insert prods values(4,'Books',50),(2,'Pens',400)
select * from prods (run the query by pressing ctrl+L)
create clustered index pid_indx on prods(pid)
select * from prods -- check the rows are sorted in asc order to pid
FAQ:- Difference between P.K and Clustered Index?
* Primary key enforce uniqueness and allows to eshtablish relationship. But by default clustered index cannot.
select * from prods where pid=2 -- press ctrl+L to check execution plan
insert prods values(3,'Pencils',500) -- Check this row is inserted as second record.
Note: A table without clustered index is called HEAP where the rows and pages of the table are not present in any order.
NonClustered Index-----------------------
* It cannot sort the rows physically.
* We can place max 249 nonclustered indexes on table.
* Both data and index pages are stored seperately.
* It locates rows either from heap (Table scan) or from clustered index.
* Always we have to place first clustered index then nonclustered.
* If the table is heap the index page consists of
IndexKeyColvalues rowreference
* If the table consists of clustered index then index page consists of
IndexKeyColValues Clusteredindexkeycolvalues
* Nonclustered indexes are rebuilded when
* Clustered index is created/droped/modified
Ex: Create nonclustered index on pname column of prods table.
create index indx1 on prods(pname)
select * from prods where pname='Books' -- check execution plan
* To disp indexes present on a table
sp_helpindex <tname>
* To drop index
drop index prods.pid_indx
* To disp space used by the index
sp_spaceused prods
Using Included Columns in NonClustered Index--------------------------------------------------------
* We can maintain regularly used columns in nonclustered index so that no need that SQL Server should take data from heap or clustered index.
* If the no of rows are more it provides better performance.
Ex:
--step1
USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID)
GO
--step2
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN '98000'
AND '99999';
GO
Index Management
FillFactor------------
* Percentage of space used in leaf level index pages.
* By default it is 100%.
* To reduce page splits when the data is manipulated in the base table we can set proper FillFactor.
* It allows online index processing
* While the index rebuilding process is going on users can work with the table.
Page Split------------
* Due to regular changes in the table if the index pages are full to allocate memory for the index key columns SS takes remaining rows into new page. This process is called Page split.
* Page split increases size of index and the index pages order changes.
* This situation where unused free space is available and the index pages are not in the order of key column values is called fragmentation.
* To find fragmentation level we can use
dbcc showcontig
or
We can use sys.dm_db_index_physical_stats DMF as follows
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'),
OBJECT_ID('Production.Product'), NULL, NULL, NULL)
AS a JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id =b.index_id;
* To control fragmentation we can either reorganize the index or rebuild the index.
1. Reorganizing Index * It is the process of arranging the index pages according to the order of index key column values.
* If the fragmentation level is more than 5 to 8% and less than 28to 30% then we can reorganize the indexes.
* It cannot reduce the index size as well as statistics are not updated.
syn:
ALTER INDEX <indexName>/<All> on <tname> REORGANIZE
2. Index Rebuilding * It is the process of deleting and creating fresh index.
* It reduces the size of index and updates statistics
* If the fragmentation level is more than 30% then we can rebuild indexes.
syn:
ALTER INDEX <indexName>/<ALL> on <tname> REBUILD
Note:
If we have mentioned ONLINE INDEX PROCESSING option then rebuilding takes space in TEMPDB.
To check consistancy of a database we can use DBCC CHECKDB('dbName') it disp if any corrupted pages are present, use space in tempdb.
Transactions and Locks
----------------------------
* A transaction is single unit of work which may consists of one or more commands.
* Transactions works with ACID properties
* Automicity
* Consistancy
* Isolation
* Durability
* SQL Server supports 2 types of transactions
* Implicit
* Explicit
* By default SS supports implicit transaction where for every insert, update and delete 3 records are stored in T.Log file
Begin tran
insert/update/delete
commit tran
* To implement business logic i.e. according to the required if we want to commit or rollback the changes we can use explicit transactions.
Begin Tran
----
commit/rollback tran
* Any transaction which consists of manipulations places locks on the tables.
* By default when we make a db as current db automatically Shared Lock is placed.
* While working with insert,update,delete by default SS places Exclusive lock.
* Type of locks placed on objects depends on isolation levels.
Isolation Levels
-------------------
* It is a transaction property.
* Types of locks placed by SS on the resource depends on isolation levels.
* SS supports 5 isolation levels
* Read Committed (Default)
* Read Uncommitted
* Repeatable Reads
* Snapshot
* Serializable
* To check the isolation level
dbcc useroptions
* To set the isolation level
SET TRANSACTION ISOLATION LEVEL <requiredisolationlevel>
* To handle the concurrency related problems SS places locks
* SS supports 2 types of concurrencies
* Optimistic Concurrency
* Uses Shared Locks
* More concurrency
* Pessimistic Concurrency
* Uses Exclusive Locks
* Low concurrency
Ex: Open new query window
--user1
use Test
go
begin tran
update emp set sal=5000
Take new query -->
--user2
use Test
go
select * from emp (--query runs continuesly till user1 session releases lock)
Take new query
--user3
set transaction isolation level read uncommitted
select * from emp
--Take new query
sp_lock -- To view locks information
or
select * from sys.dm_tran_locks
--check blocking using
sp_who/sp_who2
-- To check locks placed by a particular session
sp_lock <spid>
sp_lock 56
