How can We View the Query Plan Cache in SQL Servers
To view the query plan cache’s we will have to use the Dynamic Management Views (DMV’S) which provided real time insights into various aspects of SQL server operations. Follow the below steps to view the query plan cache in SQL Servers:
Step1: Connect to SQL Server Management Studio(SSMS).
Step2: Open a new Query window.
Step3: Execute the following commands
SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY cplan.usecounts DESC
Step4: The usecounts columns represents how many time a query plan was reused.
Step5: We can use the following command to clear the plan cache
DBCC FREEPROCCACHE
Step6: After clearing the plan cache let’s run a simple query multiple times to see how query plan works by reusing the query plan cache.
Select * from Employees Where FirstName = "Steve"
After executing the query for a single time the usecount =1.
Now when we again execute this query the usecount = 2 which indicates that we were able to reuse the same query plan again.
SQL Server Query Plan Cache
In the SQL server, the query plan cache plays an important role in ensuring efficient and optimized query execution. In this article, we are going to dive deep into the basics of query plan cache and its workflow. We will also learn how to view the query plan cache in our SQL Server databases.
Contact Us