Enormous Tables - How to still get Best Performance

Question

Enormous Tables - How to still get Best Performance

Rofty

SSCrazy

Points: 2585
More actions
March 9, 2010 at 5:32 am

#98552

Hey All
Enormous
I have a bit of a problem with very important design for new trade database I’m working on. This database will be on its own server, with only three tables.
Quad
MetricGroup
Vector
MetricGroup and Vector both have a QuadKey that relates to Quad.
These tables will be rather big in size after initial data importing:
Quad - 20,000 records
Metric Group – 10,000,000 records
Vector – 200,000,000
On a daily basis 20,000 and 400,000 records will be added to MetricGroup and Vector. And later this database will be used to create a Cube for analysis.
I realize this is big database. And that performance will take a big knock as well.
Indexes will be created to ensure quicker searching but will optimized with Page Sizes and Fill factors to ensure inserting and updating performance.
My question is, whether there are any issues I might run into as far as performance goes?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's a rather vague question.

Honestly a well-designed and well-tuned database won't slow down as rows are added. Consider partitioning if you can on the large tables, evaluate the partition columns carefully. Other than that, well-indexed tables, well written queries and make sure that the hardware can support what you're running.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Are you using the Enterprise or Standard Edition of SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 3

Those table sizes are only moderate - not too scary at all. Correct design is key, as mentioned in the previous comments. With a relatively small number of additions per day (no updates?) mid-range commodity hardware should suffice. Consider indexed views if there are many queries that would benefit from that sort of pre-aggregation.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

That's why I was asking "which edition"... IIRC, indexed views and partitioned tables (which I believe would also help here) are only available in the Enterprise Edition (understood they're also in the Developer's Edition which isn't appropriate here).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Actually, IIRC, Indexed Views are available in Standard Edition, just that the QO in Enterprise Edition will automatically use an Indexed View if it is appropriate for the query.

Paul White SSC Guru Points: 150468 More actions · Answer 6

Indexed views can be created in any edition of SQL Server. (reference)

You have to use the NOEXPAND hint to access them in non-Enterprise-equivalents though. This is not necessarily a bad thing, in my view (ha! ha!). The optimizer's 'automatic' consideration of indexed views to match plans in Enterprise-and-equivalents is pretty conservative, and frequently produces daft plans. NOEXPAND is your friend with indexed views.

Paul

edit: to add the reference linky thing

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Rofty SSCrazy Points: 2585 More actions · Answer 7

Ok ... so the database isn't as big as I thought. The biggest I ever worked on before was 800MB in SS2K.

The company are currently running SQL 2005 Standard Edition, but looking into the future they will invest in SQL 2008 Enterprise. Will this mean that some of the mentioned improvements (IIRC, indexed views and partitioned tables) wont be available?

The current design is also reviewed to ensure maximum efficiency.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Partitioning's not in Standard Edition

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rofty SSCrazy Points: 2585 More actions · Answer 9

I figure that ...

Guess I'll just have to do without that for a while.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Paul White SSC Guru Points: 150468 More actions · Answer 10

roelofsleroux (3/9/2010)
The company are currently running SQL 2005 Standard Edition, but looking into the future they will invest in SQL 2008 Enterprise. Will this mean that some of the mentioned improvements (IIRC, indexed views and partitioned tables) wont be available?

SQL 2005 Standard is fine.

Enterprise is much more expensive, and partitioning is not that huge a deal - you can always use partitioned views instead.

Upgrading to 2008 Standard might be a good option.

Indexed views can be created and used in any edition.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 11

Thanks for the correction Lynn and Paul. I messed up on that one!:blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rofty SSCrazy Points: 2585 More actions · Answer 12

Thanks for all the feedback guys.

Appreciate it. 🙂

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Paul White SSC Guru Points: 150468 More actions · Answer 13

Jeff Moden (3/10/2010)
Thanks for the correction Lynn and Paul. I messed up on that one!:blush:

It was covered by the "IIRC", so no need to :blush:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi