SQL Server DBA 30 question [itpub]

Source: Internet
Author: User

Many developers want to become a database training, and many people first position themselves as a DBA. What knowledge and skills does DBA need to master? Below are my

During DBA work and interviews, there were thirty questions about DBAs and many minor questions about the thirty major questions, it covers many technical knowledge points of SQL Server 2008 R2 training. I would like to share with you some help to those who wish to become DBAs:

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, and what are the advantages and disadvantages of indexes? How to create appropriate indexes for SQL statements and what should be paid attention to when creating indexes, 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; the T-SQL has a better index, but the index is not used at runtime, what are the possible causes;

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, 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? The training logs of SQL Server are usually retained, and under what circumstances new SQL logs will be generated. What are the differences between the recovery modes of database logs; 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 whenever possible when the backup file is faulty; how to return the identity attribute of a table to zero;

15. Differences between checkpoint and lazywriter; What are the differences between 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 are different from those of tracing ing. The principles and requirements of synchronization and Asynchronization in tracing ing are as follows: What processing should be done on database logs after tracing is established;

18. replication configuration and application scenarios; what are the replication modes; What are the differences between push and pull; what database will be generated after replication is built; and what statements are used to view the error when an error is reported, clear the statement used by replication for a database and view the tables used by the synchronization chain information;

19. can I truncate a table at the replication publishing end? Why; how to handle replication identity columns, how to handle Missing field errors, how to handle primary key conflict errors, how to skip specified errors, how to handle deleted subscription tables, and how to handle large-scale data changes; A synchronization chain has a serious delay caused by one-time data changes to a table in a 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 the advantages and disadvantages? How to transmit messages between different servers;

21. How to track database data changes, including the advantages and disadvantages of these methods (CDC (Change Data Capture), CT (change tracking), and trigger;

22. SQL optimization steps: how to identify SQL statement problems, how to locate problems, and how to solve these problems;

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

24. How to consider and develop a database backup plan. The company requires backing up a very large database or table, and requires as little data as possible to be lost. What method may you use;

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

26. database migration steps: re-build a relatively large test system (at least 10 database instances). If the original database data volume is not large, but there are a large number of databases, the new system data is not required, how to implement it quickly;

27. A brief procedure for creating a cluster requires at least a few IP addresses, services to be installed, fixed disks to be installed, raid settings, and disk partitioning; differences between sqlservercluster and MySQL cluster and Oracle RAC;

28. if you encounter a storage process with unsatisfactory performance and complex code, it is difficult to solve the problem through database optimization. How do you persuade developers to modify it (maybe developers are unwilling to modify it );

29. have you ever encountered a system fault caused by your misoperations? How did you handle it at the time? If not, what should you do if you accidentally delete an important table;

30. what kind of DBA training are you going to be, how are you going to prepare for it (or what kind of career planning you have, and how are you going to spend your DBA career); if you enter the company, what do you want most.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.