2-year summary of SQL Server DBA tuning

Original: 2-year summary of SQL Server DBA tuning

2 years SQL Server DBA Summary of tuning

When I was 2 DBA I felt that there was something that needed to be shared with everyone to discuss the book first.

Book List

1. "In-depth analysis of SQL Server 2008 series" This is the MSSQL 2005 Technology Insider series. Version 2012 also out of interest can see, Technical Insider Series is I contact the earliest book, inside the content is very large, but are donuts. So many of them can be carefully tasted, look back again.

2. Troubleshooting SQL Server A Guide for the Accidental DBA This book is the first book I have touched on performance tuning. Links have been given to download, but need to register Sqlservercenter, this site is a more famous SQL Server site. Many foreign Daniel.

3. The online documentation, the help documentation that comes with the SQL Server installation, is completely scary and contains almost everything from the Technical Insider series.

4. "The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals" This book is the core of SQL Server 2000, from the perspective of software development, SQL Server 2000, very in-depth author is also very famous, but died too early. The SQL Server framework is understood mainly from this book, but there is no Chinese version.

5. "SQL Server 2008 core profiling and Troubleshooting" The second book on performance tuning, when the book is more absolute, the principle of the first tuning. The book is divided into 2 parts the first part is the principle, the second part is the performance tuning. is also a good one, the book on the function of extended events to do a more detailed explanation. I haven't seen it in any other books.

The 2012 original English version of the book is already out.

6. "Microsoft SQL Server Enterprise Platform Management practice" is a rare domestic good book, book writing is in line with the Chinese mentality, pointing to the problem itself, it is suitable for reference. It is very practical to talk about performance tracking adjustments, from capture to processing.

7. "SQL Server Survival Cheats" and "The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals" are the same authors, this book is mainly for SQL Server 2005 and the previous copy of the individual points are very deep, the shortcomings of the things too little.

8. "SQL Server 2008 Query Performance Tuning" This book is a practical book, the discovery of various bottlenecks, performance baseline resume, from the perspective of query, storage process, analysis of performance, to explain the potential performance problems.

9. "Pro SQL Server Service Broker" explains about Service Broker, asynchronous message handlers, a lot more than the larger companies will use, I know that the new egg is the use of this, the book around a large example is clear, easy to accept.

10. Pro SQL Server policy-based Management knowledge of policy management, personally feel more chicken.

Security

The landlord is the DBA of a small company so there is less security to use, to manage some permissions and passwords

Availability of

To SQL Server 2012 implements a variety of availability scenarios, 1. Log shipping, 2. Database replication, 3. Database mirroring, 4.alwaysonline.

1. Log shipping, landlord feel is the embryonic form of database mirroring. Try to send and redo logs without database mirroring

2. Database replication, database replication has more than one classification: snapshot, transaction, merge. Transactional replication is the most widely used, from SQL Server 2000 to SQL Server 2005 transactional replication has been improved quite to the specific can see online documentation.

3. Database mirroring, I for a database that does not require read and write separation, database mirroring is the most widely used usability scheme, database mirroring and other advantages than the most prominent advantage is easy to switch.

Performance

The head of the DBA should be performance tuning. Performance tuning The big Head is the index, and the most demanding performance index is essential. A performance mainly embodies the execution time, execution time = run time + wait time. I think this formula is classic. When you don't have a clue, you can help you figure out how to troubleshoot the problem. Performance tuning must be very familiar with the performance indicators.

Performance baselines

When you've just entered a company that knows nothing about the current load on the company database, the first thing to do is create a database performance baseline. Some people will ask what the baseline can be used for, and many feel useless, and I feel useless when I first entered the job. But a performance baseline is the start of a performance tuning, monitoring.

Generally more formal companies, a business before the launch will pass the stress test to predict the performance of the server where the border, to reach the performance of the border after the performance of various indicators of how. If the performance baseline is close to the performance boundary, then it's time to consider swapping servers or adding servers. This is one of the uses of performance baselines.

Get a server I'll do it first. Performance baselines, performance baselines are the performance of the database when the server is running. I will crawl 24-hour performance metrics as a performance baseline (see my related article:SQL Server performance baselines and monitoring ,SQL Server performance Tuning ( Performance Baseline ).

Here are the metrics I used to crawl

Cpu:

\processor (_total) \% Processor time
\processor (_total) \% Privileged Time

\sqlserver:sqlstatistics\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\availablebytes
\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:accessmethods\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 > 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

What does the indicator mean? I won't explain, you can open perfmon and read the instructions.

