473,320 Members | 1,691 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL 2000 Physical Layout Question

rc
Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks
Jul 23 '05 #1
5 1599
Ray
Not really. Although there many be some small gain in having multiple files
in file group. At least equal to the number of processors in the server. I
would recommend the following layout for data and index.

Assuming four processors, create four files in your primary file group to
hold the data. Preallocate the files as ((the size of the table + growth) *
2)/4 this should give you enough room to grow and reindex the table. Create
a second file group for the nonclustered indexes. This way if you ever want
to move the index file group off to another set of disks you're ready.

"rc" <rc@spam.com> wrote in message
news:v0********************************@4ax.com...
Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks

Jul 23 '05 #2
Hi rc,
Ray is on the right track with his solution. However, with a 90 million
row table you may have to do something more dramatic in order to
increase performance. I suggest you look into creating a partitioned
view (check it out in Books Online).

Let's assume your 90 million row table has its clustered index based of
the key "Social Security Number" and your application does MOST of its
searches using this key. You can split the single table into 10 smaller
tables (or more or less) using the SSN as your guide. Any SSN that
starts with 0 goes into one table, 1 to another table, 2 to another and
so on. Finally you create a view that performs a "select *" from each
table - the syntax can be found in BOL.

Now any query only needs to perform an inital search to see in which
table your record is located and then it performs a search on 1/10 the
amount of the original rows (assuming an event dispersement of SSNs).

Jul 23 '05 #3
rc
On 4 Feb 2005 11:43:40 -0800, "joshsackett" <jo*********@gmail.com>
wrote:
Hi rc,
Ray is on the right track with his solution. However, with a 90 million
row table you may have to do something more dramatic in order to
increase performance. I suggest you look into creating a partitioned
view (check it out in Books Online).

Let's assume your 90 million row table has its clustered index based of
the key "Social Security Number" and your application does MOST of its
searches using this key. You can split the single table into 10 smaller
tables (or more or less) using the SSN as your guide. Any SSN that
starts with 0 goes into one table, 1 to another table, 2 to another and
so on. Finally you create a view that performs a "select *" from each
table - the syntax can be found in BOL.

Now any query only needs to perform an inital search to see in which
table your record is located and then it performs a search on 1/10 the
amount of the original rows (assuming an event dispersement of SSNs).


Thanks both of you, I will have a think about the info provided

Jul 23 '05 #4
What really matters here is the number of disk spindles for the filegroup
containing the 50GB table. The more disk spindles it got, the more disks
the server can keep busy by issuing readahead IOs, and the better the
performance of the scans over the table. So try to add more physical disks
to the filegroup.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"rc" <rc@spam.com> wrote in message
news:v0********************************@4ax.com...
Hi

We run SQL 2000 on Windows 2000. The database has one big table with
approx. 90m rows in it, it also as 4 indexes on it, one of them is a
clustered index. They physical size of the database is approx. 50Gb

I am planing to migrate the database to more powerful server. My
question is on the new server would we get any benefit from have say 5
x10 Gb file for the database as opposed to a single 50GB file.

The database will have exclusive access to RAID 10 array with
dedicated controller for the data, another array for the T.Log

Any thoughts hints would be very helpful

Thanks

Jul 23 '05 #5
You might also wan´t to look in to putting the indexes(non-clustered)
on a seperate filegrou, residing on a seperate physical disk. That was
the server kan access index - and table data simultaneusly... might
speed things up as well

Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mike | last post by:
Hi guys, I need to convertvirtual (http:// ...) path to physical (c:\...) in the desktop application. Any ideas? Thansks, Mike
1
by: genetic.error | last post by:
I installed VS.NET 2003 on Windows 2000 Pro SP3. Since doing so, I have no internet connection. Also, I have no access to my home network. I've done everything I can think of to correct the...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
12
by: Puvendran | last post by:
Hi, We have encountered deadlock on a table which is used to generate sequential numbers for different categories eg typical entries Category Value TRADE_NO ...
2
by: L Mehl | last post by:
Hello -- Is there a way to configure 2000 so it will save a view layout after I change it in the diagram pane? Thanks for any help. Larry Mehl
12
by: Nobody | last post by:
DB2 500G database, Wintel, heavily loaded OLTP (5M+ transactions a day; all transactions are extremely small, all selects are controlled (no ad-hoc), 99% of all selects are very small (no table...
3
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what...
9
by: dgk | last post by:
Is there some built in way to know whether a physical folder path is 'My Documents" for a specific user? I can always use xxx.StartsWith to compare it to the enumeration returned by the Personal...
3
by: datapro01 | last post by:
I am a DB2 DBA that has been asked to become familiar enough with SQL Server in order to become actively involved in its installation, implementation, and to review database backup/recovery...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.