Performance optimizations for SQL Server administrator-required skills

Source: Internet
Author: User
Tags documentation

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

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.