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

SQL Server has used the same compiled plan for the SELECT statement and incremented the Usecounts of the executable plan. Different user will execute the SELECT statement with different empid values, but will use the same compiled plan and increase the Usecounts value of the executable plan.

Now, let’s execute the same statement with the username on the SELECT statement and verify the results.

SELECT EmpId, EmpName FROM dbo.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

1

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[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]

Now, we have two more rows in the cache plan because we have used different usernames in the SELECT statements. SQL Server will generate a new compiled and execution plan for different users. The same user will execute the SELECT statement more than one time will use the same compiled plan and only increase the Usecounts value of the executable plan.

Let us execute the same SELECT statement with different empid and verify the results.

SELECT EmpId, EmpName FROM dbo.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

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[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]

Now, let’s execute the same statement with the databasename and username on the SELECT statement and view the results.

SELECT EmpId, EmpName FROM vijay.dbo.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

1

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [vijay].[dbo].[DummyTable1] WHERE [EmpId]=@1
Executable Plan

1

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[DummyTable1] WHERE [EmpId]=@1
Compiled Plan

2

2

(@1 tinyint)SELECT [EmpId]=[EmpId],[EmpName]=[EmpName] FROM [dbo].[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]

As a DBA or developer, we can minimize the creation of compilation plans if we add the databasename, username, and WHERE condition for a SELECT statement. Don’t change the combination of the SELECT statement to allow creating a new execution plan unless it is really required. If you do, it will create a new execution plan on the SELECT statement. We can minimize the execution plan to upgrade the system’s performance.

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 |