SQL Server database performance Basics

The performance of SQL Server databases has not been written for a long time. Before the system is officially launched, DBAs generally need to test the server performance. For example, you have many servers and some are web servers, some are cache servers, some are file servers, and some are database servers. The performance of these servers is relatively good,

The performance of SQL Server databases has not been written for a long time. Before the system is officially launched, DBAs generally need to test the server performance. For example, you have many servers and some are web servers, some are cache servers, some are file servers, and some are database servers. The performance of these servers is relatively good,

Basic SQL Server database performance

I haven't written an article for a long time. Before the system is officially launched, DBAs usually test the server performance.

For example, you have many servers, some are web servers, some are cache servers, some are file servers, and some are database servers.

The database server has relatively good performance, such as disks, memory, and CPU,

Select one of the servers asDatabase ServerWe need to test the performance of each server before.

You also need to set some hardware parameters, such as setting disk controller parameters. For more information, see the difference between Writeback and Writethrough.

So how to test it? How can we get the test indicators?

You can refer to this article: SQL Server Database Engine Performance Tuning Basics

Body

As the market share of SQL Server grows over time, more and more SQL Server performance tuning needs.

Different teams and individuals use various methods to improve the performance of SQL Server servers,

In addition, I think these documents that record the basic steps of SQLSERVER troubleshooting and improve the performance of various programs are meaningful to the SQLSERVER community.

Disk

To effectively run SQLSERVER, monitoring and optimizing SQLSERVER's disk subsystem is an important aspect.

We need to clarify the disk performance requirements.

Avg. Disk Sec/Read: the average value of data Read from the Disk per second.

The following list shows the range of the counter value and indicates the meaning of the range of the counter.

Less than 10 MS-Very good
Between 10-20 MS-Yes
Between 20-50 MS-Slow, need attention
Over 50 MS-Severe I/O bottlenecks

Disk Performance Testing Tool

(1) CrystalDiskMark

(2) HDTUNE hard disk detection and Repair Tool

(3) ATTO Disk Benchmark

Identify I/O bottlenecks

PhysicalDisk Object: Avg. Disk Queue: Average number of disk read/write requests queued for the selected physical disk during sampling

If the length of your disk queue exceeds twice the usage peak of the SQLSERVER disk, it may cause an I/O bottleneck.

Avg. Disk Sec/Read: Average value of data read from the disk per second

Avg. Disk Sec/Write: Average time of writing data to the disk,Avg. Disk Sec/ReadReference indicators

Physical Disk: % Disk Time the Disk Time is the percentage of the Time taken when the selected Disk drive is busy processing read/write requests. One metric is that if this value is greater than 50%, there is an I/O bottleneck.

Avg. Disk Reads/Sec: The ratio of read operations on the disk. Make sure that this number is less than 85% of the Disk Throughput. When this value exceeds 85%, the disk access time will increase exponentially.

Avg. Disk Writes/Sec c: The write operation rate on the disk. Make sure that this number is less than 85% of the Disk Throughput. When this value exceeds 85%, the disk access time will increase exponentially.

For more information, see How to Create a performance counter set: http://technet.microsoft.com/en-us/library/cc722148.aspx

Disk Drive location:

For different purposes, you need to use different drives to store the following things.
Independent disk latency requirements:
Database larger than 15 ms

Transaction log is greater than 2 ms

Tempdb database is larger than 2 ms

Disk speed priority

It means that Tempdb is placed on a separate physical disk, transaction log files are placed on a separate physical disk, data files are placed on a separate physical disk, and the operating system is placed on a separate physical disk,

Store database backup files on separate physical disks

In general, our practice: it is impossible to have so many separate physical disks, which are generally stored as disk arrays.

C Disk Operating System File

Data Files and transaction log files and Tempdb data files and Tempdb log files on disk D

Store the backup files in the database on the edisk

When formatting a disk, do not use the disk to store SQL server data files and log files.Default disk allocation Unit

Use64 k cluster size Allocation UnitTo format the disk, you can refer to this article: If procmon.exe is used to monitor the logwrite size of SQLSERVER.

Anti-Virus Software

Anti-virus software may cause problems with some functions of SQLSERVER. It is very important to exclude database files from the scanning range by using the anti-virus software exclusion function (with the exception of soft scanning)

The following file types must be excluded

*. Mdf, *. ndf, *. ldf, *. bak

Backup of YourSQLDba failed due to anti-virus software

The article said that because the backup folder is scanned by anti-virus software and the backup folder is locked, SQLSERVER failed to back up the database.

Memory

Always allocate the maximum memory to the SQLSERVER instance and set it in the server properties.

Note: The maximum memory setting is only valid for the buffer cache of SQLSERVER, excluding some memory functions required by SQLSERVER, such as replication.

(The maximum memory setting of SQLSERVER2012 can limit the memory of buffer cache and non-buffer cache)

To specify the memory usage of the Non-Buffer Pool, use the following description.

Memory Requirements outside the SQL Server's buffer pool (this requirement is not to say that after you set the maximum memory of SQL Server, the remaining memory requirements, whether you have set the maximum memory of SQL Server

The following items are fixed memory to be consumed by the server. Whether your server memory is 4G, 8G, or 16G, the following items will occupy the server memory)

(1) The operating system needs to occupy 2 GB of memory. For a 64-bit operating system, the memory occupied by the operating system must not exceed 3 GB.

