When you develop an application and a database schema,
you should keep performance and scalability in mind.
You make many choices during the application design phase
that can eventually affect the performance and the scalability of the system.
These choices include the following:
* The use of temporary worktables
Often these tables are effective when the database is small,
but as the amount of data becomes larger and larger, they cease to function properly.
* The use of aggregate functions
The use of aggregate functions such as MIN(), MAX(), and AVG() scales with the amount of data used.
So be careful that your data set does not eventually become unwieldy.
* The use of indexes
As the amount of data grows, the use of indexes becomes much more important.
* The use of transactions
The use of explicit transactions is great for assuring that operations are atomic. However, as the number of concurrent users grows, it is important to reduce locking as much as possible.
As you can see, you should keep several factors in mind
if you want to design a system that performs well as the workload grows.
By incorporating performance optimization techniques from the design stage,
you should be able to create a scalable system.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook