space allocation at the time of table creation and capacity planning

[spaliwal]

Hi,

I have to do capacity planning for the sql server 2000 database. Like parameters INITIAL, NEXT ,MINEXTENTS and PCTINCREASE in Oracle , are there any parameters are there to allocate contiguous space for a table at the time of creation.It will finally help me to calculate complete database size as well. Pl.give detaild info in this regards.


Thanks in advance,

Shaileshn

 

[tlbroadbent]

You can specify the intial size, file growth (in MB or percent) and maximum size when creating a database. Read SQL BOL or the following.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_1up1.asp

Chapter 6 of the Online SQL Operations Guide addresses Capacity and Storage Management.

http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/Default.asp

The following link is about Capacity Planning on SQL 7 but will still apply to SQL 2000.

http://www.microsoft.com/TechNet/prodtechnol/sql/tips/storagen.asp

The following link discusses SQL Server 2000 "Database Architecture: The Storage Engine."

http://msdn.microsoft.com/library/en-us/dnsql2k/html/thestorageengine.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[spaliwal]

Hi TL Broadbent,

Thnaks for your prompt reply its a collection of very good articles , I am going thru all the articles, but did not get exactly, what i want, is there any space allocation way to a individual table , say 50MB is allocated to TableA, 20MB to tableB , say 30 MB to TableC , and their growth parameter , In oracle we can do that by INITIAL we can specify Initial contigious space allocated to table MINEXTENTX and MAXEXTENTS tell about min and max of extents then PCTINCREASE is next extent will be a percentage of previous extest/inital extent. Also PCTUSED and PCTFREE is for block fill factor , in TABLESPACE clause we can specify tablespace where this space/extent will be allocated.Pl. Help me if u can give anology to all this parameter in SQL server 2000.

Thanks,

Shailesh

 

[tlbroadbent]

You can't preallocate table space in SQL Server. As I mentioned, you can preallocate space for databases with a growth factor and maximum size. I don't see any need for preallocation of table space in SQL Server. In fact, it is not necessary to preallocate database space though it is advantageous during the initial load of data if you will be adding a large number of rows.

Prior to SQL Server 7.0, database device space had to be preallocated. Now databases can grow and shrink dynamically.

We use regular database maintenance runs to maintain the size of databases. We frequently shrink databases and files. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[spaliwal]

Hi tlbroadbent,
Thanks again, But dont you feel it will increase the chances of database fregmentation, see for a paricular table we know the max size will go say 50MB in next six month , as sql server can not provide contigious space allocation for it. It will be a cause of row chaning and row migration ? In contrast Oracle provide this provision for table level space planning and its a major performance improvement also.

Shailesh

 

[moonshadow]

Terry isn't quite right (I believe this may be a first). SQL Server actually puts it's database objects in files in Filegroups. It's these files which can be allowed to expand automatically. Usually a DBA will create one file for the whole database, but it is possible to create many files, and group them into filegroups. A database object, such as a table or an index, can then be created on a particular filegroup. It would then be possible to do what you want. Look in SQL Server 2000 BOL under FileGroups/Described. It should give you all the information you need. If the filegroups are on different drives, it can lead to performance improvements. It is also possible to back up particular file groups individually which is helpful when your processing schedule does not allow enough time to back up the full database.

 

[tlbroadbent]

Shailesh,

Moonshadow is correct about using files and file groups to improve IO. In addition, preallocation of disk space can reduce fragmentation. SQL BOL contains information about "Using Files and Filegroups to Manage Database Growth." There is info about fragmentation as well. See SQL BOL or the online documentation at...

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_9hwo.asp

We have relatively small databases and only two have multiple files. However, we are using RAID 5 disks so files and IO are already spread across multiple disks.

NOTE: When tables are placed on separate files, file space can be preallocated but there is no option to preallocate table space on the file. However, for practical purposes table space is preallocated because file space is preallocated. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.