2年SQL Server DBA调优方面总结
公告 2年SQL Server DBA调优方面总结 2年SQL Server DBA调优方面总结 当2年dba 我觉得，有些东西需要和大家分享探讨，先书单。 书单 1.《深入解析SQL Server 2008 系列》 这个就是mssql 2005 的技术内幕系列。2012版的也出了有兴趣可以看看，技术内幕系列是
公告
2年SQL Server DBA调优方面总结
2年SQL Server DBA调优方面总结
当2年dba 我觉得，有些东西需要和大家分享探讨，先书单。
书单
1.《深入解析SQL Server 2008 系列》 这个就是mssql 2005 的技术内幕系列。2012版的也出了有兴趣可以看看，技术内幕系列是我接触最早的书，里面内容涵盖量很大，但是都是点到为止。所以很多都是可以细细品味，回头再看的。
2.《Troubleshooting SQL Server A Guide for the Accidental DBA》 这本书是我接触最早的关于性能调优的书。链接已经给出可以去下载，不过需要注册SQLServerCenter ，这个网站是SQL Server 方面比较出名的网站。很多国外大牛。
3.《联机文档》也就是sql server 装机后自带的帮助文档，内容全面的吓人，几乎包含了技术内幕系列的所有内容。
4.《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》这本书是将sql server 2000的内核，从软件开发的角度来看SQL Server 2000，很深入作者也十分的出名，可惜死的太早。对sql server框架理解主要来源于这本书，可惜没有中文版。
5.《SQL Server 2008 内核剖析和故障排除》接触的第二本关于性能调优的书，真本书比较绝的地方时，先将原理再讲调优。全书分为2部分第一部分就是原理，第二部分是性能调优。也是不错的一本，书中对扩展事件的功能做了比较详细的解释。我在其他书上是没看到过的。
该书的2012英文原版已经出了。
6.《Microsoft SQL Server企业级平台管理实践》是一本少见的国产好书，书的编写很符合中国人心理，直指问题本身，很适合当工具书。其中有关于性能跟踪调整，从捕获到处理讲的很实际。
7.《SQLSERVER求生秘籍》和《The.Gurus.Guide.To.SQL.Server.Architecture.And.Internals》是同一个作者，这本书主要是针对SQL Server 2005和上一本一样对个别点讲的很深入，缺点讲到的东西太少。
8.《SQL Server 2008查询性能调优》这本书比较实用的一本书，讲了各个瓶颈的发现，性能基线的简历，从查询，存储过程角度出发，分析性能，讲解可能出现性能问题的点。
9.《Pro SQL Server 2008 Service Broker》 讲解关于Service Broker，异步消息处理程序，很多比较大的公司会使用，我知道的是新蛋是使用这个的，全书围绕一个大例子比较清晰，容易接受。
10.《Pro SQL Server 2008 Policy-Based Management》关于策略管理方面的知识，个人觉得比较鸡肋。
安全性
楼主是小公司的DBA所以关于安全性使用的比较少，就管理一些权限和密码
可用性
到SQL Server 2012实现了多种可用性方案，1.日志传送，2.数据库复制，3.数据库镜像，4.alwaysonline。
1.日志传送，楼主觉得是数据库镜像的雏形。没有数据库镜像那样试试的传送和redo日志
2.数据库复制，数据库复制有比较多的分类：快照，事务，合并。事务复制是被应用最广的，从sql server 2000到sql server 2005事务复制被改进了很对具体可以看联机文档。
3.数据库镜像，我对于不需要读写分离的数据库中，数据库镜像是被应用最广的可用性方案，数据库镜像和其他的比最突出的优点是切换方便。
高性能
DBA的大头应该是性能调优。性能的调优大头是索引，最求更高的性能索引是必不可少的。一个性能主要体现的执行时间上，执行时间= 运行时间+等待时间。这个公式我觉得很经典。当你没有头绪的时候能帮你梳理清楚应该怎么排查问题。做性能调优一定要对性能的指标十分熟悉。
性能基线
当你刚刚入职一家公司，对公司数据库现在的负载一无所知，那么一开始要做的事情就是创建一个数据库性能基线。有人会问基线能用来干什么，很多人感觉没用，我刚入职时我也觉得没用。但是性能基线是一个性能调优，监控的开始。
一般比较正规的公司，一个业务上线前会通过压力测试预计这个服务器的性能边境在哪里，到达性能边境之后各个性能指标的表现是如何的。如果如果性能基线接近了性能边界，到了这个时候，那么就要考虑换服务器或者加服务器了。这个是性能基线的一个用处。
拿到一个服务器我先会做一下性能基线，网站空间，性能基线也就是服务器在正常运转的时候数据库的性能指标的表现。我会抓取24小时的性能指标作为性能基线（可以看我相关的文章：SQL Server 性能基线和监控，SQL Server 性能调优(性能基线)）。
以下是我使用的抓取的指标
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 > 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
指标代表啥意思我就不解释了，美国服务器，你可以开perfmon，挨个看说明。
假设你现在已经有了性能指标了，那么你就可以根据性能基线简历告警了，以前的文章（SQL Server 性能基线和监控）中我已经提供了使用powershell如何监控性能。
性能运行性能问题分析：
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1359
52
How to use php-fpm for high-performance tuning
Jul 08, 2023 am 11:30 AM
How to use php-fpm for high-performance tuning PHP is a very popular server-side scripting language that is widely used to develop web applications and dynamic websites. However, as traffic increases, the performance of your PHP application may suffer. In order to solve this problem, we can use php-fpm (FastCGIProcessManager) for high-performance tuning. This article will introduce how to use php-fpm to improve the performance of PHP applications and provide code examples. one,
How to install, uninstall, and reset Windows server backup
Mar 06, 2024 am 10:37 AM
WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows
Summarize the usage of system() function in Linux system
Feb 23, 2024 pm 06:45 PM
Summary of the system() function under Linux In the Linux system, the system() function is a very commonly used function, which can be used to execute command line commands. This article will introduce the system() function in detail and provide some specific code examples. 1. Basic usage of the system() function. The declaration of the system() function is as follows: intsystem(constchar*command); where the command parameter is a character.
Windows Server 2025 preview version welcomes update, Microsoft improves Insiders testing experience
Feb 19, 2024 pm 02:36 PM
On the occasion of releasing the build 26040 version of Windows Server, Microsoft announced the official name of the product: Windows Server 2025. Also launched is the Windows11WindowsInsiderCanaryChannel version build26040. Some friends may still remember that many years ago someone successfully converted Windows NT from workstation mode to server mode, showing the commonalities between various versions of Microsoft operating systems. Although there are clear differences between Microsoft's current version of the server operating system and Windows 11, those who pay attention to the details may be curious: why Windows Server updated the brand,
Summary of machine learning hyperparameter tuning (PySpark ML)
Apr 08, 2023 pm 07:21 PM
An important task in ML is model selection, or using data to find the best model or parameters for a given task. This is also called tuning. You can tune a single estimator, such as LogisticRegression, or an entire pipeline that includes multiple algorithms, characterizations, and other steps. Users can tune the entire Pipeline at once, rather than tuning each element in the Pipeline individually. An important task in ML is model selection, or using data to find the best model or parameters for a given task. This is also called tuning. A single Estimator (such as LogisticRegression) can be tuned, or
Innovating the way to fine-tune LLM: comprehensive interpretation of the innovative power and application value of PyTorch's native library torchtune
Apr 26, 2024 am 09:20 AM
In the field of artificial intelligence, large language models (LLMs) are increasingly becoming a new hot spot in research and application. However, how to tune these behemoths efficiently and accurately has always been an important challenge faced by the industry and academia. Recently, the PyTorch official blog published an article about TorchTune, which attracted widespread attention. As a tool focused on LLMs tuning and design, TorchTune is highly praised for its scientific nature and practicality. This article will introduce in detail the functions, features and application of TorchTune in LLMs tuning, hoping to provide readers with a comprehensive and in-depth understanding. 1. The birth background and significance of TorchTune, the development of deep learning technology and the deep learning model (LLM)
How to use PHP for performance analysis and tuning
Jun 06, 2023 pm 01:21 PM
As a popular server-side language, PHP plays an important role in website development and operation. However, as the amount of PHP code continues to increase and the complexity of applications increases, performance bottlenecks become more and more likely to occur. In order to avoid this problem, we need to perform performance analysis and tuning. This article will briefly introduce how to use PHP for performance analysis and tuning to provide a more efficient running environment for your applications. 1. PHP performance analysis tool 1.XdebugXdebug is a widely used code analysis tool.
Detailed explanation of tuning practices to improve Go language website access speed
Aug 26, 2023 pm 07:27 PM
Detailed explanation of tuning practices to improve Go language website access speed Abstract: In the rapidly developing Internet era, website access speed has become one of the important factors for users to choose a website. This article will introduce in detail how to use Go language to optimize website access speed, including practical experience in optimizing network requests, using cache, and concurrent processing. The article will also provide code examples to help readers better understand and apply these optimization techniques. 1. Optimize network requests In website development, network requests are an inevitable link. And optimizing network requests can
