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.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2008-12-05 : 05:44:55
|
| Current implementation• Receive anything from 8,000,000 – 20,000,000 transactions a day• Each transaction populates a number of tables (header, multiple children (star topology))• Integration layer processes data and persists in DB (can be anything from 1 dump per day to real time integration (delta dumps))• DB is queried by online system • Online system generates SQL on the fly (using Linq)• Due to size of tables, and to ensure good performance, only 6 weeks of data is retained (42days X 8,000,000 = 336,000,000 transactions at least – each transaction can hold anything from 10-200 items = 3,360,000,000-67,200,000,000)• Tables are also partitioned (one partition for each table per day)o E.g. 6 weeks = 42 days42 days = 42 partitions X number of tables• Every day, a job is run to drop all partitions in the database for a specific date and create a new one for the next/new dayIssue• Partitioning requires enterprise edition of SQL ServerResolution• Create own partitioning :• We create table templates, and every night in a job, all tables are dropped for the latest day and tables are created for the next/new day • Issue: This requires access in procedures to the system views/catalog (which could be an issue as we may not be allowed access)QUESTION• Is there a better way to tackle this? We just need the best performance as possible, and without using partitioning (due to enterprise requirement).• The data is rarely updated (it just a write dump (1 to multiple times a day) and then frequent reads by an online system). • I understand that the indexing strategy is of utmost importance – we cannot at any point have the query optimiser performing an index or table scan. • However, is our approach wrong, and what other ways can we tackle this problem?• Can anyone give me some links to information/documentation that may be of assistance?ThanksHearty head pats |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-05 : 07:08:44
|
| Are you currently using Enterprise Version but moving to a lower version? |
 |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-12-05 : 07:10:56
|
| That's correct. We preferably want to move to standard edition.Hearty head pats |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-05 : 07:27:47
|
| What is happening to the enterprise version? The reason I ask is you could use it to "create your own partitions" on the non-Enterprise versions. |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-12-05 : 09:08:08
|
| We are a software development house, and therefore, under our instruction, our clients have to purchase the SQL versions that we require. This is fine if they already own enterprise edition, but it would be better for us if we can reduce the required costs (as not all clients own SQL Server). Also, we are going to host some applications in house, and therefore, need to reduce our costs.Hearty head pats |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-05 : 09:16:12
|
| Moving away from Enterprise for the number of Transactions you describe will probably cost money through lost business due to poor performanace.But here is link to "creating your own partitions":http://sqldev.wordpress.com/2008/03/16/sql-server-table-partitioning-without-enterprise-edition/ |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-12-05 : 09:22:53
|
| Thanks for the link. I shall have a read.Do you think there are any other ways to approach this issue other than partitioning?Hearty head pats |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-05 : 11:22:04
|
| Additional Hardware / Clustering /SANs / Mirroring etc but they all cost money. Vertical partitioning (available in all versions) may help slightly.Note: Processor licensing for SQL Server counts a multi-core processor as needing a single processor licence. So running Enterprise on a smaller number processors but with higher number of cores can reduce licence costs. |
|
|
|
|
|
|
|
|
Configuring a large DB for performance