SQL Server Performance Forum – Threads Archive
Does this affect Query Optimizer?
Are the following cases the same regarding to Query Optimizer? Case 1.create nonclustered index [test1] on [dbo].[table1] ([column2])
select * from test1 where column2 = ‘test’
go Case 2.
create nonclustered index [test1] on [dbo].[table1] ([column2])
go
select * from test1 where column2 = ‘test’
go Case 3. (the nonclustered index already exists)
select * from test1 where column2 = ‘test’
go I ask because I don’t know when Query Optimizer will create the Compiled Plan.
I my oppinion there is no differences at all.
Luis Martin
Moderator
SQL-Server-Performance.com
Case I will not be cached at all as there is DML (data modification Language) in the same.
For Case II and Case III the SELECT statement may be cached as they are independent batches.
For Case II the DML will not be cached. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
For Case 1, will Query Optimizer try to optimize the select statement before the index is created? When I run Case 1 in Query Analyzer, is the following action correct? Parse the create statement
Compile the create statement
Execute the create statement to create the index
Parse the select statement
Optimize the select statement with the index just created
Nope the parsing and compilation will happen before the execution will start. However, you may see a recompile after the Index has been created. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Gaurav, You mean this: parse and compile both create and select statements since they are in the same batch
execute create
recompile select
execute select
Yup! That is correct. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Gaurav, How does SQL Server knows that the select statement need to recompile? The syscacheobjects table has a refcounts but I don’t know which system table contains the actual reference information. I have not checked the Systbl.chm – Electronic version of System Tables Map. I have the SQL Server 7 System Tables Map printed copy but I cannot find out which table. I have the SQL Server 2000 printed copy at home.
When Query Engine looks at the plan and observes that there has been an schema change on the underlying tables in the query, it decides to go for recompile. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Gaurav, So it means the Query Engine will do the following when executing a query: 1. Review the Compiled Plan object in the cache and check all the underlying tables for schema change.
2. Compare the following columns of the syscacheobjects with the current setting:
– setopts
– langid
– dateformat
I have no idea how engine checks it. Sorry! Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Gaurav, I will research this later. I guess this should be same as or close to how stored procedure get recompiled. Thank you for all the replies.
]]>