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.

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan

2

2

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]

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

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

2

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]
Compiled Plan

2

2

()SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [DummyTable1]

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.

Cacheobjtype Refcounts Usecounts Sql
Executable Plan

1

1

(@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]

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

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 values, SQL Server has used the same compiled plan for the SELECT statement, 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 |