2-year SQL Server DBA tuning Summary

2YearSQL Server DBAOptimization Summary

When I was a dBA for two years, I felt that some things needed to be shared and discussed with you.

Book order

1. "in-depth analysis of SQL Server 2008 series" is the technical insider series of MSSQL 2005. I am also interested in version 2012. The technical insider series is my first book. It covers a large amount of content, but it's all just a few clicks. So many of them can be savedcarefully.

2. troubleshooting SQL server a guide for the accidental DBA is my earliest book on Performance Tuning. The link has been provided for download, but you need to register sqlservercenter, which is a well-known website in SQL Server. Many foreign Daniel.

3. The online document is the help document that comes with SQL Server installation. The content is comprehensive and scary, and contains almost all the content in the technical insider series.

4. the. gurus. guide. to. SQL. server. architecture. and. internals is the core of SQL Server 2000. From the perspective of software development, SQL Server 2000 is well known for its depth. Unfortunately, it is too early to die. The understanding of the SQL Server framework mainly comes from this book. Unfortunately, there is no Chinese version.

5. The second book on performance tuning that comes into contact with SQL Server 2008 Kernel Analysis and troubleshooting first focuses on tuning the principles. The book is divided into two parts: the first part is the principle, and the second part is performance tuning. It is also a good book, which provides a detailed explanation of the extended Event function. I have never seen it in other books.

The 2012 English version of the book has been published.

6. Microsoft SQL Server enterprise-level platform management practice is a rare Chinese book. The book is written in line with the Chinese psychology and is suitable for reference. There are performance tracking adjustments, from capture to processing.

7. SQL Server survival tips and. gurus. guide. to. SQL. server. architecture. and. internals is the same author. This book focuses mainly on SQL Server 2005 and the previous one, which gives a deep dive into individual points. There are too few shortcomings.

8. SQL Server 2008 query performance tuning this book is a more practical book that describes the discovery of various bottlenecks, performance baseline resume, and performance analysis from the perspective of queries and stored procedures, this section describes possible performance issues.

9. pro SQL Server 2008 service broker explains how to use service brokers and asynchronous message processing programs in many large companies, the book is clear and easy to accept around a large example.

10. About policy management in "Pro SQL Server 2008 Policy-based management", I personally think it is quite chicken.

 

Security

The owner is a DBA of a small company, so there is little security to use, so it manages some permissions and passwords.

Availability

SQL Server 2012 provides multiple availability solutions: 1. Log transmission, 2. database replication, 3. database image, and 4. alwaysonline.

1. Log transmission, which is the prototype of database images. Try transfer and redo logs without database images

2. There are many types of Database Replication: snapshot, transaction, and merge. Transaction replication is the most widely used. From SQL Server 2000 to SQL Server 2005, transaction replication has been improved. For more information, see the online documentation.

3. database images: for databases that do not require read/write splitting, database images are the most widely used availability solutions. The most prominent advantage of database images and other ratios is the ease of switching.

High Performance

Performance tuning should be a major concern for DBAs. Performance Tuning is the index, and the most demanding performance index is essential. The execution time = running time + waiting time for a performance. I think this formula is classic. When you have no clue, you can help you sort out how to troubleshoot the problem. Performance Tuning must be very familiar with performance indicators.

 

Performance baseline

When you have just started a company and have no idea about the current load of the company's database, you must first create a database performance baseline. Some people may ask what baseline can be used for. Many people feel useless. I also feel useless when I first started. However, performance baseline is the beginning of performance tuning and monitoring.

 

Generally, a formal company uses stress tests to predict the performance boundary of the server and the performance indicators after reaching the performance boundary. If the performance baseline is close to the performance boundary, consider changing the server or adding the server. This is a use of performance baselines.

 

To get a server, I will first make a performance baseline, which is the performance index of the database when the server is running normally. I will capture 24-hour performance indicators as a performance baseline (refer to my related articles:SQL ServerPerformance baseline and monitoring,SQL ServerPerformance Tuning(Performance baseline)).

 

The following are the indicators I used to capture

CPU:

\ Processor (_ total) \ % processor time
\ Processor (_ total) \ % privileged time

\ Sqlserver: SQL
Statistics \ batch requests/sec
\ Sqlserver: SQL statistics \ SQL compilations/sec
\ Sqlserver: SQL statistics \ SQL re-compilations/sec
\ System \ processor Queue Length
\ System \ context switches/sec

Memory:

\ Memory \ available
Bytes
\ Memory \ pages/sec
\ Memory \ page faults/sec
\ Memory \ pages input/sec
\ Memory \ pages output/sec
\ Process (sqlservr) \ private bytes
\ Sqlserver: Buffer Manager \ buffer cache hit ratio
\ Sqlserver: Buffer Manager \ page life expectancy
\ Sqlserver: Buffer Manager \ lazy writes/sec
\ Sqlserver: Memory Manager \ Memory Grants pending
\ Sqlserver: Memory Manager \ target server memory (KB)
\ Sqlserver: Memory Manager \ Total server memory (KB)

Disk:

\ Physicaldisk (_ total) \ %
Disk time
\ Physicaldisk (_ total) \ current disk Queue Length
\ Physicaldisk (_ total) \ avg. Disk Queue Length
\ Physicaldisk (_ total) \ disk transfers/sec
\ Physicaldisk (_ total) \ disk Bytes/sec
\ Physicaldisk (_ total) \ avg. Disk SEC/read
\ Physicaldisk (_ total) \ avg. Disk SEC/Write

SQL Server:

