SQL Server Management These you know what?

SQL Server Management, did you do a good job? These may help you with your work, and you may wish to take a patient look.

1. The database file has. mdf. ldf three kinds of files, MfD main database file, NDF is from the database file, LDF for log database files, each database MDF file only one, NDF file can have multiple.

2. Data page size for 8k,sql database The biggest feature is that there is no spread of data, such as a 3k of data, then 5 data requires 3 pages of storage, not two pages. Database Expansion page is 8 pages, that is, if a table storage space is not enough, we want to insert a 3k of data, the database is not allocated one page, but 8 pages, that is, 64k space, this is our Windows system is also, we see a text file, and then enter a A, So this time the system for our files allocated memory is not 2 bytes, but 1k space.

If a data page size is only 8k, how do you store the data like txt,image?

So we're storing the txt,image heap address, and their data is stored in the heap, and their addresses are no more than 8 bytes.

3. Database memory, we read a piece of data is first in memory, if the memory is not, we will find in the database, and then read the data into memory. So we're going to update a piece of data?

We update (add, delete, change) a data is not a commit when the data is written to the database, but the database will have a chekpoints, in the checkpoints when the database, will be a page of the data to the database to write. This time in the sql2005 is about 15 minutes only once, then where is the data without the transaction? Then there may be two places, one in Temptdb and one in Buffercahce.

4. A number of issues should be noted for large system databases, we all know that the database bottleneck is the disk array has I/O bottleneck, if our database is more frequent, so that our database log files grow faster, if we put the database files and log files on a disk, then, will result in the more and more to operate the database data slower, Then we can put the log files and data files on a different disk, a disk two channels, if placed on different disks so write data and log can be parallel super, as well as our best 6 hours a day to do a log backup, so we can reduce the size of our log file, The log file is deleted from the log after it is backed up.

5. The use of database filegroups, if we have a large database design, we can also design a database file group, put different data in different filegroups, different file groups on different disks, if the table is large, we can also put a table in different filegroups, many times we put a table in different file groups, It may be quicker than putting it in a filegroup, because it is accessed in parallel in different filegroups, but in a filegroup that requires sequential access and is generally not necessary, it is recommended that you do table partitioning.

6. When building a database, we need to be aware that our data files are placed in the format of the disk FAT32 allow database files to be up to 2g if we set up a good database and then find this problem is trouble, to keep adding database files, so it is recommended to put on the NTFS-formatted disk, the database maximum capacity to modify the rules, Can change the size of the small, then there is no way to change the database? Some compressed the database.

Here we can compress the data size.

7. Backup DATABASE considerations:

1. Be sure to select the following two hooks, so as to ensure the success of our backup.

The best steps for us to back up the database (especially when doing maintenance planning) are generally

1. Check the integrity of the database

2. Log tail backup (we'll talk about it later when we're recovering)

3. Backup

The best of these are the ones that come in order.

We backup for one week is best weekend full backup, 1-5 differential backup, 6 hours to do a log backup

8. Database performance Monitoring Open method:

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

We can see the following:

0 is not a deadlock, then prove that our system is very healthy ah ... , but monitoring is typically monitored while the server is idle, because this is also a resource-intensive.

There are wrong places to welcome everyone to shoot bricks, hope to communicate and share.