What other useful DBA reports do you suggest?

Question

What other useful DBA reports do you suggest?

VoldemarG

Hall of Fame

Points: 3883
More actions
August 31, 2021 at 7:21 pm

#3925574

What other useful DBA reports do you suggest? Maybe something we would run every month or so to check that our environment is configured/running optimally.
We have these so far: see screenshot.
I also already have (adding soon) certain reports on block leaders and Waits report based on sp_whoisactive, report on 'Worst of the Worst SQL' (incorporating some Jeff's ideas)...
//We also use AppDynamics, too. So it would be preferred to add such new DBA reports that are not immediately available via AppD.
<>
WHAT ELSE would you suggest?
Likes to play Chess
September 15, 2021 at 2:54 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

VoldemarG Hall of Fame Points: 3883 More actions · Answer 1

a couple more things we are thinking about:

Report showing database with excessive number of virtual log files. So we can then shrink and re-grow the tlog files to minimize vlog files. A process to periodically run DBCC CHECKDB on database. Also detail best practices for dealing with specific results.

Table pagesplit/fullness analysis.

......

Likes to play Chess

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 2

I don't think there are a standard set of canned reports that someone can simply hand you. There are too many variables at play. Personally, I've usually used a 3rd party monitoring tool. Yeah, they cost money, but they save you time. For example, the time you're going to spend building and implementing reports after you've spent time building and implementing monitoring data collection. Instead, a solid 3rd party monitoring tool (I'd suggest Redgate SQL Monitor, but I do work for them, so...) will provide you with the majority of what you're asking for. You can then spend your time doing the important stuff, automating consistency checks, tuning queries, proactively dealing with data growth, all the other stuff that you don't have enough time for already.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

frederico_fonseca SSCoach Points: 16037 More actions · Answer 3

Adding to what Grant said (I do use SQL Monitor and also other similar monitor tools).

one of the most important things is the ability to correlate different metrics that happened at the same time - if you had a spike on CPU at 1 point in time you will wish to see which SQL's were executing at that exact moment - having those SSRS paginated reports will not give you that.

some of the tools will also allow you to add custom metrics - so some of the items you have on your ssrs would also become part of that third party tool database and could be correlated.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

VoldemarG wrote:

What other useful DBA reports do you suggest? Maybe something we would run every month or so to check that our environment is configured/running optimally. We have these so far: see screenshot.
I also already have (adding soon) certain reports on block leaders and Waits report based on sp_whoisactive, report on 'Worst of the Worst SQL' (incorporating some Jeff's ideas)... //We also use AppDynamics, too. So it would be preferred to add such new DBA reports that are not immediately available via AppD.
<>
WHAT ELSE would you suggest?

It would be real nice if you shared those with the world. You should zip them up and attach them.

I'm particular interested in the one related to indexes, stats, a page splits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webrunner SSC-Dedicated Points: 31728 More actions · Answer 5

Great idea, thanks for this. For some reason I'd always thought of SSRS as client only, but it obviously can be really useful for DBA reports.

I would add backup-related reports.

Failed full backups

Failed log backups

Broken log chains (not sure how to do this via t-sql)

Also the Brent Ozar sp_BlitzBackups results

https://www.brentozar.com/archive/2017/05/announcing-sp_blitzbackups-check-wreck/

https://www.brentozar.com/archive/2012/02/how-to-test-your-backup-strategy/

Thanks again!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Jeff Moden wrote:

VoldemarG wrote:
What other useful DBA reports do you suggest? Maybe something we would run every month or so to check that our environment is configured/running optimally. We have these so far: see screenshot.
I also already have (adding soon) certain reports on block leaders and Waits report based on sp_whoisactive, report on 'Worst of the Worst SQL' (incorporating some Jeff's ideas)... //We also use AppDynamics, too. So it would be preferred to add such new DBA reports that are not immediately available via AppD.
<>
WHAT ELSE would you suggest?
It would be real nice if you shared those with the world. You should zip them up and attach them.
I'm particular interested in the one related to indexes, stats, a page splits.

Heh... remember, one way streets can appear in any direction. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)