In previous post we have seen why performance is important for any organization and how it related with fame and name in market.Now lets start our technical discussion from this post onwards.
Before start performance tuning concept we need to know some thing about Buffer Pool.
SQL Server buffer pool , also called an SQL Server cache ,is a place in system memory that is used for table and index data pages as they are modified or read from disk also it contains execution plane .As this is basic post I will try to make as simple as I can do.So when we are doing performance testing we must clear buffer cache.One of my friend ask me what will happen if we will not clear buffer pool.Answers was " It can mislead your testing as data (pages) are in buffer so it will not fetch from disk and it will choose execution plan from cache so you will see performance but it is not ".
He asked me again
How will you clear buffer pool i,e Index/Data pages and execution plan from memory?
Ans -->
It is very simple , Sql server has DBCC command ,We will use following command
DBCC dropcleanbuffers
Note : This command is very useful it will help us to do our performance testing without shut-down or restarting server.
If your more intrested to know what is there in my buffer at that moment and if you want see it .
You can use following DMV which will give all detail .
select * From sys.dm_os_buffer_descriptors
If you run above command you will lose your data /index pages and execution plan from buffer pool.That what we want .Now you are ready to start your performance testing which will give you accurate result on test and production if it is clone.
There is one more command ideally used before clearing cache its CheckPoint
CHECKPOINT :
Writes all dirty pages for the current database to disk.Dirty pages are data pages that have been entered into the buffer cache and modified ,but not yet written to disk.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
Checkpoint
Go
DBCC DropCleanBuffers
Please use above above command before comparing stats ie. before and after.Next post we will start actual performance tuning.Please comment if you like this post.
Before start performance tuning concept we need to know some thing about Buffer Pool.
SQL Server buffer pool , also called an SQL Server cache ,is a place in system memory that is used for table and index data pages as they are modified or read from disk also it contains execution plane .As this is basic post I will try to make as simple as I can do.So when we are doing performance testing we must clear buffer cache.One of my friend ask me what will happen if we will not clear buffer pool.Answers was " It can mislead your testing as data (pages) are in buffer so it will not fetch from disk and it will choose execution plan from cache so you will see performance but it is not ".
He asked me again
How will you clear buffer pool i,e Index/Data pages and execution plan from memory?
Ans -->
It is very simple , Sql server has DBCC command ,We will use following command
DBCC dropcleanbuffers
Note : This command is very useful it will help us to do our performance testing without shut-down or restarting server.
If your more intrested to know what is there in my buffer at that moment and if you want see it .
You can use following DMV which will give all detail .
select * From sys.dm_os_buffer_descriptors
If you run above command you will lose your data /index pages and execution plan from buffer pool.That what we want .Now you are ready to start your performance testing which will give you accurate result on test and production if it is clone.
There is one more command ideally used before clearing cache its CheckPoint
CHECKPOINT :
Writes all dirty pages for the current database to disk.Dirty pages are data pages that have been entered into the buffer cache and modified ,but not yet written to disk.Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
Checkpoint
Go
DBCC DropCleanBuffers
Please use above above command before comparing stats ie. before and after.Next post we will start actual performance tuning.Please comment if you like this post.
No comments:
Post a Comment