SQLSERVER Interview Questions-part3
- What
is blocking and how would you troubleshoot it?
Blocking happens when one connection
from an application holds a lock and a second connection requires a
conflicting lock type. This forces the second connection to wait, blocked
on the first.
- How
to restart SQL Server in single user mode?
SQL Server can be started from command
line, using the SQLSERVR.EXE. This EXE has some very important parameters
with which a DBA should be familiar with. -m is used for starting SQL
Server in single user mode and -f is used to start the SQL Server in
minimal confuguration mode.
- what
are the DBCC commands that you commonly use for database maintenance?
DBCC CHECKDB - Ensures that tables in
the db and the indexes are correctly linked.and DBCC CHECKALLOC - To check
that all pages in a db are correctly allocated. DBCC SQLPERF - It gives
report on current usage of transaction log in percentage. DBCC
CHECKFILEGROUP - Checks all tables file group for any damage.
- What
are statistics?
Statistics determine the selectivity
of the indexes. If an indexed column has unique values then the
selectivity of that index is more, as opposed to an index with non-unique
values. Query optimizer uses these indexes in determining whether to
choose an index or not while executing a query.
- Under
what circumstances they go out of date, how do you update statistics?
Some situations under which you should
update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed,
or removed (that is, if the distribution of key values has changed), or
the table has been truncated using the TRUNCATE TABLE statement and then
repopulated
3) Database is upgraded from a previous version Look up SQL Server books
online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC
SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats,
sp_createstats, sp_updatestats
- What
are the different ways of moving data/databases between servers and
databases in SQL Server?
BACKUP/RESTORE, dettaching and
attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT,
SELECT...INTO, creating INSERT scripts to generate data.
- Explian
different types of BACKUPs avaialabe in SQL Server?
Full database backup, differential
database backup, transaction log backup, filegroup backup.
- What
is database replicaion? Replication
is the process of copying/moving data between databases on the same or
different servers.
- What
are the different types of replication you can set up in SQL Server?
SQL Server supports the following
types of replication scenarios:
* Snapshot replication
* Transactional replication (with immediate updating subscribers, with
queued updating subscribers)
* Merge replication \
- How
to determine the service pack currently installed on SQL Server? The global variable @@Version stores
the build number of the sqlservr.exe, which is used to determine the
service pack installed.
