T-SQL Tuesday #15 DBA Automaton

Often, we hear about DBA’s automating everything under the sun.  Why?  It simplifies the job and creates time to work on other projects.

I have been incognito for the last couple of weeks and nearly missed TSQL Tuesday this month.  If it weren’t for somebody pinging me requesting the link to the list of upcoming hosts, I would have missed it entirely.  The topic merges well with the work I have been doing for the past few weeks.  In fact, there are so many things that DBA’s do on a regular basis to automate things – there should be plenty of items to cherry pick for a topic.  Unfortunately, that doesn’t make this topic any easier for me.  My decision process for this is partially based on thinking through what might be unique without having read what others may have written.  (While thinking about reading blogs, it would be nice if there was an automated method to ingest all of the blogs into my head without having to iterate through them one at a time – manually.)

This month the party is hosted by Pat Wright (blogtwitter).  Pat has asked us to describe some of the things we automate – or some of our automation that we have implemented.  Often, we hear about DBA’s automating everything under the sun.  Why?  It simplifies the job and creates time to work on other projects.  With all of the automation, I wonder if DBA’s are related to Hephaestus in any fashion.  If we had our way, it seems that our databases would be…Automatons.

A Lesser Automation

Now that I have rambled for a good bit, I guess it is time to get to the meat of the topic.  First, we need to understand automation.  So, what is automation?

  1. The automatic operation or control of equipment, a process, or a system.
  2. The techniques and equipment used to achieve automatic operation or control.

A closely related word to automation is:

  1. Computerization – the control of processes by computer

In other words, for a DBA, automation is the implementation of a process or control for the computer to operate without the DBA doing the work.  This is typically something that is repetitive or menial or tedious or frequently done.  But that is not always the case.  This can also be something that is fun and/or only done once or twice a year.

I have one of those cases where I may use the automation even less often than once a year, or maybe it could come about more frequently.  It all depends on the needs of designing and testing new databases.

This process is to help in properly sizing the database before the database is finally released to production.  With good project requirements, you may have a good idea of what the fields and sizes of those fields should be.  In some cases, you will be getting data from an external source in some fashion or another.  This data does not always come with storage requirements or data size parameters.  You can make guesses at it by looking at the data – but sometimes, something more is required.

It is when more is required that this script comes in a bit handy for me.  The script is ugly, but it does the deed.  The base idea is to retrieve the data length for a sample of data from each of the tables (after import into a staging database).  This is done so I can run statistical analysis on the data later.  And cringe now because I use a nested cursor to get at what I want.

So Here is the script that will load a sample of data from every table in every database for every column and give you the length of the pertinent columns (I have excluded obvious columns such as numeric types and certain LOB types).

And yeah, I rushed through this to get it finished up quickly.  I hope to have more time to delve into it later.  At least with this script, I can load a table with adequate data to be able to generate histograms on the data length/size distribution and then make appropriate sizing decisions based on the statistics.  Be warned – though automated it is slow and should not be run on a production server.

3 thoughts on “T-SQL Tuesday #15 DBA Automaton”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.