SQL DBA interview questions+ Wipro

Wipro 5+:

1. What is the performance counters used to check memory utilization.

2. Which DMV's we can use to check perform counters?

3. Which DMFs can we use to check the complete command running in a session.

4. Which exe file can be used to trace the server actitivites.

5. Statistics - How to update on a particular table index.

6. Memory configuration.

        If there is 16GB RAM, how can we make the server to use min 12 and          max 14.

        Lock pages in memory

7. How to improve the performance of Log Shipping.

8. How to move system dbs

        * TempDb

        * master

9. Execution plans

10.Why use DAC? How to connect?

11.Regular issues?

 

Best practices for installing service packs, cumulative updates and hotfixes for SQL Server

Test on a test/dev SQL Server first and only after you have confirmed that all applications are working as expected then install it on a production SQL Server. 

Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme. 

Run DBCC CHECKDB on ALL databases (user and system databases) and ensure that there were no errors reported. 

Backup ALL databases (user and system databases) and full-text catalogs (if applicable). This is NOT required but highly recommended. 

Stop Monitoring and Anti-virus services 

Make sure you have the proper permissions to install (administrative privilege on server/cluster node) 

The below points are for clustered SQL Server instance 

Make sure ALL SQL resources come online on ALL cluster nodes 

Make sure that ALL disk resources (even the ones that not being used by SQL Server) are online and not in failed state. 

Verify that there are no dependencies other than those created by the SQL Server setup on any SQL Server cluster resources. 

Run MPSRPT_SQL.exe on all cluster nodes (not required but recommended) 

Make sure all remote desktop connections are closed. You can connect to the node (you are running the setup from) using remote desktop connections but you should disconnect any remote connections to other cluster nodes.