Pros and Cons to implement partions in SQL 2k tabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Pros and Cons to implement partions in SQL 2k tabl

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-21 : 21:00:21

Good evening
In a PROduction System the largest tables have for about 500M of rows and more
The problem arises when we need to debug the oldest data from 1999 to 2006

this processes entails a time comsuming time process becasue before deletng the rows from a specficic range i.e from 19960101 to 19960131 w need to backup and deleting the specific rows takes so much time even though i use PK keys

According to your knowledege adn experience id like to know it its worth an pratical to implement particioning to those tables in SQL 2K and the deleting the partition in order to free up space in FG becuase theyre running out of space?
Im not a DBA nor a developer but i wonder if this process needs a manteinance window ?
Thnks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 01:38:45

partitioning and putting in separate FG would help if you want to do backup separately for the old partition data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-22 : 22:11:49

thank you very much, one more question, meanwhile i get documented with partitioning, this process takes less time than debugging

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-23 : 01:05:56

quote:
Originally posted by alejo46

thank you very much, one more question, meanwhile i get documented with partitioning, this process takes less time than debugging

sorry i didnt understand your question

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-23 : 12:57:46

sorry, by mystake i pressed the enter key so the message was not completed, but the question is the partitionig process takes so much time ? what factors should be taken before starting this process ?
the purpose of implementing partitioning tables in SQL 2K tables is to minimize the debug process so instead of deleting rows for huge tables i would delete the partition and that would be faster in order to free up some space faster, may you can correct me if im wrong ?
ie i´ve got this table with the output sp_spaceused:
name|rows|reserved|data|index_size|unused
SALDOS_PREPAGO_DIARIOS_ALTAMIRA_SALDOS|2147483647 |863440848 KB|541344904 KB|233418456 KB|88677488 KB
this has a column called:
FEC_SALDO|smalldatetime|no|4| | |no|(n/a)|(n/a)|
and the index:
index_name|index_description|index_keys
IX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDO
id like to start partioning ths table by month

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-24 : 02:09:31

which column are you partitioning on? is the column the clustered indexed one?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-24 : 15:15:24

Good afternon
replying your question the column is calld: FEC_SALDO and its a nonclustered

index_name|index_description|index_keys
IX_FEC_SALDO_SALDOS_PREP_DIA_ALTAMIRA_SALDOS|nonclustered located on INDICES|FEC_SALDO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 00:47:59

how is distribution of data based on the values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-25 : 12:07:39

excuse me i didnt understand your question, what do you mean exactly when you say how is distribution of data based on the values?
it has something to do with the Filegroup for allocating the table and its indexes ?
thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-07-25 : 17:01:54

SQL Server 2000 does not support partitioned tables.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 02:09:21

quote:
Originally posted by alejo46

excuse me i didnt understand your question, what do you mean exactly when you say how is distribution of data based on the values?
it has something to do with the Filegroup for allocating the table and its indexes ?
thanks

I was asking on amount of data for each of partitions. Are they in same order of magnitude?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

alejo46
Posting Yak Master

157 Posts

Posted - 2013-07-26 : 03:56:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources