Basic performance of SQL Server database

Long time no article, before the system officially launched, the DBA will generally test the performance of the server

For example you have a lot of servers, some do Web server, some do cache server, some do file server, some do database server

The server to do the database server performance is relatively good, disk, memory, CPU and so on,

Then you need to test the performance of each server before choosing one of the servers as the database server

And you need to set some hardware parameters, such as setting the parameters of the disk controller, refer to the article: writeback and Writethrough differences

So how exactly do you test it? How do you get the test indicators?

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

Body

With the development of the market share of SQL Server over time, there is a growing demand for SQL Server performance tuning.

There are different teams and individuals that use a variety of methods to improve the performance of SQL Server servers

And I think these are the basic steps for documenting SQL Server troubleshooting and the documentation that improves the performance of various programs is meaningful to the SQL Server community

Disk

Monitoring and optimizing the disk subsystem of SQL Server is an important aspect for SQL Server to run efficiently.

We need to be very specific about disk performance requirements

AVG. Disk Sec/read This counter refers to the average of data read from disk per second

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

Less than ten MS- very good
Between 10-20 MS- can also
Between 20-50 MS- slow, need attention
Greater than ms– severe I/O bottlenecks

Disk Performance Testing Tools

(1) CrystalDiskMark

(2) Hdtune HDD detection and Repair Tool

(3) ATTO Disk Benchmark

Identify I/O bottlenecks

PhysicalDisk Object:avg. Disk Queue: The average of disks read and write requests that are queued during sampling by the selected physical disk

If your disk queue length is often more than twice times the peak of SQL Server disk usage, that means there may be an I/O bottleneck

avg. disk Sec/read: Average of data read from disk per second

Avg . Disk Sec/write: Average time to write data to disk,avg. disk Sec/read Reference indicator

physicalDisk:%disk Time is the percentage of time that the selected disk drive spends busy processing read-write requests, and one indicator is that if the value is greater than 50%, there is an I/O bottleneck

Avg. Disk reads/sec: The ratio of read operations on disk. Make sure that this number is less than 85% of the disk throughput. When this value exceeds 85% disk access time will grow exponentially

AVG. Disk writes/sec C: The ratio of write operations on disk. Make sure that this number is less than 85% of the disk throughput. When this value exceeds 85% disk access time will grow exponentially

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

the location of the disk drive :

For different purposes, you need to use a different drive to store the following things
Stand-alone disk latency requirements:
Database greater than 15ms

Transaction log greater than 2ms

tempdb database greater than 2ms

Priority of Disk speed

This means that tempdb is placed on separate physical disks, the transaction log files are placed on separate physical disks, the data files are placed on separate physical disks, the operating system is placed on separate physical disks,

The database backup file is placed on a separate physical disk

Generally we do: there is no way to have so many separate physical disks, usually is to do the storage of the disk array

C-Drive operating system files

D-Drive data files and transaction log files and tempdb data files and tempdb log files

e-Drive Database backup file

When formatting a disk, do not use the default disk allocation unit for the disk where SQL Server data files and log files are to be stored

Use the 64k cluster size Allocation Unit to format the disk, as for why you can take a look at this article: How to use Procmon.exe to monitor the logwrite size of SQL Server

Anti-virus software

Antivirus software will be a problem with some of the functions of SQL Server, using the elimination of anti-virus software to exclude the database files in the scope of the scan is very important (into the killing soft scan exception)

The following file types are required to be excluded

*.mdf, *.NDF, *.ldf, *.bak

Related article: Antivirus software causes YOURSQLDBA backup to fail

The article said that because antivirus software scanned the backup folder and locked the backup folder, SQL Server backup database failed

Memory

Always assign the largest memory to the SQL Server instance set on the Servers property

Note: The maximum memory setting is only valid for the buffer cache portion of SQL Server and does not include some of SQL Server's memory-required features, such as replication

(The maximum memory setting for SQLSERVER2012 can already limit the memory in buffer cache and non-buffer cache portions)

To indicate the memory footprint of the Non-buffer Pool, use the following instructions

Memory requirements outside of SQL Server's buffer pool (this requirement does not mean that you have set SQL Server maximum memory after the remaining memory needs, regardless of whether you have set SQL Server's maximum memory

The following items are the memory that the server needs to be fixed, regardless of whether your server memory is 4g,8g or 16G, the following items will be fixed to occupy the server's memory)

