Now, let’s execute the following commands to clear the cache before the stored procedure experiment.
DBCC FREEPROCCACHE
GO
Stored Procedures
We will create a stored procedure with one parameter, and see how it is differs from the SELECT statement and views.
CREATE PROC spDummyTable1 (@EmpID Int) AS
SELECT EmpID, EmpName FROM DummyTable1 WHERE EmpID = @EmpID
Now, let’s execute the following commands to display the data and cache information for the spDummyTable1 we created.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
1 |
spDummyTable1 |
| Compiled Plan |
2 |
1 |
spDummyTable1 |
SQL Server displays the compiled and executable plan for the spDummyTable1 stored procedure.
Let us execute the same statement again and see the cache details.
EXEC spDummyTable1 1
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
2 |
spDummyTable1 |
| Compiled Plan |
2 |
1 |
spDummyTable1 |
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 stored procedure.
Let’s execute the same stored procedure with a different empid parameter value and view the cache details.
EXEC spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
3 |
spDummyTable1 |
| Compiled Plan |
2 |
1 |
spDummyTable1 |
The value of Usecounts has been incremented. Though, we have given different empid value, SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
Now, let us execute the same stored procedure with the username and see the cache details.
EXEC dbo.spDummyTable1 5
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
4 |
spDummyTable1 |
| Compiled Plan |
2 |
1 |
spDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.
Let’s execute the same stored procedure from different user. I have created a new user, called ‘user1,’ and given ‘Exec’ permission for spDummyTable1 stored procedure. I have opened new Query Analyzer and connected using UID : user1; PWD : user1. I have EXECuted the following command.
EXEC dbo.spDummyTable1 3
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
Different users will execute the stored procedure with different or same empid value and will use the same compiled plan and increase the Usecounts value of the executable plan.
Now, let’s execute the same stored procedure with the databasename and username and see the cache details.
EXEC vijay.dbo.spDummyTable1 7
GO
SELECT cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects
GO
| Cacheobjtype | Refcounts | Usecounts | Sql |
| Executable Plan |
1 |
5 |
spDummyTable1 |
| Compiled Plan |
2 |
1 |
spDummyTable1 |
No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.