-- =============================================
-- Query to determine if a partition's size may
-- be reaching a critical maximum
-- Note: Currently uses SP_SEND_DBMAIL instead of XP_SENDMAIL
-- =============================================
DECLARE @DbName VARCHAR(25)
DECLARE @sql VARCHAR(100)
DECLARE @ThresholdCountMsg INT
DECLARE @Subject VARCHAR(100)
SET @DbName = DB_NAME(); --get name of current database to use in email alert
SET @Subject = 'Partitioning Threshold Alert On ' + @DbName + '!'
SET @ThresholdCountMsg = 14000000; -- set partition row count
IF OBJECT_ID('tempdb..##yourPartitionSizeCheck') IS NOT NULL
BEGIN
DROP TABLE ##yourPartitionSizeCheck
END;
--use global temp table for email alert
CREATE TABLE ##yourPartitionSizeCheck
(
table_name varchar(20),
partition_number int,
row_count int
);
INSERT INTO ##yourPartitionSizeCheck
SELECT b.name AS 'table_name',
partition_number,
a.[rows] AS 'row_count'
FROM sys.partitions AS a INNER JOIN
sys.objects AS b ON a.object_id = b.object_id
INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id
WHERE LOWER(b.name) = 'yourPartitionedTableName'
and a.index_id < 2
and a.[rows] > @ThresholdCountMsg;
IF @@ROWCOUNT > 0
EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'YourSqlmail',
@recipients = 'your.email@yourcompany.com',
@subject = @Subject,
@body = 'The following partitions may be approaching performance degradation: ',
@execute_query_database = @DbName,
@query = 'SELECT table_name, partition_number, row_count FROM ##yourPartitionSizeCheck';— Another way to query the definition of an object:
SELECT OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE LOWER(DEFINITION) LIKE ‘%tbl%’
If your database has a very large table, you can “partition” this large table onto separate “filegroups.” Filegroups are special types of files that allows a user to divide data onto different disks for the purpose of optimizing query performance and maintenance operations. For example, say you have a tall file cabinet where you keep lists of customer names. As the number of files grow, the longer it takes to locate any given file. Database tables operate on the same principle.
The first thing you will need to do when planning out a partitioned table is to decide how you will divide the table up. As with our file cabinet example, we might decide to create one file cabinet where last names begin with A – F, another cabinet for files G – N, and another for files O – Z. Likewise, our table might also be divided up using last names as a way to organize. You can see how faster it would be to locate the last name “Jones” in one of three cabinets instead of a giant cabinet.
As business changes and data grows, partitioning can become more complicated; however, the example above does provide a basic understanding as to how partitioning can save time and resources.