(1) The operating system needs to occupy 2GB of memory, if it is a 64-bit operating system, the operating system consumes less than 3GB

(2) A multiple of SQL Server worker threads, you can set the maximum worker thread

0.5MB memory per thread (X86 server)

2MB memory per thread (X64 server)

Each thread uses 4MB of memory (Itanium server)

Note: 0.5MB memory holds the data structure and related information of the thread itself, excluding data

Why do various servers allocate different thread memory, this is assigned by the operating system, SQL Server does not make special settings!

If you set the maximum number of worker threads to 10, the server is X86, and the server runs out of 10 threads, the memory used is 10*0.5MB=5MB memory

(3) 1GB of multi-page memory footprint, linked servers and other SQL Server peripheral programs occupy

(4) Programs running on the server may consume 1~3GB memory, such as a backup program

Example

For example, a 8-core server, 16GB of memory, running SQLSERVER2012 X64, running a third-party backup program, you can refer to the following list

(1) 3GB to Windows (2GB for + windows)

(2) 1GB to SQL Server worker thread (576 X2MB approximate)

Maximum number of worker threads automatically configured for various CPU and SQL Server version combinations
Number of CPUs 32-bit computer 64-bit computer
<=4 256 512
8 288 576
16 352 704
32 480 960

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

(4) A GB to the backup program.

You can find more information about "max worker options" http://technet.microsoft.com/en-us/library/ms187024 (v=sql.105). aspx

(For SQL Server 2008).

Turn on the lock Pages in Memory option

Windows Group Policy determines which Windows account enables a process to keep his data in physical memory, preventing the operating system from swapping program data from physical memory for virtual memory on disk

This can give you a performance boost, especially when you're experiencing memory pressure.

Optimization of the TempDB database

By default, the tempdb database has only one data file and a transaction log file. However, for performance optimization, follow the recommendations given below for best practices

Storage plan for the tempdb database

(1) Setting the recovery mode of the tempdb database as simple (default is simple), simple mode can automatically reclaim log space to keep log space requirements to a minimum

(2) Do not allow tempdb data files to grow automatically, which can reduce the CPU overhead of managing dynamic file growth

For the tempdb database, you can separate multiple data files (the total number of tempdb database data files =cpu The number of logical processors, such as a 8-core server can be divided into 8 data files)

The size of each data file will be the same

(3) Try to store these data files on a different disk drive to take advantage of the parallel I/O

(4) TempDB data files and log files should be stored on a faster disk (if possible on a RAID 1 disk)

(5) using RAID-10 or SSD disk

(6) Pre-defined file size for tempdb database

(7) Set the total tempdb size to 25% of the largest database in the current DB instance

(8) Set tempdb data file auto-grow fixed size less than 200MB

(9) You should set the tempdb database to the number of data files consistent with the number of logical CPUs, up to a maximum of 8 data files

Optimization of CPU

Setting the maximum degree of parallelism (max degree of Parallelism)

Defines how many logical CPUs can execute a query in parallel

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

For a SharePoint LOB application, when you see a lot of cxpackets waiting types on your SQL Server servers,

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

Index fill factor

If your SQL Server servers have very high transaction volume TPS (transaction per second)

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

and use the following SQL statement to detect 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   

Monitor system performance with Performance Monitor (Perfmon.exe)

In order to capture SQL Server specific performance metrics, you need to use the following counters

Processor:% Processor time: Average should be less than 75% (preferably less than 50%)

system:processor Queue Length: Average per logical CPU should be less than 2, for example on a 2 logical CPU machine, he should remain at 4

memory-pages/sec: Average should be less than 20 (preferably less than 15%)

memory-available Bytes : The available memory should remain above 50MB

Physical disk-% Disk Time:
physical disk-avg. Disk Queue Length : Each disk should be less than 2 average, for example: a RAID5 disk, which should average less than 10

physical disk-avg. Disk Reads/sec: Depending on the CPU and disk size, should be less than 85% of the relative disk's throughput

Network interface-bytes total/sec : Used to count net bandwidth parties

SQL Server:buffer manager-page life expectancy: For statistical memory, should be kept in 300 seconds
SQL Server: General statistics on the number of users ' connections to approximate 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 the database data file, measuring the performance of the disk subsystem
SQL server:databases-percent Log : Measure the performance of the disk subsystem

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o