未加星标

Getting Started with the SQL Server First Responder Kit

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Your SQL Server database is slowly grinding to a halt, your DBA is on vacation, and you don’t know where to start. This is when you break out the SQL Server First Responder Kit . This open source project consists of a set of scripts to help the DBA, or accidental DBA , fix and tune a SQL Server instance.

These scripts are usually installed as stored procedures in the “master” database on your server. This, combined with the “sp_” prefix, ensures that they are available no matter which database you happen to be looking at.

Side note: SQL Server always looks in master first for stored procedures that begin with “sp_”. So if you use that prefix for normal, database specific stored procedures, you’ll slow down the server slightly as it looks in the wrong place.

sp_BlitzWho Who is causing the immediate problem?

The first tool you generally use when something goes wrong. It will tell you who is connected, what they are running, and how badly it is affecting the database.

[Click on the image to enlarge it]
Getting Started with the SQL Server First Responder Kit

If you discover a runaway process that just needs to stop, you can use the “kill” command along with the relevant session id.

If the problem isn’t clear, you can then move on to sp_BlitzFirst.

sp_BlitzFirst What are you waiting for?

The sp_BlitzFirst tool helps you discover what your database is waiting for. In the example, you can see the #1 problem is that things other than SQL Server are consuming too much of the CPU’s time.

[Click on the image to enlarge it]
Getting Started with the SQL Server First Responder Kit

Unless you are testing out the scripts on a developer’s machine, that’s a pretty unusual diagnosis. What’s more common is that you’ll find one or more “wait stats” to blame.

In SQL Server, everything that could possibly slow down a query is tracked as a wait stat . This includes disk and network I/O, locks on tables/rows, waiting for CPU or memory resources, etc. The link in the output will help with common wait types, but with hundreds of different wait types being tracked it can often be hard to find information on the specific wait stat that is affecting you.

sp_Blitz Is this database even setup correctly?

Another tool that you should consider when first taking ownership of a database server is sp_Blitz. This tool will identify common problems with the way SQL Server databases are setup. Each issue includes information on how to solve the problem and a priority score indicating what order the issues should be addressed.

[Click on the image to enlarge it]
Getting Started with the SQL Server First Responder Kit

Here you see that many of the databases haven’t been backed up or checked for corruption in an unacceptably long time.

Other problems it can detect include:

Bad configuration settings, especially the ones that are “wrong by default” such as the cost threshold for parallelism. Dangerous file locations such as storing transaction logs on the OS drive. Non-production licenses being used. Missing alerts for database corruption, low memory, etc. Common security setup errors such as incorrect database owners.

sp_BlitzCache Which queries should be tuned?

With the immediate problems solved, you can start looking at ways to proactively improve performance. One tool for this is sp_BlitzCache. This looks at SQL Servers query plan cache to determine which queries are having the biggest impact on the database over time. It can also warn you about common problems in queries such as computed columns with scalar operators and implicit casts.

The main difference between sp_BlitzFirst and sp_BlitzCache is that sp_BlitzFirst looks at what is happening in real time. By contrast, sp_BlitzCache looks at historical data to help you identify trends, so it doesn’t require you to catch the problem query in the act.

sp_BlitzIndex How are my indexes doing?

If performance problems seem to be systemic rather than isolated to specific queries, the next place to look is the indexes. It is a well-known fact that missing indexes can have a profoundly negative effect on performance, causing queries to easily take ten, one hundred, even more than a thousand times longer than it should.

An equally important problem is too many indexes. Besides telling you about missing indexes, sp_BlitzIndex can tell you when more time is being spent updating an index than actually using it. Unnecessarily updating indexes not only slow down writes, but can push other data out of the cache, effectively slowing down unrelated queries.

The SQL Server First Responder Kit was originally developed by Brent Ozar Unlimited and is now an open source project with an MIT license.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLCPUSQL Server
分页:12
转载请注明
本文标题:Getting Started with the SQL Server First Responder Kit
本站链接:http://www.codesec.net/view/533040.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(72)