1

I've created a table that stores top 10 biggest tables on a database. it's schema is like this:

(example with 3 tables only)

| Table | size_mb | Date       |
|-------|---------|------------|
| tb1   | 2       | 01/01/2020 |
| tb2   | 5       | 01/01/2020 |
| tb3   | 1       | 01/01/2020 |
| tb1   | 4       | 02/01/2020 |
| tb2   | 12      | 02/01/2020 |
| tb3   | 2       | 02/01/2020 |
| tb1   | 10      | 03/01/2020 |
| tb2   | 20      | 03/01/2020 |
| tb3   | 5       | 03/01/2020 |

as our tables grow really fast, I have table size every day. in the example above, theres only the first day of the month, because this is what I'm trying to achieve with some kind of pivot or something.

What I would really want is to pivor this table by month.

I mean, I would like to have something like this:

| Table | size_JAN | size_FEV | size_MAR |
|-------|----------|----------|----------|
| tb1   | 2        | 5        | 6        |
| tb2   | 5        | 7        | 10       |
| tb3   | 1        | 2        | 6        |

I'm trying to think not about pivot itself but, if there's a good way to send a monthly report with this information, together with historical data.

EDIT:

the best I could do is to separate by month:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Tabela) 
                    from tbldatabasesize_tables
                    group by Tabela
                    order by Tabela
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT Ano,
                    case when mes = 1 then ''Jan''
                         when mes = 2 then ''Fev''
                         when mes = 3 then ''Mar''
                         when mes = 4 then ''Abr''
                         when mes = 5 then ''Mai''
                         when mes = 6 then ''Jun''
                         when mes = 7 then ''Jul''
                         when mes = 8 then ''Ago''
                         when mes = 9 then ''Set''
                         when mes = 10 then ''Out''
                         when mes = 11 then ''Nov''
                         when mes = 12 then ''Dez''
end as Mes,' + @cols + ' from 
             (
                select 
                    year(DATE_REPORT) as Ano,
                    month(DATE_REPORT) as Mes,
                    Tabela, 
                    convert(int,Tamanho_Da_Tabela_em_MB) as Tamanho_Da_Tabela_em_MB
                from tbldatabasesize_tables
            ) x
            pivot 
            (
                avg(Tamanho_Da_Tabela_em_MB)
                for Tabela in (' + @cols + ')
            ) p '

execute(@query);




| Ano  | Mes | TB1| TB2 | TB3 | TB4 | TB5 | TB6 | TB7 | TB8 | TB9| TB10 |
|------|-----|-------------|-------------------|---------------------|----------------|------------|-----------|---------------|--------------|--------------------------------|------------------|
| 2020 | Mar | 3439        | 26363             | 77232               | 235643         | 32718      | 196380    | 11141         | 6570         | 9606                           | 24820            |
| 2020 | Abr | 3445        | 26413             | 77660               | 239613         | 32718      | 199713    | 11210         | 6591         | 9926                           | 24952            |
| 2020 | Mai | 3453        | 26493             | 78479               | 244071         | 32718      | 203283    | 11341         | 6613         | 10270                          | 25084            |
| 2020 | Jun | 3472        | 26583             | 79440               | 247916         | 32718      | 197185    | 11482         | 6630         | 10514                          | 25217            |
4
  • Tough to answer this question, really. What are your criteria for "best"? I'm a visual person so outputting just numbers in a table makes it really hard to spot outliers or severe deltas unless you also rank them or highlight them in some way. Commented Jun 18, 2020 at 16:40
  • Also instead of month(DATE_REPORT) as Mes which then requires a large case expression outside, why not CONVERT(char(3), DATENAME(MONTH,DATE_REPORT)) which does not? You can also do it outside with CONVERT(char(3), DATENAME(MONTH, DATEADD(MONTH, Mes , '20191201'))) as in this fiddle but that is more tedious and less self-documenting when you have the original date to derive it from directly. Commented Jun 18, 2020 at 16:44
  • Another way, since you know there are only 12 months, would be to separate by month and do a UNION to get it all together.
    – SQLserving
    Commented Jun 18, 2020 at 19:19
  • Thanks @AaronBertrand, again an amazing tip the goes to my knowledge base.
    – Racer SQL
    Commented Jun 22, 2020 at 13:31

1 Answer 1

0

There's a hierarchy in analytics value that goes descriptive, diagnostic, predictive, prescriptive. What your question talks about is "descriptive." I think you should aim for "predictive."

You're collecting these numbers because you're concerned about exhausting some resource - disk space, backup size, sequential IDs, whatever. That's what will cause problems, not the absolute size of the table per se. Therefore that is what should be reported.

The output should be

Extrapolating over the last 4 months, Disk D: will be full by Jan 2021.
Last month's report predicted Feb 2021.

There are many ways to calculate the exhaustion point. Simple linear extrapolation is one. I've used Monte Carlo to some success.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.