Today I got news: a joint venture of my employer will merge it's IT operations into ours. What does it mean to me?My largest DB table goes from 100 million to 500 million rows. What can I make to improve MS SQL Server database scalability?
Good things for database scalability:
- do the right indexes(avoiding autocounters if possible)
- partition tables' data
- put old data ointo separate table (archive)
- distribute DB, Temps DB and Transaction logs to different filegroups
- distribute above to hard drive partitions
- use nolock, readpast and/or READUNCOMMITTED where possible
- shift processing of some data to out of business hours times
To avoid:
- triggers
- cursors
- PK-FK relations (CHECK constraints are cheaper)
for SQL Server 64 bit database scalability check out:
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/01/21/5853.aspx
http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/05/16/13724.aspx