Performance optimizations for SQL Server administrator-required skills

Performance optimizations for SQL Server administrator-required skills

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.

1. First, 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, master , and model. If any databases are using replication on the server instance, you must also back up the 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.

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/07/26/wkiom1newsnab8_oaaeaqgp6phe243.png "height=" 410 "/>

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m02/a5/d6/wkiol1newppt8ge4aaglmwkvzsk933.png "height=" 458 "/>

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

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

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/a5/d7/wkiol1newppxpkf-aahnkjzm8w4462.png "height=" 480 "/>

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m02/07/26/wkiom1newsuqcsx1aah2rsp4b6u024.png "height=" 414 "/>

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

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m01/a5/d7/wkiol1newpwg32ovaahp8wwqanu037.png "height=" 444 "/>

3. Database CPU performance tuning;

Database---Properties--advanced

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

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/07/26/wkiom1news3tjso2aahtj1ywgno521.png "height=" 445 "/>

4. We need to back up log regularly

We regularly back up the database transaction log through scheduled tasks

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m02/07/26/wkiom1news7xakyyaahsk28nbss450.png "height=" 409 "/>

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;

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m02/a5/d7/wkiol1newpms5cbnaaih-xb58hi252.png "height=" 416 "/>

6.SQL Maximum Memory number

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

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/a5/d7/wkiol1newpna4c7uaahmbmexp6y433.png "height=" 443 "/>

7. Modify the database memory size;

Recommended initial 100MB, Growth unlimited

Log initial 100MB, growth 2G

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m02/07/26/wkiom1newtgss70iaahsvg8f4kk052.png "height=" 436 "/>

9 Define periodic Check DB status, we define scheduled tasks to complete

Check all DB periodically to ensure database integrity

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/a5/d7/wkiol1newpuakkkiaahsb1fsnt8188.png "height=" 425 "/>

10. Turn on backup compression

Turn on database backup compression

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m00/07/26/wkiom1newtorwakjaaidkjinds0145.png "height=" 441 "/>

11. 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

650) this.width=650; "title=" image "style=" Margin:0px;background-image:none; "border=" 0 "alt=" image "src="/http S3.51cto.com/wyfs02/m01/a5/d7/wkiol1newp3tchebaaicjs3cgou919.png "height="/>

This article from "Gao Wenrong" blog, declined reprint!

Performance optimizations for SQL Server administrator-required skills