Performance optimizations for SQL Server administrator-required skills

Performance optimizations for SQL Server administrator-required skills Gao Wenrong 1 People reviews1171 people read 2017-09-22 08:27:41

SQL Server is one of the essential services of the enterprise, so it is a great challenge for the daily operation of the administrator, the administrator is very important to the service guarantee, so as the administrator is the backup of the data and the service, then familiar with the disaster recovery, it is only the premise, Next we need to optimize the service, today to talk about the daily optimization of SQL Server, our company every year, Microsoft will be in the environment of SQL Server Health Check, this summarizes some of the daily needs to be aware of problems, share to the needy friends.

The first is backup.

There are many ways to back up, and you can choose according to your hobbies. What are the specific backups?

SQL Server maintains a set of system-level databases, called system databases, that are critical to the operation of the server instance. You must back up multiple system databases after a large number of updates. The system databases that must be backed up include msdb、 MasterAnd Model。 If you have any databases that use replication on the server instance, you must also back up Distribution System database. By backing up these system databases, you can restore and restore the SQL Server system in the event of a system failure, such as a lost hard disk.

Of course, we have an article introduced the daily Backup and disaster recovery documentation, you can refer to my blog in the SQL Server class.

The following links can refer to Microsoft's backup introduction and documentation reference.

Https://docs.microsoft.com/zh-cn/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server

1. Use the Backup Schedule task to make a backup of the database.

2. Database version, right-click the database Server---properties---platform

3. Database CPU performance tuning;

Database---Properties--advanced

Defined by the CPU, which is typically half the number of CPU cores

4. We need to back up Log regularly

We regularly back up the database transaction log through scheduled tasks

5. Cleanup history

Retain the number of copies of the backup data, according to their own needs and disk size definition;

The number of days to retain backup data by configuring the cleanup history;

6. Maximum number of SQL memory

The default is maximum memory, but we need to define it according to physical memory, which is the 80-90% of physical memory.

7. Modify the database memory size;

Recommended initial 100MB, Growth unlimited

LOG initial 100MB, Growth 2G

8. Define periodic check DB status, we define the scheduled task to complete

Check all DB periodically to ensure database integrity

9. Turn on backup compression

Turn on database backup compression

10. Set the number of tempdb databases to increase the amount of concurrency

By default there is only one, we create 7 new, increase to 8, and then define the storage size to 100M

Copyright belongs to the author: from 51CTO blog author Gao Wenrong original works, declined reprint, otherwise will be held liable

Performance optimizations for SQL Server administrator-required skills