\ Sqlserver: Access
Methods \ freespace scans/sec
\ Sqlserver: Access Methods \ full scans/sec
\ Sqlserver: Access Methods \ table lock escalations/sec
\ Sqlserver: Access Methods \ worktables created/sec
\ Sqlserver: General Statistics \ processes blocked
\ Sqlserver: General Statistics \ User connections
\ Sqlserver: latches \ Total latch wait time (MS)
\ Sqlserver: locks (_ total) \ lock timeouts (timeout & gt; 0)/sec
\ Sqlserver: locks (_ total) \ lock wait time (MS)
\ Sqlserver: locks (_ total) \ Number of deadlocks/sec
\ Sqlserver: SQL statistics \ batch requests/sec
\ Sqlserver: SQL statistics \ SQL re-compilations/sec

I will not explain what the indicators mean. You can open perfmon and read the instructions one by one.

If you already have performance indicators, you can generate alerts based on the performance baseline resume (SQL ServerPerformance baseline and monitoring.

 

Performance running performance problem analysis:

After the baseline is set up, monitoring is also set up, and an alarm is triggered. According to the lecture about tuning, the book will start to separate, divided into CPU bottleneck, Io bottleneck, or memory bottleneck. I have no need to talk about the confirmation of these bottlenecks here. In previous articlesSQL ServerPerformance Tuning (Io),SQL ServerPerformance Tuning (CPU),SQL ServerPerformance Tuning (memory)All of them. Identify bottlenecks.

 

In fact, these methods for identifying bottlenecks are not comprehensive enough. experience is required to identify bottlenecks because performance problems often occur. Instead, they are not an indicator, but a batch of indicators have problems, for example, if your index is not properly built, it may cause full table scanning, Io increase, CPU spike, and paging in memory, so it is sometimes difficult to judge.

 

If it is determined that the performance problems such as I/O, CPU, and memory are caused. In the final analysis, there are only two methods, and one is adjustment. 2. hardware upgrade.

 

If the problem occurs, it is urgent to solve the problem. 1. Use top 10 I/O and top 10 CPU to view the statements to be optimized and tune according to the execution plan. In addition, profiler is used, provided that the current server allows you to use profiler. An extended event occurs after 2008, which may be able to be handled. However, I have not tracked the extended Event, and there are not many related materials.

 

So how can we determine the statements with a large amount of memory? The memory is a bit special. SQL Server puts the data in the buffer pool, which can be used by everyone. The memory pressure is divided into internal and external, internal memory pressure is caused by the SQL server itself, and external memory pressure is calculated by other processes (related only can you view the SQL Server 2005 troubleshooting White Paper ).

Memory bottleneck occurs, that is, the buffer pool is full. It is easy to clear the original buffer pool data to store the newly read data, which statement reads the most and which statement uses the most memory (For details, refer to Microsoft SQL Server Enterprise platform management practices).

 

If a problematic SQL statement has been located, the next step is to optimize it. The most important thing to use in it is the execution plan. Different people have different ideas on how to optimize SQL statements according to execution plans. Optimization Methods and features. Therefore, do not upgrade to the nearest possible value. However, the running time is mainly as follows: Execution Plan, statistics, and index.

Performance wait Problem Analysis:

Wait time: Lock wait and lock wait.

There are three articles about resource waiting ,《SQL ServerPerformance TuningWait event,SQL ServerPerformance TuningWait event(2),SQL Server 2008Performance TuningSessionLevelWait eventThe author is the same person. You can adjust the waitevent angle. So before that, you need to first understand the meaning of the relevant indicators in SYS. dm_ OS _wait_stats.
SQL Server 2005 waits and queues provides a detailed description of each metric. SQL Server 2008 query performance tuning has a good SQL statement about collection congestion.

 

When the collected congestion occurs at the lock level, there is no other way to useIndexOr addNolock, Or enable the isolation level of the snapshot, but I personally do not agree with the Snapshot isolation level. Some people have already tested it. When the Snapshot isolation level is started, the load of tempdb increases significantly, solving one problem leads to another more difficult problem. If the SELECT statement is used, overwrite the index as much as possible to reduce the deadlock caused by referencing Multiple indexes and update. Of course, this depends on the specific system running environment.

 

If it appears on the latches, the larger indicators are generally pageiolatch_x series, writelog, pagelatch_x, and pagelatch_x on tempdb.

 

Pageiolatch_x is generated while waiting for disk I/O. The reason that disk I/O occurs is that there is no data in the memory, that is, the memory is not enough to see this situation, so add the memory. Or optimize the business rules.

 

Writelog waits when writing logs, and logs are written in sequence. In essence, the disk is not fast enough when multiple transactions are written into logs. If there is any problem, we recommend that you 1. separate logs and data files and place them on two independent disks or raid, 2. replace it with a faster disk.

 

Pagelatch_x is the latch generated by operating the buffer pool data page. If the waiting time is too large, it is easy to call this page with too many sessions, which reduces the access to the page. 1. Use index optimization statements to minimize the number of pages read by SQL statements. 2. Try to scatter data on multiple pages. 3. read/write splitting.

 

Pagelatch_x on tempdb mainly occurs on GAM, SGAM, and PFS pages, because order by, group by, temporary table, table variable, and lazy operator. All use tempdb, which opens up a space. If the concurrency is large. The pagelatch_x on tempdb will wait a lot. 1. Reduce the sort operator in the execution plan and the lazy operator. 2. Expand the data file of tempdb. The upper limit is the number of CPUs (the condition is that the capacity of tempdb should be balanced ).