2-year SQLServerDBA tuning Summary

Announcement 2-year SQLServerDBA tuning Summary 2-year SQLServerDBA tuning Summary 2-year dba tuning summary when 2-year dba I think there are some things that need to be discussed with you, first book. Documentary 1. In-depth analysis of SQLServer2008 series this is the technical insider series of mssql2005. If you are interested in version 2012, you can see that the technical insider series is

Announcement 2-year SQL Server DBA tuning Summary 2-year SQL Server DBA tuning Summary 2-year dba tuning summary when 2-year dba I think there are some things that need to be discussed with you, first book. Documentary 1. In-depth analysis of SQL Server 2008 series, which is the technical insider series of mssql 2005. If you are interested in version 2012, you can see that the technical insider series is

Announcement

2-year SQL Server DBA tuning Summary

2-year SQL Server DBA tuning 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, website space, and performance baseline, that is, the performance indicators of the database when the server is running normally. I will capture 24-hour performance indicators as a performance baseline (see my related articles: SQL Server performance baseline and monitoring, SQL Server Performance Tuning (performance baseline )).

The following are the indicators I used to capture

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

I will not explain what the indicators mean. For us servers, 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. previous articles (SQL Server performance baseline and monitoring) I have provided how to monitor performance using powershell.

Performance running performance problem analysis: