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

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

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

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.