October 23, 2007 at 3:43 am
I have stored procedure to rebuild indexs.
Some of my tables have patritions, some don't have.
Problem is when I try to rebuild index on partition (table with this index doesn't have partitions)
(my function to get this index return me for this index partition number)
ALTER INDEX @index_name ON @table_name REBUILD PARTITION = @partition_number
I get error:
Cannot specify partition number in the alter index statement as the index 'index_name' is not partitioned.
How can I recognize that index is partitioned ?
October 23, 2007 at 7:06 am
You should be able to join up to sys.partitions on object_id,index_id and pull the partition_number.
Tommy
Follow @sqlscribeOctober 23, 2007 at 7:54 am
- http://www.davidemauri.it/DasBlog/CategoryView,category,Sql%20Server%202005.aspx
CREATE view [V_partitioned_objects]
as
select distinct
p.[object_id],
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from
sys.partitions p
inner join
sys.indexes i on p.[object_id] = i.[object_id] and p.index_id = i.index_id
inner join
sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
sys.partition_functions pf on ps.function_id = pf.function_id
- part from :
How can SQL Server 2005 help me evaluate and manage indexes?
-- SQLCAT Blog:
http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx
--
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id --, partition_number
, row_lock_count, row_lock_wait_count
, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply