If you got the chance to interview a person for applicative
(meaning development) DBA position, you will find the
following questions helpful:
SQL Server builds an execution plan in order to save
all steps it does upon submitting a query for execution
(like parsing, optimizing) and of course for reuse purpose.
2. When the execution plan created?
It created on the first execution of a SP.
3. Is an execution plan takes into an account values of SP`s input parameters?
Yes, the execution plan is built considering the value of SP`s
input parameters.(Prameters Sniffing)
4. Is an execution plan is permanent? If not, when the SQL Server changes it?
It is not permanent, the SQL Server can decide to recompile
a SP and different execution plan can be created. There are
many reasons for SP recompilation, like Schema/Statistics
changes, Temp table(s) usage and etc.
5. When you rebuild a Clustered Index are non clustered indexes rebuilt too?
Starting from SQL Server 2005, the answer is NO!
SQL Server 2005 will re-use the old uniquifier values so the
cluster keys don't change. This means that non-clustered indexes
are NOT rebuilt.
See Paul Randal Article.
6. Where does a Non Clustered index points to?
In case you have table without a Cluster index, it ponits to
data pages, once clustered index is created, non clustered
indexes will be reorganized and point to clustered index.
To be continued....