|
serverdba
Starting Member
4 Posts |
 Posted - 01/24/2013 : 13:10:22
|
is it your partition right or left. run this 2 script below, which will give you some more insight as from script (B) you need to find filegroup, partitionID for Nextused column.
Also check if you have any waittype PageIOLatch_EX .
Script (A)
SELECT
OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
, P.partition_number
, P.rows
FROM sys.partitions AS P
JOIN sys.indexes AS SI
ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.type IN(0,1);
--AND OBJECT_NAME(SI.object_id) = 'table Name';
Script (B)
SELECT
SPS.name AS PartitionSchemeName
, CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
ELSE NULL END AS PartitionID
, SPF.name AS PartitionFunctionName
, SPRV.value AS BoundaryValue
, CASE WHEN SDD.destination_id > SPF.fanout THEN 1
ELSE 0 END AS NextUsed
, SF.name AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF
ON SPS.function_id = SPF.function_id
JOIN sys.destination_data_spaces AS SDD
ON SDD.partition_scheme_id = SPS.data_space_id
JOIN sys.filegroups AS SF
ON SF.data_space_id = SDD.data_space_id
LEFT JOIN sys.partition_range_values AS SPRV
ON SPRV.function_id = SPF.function_id
AND SDD.destination_id =
CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id
ELSE SPRV.boundary_id + 1 END
WHERE SPS.name = 'Scheme Name' |
 |
|
Partition Switching Problem
