SQL SERVER – Data Pages in Buffer Pool – Data Stored in Memory Cache

This will drop all the clean buffers so we will be able to start again from there. Now, run the following script and check the execution plan of the query.

Have you ever wondered what types of data are there in your cache? During SQL Server Trainings, I am usually asked if there is any way one can know how much data in a table is stored in the memory cache? The more detailed question I usually get is if there are multiple indexes on table (and used in a query), were the data of the single table stored multiple times in the memory cache or only for a single time?

Here is a query you can run to figure out what kind of data is stored in the cache.

USE AdventureWorks
SELECT COUNT(*) AS cached_pages_count,
name AS BaseTableName, IndexName,
FROM sys.dm_os_buffer_descriptors AS bd
SELECT s_obj.name, s_obj.index_id,
s_obj.allocation_unit_id, s_obj.OBJECT_ID,
i.name IndexName, i.type_desc IndexTypeDesc
index_id ,allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
index_id, allocation_unit_id, OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS s_obj
LEFT JOIN sys.indexes i ON i.index_id = s_obj.index_id
AND i.OBJECT_ID = s_obj.OBJECT_ID ) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY cached_pages_count DESC;

Now let us run the query above and observe the output of the same.

pagecached SQL SERVER   Data Pages in Buffer Pool   Data Stored in Memory Cache

We can see in the above query that there are four columns.

Cached_Pages_Count lists the pages cached in the memory.
BaseTableName lists the original base table from which data pages are cached.
IndexName lists the name of the index from which pages are cached.
IndexTypeDesc lists the type of index.

Now, let us do one more experience here. Please note that you should not run this test on a production server as it can extremely reduce the performance of the database.


This will drop all the clean buffers and we will be able to start again from there. Now run following script and check the execution plan for the same.
USE AdventureWorks
SELECT UnitPrice, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID BETWEEN 1 AND 100

The execution plans contain the usage of two different indexes.

pagecached1 SQL SERVER   Data Pages in Buffer Pool   Data Stored in Memory Cache

Now, let us run the script that checks the pages cached in SQL Server. It will give us the following output.

pagecached2 SQL SERVER   Data Pages in Buffer Pool   Data Stored in Memory Cache

It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.

Let me know what you think of this article. I had a great pleasure while writing this article because I was able to write on this subject, which I like the most. In the next article, we will exactly see what data are cached and those that are not cached, using a few undocumented commands.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – List All the DMV and DMF on Server

“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings.

Answer is very simple:

SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE 'dm_%'

dmvnames SQL SERVER   List All the DMV and DMF on Server

Update: Madhivanan has corrected the script here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Most Expensive Queries Using DMV

The title of this post is what I can express here for this quick blog post. I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries on SQL Server Box.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

You can change the ORDER BY clause to order this table with different parameters. I invite my reader to share their scripts.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Get Latest SQL Query for Sessions – DMV

In recent SQL Training I was asked, how can one figure out what was the last SQL Statement executed in sessions.

The query for this is very simple. It uses two DMVs and created following quick script for the same.

SELECT session_id, TEXT
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

While working with DMVs if you ever find any DMV has column with name sql_handle you can right away join that DMV with another DMV sys.dm_exec_sql_text and can get the text of the SQL statement.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit

During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double the CPU (add the same number of CPU that we have on current system). The same discussion has triggered this quick article.

Here is the DMV which can be used to find out Max Worker Count

SELECT max_workers_count
FROM sys.dm_os_sys_info

Let us run the above query on my system and find the results.

maxworkercount SQL SERVER   Find Max Worker Count using DMV   32 Bit and 64 Bit

As my system is 32 bit and I have two CPU, the Max Worker Count is displayed as 512.

To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:

