Applicative / Development DBA Interview Questions - Part 1
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....
(meaning development) DBA position, you will find the
following questions helpful:
1. What is an execution plan and what for it needed?
A query execution plan is
an ordered set of steps used
to access or modify
information in a database.
An SQL statement tells
what you want and the
execution plan tells you how
the SQL Server going to
do it, meaning what
operations,operators it
will use (Table/Index
scan,Index Seek, Nested loop and etc)
an ordered set of steps used
to access or modify
information in a database.
An SQL statement tells
what you want and the
execution plan tells you how
the SQL Server going to
do it, meaning what
operations,operators it
will use (Table/Index
scan,Index Seek, Nested loop and etc)
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....