This seems so fundamental that I almost think I shouldn't mention it but I will anyway.

When designing a database, its indexes, and the queries that access it, do everything you can to:1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),2. avoid physical disk writes (they are significantly slower than disk reads),3. avoid physical disk reads.

Your hard drives are the slowest components on your system by several orders of magnitude.

Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.

so glad to join this article and saw many splendid ideas about the performance tunning tips..But we should think about the fragmentation about the database table.It will reduce the performance more if more fragmentation exists in the table.You can check the fragmentation of the table through the view sys.dm_db_index_physical_stats. The view returns size and fragmentation information for the data and indexes of specified or view.

The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. So, you can decide whether you need rebuild or reorganize the index of the table to reduce the framentation.

KSB-----------------------------Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

This was an excellent beginner’s article. Performance tuning is an art in itself.

It’s particularly confusing when your first starting out, as there is such a wide variety of issues to consider, not only in identifying which measurements are important but also identifying the correct measures to take to improve performance.

The article has also encouraged other users to post their experiences and knowledge of performance tuning which will also be useful for the beginner.

crainlee2 (11/17/2008)This seems so fundamental that I almost think I shouldn't mention it but I will anyway.

When designing a database, its indexes, and the queries that access it, do everything you can to:1. not use the database at all (I've known programmers who used it for logging when a flat file would have been much more efficient.),2. avoid physical disk writes (they are significantly slower than disk reads),3. avoid physical disk reads.

Your hard drives are the slowest components on your system by several orders of magnitude.

Do everything you can to design for the use of the system and database caches. This will speed up everything. This means lots of memory dedicated to SQL Server and correct indexing of all search fields.

And no correlated sub-queries - period.

LC

Interest Thing you’re proposing: Not to use the database at all, just cache. But how? And what if you don’t have enough memory? I encourage you to write an article about that.

I think he is talking about a case to save let's say the application's user's parameter or stuff like that, where the usage of the db is not strickly required. This is more a case of correct analysis and planning. If the apps needs the db, then use it, otherwise it might be worth it to use the users' local machine to save some workload on the server.