(2) a multiple of SQL Server worker threads. You can set the maximum worker thread in SQL Server attributes,

Each thread uses MB memory (X86 server)

Each thread uses 2 MB memory (X64 server)

Each thread uses 4 MB memory (Itanium server)

Note: The MB memory is storedThe data structure and related information of the thread, excluding the data.

Why is the thread memory allocated by various servers different? This is allocated by the operating system, and SQLSERVER has not made any special settings!

If you set the maximum number of working threads to 10, and the server is X86, the server uses up 10 threads, the occupied memory is 10*0.5 MB = 5 MB.

(3) 1 GB of multi-page memory, link server and other SQL Server peripheral programs

(4) programs running on the server may occupy 1 ~ 3 GB memory, such as backup program

Example

For example, an 8-core server with 16 GB memory runs SQLSERVER2012 X64 and a third-party backup program runs on it. You can refer to the following list

(1) 3 GB for Windows (2 GB for 32 Bit Windows)

(2) 1 GB for SQLSERVER worker threads (576× 2 MB)

Maximum number of worker threads automatically configured in combination with various CPU and SQLSERVER versions
32-bit computer 64-bit computer
& Lt; = 4 256 512
8 288576
16 352 704
32 480 960

(3) 1 GB for MPAs, etc. (multi-page apply)

(4) 1 ~ 2 GB for the backup program.

You can find more information about the Max working thread options http://technet.microsoft.com/en-us/library/ms187024 (v = SQL .105). aspx

(For SQL Server 2008 ).

Enable Lock Pages in Memory

The Windows Group policy determines which Windows Account enables the process to keep its data in the physical memory, preventing the operating system from switching program data from the physical memory to the virtual memory on the disk.

This can improve your performance, especially when the memory is under pressure.

Optimize the TempDB Database

By default, the Tempdb database has only one data file and transaction log file. However, to optimize the performance, follow the best practices given below

Storage plan of the TempDB Database

(1) set the recovery mode of the Tempdb database to simple (simple by default). The simple mode can automatically recycle the log space to minimize the need for log space.

(2) do not allow automatic growth of Tempdb data files, which can reduce the CPU overhead for managing dynamic file growth.

For the Tempdb database, multiple data files can be separated (the total number of data files in the Tempdb database = the number of CPU Logic processors, for example, the 8-core server can be divided into 8 data files)

The size of each data file must be the same.

(3) Try to store these data files on different disk drives to use parallel I/O

(4) TempDB data files and log files should be stored on a fast disk (if possible, we recommend that you store them on a disk with RAID 1)

(5) use RAID-10 or SSD Disks

(6) pre-define the file size of the Tempdb Database

(7) set the total size of Tempdb to 25% of the largest database in the current database instance

(8) set the automatic increase of Tempdb data files to a fixed size smaller than 200 MB

(9) You should set the number of data files in the Tempdb database to be the same as the number of logical CPUs. A maximum of 8 data files can be created.

CPU Optimization

Set the maximum Degree of Parallelism (Max Degree of Parallelism)

Define how many logical CPUs can execute queries in parallel

Many Microsoft products, such as SharePoint and Dynamics CRM, set this setting to 1, which is recommended.

For SharePoint LOB applications, when you see many CXPACKETS waiting types in your SQLSERVER server,

You should consider setting this setting (Max Degree of Parallelism) to 1.

Index fill factor

If your SQLSERVER server has a very high transaction volume TPS (transaction per second)

Your index has a relatively high fragmentation level. Consider setting the fill factor to "80%"

Use the following SQL statement to check index fragmentation.

SELECT  DB_NAME(ps.database_id) AS 'Database Name' ,        OBJECT_NAME(ps.OBJECT_ID) AS 'Database Object' ,        ps.index_id ,        b.name ,        ps.avg_fragmentation_in_percentFROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ps        INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID                                       AND ps.index_id = b.index_idWHERE   ps.database_id = DB_ID('ReportServerTempDB')ORDER BY ps.avg_fragmentation_in_percent DESCGO 

Use Performance Monitor (Perfmon.exe) to Monitor system Performance

To capture specific performance indicators of SQLSERVER, you need to use the following counters

Processor: % Processor Time: The average should be lower than 75% (preferably lower than 50%)

System: Processor Queue Length: Each logical CPU should be less than 2 on average. For example, on a 2 logical CPU machine, it should be kept at 4

Memory-Pages/sec: The average should be less than 20 (preferably less than 15%)

Memory-Available Bytes: The available memory should be kept above 50 MB.

Physical Disk-% Disk Time:
Physical Disk-Avg. Disk Queue Length: The average value of each disk should be less than 2. For example, for a RAID 5 disk, this indicator should be less than 10 on average.

Physical Disk-Avg. Disk Reads/sec:Depending on the CPU and disk size, it should be less than 85% of the corresponding Disk Throughput.

Network Interface-Bytes Total/sec: Used to measure network bandwidth

SQL Server: Buffer Manager-Page Life Expectancy: Used for memory statistics, should be kept at 300 seconds
SQL Server: Calculates the number of user connections to estimate the memory used.
SQL Server: Databases-Transactions/sec: Number of transactions per second
SQL Server: Databases-Data File (s) Size KB: Used to measure the size of database data files and the performance of the disk subsystem.
SQL Server: Databases-Percent Log: Measure the performance of the disk subsystem.

If anything is wrong, you are welcome to make a brick o