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

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.

Continues…

Pages: 1 2 3 4 5 6




Related Articles :

  • No Related Articles Found

3 Responses to “Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server”

  1. hi,

    I ran the select statement and i got the desired result but when i run the select on syscacheobjects i am seeing only parse tree and compiled plan but i am not able to see the executable plan.

    please reply with valuable suggestions

  2. I am using sql server 2008? i think we can see cache object executable plan in sql 2000 but not in 2005/20008
    am i on the right track?

  3. Hi Vijay,

    Thanks for Good article on Select,View & SP.

    I Need Some Clarification
    I’m using Sql Server 2008

    A). When i Execute Same SQL statement with one same user but diffrent empid,
    then it produce diffrent Compiled Plan.

    B). the above is same for VIEW also.

    C). But for SP uses same Compiled Plan for diffrent empid input.

    I think this is correct behaviour in Sql server 2008.

    What you say regarding SELECT & VIEW is OPPOSITE

    Please Explain

    Regards,
    Anil Vanjre

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |