Database Maintenance Without Breaking The Bank

存储架构 2018-02-13

Database maintenance does not have to be expensive. There are free tools out there that will make your life easier. Of course, commercial DBA tools are worth their price – they are paid for a reason. Software like SolarWinds Database Performance Analyzer and Redgate SQL Monitor are two examples of SQL Server tools that can make anyone a better database steward.

If you do not have access to these tools, that doesn’t mean you’re stuck with your homebrewed solutions. If you are on a shoestring budget and commercial tools are not included in your annual budget allocation, the following free tools can help you survive your day-to-day job.

Glenn Berry’s Diagnostic Queries

If you want to know more about the configuration of your SQL Server instance, these are the queries
you need. It’s not just configuration but you can also glean the overall health of your SQL Server from the various information that these queries provide.

The queries provide the following basic configuration information:

  • SQL Server Version, Update Level, and OS Information
  • CPU information
  • Global Trace Flags
  • Memory
  • SQL Server Agent Alert Info
  • Volume Info for all LUNs
  • Drive Latency Info
  • Etc.

It also provides performance related info:

  • IO Requests
  • Missing Indexes
  • CPU Utilization
  • Buffer Usage
  • Wait Stats
  • Etc.

Ola Hallengren SQL Server Maintenance Solution

Just like Glenn Berry’s Diagnostic Scripts, Ola Hallengren’s solution
is a group of queries “for running backups, integrity checks, and index and statistics maintenance.” If you have a big daily job that gets slower and slower over time, I suggest that you set up the Index and Statics maintenance to run on regular schedule.

Here’s a typical command to execute;

EXEC dbo.IndexOptimize @Databases = N'MyDB', -- nvarchar(max)
 @FragmentationLow = NULL, -- nvarchar(max)
 @FragmentationMedium = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max)
 @FragmentationHigh = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max)
 @FragmentationLevel1 = 5, -- int
 @FragmentationLevel2 = 30, -- int
 @PageCountLevel = 1000, -- int
 @UpdateStatistics = N'ALL', -- nvarchar(max)
 @OnlyModifiedStatistics = N'Y', -- nvarchar(max)
 @Indexes = N'ALL_INDEXES', -- nvarchar(max)
 @TimeLimit = 10800 -- int 10800= 3 hours

You can also set up a SQL Server Integrity Check for the databases, tables, filegroups, indexed views, etc.

Brent Ozar’s sp_Blitz

The sp_Blitz suite
has become a day-to-day tool for me. Whether you’re checking the overall health of your SQL Server (sp_Blitz) or maintaining your Indexes (sp_BlitzIndex), this sp_Blitz has got you covered. Want to know the worst-performing stored procedure? sp_BlitzCache can help you with that. Is your SQL Server slow? Let’s do sp_BlitzFirst on that. sp_Blitz seems to have covered all the important parts of database maintenance.

Adam Machanic’s sp_whoisactive

If we talk about helpful tools on SQL Server or ask data pros about their favorite tools, I’m sure the sp_whoisactive stored procedure
comes up in the conversation. In fact, it is part of my heuristic approach to performance tuning
, which you can read about here

Paul Randal’s Wait Stats Scripts

There are great scripts on Paul Randal’s blog but the Waits Stats scripts have got to be my favorite. Waits is probably part of everybody’s performance tuning method. Waiting tasks can reveal the pain points of your SQL Server

SentryOne Plan Explorer

Who still uses the built-in SSMS Execution Plan viewer nowadays? If you do a lot of query optimization or troubleshooting, this is the most important tool you need. There is just a lot of features in this tool that you can’t find in the SSMS Execution Plan Viewer. No database maintenance tools stash is complete without Plan Explorer.

To sum up, you don’t need to shell substantial amount of money to have a decent set of tools for database maintenance. There are lots of great tools like the ones listed above. What is your favorite tool? Share it in the comments below.

The post Database Maintenance Without Breaking The Bank
appeared first on SQL, Code, Coffee, Etc.


责编内容by:SQLServerCentral (源链)。感谢您的支持!


谈谈JDBC JDBC是Java Database Connectivity的简称,它定义了一套访问数据库的规范和接口。但它自身不参与数据库访问的实现。因此对于目前存在的数据库(譬如Mysql、Oracle)来说,要么数据库制造商本身提供这些规范与接口的实现,要么社区提供这些实现。 对于Mysql数据库...
YYCache 源码剖析:一览亮点 写在前面 YYCache 作为当下 iOS 圈最流行的缓存框架,有着优越的性能和绝佳的设计。笔者花了些时间对其“解剖”了一番,发现了很多有意思的东西,所以写下本文分享一下。 考虑到篇幅,笔者对于源码的解析不会过多的涉及 API 使用和一些基础知识,更多的是剖析作者 ibireme 的设...
Does anyone use NoSQL databases to store medical r... Electronic Medical records are composed of different types of data. Visit information ( date/location/insurance info) seems to lend itself to a RDMS. ...
How to Learn to Draw: Stage Three, Visual Database What You'll Be Creating In the previous parts of the series we learned how totame the pencil and mastered hand-eye coordinatio...
Android ORM 框架:GreenDao 使用详解 转载务必保留以下信息: 本文作者:speedy CSDN 专栏: 掘金专栏: 一、GreenD...