i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
Clusters are for high availability (HA) - not load balancing. You should
look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
your queries always include the partitioning column in the WHERE clause, you
should be able to realize a performance benefit without necessarily going to
more servers. In the event that partitioned tables, or local partitioned
views, don't do it, then distributed partitioned views -0 where you do use
multiple servers - can be the solution.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
|||Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory Performance
bottleneck.
please help me to find a solution for all resource include (CPU,Ram,Disk,...).
"Tom Moreau" wrote:
> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause, you
> should be able to realize a performance benefit without necessarily going to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||Well, without seeing your entire system, that would be difficult.
If you have a disk I/O bottleneck, it could be that you're memory starved.
It could also be that your RAID array has very few spindles.
What numbers have you been getting from Perf Mon?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory
Performance
bottleneck.
please help me to find a solution for all resource include
(CPU,Ram,Disk,...).
"Tom Moreau" wrote:
> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause,
> you
> should be able to realize a performance benefit without necessarily going
> to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert
> many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many
> hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:
> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck is
> not only Disks resources but also CPU or memory, this method can resolve my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||As Tom mentioned, look in Books Online for "Distributed Partitioned Views".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...[vbcol=seagreen]
>i think it is not difficult. i want to distribute my big table between many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
> table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
> servers
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
|||That's the definition of distributed partitioned views. If you have a copy
of "Advanced Transact-SQL for SQL Server 2000", Chapter 13 gives you details
on how to do it. Using DPV's, Microsoft was able to set a new tpmC
benchmark soon after going RTM with SQL Server 2000.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:
> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck
> is
> not only Disks resources but also CPU or memory, this method can resolve
> my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||I think you are not considering the full ramifications of partitioning that
Tom has recommended, even on a single hardware installation.
It is not just a disk resource issue that partitioning attempts to resolve.
Unless you expect each of the 5 users to query the entire 10 Billion rows on
each pass, partitioning will limit the user to the specified data needed at
for any individual pass if the partitioning function is chosen correctly.
I suspect this table is large because the data is time sensitive, that is,
historical?
If so, if you had a dedicated table (or partition) for each month, week,
day, whatever the granularity is, and then one only needed data for that
particular date range, they would then only access a single table
(partition) instead of the entire data set.
If this data represents years of monthly, or daily, data, then each query
would only have to deal with a handful of tables, each only a few hundred
thousand or so records. Each query then becomes 10, 100, maybe even 1,000
or more times quicker.
Sincerely,
Anthony Thomas
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
> i think it is not difficult. i want to distribute my big table between
many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
servers[vbcol=seagreen]
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
starved.[vbcol=seagreen]
all[vbcol=seagreen]
is[vbcol=seagreen]
my[vbcol=seagreen]
should[vbcol=seagreen]
If[vbcol=seagreen]
clause,[vbcol=seagreen]
going[vbcol=seagreen]
partitioned[vbcol=seagreen]
use[vbcol=seagreen]
Insert[vbcol=seagreen]
