[Index] Size, usage, and location of your indexes

Updated my old code, since we were trying to figure out what indexes needed to get moved to the secondary (index) filegroup. You can even filter based off the usage (commented out below)
Standard disclaimer applies. Select the contents of the post, then copy/paste.

Note that there can be dupes - this is by design.  If a table spans multiple files, you'll see multiple rows returned with everything identical except for the filename.

SELECT
        o.name AS Table_Name ,
        i.NAME AS Index_Name ,
--        i.type_desc,
  CASE i.type
   WHEN 0 THEN 'Heap'
   WHEN 1 THEN 'C'
   WHEN 2 THEN 'NC'
   ELSE '?' END AS [Type],

   p.partition_number,

   p.rows AS [#Records] ,
        a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
        a.used_pages * 8 / 1024 AS [Used(mb)] ,
        s.user_seeks ,
        s.user_scans ,
        s.user_lookups,
        fg.name, 
        f.name,
        f.physical_name
FROM    sys.indexes AS i
        INNER JOIN sys.partitions AS p 
   ON i.object_id = p.object_id
            AND i.index_id = p.index_id
        INNER JOIN SYS.OBJECTS O 
   ON I.OBJECT_ID = O.OBJECT_ID
        INNER JOIN sys.allocation_units AS a 
   ON ( a.type = 2
    AND p.partition_id = a.container_id
               )
   OR 
    ( ( a.type = 1 OR a.type = 3)
    AND p.hobt_id = a.container_id)
        INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S 
   ON S.OBJECT_ID = I.OBJECT_ID
   AND I.INDEX_ID = S.INDEX_ID
   AND DATABASE_ID = DB_ID(DB_NAME())
   AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
  LEFT OUTER JOIN sys.database_files f 
   ON f.data_space_id = a.data_space_id
  LEFT OUTER JOIN sys.filegroups fg 
   ON fg.data_space_id = a.data_space_id
WHERE   OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
        AND i.TYPE_DESC <> 'HEAP'
        AND i.type <> 1 -- clustered index
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 

ORDER BY o.NAME ,
        i.name

[Partitioned Tables] Does the clustered index take up space if referenced?

Setting up a new partitioned table with associated indexes. I was curious whether the adding the partitioning key to any index would cause the index to grow - I expected not, but you never know.

Our clustered index:

create unique clustered index clustind_pk on ourtable (id, partitionedkey)

Our test indexes:

create nonclustered index A on ourtable (partitionedkey, fielda)
on ps_daily (partitionedkey)

create nonclustered index B on ourtable (fielda)
on ps_daily (partitionedkey)

create nonclustered index C on ourtable (fielda) include (partitionedkey)
on ps_daily (partitionedkey)

Then used this query to check a particular partition for all 3 indexes (thanks to Simon Sabin). Each was within 1 page of the others.

select OBJECT_NAME(p.object_id ), i.name,p.*
from sys.dm_db_partition_stats p
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE p.object_id = 2071234567
AND partition_number = 28 

[Tuning] SPARSE varchar calculation

Since it doesn't appear that anybody has done this before, here you go. I've been comparing SPARSE to COMPRESSION, and for my particular tables, I got 25% space savings via parse. However, I got 40% savings from ROW compression, and 50% savings from PAGE.

Standard
=(Number_Of_Rows*(Average_Varchar_Length+2)
*((100-Percent_Null)/100))
+(Number_Of_Rows*(Percent_Null/100*2))

Sparse:
=(Number_Of_Rows*(Average_Varchar_Length+4))*((100-Percent_Null)/100)

Next up is comparing the CPU for each option. Nobody's really talked about whether the compression is symmetric or asymmetric, though I'd hope it's asymmetric (aka easier to decompress than compress, in this case)

[Backups] Determine your database growth via backup history

One thing about backups in SQL Server is that the history is kept forever, unless you clean it up using a maintenance plan, or one of the not-very-well-documented SPs.

But we can have it work for us.
Case in point - database growth estimations. This is a basic view that will show what your database growth has been like for the past 60 days. Turn it into a chart with reporting services, and you can see what's growing, at what rate, and what you need to be concerned with.

Yes, this is pretty basic code, but I hadn't seen anybody do this before.
And obviously, if you're cleaning up your backup history this won't necessarily do much.

CREATE VIEW [dbo].[backup_history]
as
SELECT 
 server_name,
 DATABASE_name,
-- catalog_family_number,  --not sure what this does; unclear in BOL
 backup_size/1000000 AS backup_size,
 CONVERT(CHAR(12),backup_start_date,101) AS backup_date--, 
-- in case you want to look at a particular type of backup
-- CASE [type]
--  WHEN 'D' then 'Database'
--  WHEN 'I' then 'Differential database'
--  WHEN 'L' then 'Log'
--  WHEN 'F' then 'File or filegroup'
--  WHEN 'G' then 'Differential file'
--  WHEN 'P' then 'Partial'
--  WHEN 'Q' then 'Differential partial'
--  END AS Backup_Type,
-- [NAME], 
-- [description] 
FROM msdb.dbo.backupset 
WHERE [TYPE] IN ('D','F') --full backups, though tlogs could be interesting
AND server_name = @@SERVERNAME
AND database_name NOT IN ('msdb', 'MASTER', 'model')
AND backup_start_date > GETDATE()-60
--ORDER BY SERVER_name, DATABASE_name, backup_start_date, catalog_family_number

[Index] Find the size of your indexes

Something found online (originally by Alejandro Mesa). This is doubly handy now that 2005 can tell us which indexes aren't frequently used. Thanks to N8WEI for the correction on sys.allocation_units.

select
i.[object_id],
i.index_id,
i.NAME AS Index_Name,
i.type_desc,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
on (a.type = 2 AND p.partition_id = a.container_id)
OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id)
--where
-- i.[object_id] = object_id('dbo.thebakingdba_fanmail') --wishful thinking
-- and i.type = 1 -- clustered index
order by
i.name --p.partition_number
go

[Maint] Show free space within a database

I use this all the time. Not sure where it came from, though it's probably from Simon Sabin's Taskpad_view report for SSMS. This will run on 2000 or 2005, and will show you how big your database is, as well as how much of that is used.


create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)


create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from
(
select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]
from #data
union all
select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
--order by [Type],[Name]
)a
order by [Type],[Name]
drop table #data
drop table #log