SQL Server DBA Interview Questions & Answers
Q: What are indexes and how much non-clustered indexes are there in SQL Server 2008 R2?
Ans: An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently.
999 non-clustered indexes in SQL Server 2008 R2
Q: In case of principal server failed, do secondary takes itself in mirroring?
Ans: Yes mirror server makes database available and make it principal.
Q: How to configure ALWAYSON group on SQL Server?
Ans: https://www.hightechnology.in/alwayson-availability-groups-sql-server-2012/
Q: How to move database from one server to another?
Ans: Import/Export
Snapshot
Detach/Attach
Backup/restore
Q: Which SMTP port is used in Database mail?
Ans: 25
Q: New features in SQL Server 2012?
Ans: Column store indexes
Pagination (offset/fetch)
Contained database
Error handling
AlwaysOn Availability Groups
User-Defined Server Roles
Enhanced Auditing features
Big Data support
Q: Why we use DBCC inputbuffer?
Ans: Displays the last statement sent from a client to an instance of Microsoft SQL Server
Q: How to install SQL Server on a machine in which Integration services is running?
Ans: First of all integration services is a shared component, we can skip that and if installed we cannot able to install it
Also we will check for error log and take appropriate action.
Q: Database is in suspect mode, what to do?
Ans: DBCC CHECKDB (‘<Database Name>’) WITH NO_INFOMSGS, ALL_ERRORMSGS
Follow below steps to bring the database ONLINE and accessible.
Change the status of your database by running following query
EXEC sp_resetstatus ‘<Database Name>’;
Set the database in EMERGENCY mode by running following query
ALTER DATABASE <Database Name> SET EMERGENCY;
Check the database for any inconsistency by executing below query
DBCC CHECKDB(‘<Database Name>’);
If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you are done.
ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Take the backup of the database just to be in safe side.
Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.
DBCC CHECKDB (‘<Database Name>’, REPAIR_ALLOW_DATA_LOSS);
Finally bring the database in MULTI USER mode by running following query.
ALTER DATABASE <Database Name> SET MULTI_USER;
Q: What is Page Life Expectancy (PLE)?
Ans:
SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’
The recommended value of the PLE counter is (updated: minimum of) 300 seconds. I have seen on busy system this value to be as low as even 45 seconds and on unused system as high as 1250 seconds. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
Q: How to know which Query is running on a SPID?
Ans: DBCC INPUTBUFFER(SPID)
Q: Counters to check CPU bottleneck?
Ans: System: % Total Processor Time
This counter gives you information with respect to the total performance of the entire system i.e. it is an average of all the processors together on the system.
SQL Server: SQL Statistics: Batch Requests/sec
This counter gives you information with respect to the number for TSQL command batches received per second.
SQL Server: SQL Statistics: SQL Compilations/sec
This counter gives you information with respect to number of SQL Compilations happening per second.
SQL Server: SQL Statistics: SQL Re-Compilations/sec
This counter gives you information with respect to number of TSQL statement recompiles per second. This counter has the count of number of times statement recompile is triggered by the database engine. This counter value should be very low. Ideally SQL Statistics: SQL Re-Compilations/sec value should not be more than one percent of SQL Statistics: Batch Requests/sec.
Q: Mirroring is showing in disconnected state what to check?
Ans: Recycle the endpoint on the database mirror. To do this, follow these steps:
On the principal database, execute the following SQL script to stop the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STOPPED
Execute the following SQL script to restart the endpoint:
ALTER ENDPOINT <Endpoint Name> STATE=STARTED
Note If communication between the endpoints does not restart after you execute the scripts, execute the scripts on the database mirror. However, the database may enter a “Suspended” state after you do this. If this issue occurs, execute the following SQL script:
ALTER DATABASE <Database Name> SET PARTNER RESUME
Q: What is Endpoint in SQL Server?
Ans: This endpoint is a special-purpose endpoint that is used exclusively to receive connections from other server instances.