Assuming you have the performance metrics now, you can alert based on the performance baseline resume, which I have provided in the previous article (SQL Server performance baselines and monitoring ) to monitor performance using PowerShell.

Performance Performance Problem Analysis:

The baseline was built and the monitoring was built, and the alarm came up. According to the book on tuning will begin to separate, divided into CPU bottlenecks, IO bottlenecks, or memory bottlenecks. Confirmation of these bottlenecks I don't need to say that in the previous article SQL Server Performance tuning (io),SQL Server Performance tuning ( CPU), which is described inSQL Server Performance Tuning (memory) . How to identify each bottleneck.

In fact, these identification bottlenecks are not comprehensive, bottleneck confirmation needs experience, because often the performance problem, not an indicator, but a number of indicators are problematic, such as when you do not build the index, resulting in a full table scan, IO becomes larger, CPU high, memory appears paging, so sometimes very difficult to judge.

If it has been determined that part of the performance problems such as IO,CPU, memory. In the final analysis, there are only 2 methods, 1. Adjustment. 2. Hardware upgrade.

If the problem arises, be quick to solve the problem 1. Use the top Io,top CPU to view the statements that need to be optimized to tune according to the execution plan. There is also the profiler, provided that the current server also allows you to use the profiler. After 2008, there is an extended event, which may be handled by this, but I have not been involved in the tracking of extended events, and there are not many related data.

So how to determine how to use more memory statements, memory is a bit special, SQL Server put the data in buffer pool, everyone can use, memory pressure is divided into internal and external, internal SQL Server itself caused by memory pressure, External is the memory pressure of other processes (related only can view SQL Server 2005 Troubleshooting white paper).

A memory bottleneck is the buffer pool full, to clear the original buffer pool data in order to put the newly read data in the inside, then it is simple, that statement read most of which statement uses the most memory (details can view the Microsoft SQL Server Enterprise-level platform management practices).

So assuming that a problematic SQL statement has been located, then the next step is to optimize it, and the most critical thing to use is the execution plan. How to optimize SQL statements based on an execution plan different people think differently. The optimization method and each have the characteristic. So no longer to be promoted in order to avoid sweeping. But the running time is mainly the following: Execution plan, statistics, index.

Performance Wait Problem analysis:

Wait time: Lock wait, latch wait.

About resource waits, here are three articles,SQL Server Performance tuning wait Event,SQL Server performance tuning wait Event (ii),SQL Server Performance Tuning session level wait event The author is the same person. Adjusted by the waitevent angle. So before you need to know about the sys.dm_os_wait_stats in the relevant indicators mainly refers to what the meaning of this SQL Server out of a "2005 Waits and Queues" in detail describes the meaning of the various indicators. SQL Server 2008 Query Performance tuning has a good SQL statement on how to collect congestion.

When collecting jams if it appears at the lock level, then there is no other way to use the index or to add nolockon the SELECT statement, or to open the isolation level with the snapshot, but the personal comparison does not favor the snapshot isolation level, a friend has already tested, With a snapshot isolation level, the load increase in tempdb is significant, and one problem resolution leads to another more intractable problem. If the SELECT statement is used, use the overwrite index as much as possible to reduce the case where multiple indexes are referenced and the deadlock is update. Of course, this also depends on the specific system operating environment.

If it is on the latch, the general larger indicator is the Pageiolatch_x series, writelog,pagelatch_x,tempdb on the pagelatch_x.

Pageiolatch_x is generated when waiting for disk IO, the cause of disk IO is that there is no data in memory, that is, memory is not enough to appear this situation, then add memory. or optimize your business rules.

Writelog is written to the log when the waiting, the log is sequential write, the essence is the transaction when the disk speed is not fast enough to wait, if there is a problem recommendation 1. Separate the log and data files into 2 separate disks or raid,2. Change to a faster disk.

The pagelatch_x is the latch generated by manipulating the buffer pool data page. If the wait is too large, it is very simple to call this page too many sessions, then reduce the access to the page. 1. Use the index optimization statement to minimize the number of pages read by SQL. 2. Find ways to spread the page's data across multiple pages. 3. Consider read-write separation.

The pagelatch_x on tempdb mainly occurs on several pages of GAM,SGAM,PFS because order By,group by, temporary table, table variable, lazy operator. will be used to tempdb, which opens up a space. If the amount of concurrency is large. Then the pagelatch_x on tempdb will be a big wait. 1. Reduce the sort operator in the execution plan to reduce the lazy operator. 2. Expand the tempdb data file to the maximum number of CPUs (one condition is that the tempdb capacity is balanced).

2-year summary of SQL Server DBA tuning