Do you understand SQL Server Management?

SQL Server Management, do you do well? The following may be helpful to your work.

1. database files include. mdf. ndf. ldf files: mfd is the master database file, ndf is the slave database file, ldf is the log database file, each database mdf file has only one, and ndf files can have multiple.

2. The data page size is 8 k. One of the largest features of the SQL database is that there is no cross-page data. For example, if one piece of data is 3 k, three pages are required for five pieces of data instead of two pages. The database expansion page is 8 pages. That is, if the storage space of a table is insufficient, we need to insert a 3 K data record. The database does not allocate one page, but 8 pages, that is, 64 K space. So is the windows system. We can see a text file and enter a. At this time, the memory allocated to our file is not 2 bytes, it is 1 K space.

If the size of a data page is only 8 KB, how can we store data such as txt and image?

In this case, the system stores the txt and image Heap addresses, and their data is stored in the heap. Therefore, the address cannot exceed 8 bytes.

3. In the database memory, we read a piece of data first in the memory. If the memory does not exist, we will find it in the database and then read the data into the memory. So what about updating a piece of data?

We update (add, delete, and modify) a piece of data not written to the database when the commit is used, but the database has a chekpoints. When the checkpoints is used, the database, write data to the database one page at a time. In sql2005, the time is about 15 minutes and only once. Where is the data without transactions? There may be two places, one in temptdb and the other in buffercahce.

4. pay attention to several issues for Large System databases. We all know that the database bottleneck is the I/O bottleneck of the disk array. If our database is too frequent, the log files in our database will increase rapidly, if we put database files and log files on a disk, the operation of database data slows down. Therefore, we can put log files and data files on different disks, one disk has two channels. If data is stored on different disks, Data Writing and logs can be performed in parallel, and we recommend that you back up logs every 6 hours, in this way, we can reduce the size of our log file, because the log file is deleted from the log after being backed up.

5. database File groups can also be used to design database file groups, store different data in different file groups, and store different file groups on different disks, if the table is large, we can also place a table in different file groups. Many times, we put a table in different file groups, which may be faster than putting it in one file group, because access is performed in parallel in different file groups, but sequential access is required in a file group. In general, Table Partitioning is not necessary.

6. when creating a database, we need to note that the data file in the disk format fat32 allows a maximum of 2 GB of database files. If we find this problem after creating a database, we will be in trouble, we recommend that you keep adding database files to an NTFs disk. the maximum capacity of the database can be changed to a large value instead of a small value. Is there any way to reduce the size of the database? Some compressed databases.

Here we can compress the data size.

7. Database Backup considerations:

1. You must select the two check boxes below to ensure that the backup is successful.

The best steps for backing up databases (especially when planning maintenance) are generally used

1. Check database integrity

2. Log tail backup (we will talk about it later in the disaster recovery)

3. Backup

It is best that these are sequential sources.

We recommend that you back up a full weekly backup over the weekend, with 1-5 differential backup and 6-hour log backup.

8. How to enable database performance monitoring:

For example, we monitor the SQL Server deadlock caused by the application to add monitoring:

We can see the following:

It's all zero. There is no deadlock, so it turns out that our system is healthy... but monitoring is usually done when the server is idle, because it also occupies resources.

If something is wrong, you are welcome to share your ideas.