Note that THIS talk, and a ton more, is the subject of Sean’s  precon at SQL Saturday #163 Dallas this fall.

We can’t really livecast Sean’s session, so I’ll live note-take.

Tonight’s session is on being able to answer questions before they’re asked.

Intro

• Right now we’re talking about estimating  database growth. There can be three kinds of growth – linear, geometric, or exponential – but we only care about the first, really.
• If you try to guesstimate database growth on a too-small scale, it will be far less accurate because it’s such a small sample…you might’ve caught the DB during peak season, or a quarterly data load.

Powershell

• So, Powershell. He has a powershell script “CollectorTableSize.ps1″ that, for every database on every server, collects information (like row count, index space used, data space used, andso on). I’m sure he’ll post this script online with the video.
• He suggests running this powershell every day. If it fails, you have more chances for it to succeed. You can also see your usage patterns better.

Growth Projection SP

• Now, the growth projection stored procedure: ”Collector.spDBGrowthProjection”. Parameters are BeginDate, EndDate, Projected Time (a sample period).  These parameters give you a way to stop the estimation before an expected spike in activity (e.g., sales peak season).
• For our purposes, it doesn’t matter what happened between these dates – whether the DB grew exponentially, geometrically or linear-ly, we’re going to assume it’s a linear growth. In our experience, long collection times (say, a few years) lead to very accurate growth estimates.
• We run the SP, and get (for demo purposes) instanceID, DBname, first date, last date, begin space, end space, time.
• It also returns a set (for demonstration purposes) with initial growth and extended growth in Kb, Mb, and Gb; and initial and extended projection in Kb, Mb, Gb.
• Final (actual) resultset provides:
• Server name, db name fist date, last date, months, beginSpace and endspace in KB / MB / Gb; initial growth and extended growth in Kb / Mb / Gb.
• If we pass in 6 months, it’ll tell us how much growth we’ll get in 6 months. This SP goes the extra mile and gives you both 6 months AND 12 months, just to curtail superfluous user questions.

Enterprise! (Make end user reports)

• But this isn’t very Enterprisey. It’s a lot of data, for what may be 100s and 100s of servers. So let’s make it more enterprisey.
• One of the most important things you must do as an Enterprise DBA is to make end user reports, so they won’t bug you again and again for “piddly little crap like this”.
• So here’s the report: DB Growth Projection, with server name, space needed and space needed with 20% cushion (both of those shown in MB and Gb). At the bottom, TOTAL space needed for all servers.

Q: You could fold in department names and business units for chargebacks!

A: Yep.

• Highly recommend having a 20% cushion with your growth conditions.
• This report is color coded:
• final estimation section in red
• actual space section in yellow
• delta details section in purple
• size detail section in blue. 
• Each Servername section is expandable for details.

Caveat: be aware that users WILL misread your report, no matter how pretty it is.

What else can you do with the growth projection?

• How about a growth pattern chart? We have that! It’s parameterized first by application and then by server. It returns a line chart by database of each DB’s growth.

Q: Do you have a “before you bother me” list?

A: Yeah! Acutally I put a link to the report in the ticket.

• There’s a disclaimer on the report: “This represents the actual data space and does not include free space in the files.”

Side note: ReplicationLatency DBA Report

This clearly has a separate collector, not yet shown.

• This report shows replication latency from publisher > distributor > subscriber in one report for all replication scenarios in the entire enterprise. That way you can pick out spikes in activity (whether it did spike, if so for how long, etc.)
• When you get a call and someone says “latency appears to be lagging”, this report instantly tells you where you should be focusing your efforts. Maybe it’s just spiking on one subscriber, and only between the distributor and the subscriber…that way you know the problem isn’t with the publisher.
• And you get this nice history trend. You can put it in a cube, or merge it with other data you’re collecting to see what’s going on systemically.

Q: What kind of interval do you collect on for someting like this?

A: It depends on the importance of the application. For my gold boxes, I do once per minute – granularity is important. For other boxes, usually 5 minutes.

What else are these reports good for?

• Auditors.  Live in audit mode. Auditors never come on a day when you’re bored and have nothing to do.

Server Role Members Report

• Report for DBAs (never for an end user); the “member name” is a link to a subreport, tells you all the other servers this user has server roles on. [Niiiiice.]

More POSH scripts

• CollectorServiceStatusGet.ps1 - checks on the status of all known SQL Server services. Alerts (alert only, no auto-turn on!) if a service is down. You have the option to keep blackout information in a table so the process doesn’t alert on servers that are supposed to be down.
• CollectorServiceAccountGET.ps1 – This is a semi-automated process. I have to kick it off every tme, but then it does everything for me. This is for those times when I want to change an account password; I want to see what services run under this account on all the boxes.  There’s always that guy who is POSITIVE he knows how many boxes there are, and then it turns out there are a handful more.  [Okay, that one's brilliant.] This script saves all the startup account info in a table, and then you can go through with a query to group by the account . This is also a good way to see if anyone is running SQL under their personal account, LocalSystem, etc.
• There’s a jobs collection that’s very similar, to see what jobs are running under which accounts. Ditto credentials.

Conclusion

• As long as you have the instance id and DB name, all these pieces of information will be tied together and you can answer questions you never dreamed of in no time at all.