partition

There are a couple of steps needed to partition a table.  We can partition an existing table or create a new partitioned table and insert into it.  Assume circa 10% slowage to qualify the data prior to putting it into the right physical structure.  Also note that ALL tables (since SQL 2005?) are partitioned – just usually one partition.

HOW DO WE CHUNK the data?

Create partition function myFunction_PF (varchar(10))
as range right for values ('b','c','d')

range right means that ‘a’ has no place to go, but that ‘e’ (and ‘z’) will be put into the fourth bucket.  ‘right’ points to the equal sign in the criteria list – values that are equal to or greater than ‘d’ go in the last bucket and values less than ‘b’ go into the first bucket.  range left would be equal to or less than ‘a’ go into the first bucket and values (not equal to but) greater than ‘d’ go into the last bucket.  That means that ‘d’ is in the third bucket if range left.

varchar is the parameter-type of data to be passed as the partitioning value (the type of the column) – in this case it could have been char as well.  Or int or datetime.  In the case of dates and times, we need to be careful about the exact type – datetime2 will NOT work if we specify datetime.

That is all we do – create a ‘sorting hat’ that will be used later.  To delete a partition function

drop partition function myFunction_PF

WHERE DO THE CHUNKS go?

Create a new schema that will describe which physical objects will be used and reference the PF.

create partition schema mySCHEMA_PS as PARTITION myFunction_PF ALL to (PRIMARY)
create partition schema mySCHEMA_PS as PARTITION myFunction_PF ALL to (newfilegroup)
create partition schema mySCHEMA_PS as PARTITION myFunction_PF to (newfg1,newfg2, newfg3, newfg4)

That says that this schema shows which function (myFunction_PF) should be used to partition yet unrevealed data to the primary file group, a (single) new file group, or among the n+1 listed filegroups.  We COULD have n file groups if we want to put several chunks of data into the same filegroup.  Delete a schema as the partition function above.

WHAT ABOUT THE DATA?

Finally, we can create a new table ON mySCHEMA_PS (partition_column_name).  On this point, the blank table “knows’ that based on the value of the data that will go into the partition column, it should use the partition function to create groups of data that will be stored in the appropriate filegroup(s) – primary, newfilegroup or newfg1..4.  When we insert data, the function will ‘sort’ the data into the correct filegroup, creating a partitioned table.

We can also take an existing table and partition it – the literature typically suggests (dropping) and creating a clustered index on the table with the partitioned schema – that will force the current data to be reorganized with the filegroups specified in the new schema.

Advertisements

new table from old

copy from one table to another, with sampling

select * into table1X from tableX tablesample (10 percent) 
-- 10% of the page space, not rows
select * into table1X from tableX tablesample (1000) 
-- 1000 lines, not the same
select * into table1X from tableX tablesample (10 percent)    repeatable (205) 
-- returns same values

quick way to copy a table into another table.  Also, of course, can create the table first, then use insert rather than select.

two/three utility scripts for counting and sizing databases and tables.

From SQL and ME, a SSMS script to determine how large each database is on a SQL Server instance (and get a list of all databases) by name, type (data, log, null) and size (MB).  The size MIGHT be the creation size and not necessarily reflect the data in the file.

SELECT [Database Name] = DB_NAME(database_id),
 [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
 WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
 ELSE Type_Desc END,
 [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
-- Uncomment if you need to query for a particular database
-- WHERE database_id = DB_ID(‘Database Name’)
GROUP BY GROUPING SETS
 (
 (DB_NAME(database_id), Type_Desc),
 (DB_NAME(database_id))
 )
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO

This query will get the tables in a single database, from symantec.com as adapted from the Database journal:

use adventureworks;
select
 a2.name [Table],
 replace(convert (nvarchar, convert(money, a1.rows), 1), '.00','') [Row Count],
 cast (a1.reserved/128.0/1024 as decimal(9,3)) [Reserved GB],
 case when a1.reserved = 0 then 0
 else cast ((100-(100.0*a1.used/a1.reserved)) as decimal(9,2)) end [Unused %]
from (
 select
 ps.object_id,
 sum (cast (ps.row_count as bigint)) rows,
 sum (cast (ps.reserved_page_count as bigint)) reserved,
 sum (cast (ps.used_page_count as bigint)) used
 from sys.dm_db_partition_stats ps
 inner join sys.all_objects a2
 on a2.object_id = ps.object_id
 and a2.type IN ('U')
 group by ps.object_id
 ) a1

left join sys.all_objects a2 ON a1.object_id = a2.object_id
where (a1.reserved/128) > 5
ORDER BY a1.rows DESC, a2.name

the good news is that you get free space, the bad news is that you get it only for each database as you enumerate it.

To get all databases and their size, it can be as simple as this

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name