SQL Server DBA Eve asked

Many developers want to be a DBA, and a lot of people start by positioning themselves as a dba,dba. What kind of knowledge and skills do you need to know? Here's my

While doing DBA work and interviewing DBAs, some of the 30 DBA issues that have been collated, as well as the small number of issues in the 30 big questions, include SQL Server DBA's more technical

The knowledge point, share with you, hope to be a friend who is willing to do dba some help:

1. The difference between char, varchar, nvarchar (including use and space occupancy); XML types find out which methods are available for a node's data, which are efficient; use storage

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

2. What is the system db, what is the role, need not do backup, why; damaged how to do the restore (mainly the master library);

3. What operations will be used to tempdb, and if tempdb becomes abnormally large, what the probable cause is, what to do with it;

4. What are the types of index, what are the differences and how they are implemented, what are the advantages and disadvantages of the index, and how to create an appropriate index for the SQL statement, and what is required when the index is created

Note the item, how to see if the index you created is being used, how to maintain the index, how to check for index corruption, how to fix it, or a better index for T-SQL, but run

When the index is not used, what may be the reason;

5. Can we build the index on the view, if it can be built, what will be the advantages and disadvantages; What is the difference between the index on the view and the index on the table;

6. Job information which tables we can get, which views the system is running, and how to get the IO, time and other information of a T-SQL statement;

7. Online system, a table has 50 million records, now you want to import 20 million of the records into a table in another server, after the completion of the 20 million data need to be deleted

Besides, what do you prepare to deal with, what are the pros and cons;

8. The database server reported insufficient disk space, how you will respond, demand recovery as soon as possible;

9. What is the difference between temporary table, table variable, CTE (common table expression), what is different from the storage location, and how to decide which to choose when using;

What are the isolation levels of SQL Server, which is the default level, what are the main types of locks in the database, and how row versioning is implemented;

11. How deadlocks are tracked, how blocking is tracked and looked up, how processing is done when a problematic statement is found, and what events we typically need to track when tracking with profiles;

What are the main Windows logs, SQL Server logs generally keep a few, under what circumstances will generate new SQL logs, the database log recovery model,

What the difference is; the database log suddenly becomes large, and you cannot shrink, what the probable cause is, how to find the cause, and how to deal with it separately;

13. What is the concept of partitioned tables and partitioned views, and what are the benefits of using them in general?

14. How to compare the differences between the table data of two identical structures, how to fix the table if it is damaged, how to restore the data in case of a problem with the backup file;

The identity attribute is zeroed;

CheckPoint, LazyWriter and eager write differences and roles; the difference and role of DDL Trigger and DML Trigger;

Mirroring and logshipping differences and usage scenarios; SQL Server's mirroring is closer to Oracle's technology, and what's the difference between them;

Mirroring construction steps, mirroring three modes of difference, mirroring synchronous and asynchronous principle and requirements, build up mirroring, need to do the database log

What to deal with;

Replication configuration and usage scenarios; What are the different modes of replication, what is the difference between push and pull, and what library will be generated when the replication is built;

What to use to check the reading of the wrong specific statements, clear out a library of replication using what statement, to see the synchronization chain information mainly through which tables;

Replication the published table can be truncate, why, Replication identity column handling, missing field error handling, primary key conflict error

, how to skip the specified error, how to handle a large-scale change in the Subscriber table, a synchronization chain because one of the tables changes data at once is

Large synchronization chain caused by severe delay, the need to restore the synchronization chain as soon as possible, how to deal with.

SSB (service Broker) usage scenarios, how to create, what objects are created, what are the pros and cons, and what are the main ways to implement messages between different servers

The way in which the errors can be routed;

21. What are the methods for tracking changes to database data, which are used (CDC (change data Capture), CT (change Tracking), trigger, etc.)

Advantages and Disadvantages

SQL tuning steps, how to determine the existence of SQL statement problems, how to locate the problem, how to solve these problems; Nested join, Merge join and hash join differences;

23. Database troubleshooting steps, how to deal with emergency database problems;

24. How to consider and develop a database backup plan; The company requires a very large database or a table to be backed up, and requires as little data as possible loss, you may adopt what

Method

25. If you want to do database monitoring, you will be concerned about those indicators (including SQL Server and Windows), how to develop performance baselines, which monitoring software you have used;

26. Database migration steps; rebuild a larger set of test systems (minimum of 10 db instances), if the original database volume is not large, but DB is more, the newly built system data

Do not need, how to quickly achieve;

27. Create cluster brief steps, require a minimum number of IPs, what services need to be installed, what fixed disks are required, how raid is set, how disks are partitioned, SQL Server

The difference between Cluster and MySQL Cluster and Oracle RAC;

28. If you encounter a stored procedure with unsatisfactory performance and complex code, it is difficult to solve the problem through database tuning, how do you persuade the developer to modify it (possibly developer

Not be willing to modify);

29. Have you ever encountered a system failure caused by your mis-operation, how did you handle it, and if not, suppose you mistakenly deleted an important table, how do you

Processing

30. What kind of dba you are going to be, how will you prepare (or what career plan you have, and how you intend to spend your DBA career); If you enter the company, you

What is most wanted to get.

SQL Server DBA Eve asked