SQL Server DBA thirty questions

Many developers want to be a DBA, and many people first position themselves as a DBA. What knowledge and skills do DBAs need to master? Below are my

During DBA work and interviews with DBAs, there were thirty questions about DBAs. Among the thirty questions, there were many minor questions, covering the skills of SQL Server DBAs.

TIPS:

 

1. Differences between char, varchar, and nvarchar (including usage and space occupation); methods for finding data of a node in XML format and high efficiency;

What is the difference between the process and the use of T-SQL to query data;

2. What are the functions of the system DB? Backup is not required. Why? How to restore the system database (mainly the master database) if the database is damaged );

3. What operations will be used in tempdb? What are the possible causes of an exception in tempdb;

4. What types of indexes are available, what are their differences and implementation principles, what are the advantages and disadvantages of indexes, how to create appropriate indexes for SQL statements, and what are the requirements for index creation?

Note, how to check whether the index you created is used; how to maintain the index; how to check if the index is damaged, how to fix it; T-SQL has a better index but runs

The index is not used. What is the possible cause;

5. Can we create an index on a view? What are the advantages and disadvantages of an index on a view? What is the difference between an index on a view and an index on a table;

6. What tables can we get the job information, which views can be obtained by running statements, and how to get the IO and time information of a T-SQL statement;

7. In the online system, a table has 50 million records. Now you need to import 20 million records to a table on the other server. After importing the 20 million records, you need to delete the data.

Besides, what are your advantages and disadvantages;

8. The database server reports that the disk space is insufficient. How can you handle this problem and request recovery as soon as possible;

9. What are the differences and relationships between temporary tables, table variables, and CTE (common table expressions), storage locations, and how to decide which one to choose for use;

10. What are the isolation levels and default levels of sqlserver, what are the major lock types of databases, and how row version control is implemented;

11. How to track deadlocks; how to track and find blocking; how to process problematic statements; what events should we track when using profile;

12. What are the main types of Windows logs? sqlserver logs are generally retained, and under what circumstances new SQL logs will be generated. Which of the following Database Log Recovery modes are available,

What is the difference? database logs suddenly become very large, and you cannot contract, what are the possible causes, how to find the causes, and how to handle them separately;

13. What are the concepts of partition tables and partition views? What are the benefits of using partition tables and views in general;

14. How to compare the differences between two tables with the same structure; how to fix the table if the table is damaged; how to restore the data if the backup file is faulty; how to restore a table

The identity attribute of is returned to zero;

15. Differences and functions of checkpoint, lazywriter, and eager write; differences and functions of DDL trigger and DML trigger;

16. Differences and use cases between login ing and logshipping; which of the following technologies of mirrorand Oracle are similar and what are their differences;

17. The construction steps of tracing ing, the difference between the three modes of tracing ing, the principles and requirements of synchronous and asynchronous in tracing ing, after setting up the tracing ing, you need to do the Database Log

What processing;

18. Replication configuration and use cases; what are the replication modes; What are the differences between push and pull; what database will be generated after replication is established; when an error is reported

Use specific statements to view the error, clear the statements used by replication for a database, and view tables through which the synchronization chain information is mainly used;

19. Can I truncate a table at the replication publishing end? Why? How does the replication identity column handle, how does the missing field error handle, and how does the primary key conflict error occur?

And how to skip the specified error, how to process the deleted table on the subscription end, and how to process the data with large scale changes. A synchronization chain changes the data at one time because a table

This causes serious latency of the synchronization chain. It is required to restore the synchronization chain as soon as possible and how to handle it.

20. What objects will be created for SSB (Service Broker) scenarios and how to create them? What are their advantages and disadvantages? How to Implement messages between different servers?

Transmission; which methods can be used for troubleshooting;

21. What methods are used to track changes to database data, such as Change Data Capture, change tracking, and trigger?

Advantages and disadvantages;

22. SQL optimization steps: How to Determine the SQL statement problems, how to locate the problems, and how to solve these problems; the differences between nested join, merge join and hash join;

23. Database troubleshooting steps and how to handle emergency database problems;

24. How to consider and develop a database backup plan. Companies require backing up a very large database or table, and require as little data as possible to be lost.

Method;

25. If you want to perform database monitoring, you will pay attention to those indicators (including SQL Server and Windows), how to develop performance baselines, And what monitoring software you have used;

26. database migration steps: re-build a large test system (at least 10 database instances). If the original database data volume is not large, but the database volume is large, the new system data

And how to implement it quickly;

27. simple steps to create a cluster: it requires at least a few IP addresses, services to be installed, fixed disks to be installed, raid settings, and disk partitioning. sqlserver

Differences between cluster and MySQL cluster and Oracle RAC;

28. If you encounter an unsatisfactory performance,CodeComplex stored procedures are difficult to solve problems through database optimization. How do you persuade developers to modify it?

Members are not willing to modify );

29. Have you ever encountered a system fault caused by your misoperations and how did you handle it? If not, what should you do if you accidentally deleted an important table?

Processing;

30. What kind of DBA are you going to become, and how are you going to prepare (or what kind of career planning you have, and how are you going to spend your DBA career); if you enter the company, you

What is most desired.