T-SQL Tuesday 16: Aggregation

So it’s that time again, T-SQL Tuesday.  This week our event is Sponsored by Jes Borland (Blog|Twitter).  So Jes what’s the subject?

“I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?”

So Dear Reader, Aggregation it is!

SOME THINGS JUST GO TOGETHER

We’ve all probably done our fair share of fancy aggregates, I have to say that most of the code I’ve used aggregates for is for business functionality and sitting with clients that I’ve worked for in the past.  I think we all have a nightmare report, script, or task that we accomplished using aggregates and while those things make for interesting stories, they aren’t that useful.  I’d like to give you something that you can use, (if you don’t already have a script to do what I’ve done here). 

Sometimes the most useful things are simple, like PB & J (simple and delicious!).  Here are a couple of queries that I use that have the SUM aggregate function in them.  Simple but effective.

The first is a quick script that extracts the information from DBCC SHOWFILESTATS and uses the "undocumented" sp_msforeachdb in order to gather total space used by all datafiles on an instance, total space reserved for all data files on an instance, then a breakdown by datafile per database, lastly we take the info from DBCC SQLPERF(LOGSPACE)and sort by the log file size.

The second script is of the same concept.  You can use the built-in reports “Disk Usage by Tables” to get the same info, but sometimes it’s just nice to build a script to work for you.  This script uses sp_spaceused and the "undocumented" sp_msforeachtable in order to build a list of tables by row number and the Reserved size for Data. (I use the Second script on my Compression demos to help you find an appropriate table (size wise there are multiple steps J) while examining compression).  Then I do a SUM function on each of the fields to show an aggregate of your database by user table.

SCRIPT1 – Get Database Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable%' and name not like'#myTable2%')

BEGIN

    DROP TABLE #myTable;

END

CREATE TABLE #myTable (

                      [myID] [int] IDENTITY(1,1) NOT NULL,

                      fileid varchar(4),

                      fileGroup varchar(4),

                      TotalExtents int,

                      usedExtents int,

                      DBName sysname,

                      filename1 varchar(8000));

GO

                     

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable2%')

BEGIN

    DROP TABLE #myTable2;

END

CREATE TABLE #myTable2(

                      dbName sysname,

                      logSize float,

                      spaceUsed float,

                      dbStatus int

                      );                   

GO

INSERT INTO #myTable

EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');

GO

                     

INSERT INTO #mytable2

EXEC( 'dbcc sqlperf(logspace)');

GO

SELECT

((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB

,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB

FROM #myTable;

GO

SELECT

    DBName

    ,usedExtents

    ,TotalExtents

    ,((usedExtents*64)/1024) AS DatabaseUsedSpaceinMB

    ,((totalExtents*64)/1024) AS DatabaseSizeinMB

    ,(((totalExtents*64)/1024)-((usedExtents*64)/1024)) as DataFileFreeSpace

 FROM

    #mytable

 ORDER BY

    ((totalExtents*64)/1024) DESC;

 GO

SELECT

    *

FROM

    #myTable2

ORDER BY

    logsize DESC;

GO

DROP TABLE #myTable;

DROP TABLE #myTable2;

GO

SCRIPT2-Get Table Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF EXISTS(SELECT * FROM TEMPDB.sys.tables WHERE name like '#myTable%')

BEGIN

    DROP TABLE #myTable

END

CREATE TABLE #myTable(

                               [Name] sysname,

                               [Rows] int,

                               [Reserved] varchar(500),

                               [Data] varchar(500),

                               [Index_Size] varchar(500),

                               [Unused] varchar(500)

                               );

INSERT INTO #myTable

EXEC ('sp_msforeachtable @command1="sp_spaceused [?]"');

GO

SELECT * FROM #myTable

ORDER BY

    [rows] DESC,

    LEFT([reserved],(LEN([reserved])-3)) DESC;

SELECT

    SUM(CAST(LEFT([reserved],(LEN([reserved])-3)) as int)) as [Total_Reserved(KB)]

    ,SUM(CAST(LEFT([data],(LEN([data])-3)) as int)) as [Total_Data(KB)]

    ,SUM(CAST(LEFT([Index_Size],(LEN([Index_Size])-3)) as int)) as [Total_IndexData(KB)]

    ,SUM(CAST(LEFT([Unused],(LEN([Unused])-3)) as int)) as [Total_UnusedSpace(KB)]

FROM  #myTable

GO

DROP TABLE #myTable;

  

Happy T-SQL Tuesday I hope you enjoy the scripts!

Thanks,

Brad