Some useful SharePoint 2010 Stats for Capacity Planning

SQL statements below can be used to gather the trending on the space usage by SharePoint sites.

SELECT distinct
CONVERT(CHAR(4), [TimeCreated], 120) As [Year],
DATEPART(month,[TimeCreated]) As [Month],
CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120) as [CreatedMonth],
COUNT(*) as [DocumentedCreated],
SUM(isnull(cast(Size as bigint),0))/1024/1024 As TotalMegaBytes
from <content db name>.dbo.AllDocs D With (NOLOCK)
where [TimeCreated] > '2010-1-1'
group by CONVERT(CHAR(4), [TimeCreated], 120), DATEPART(month,[TimeCreated]),CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120)
order by [Year], [MONTH],[CreatedMonth] asc

SELECT distinct
CONVERT(CHAR(4), [TimeCreated], 120) As [Year],
DATEPART(month,[TimeCreated]) As [Month],
CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120) as [CreatedMonth],
COUNT(*) as [SitesCreated],
SUM(isnull(cast(DiskUsed as bigint),0))/1024/1024 As TotalMegaBytes
from <conten db name>.dbo.Sites D With (NOLOCK)
where [TimeCreated] > '2010-1-1'
group by CONVERT(CHAR(4), [TimeCreated], 120), DATEPART(month,[TimeCreated]),CONVERT(CHAR(4), [TimeCreated], 100) + CONVERT(CHAR(4), [TimeCreated], 120)
order by [Year], [MONTH],[CreatedMonth] asc