Now, let’s execute the following commands to display the data and cache information for the table we created and that is now stored in SQL Server’s syscacheobjects system table.
SELECT EmpId, EmpName FROM DummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.syscacheobjects
GO
The result will display many columns, but we are only interested in four of them, as shown below.
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan |
1 |
1 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Compiled Plan |
2 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Here’s what the information displayed means:
Cacheobjtype: The type of object stored in the cache, which can include:
- Compiled Plan
- Executable Plan
- Parse Tree
- Cursor Parse Tree
- Extended Stored Procedure
We will be concentrating mainly on the Compiled Plan and the Executable Plan cacheobjtype type objects.
Refcounts: Number of other cache objects referencing this cache object. A count of 1 is the base.
Usecounts: Number of times this cache object has been used since inception.
Sql: Text of the statement.
Now, let’s execute the same SELECT statement:
SELECT EmpId, EmpName FROM DummyTable1
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan |
1 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Compiled Plan |
2 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the SELECT statement and incremented the Usecounts of the executable plan. N number user will use the same compiled plan when we execute the same SELECT statement.
Now, let us add a ‘WHERE’ clause on the SELECT statement and see the result from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
SQL Server has not used the existing cache plan because of the change in the SELECT statement. SQL Server will generate a new cache plan for the SELECT statement along with old cache plan.
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan |
1 |
1 |
(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1 |
Compiled Plan |
2 |
2 |
(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1 |
Executable Plan |
1 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Compiled Plan |
2 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Let us execute the same SELECT statement with a different empid and verify the result.
SELECT EmpId, EmpName FROM DummyTable1 WHERE EmpId = 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Cacheobjtype | Refcounts | Usecounts | Sql |
Executable Plan |
1 |
2 |
(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1 |
Compiled Plan |
2 |
2 |
(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] WHERE [EmpId]=@1 |
Executable Plan |
1 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Compiled Plan |
2 |
2 |
()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1] |
Though we have given different empid values, SQL Server has used the same compiled plan for the SELECT statement, and incremented the Usecounts of the executable plan.