Table Partitions - collecting statistics

• pault1867

  Grasshopper

  Points: 20

  More actions

  September 2, 2009 at 6:22 am

  #210279

  Hi,

  I am experimenting with partitioning some tables in one of our databases.

  I have a few statistics I want to collect to compare the before partitioning and after partitioning state.

  1) I am using DBCC showcontig('tableName') to get the page usage on the tables before partitioning

  but how do I get the index page usage?

  2) once I have partitioned the table, how can I get the statistics on each table partition?

  I have found this query to get the partition ids

  SELECT * FROM sys.partitions WHERE object_id = OBJECT_ID ('REVENUE_FACT');

  3) one more question: I want to be able to identify which data set is going into a particular partition. Does anyone know how to do this?

  Thank you,

  Paul

• pault1867

  Grasshopper

  Points: 20

  More actions

  September 3, 2009 at 3:02 pm

  #1049100

  Hi I found the table and proc script:The Automated DBA: Space Usage Snapshotter (sysadmin; central DB) " by By Jesse Roberge

  This should do the trick.

  Thanks to Jesse