For x86 (32-bit) upto 4 logical processors  max worker threads = 256
For x86 (32-bit) more than 4 logical processors  max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors  max worker threads = 512
For x64 (64-bit) more than 4 logical processors  max worker threads = 512+ ((# Procs – 4) * 16)

In addition to this, you can configure the Max Worker Thread by using SSMS.

Go to Server Node >> Right Click and Select Property >> Select Process and modify setting under Worker Threads.

maxworkersetting SQL SERVER   Find Max Worker Count using DMV   32 Bit and 64 Bit

According to Book On Line, the default Worker Thread settings are appropriate for most of the systems.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action

I just received an email from one of the readers asking for help with error he encountered while attempting to run DMV.

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.


The above error is usually generated when the user who is trying to run the DMV does not have access to the run the DMV. I suggested him to contact his server admin to grant him VIEW SERVER STATE permissions so that he can run the DMV.


If user does not have VIEW SERVER STATE permissions when he runs any DMV, an error is generated.

dmverror SQL SERVER   DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action

When the following script is run by Admin (please note that the user cannot modify its own property in this case), it will give the necessary rights to the user.


After the above script is run on the same DMV, it should not show any error.

dmverror1 SQL SERVER   DMV Error: FIX: Error: Msg 297, Level 16 The user does not have permission to perform this action

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Recently Executed T-SQL Query

About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query.  Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC INPUTBUFFER.

The Short Answer in is both of them will be deprecated.

Please refer to following update query to recently executed T-SQL query on database.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs  that are associated with Transactions.

1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.

2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.

Now, what really interests me is the following observation. These two DMVs , in actual fact, display the distinction between active transactions and current transactions. Current transaction can be active transaction at the time of execution, but not all active transactions are current transactions for current transactions DMV.

Let us take a look at an example where first we will run both these DMVs together in a single transaction and then run them separately.   We will notice that in the former case (i.e., when they are run in a single transaction) both of them give same transaction ID in result. While in the latter case (i.e., when they are run separately) they give different transaction IDs in result.

SELECT * FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
/* Begin Transation */
* FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
/* End Transation */

Let us see the following result image and observe the behaviour we discussed above.

tranandid SQL SERVER   Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

To obtain same transaction ID, both the DMVs should be between BEGIN TRANSACTION and END TRANSACTION. Same transaction ID is preserved between BEGIN and END clause of transaction.

Having said all that, now I am eager to receive answer to this question from my blog readers – In what circumstances do you think this behaviour can be useful?

If possible, please write an article on a real life scenario and send it to me.  I will be vary happy to publish it on this blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Find If Index is Being Used in Database

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.

We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.

USE AdventureWorks
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
WHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');

Now let us run following two SELECT statement which will utilize Indexes on table Employee.

USE AdventureWorks
FROM HumanResources.Employee WITH (INDEX = 1)
WHERE EmployeeID = 1
FROM HumanResources.Employee WITH (INDEX = 2)
WHERE LoginID = 'adventure-works\guy1'

Note : WITH (INDEX  = Number) is not required but I have used it to make sure that first query uses Index 1 and second query uses Index 2. Both the query will return the same result. Now once again we will run our initial query  getting data from sys.dm_db_index_usage_stats and compare our result with initial data.

indexused SQL SERVER   2008   Find If Index is Being Used in Database

(Click on image to see larger image)

It is clear from comparing both the result set that when running query on tables it updates sys.dm_db_index_usage_stats and increment column user_seeks.

Above whole process explains that any index usage is stored in the sys.dm_db_index_usage_stats. DMV sys.dm_db_index_usage_stats stores all the usage since SQL Server is restarted. Once SQL Server service is restarted sys.dm_db_index_usage_stats is reset to zero but over the period of the time it updates the values in the columns. If we run our initial query without WHERE condition we can get many rows which contains IndexName and their usage. That will give us idea how many indexes are heavily used. If using WHERE condition we do not find our index in the table it is clear indication that Index is not used much.

If SQL Server services are not restarted in reasonable amount of time and if any index usage is not found, the index should be dropped. Again, make sure you have test your performance after dropping the index. If it gets worst put that index back and continue exercise.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one particular database. For SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command. DBCC SHOWCONTIG has some limitation for SQL Server 2000.

SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database.
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test_contig'), NULL, NULL , 'DETAILED')

Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.

i.name AS IndexName,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

The results will help DBA to make necessary reports.

Reference : Pinal Dave (http://blog.SQLAuthority.com)