ABOUT sql server 2005 partition

hello,

We are writting an erp for group,and want to use partition of sql server 2005 for large data,our environment is window 2003 server,8g memory.my one solution will create many physical files(about 500-600) to fit 500-600 partitions,how about performance? or I will use another arithmetic in order to reduce partitions?

Hi,

Wondering why you will need that many partitioons for. How big your table is likely to be ? how many rows? What are planning to use as you partition key?

Jag

|||

hello,

Thank you for your attention,following are our case:

we wrote an erp for a group,it have five child companys,three factorys,six trademark,we finish distribution sale,supply chain,production(not running),our database size now is 19G on sql server 2000,the system is slow,we now upgrading hardware and software,database will use sql server 2005.

I analyse the system, if I use partition to divide trademark,customer data and warehouse data and supplier data,producing,and every child company also will use partition,I think it will be better more,if a company have 10 partitons, five companys will reach 50 partitions,I really not know how many partitions will right,and performace is best?

I also want to do an online system for small company,if using design above,50 companys will up to 500 partitions,I think this case,the arithmetic will different from above,it will not grow with company amount,is right?

Thank you,again.

|||

It really seems like that is a lot of partitions, especially for a database that is only 19gb. Really, the idea behind using partitions is somewhat two-fold. First, you can segreate your archive data from your live data for performance reasons. Also, you can segment your historical or archive data itself for performance benefit. You can also move data from partition to partition as needed. However, there is a limit the number of partitions you can create, and as the number of partitions grows, the amount of administration grows. This leads to the type of hardware you are using.....are you using a SAN or some sort of high-performance shared disk storage? If so, what RAID level are you using? If this design is optimal, it might be time to look at how your application is written. Most likely, you will see the highest performance gain by slight design changes and query optimizations as compared to hardware upgrades and partitioning.

Tim

|||

hello,

thank you for your suggest,I learn a lot.

The company have two system,one for sale,one for purchase,we now will incorporate these system,incorporation size are at least 30g,retail system are working recently,all shops are about 400 amount,so data will grow faster than before,at next quater,the production system will begin,also will produce so much data,we use raid5,donn't use SAN,Perhaps I need study more knowledge about SAN.

we upgrade hardware and software,the old system is slow,often happen concurrentcy problem,the erp first is bought ,but couldn't fit the company,the architecture is very bad,we do a great lot of redevelopping,now I redesign architecture,consider optimization at transation,query,analyse,and in order to predigest the communication between company, customer, warehouse,factory,supplyer, I put some table together to one table,using partition to divide them,I think it will have good performance for transation, and persormance for group report,my design have consider about partition administrator, but as you have mentioned,partition for history data, yes,it will good for performance,there is another solution, I want to design an OLAP system for histotry analysing,is it right?

at last,how much about partition limit? thank you