SQL Server routine maintenance

1. Purpose

1.1.1. Monitor the current running status of the database to ensure stable operation of the database

1.1.2. Monitor database backup/ha to reduce risks and responsibilities when problems occur

1.1.3. Monitor the overall running status of the database and adjust the database performance to ensure the efficient operation of the database

1.1.4. Minimize the frequency of emergency failures

1.1.5. Early detection of potential system problems and elimination of possible faults in the bud

2. Operating System Environment Monitoring

2.1. Check System Logs

2.1.1. Use the "Event Viewer" to view system logs and check the error information for troubleshooting.

2.1.2. Focus on SQL Server-related log information.

2.1.3. Back up and clear expired log information in a timely manner.

2.2. Check disk space

2.2.1. Check the disk space usage of the data file

2.2.2. Check the disk space usage of the log file

2.2.3. Check the backup disk space usage

3. database performance monitoring

3.1. Memory

3.1.1. DBCC memorystatus

 

DBCC memorystatus /* Focus on viewing the buffer counts Section Among them, committed memory and target memory are the most important. Committed memory indicates the amount of memory that SQL Server has obtained. Target memory indicates the amount of memory required for effective operation. If there is a large difference between the two, it indicates there may be memory pressure. */

3.1.2. sqlserver: Buffer Manager à page life expectancy: shows the length of time (in seconds) for the data page to reside in the buffer pool ). The larger the value, the healthier the system. If there is a memory bottleneck, this value will be less than s or less.

3.1.3. sqlserver: Buffer Manager à buffer cache hit ratio: displays the database memory hit rate. The requested data or the number of times the page is found in the buffer pool. If it is low, the memory is insufficient. Generally, this value is greater than 98%.

3.1.4. sqlserver: Buffer Manager à stolen pages problems may occur when the ratio of this indicator to the number of pages in the target memory is large.

3.1.5. sqlserver: Buffer Manager à memory Grants pending displays the process queues waiting for memory authorization. This indicator is ideal when it is 0.

3.1.6. sqlserver: Buffer Manager à checkpoint pages/sec displays the number of dirty pages written to the disk per second during the checkpoint operation. If the value is high, the memory is missing.

3.1.7. sqlserver: Buffer Manager à lazy writes/sec displays the number of times dirty pages are written from memory to disk per second. This value should be as close as possible to 0. When it is greater than 20 or more, make sure that the buffer pool is insufficient.

3.2. Disk

3.2.1. physicaldisk à avg. Disk aueue length shows the queue length of each disk. Do not select the _ total object, but view the queue length of a drive letter. If the parameter value is greater than 2, performance may be affected.

3.2.2. physicaldiskavg. Disk SEC/read displays the average disk Time for each read. Ideally, the value is less than 10 milliseconds.

3.2.3. physicaldiskavg. Disk SEC/Write shows the average disk Time for each write. Ideally, the value is less than 10 milliseconds.

3.2.4. SQL serveraccess methodsfullscans/sec displays the number of requests for full index scans per second or full table scans per second. If the scan frequency is greater than 1 per second, the index is missing or the index is poor.

3.2.5. SQL serveraccess methodspagesplits/sec displays the number of page splits per second. It can be avoided through proper index maintenance or good fill factor.

3.3. CPU

3.3.1. systemprocessor Queue Length indicates the system queue length. If the average value is greater than 3, the CPU bottleneck exists.

3.3.2. processor à % privilege time shows the time spent on internal operations of the operating system.

3.3.3. SQL Server à SQL statistics à SQL compilations/sec displays the number of times the query plan is compiled.

3.3.4. SQL Server à SQL statistics à SQL re-compilations/sec shows the number of times the query plan was recompiled.

3.3.5. when SQL compilations/sec and SQL re-compilations/sec are too many times, it indicates that the re-compilation plan may be due to the absence of Bound variables, or the number of re-compilation times is too high, it indicates that there are application bottlenecks.

4. Database Maintenance

4.1. primary database (Business Database)

4.1.1. Check the database growth status. The Weekly growth volume should be in a stable State. If abnormal growth occurs, check the cause.

4.1.2. Pay attention to the disk space of the data file to ensure at least one week of growth.

4.1.3. Check the growth of each log file. The Weekly growth volume should be in a stable State. If abnormal growth occurs, check the cause.

4.1.4. Pay attention to the disk space where the log file is located to ensure at least one week of growth space.

4.1.5. If the log file is too large, consider whether to clean it. If you clean it, use the backup log and then compress the log file.

4.2. Image Database

4.2.1. Steps except log clearing are the same as those of the primary database

4.2.2. After the log files in the image database are too large, you can clean up the logs in the primary database.

4.3. auxiliary database (log shipping)

4.3.1. Steps except log clearing are the same as those of the primary database

4.3.2. After the log files of the standby database are too large, you can clear the logs in the primary database.

4.4. view the job running status

You can run the following script to view the job status or the job activity monitor on the graphic interface.

 

-- 0: fail 1: succ 3: Cancel 5: first run and MSDB. DBO. syscategories. category_id not between 10 and 20 Select Category = JC. Name, category_id = JC. category_id, job_name = J. Name, Job_enabled = J. enabled, Last_run_time = cast (JS. last_run_date as varchar (10) + '-' + Cast (JS. last_run_time as varchar (10 )), Last_run_duration = Js. last_run_duration, Last_run_status = Js. last_run_outcome, Last_run_msg = Js. last_outcome_message + Cast (nullif (JS. last_run_outcome, 1) as varchar (2 )), Job_created = J. date_created, Job_modified = J. date_modified From MSDB. DBO. sysjobs J Inner join MSDB. DBO. sysjobservers JS on J. job_id = Js. job_id Inner join MSDB. DBO. syscategories JC on J. category_id = JC. category_id Where J. Enabled = 1 and JS. last_run_outcome in (0, 1, 3, 5)

4.5. view the image status

You can use the following script to view the image status or the image monitor.

 

Select DM. mirroring_partner_instance, DM. mirroring_role, DM. mirroring_state, DM. receiving_witness_state, DM. mirroring_safety_level, DM. mirroring_witness_name From SYS. database_grouping ing DM Join SYS. Databases D on (DM. database_id = D. database_id) Where (D. Name = n 'dbname') and mirroring_guid is not null

4.6. view log shipping status

You can run the following script on the monitoring server to view the log transfer status.

 

Use master Go Exec sp_help_log_shipping_monitor Go Exec sp_help_log_shipping_monitor_primary 'testserver1', 'northwind' Go Exec sp_help_log_shipping_monitor_secondary 'testserver2', 'northwind' Go

4.7. view the backup status

4.7.1. Check whether the backup is successful

4.7.2. Verify the backup set (Restore verifyonly)

4.7.3. Conduct regular database disaster recovery drills (including Backup Recovery and ha)