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

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

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

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

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

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

No difference at all. SQL Server has used the same compiled plan for the stored procedure and incremented the Usecounts of the executable plan.