Overall, stored procedure will compile once and use the same compiled plan and increment the Usecounts value of the executable plan. A stored procedure is pre-loaded in memory for faster execution. It will really augment your system on performance base. This really shows us the importance of the stored procedure as compared with the SELECT statement and views.
Now, let’s execute the following commands to clear the cache before the upcoming view experiment.
DBCC FREEPROCCACHE
GO
Views
We will create a view and how it is differs from theSELECT statement and stored procedures.
CREATE VIEW vwDummyTable1 AS
SELECT EmpID, EmpName FROM DummyTable1
GO
Now, let’s execute the following commands to display the data and cache information for the vwDummyTable1 we created.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
1 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
| Compiled Plan |
2 |
2 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
SQL Server has displayed the compiled and executable plan for the spDummyTable1 stored procedure.
Let’s now execute the same statement again and view the cache details.
SELECT EmpId, EmpName from vwDummyTable1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
2 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
| Compiled Plan |
2 |
2 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
The value of Usecounts has been incremented. SQL Server has used the same compiled plan for the SELECT statement on the view and incremented the Usecounts of the executable plan. N number user will use the same compiled plan when we execute the same SELECT statement on view.
Now, let’s add a WHERE clauses on the view SELECT statement and view the results from the master.dbo.Syscacheobjects.
SELECT EmpId, EmpName from vwDummyTable1 WHERE EmpID = 4
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
SQL Server has not used the existing cache plan because of change in the view’s SELECT statement. SQL Server generates a new cache plan for the SELECT statement on view along with old cache plan.
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
1 |
(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1 |
| Compiled Plan |
2 |
2 |
(@1 tinyint)SELECT EmpID, EmpName FROM [vwDummyTable1] WHERE [EmpID]=@1 |
| Executable Plan |
1 |
2 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
| Compiled Plan |
2 |
2 |
()SELECT EmpID, EmpName FROM [vwDummyTable1] |
Let’s execute the same view SELECT statement with different empid and view the results.
SELECT EmpId, EmpName FROM vwDummyTable1 WHERE EmpID = 8
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 value, SQL Server has used the same compiled plan for the SELECT statement on the view and incremented the Usecounts of the executable plan.