Wednesday, November 29, 2017

Use T-SQL to create caveman graphs

I found this technique on Rich Benner's SQL Server Blog: Visualising the Marvel Cinematic Universe in T-SQL and decided to play around with it after someone asked me to give him the sizes of all databases on a development instance of SQL Server

The way it works is that you take the size of the database and then divide that number against the total size of all databases. You then use the replicate function with the | (pipe) character to generate the 'graph'  so 8% will look like this ||||||||

You can use this for tables with most rows, a count per state etc etc. By looking at the output the graph column adds a nice visual effect to it IMHO

Here is what the final query looks like

       database_name = DB_NAME(database_id) 
     , total_size_GB = CAST(SUM(size) * 8. / 1024/1024 AS DECIMAL(30,2))
  , percent_size = (CONVERT(decimal(30,4),(SUM(size) /
     (SELECT SUM(CONVERT(decimal(30,4),size))  
     FROM sys.master_files WITH(NOWAIT)))) *100.00)
  , graph = replicate('|',((convert(decimal(30,2),(SUM(size) / 
    (SELECT SUM(CONVERT(decimal(30,2),size))  
     FROM sys.master_files WITH(NOWAIT)))) *100)))
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id

And here is the output (I blanked out the DB name in the output below), there are 48 databases, 15 of them show a bar, the rest don't because they use less than 0.5% of space.

Do you see how you can quickly tell visually that the top DB is about twice as large as the next DB? 

Those guys in Lascaux would have been so proud, only if they could see this  :-)


Srini said...

Good. Instead of referencing same table sys.master_files thrice , cte or
sum over() can be used

;with dbsize_CTE as
select DBName = DB_NAME(database_id),
CAST(SUM(size) * 8. / 1024/1024 AS DECIMAL(30,2)) as dbsize
from sys.master_files WITH(NOWAIT)
group by database_id
) ,

totsize_CTE as
select sum(dbsize) as totsize
from dbsize_CTE

select d.DBNAME,D.dbsize, percent_size = (D.dbsize/T.totsize) * 100.00 ,
replicate ('|',(D.dbsize/T.totsize) * 100.00) as GraphPercent
from dbsize_CTE as D,
totsize_CTE as T
order by percent_size desc

hot2use said...

Simplistic and easy to adapt. Cool. Thanks for the post.

Unknown said...

Wonderful ! Now,,, if only I can add in some colors ...

Unknown said...

Thank you ! I tried to compare the actual execution plans. I also looked at statistics IO for both cases, Same, I think. Curious why, but will look more

Unknown said...

sum over version

db_name(mf.database_id) database_name
, sum(mf.size) * 8. / 1024 / 1024 total_size_GB
, convert(
numeric(19, 2)
, sum(mf.size) * 100.0
/ sum(sum(mf.size)) over (partition by (select null))
) percent_size
, replicate(
, sum(mf.size) * 100.0
/ sum(sum(mf.size)) over (partition by (select null))
) graph
sys.master_files mf
group by
order by
percent_size desc