Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

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.

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

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.

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

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.