Tripp’s (2006) article discusses ways to improve the performance of a SQL Server-based application. The author approaches the subject from a "life of a query" perspective. What does the author mean by that? Discuss a database design consideration from the article and its relationship to database performance when a query is executed.
The author is describing the life if the query as the different basic stages that an SQL query executes, she only goes into two, saving and retrieving data, and then describes four different performance categories which are statistics, maintenance, indexing, and database design. She goes into quite a bit of detail on each of these subjects. It looks like some of the information in this article is missing but here’s the basic breakdown of these topics.
Statistics
These seem to be left out in this article, but these can be helpful in analyzing data. Some of the statistics that SQL programmers like to look at when trying to optimize the database are CPU usage, reads, number of milliseconds used to execute one query verses another, etc. One of the tools that can be used to do this is Benchmark Factory. The writer of the article says that this should be done during development before the database / query hits production because it is much easier to fix potential errors at this stage than during the maintenance stage.
Maintenance
Maintenance includes backing up a database, compacting, and re-indexing the database to name a few of the functions involved. With good indexing maintenance can take up less resources.
Indexing
Indexing is not required as far as I can tell, but it is a good idea because it can increase the performance of the database. There are features and benefits that can be unlocked with a good database design, so this should be considered up front to help the SQL server perform better.
Database Design
She talked at length about this topic. I found it interesting that she referred to a database being too flat. I’ve learned to far that normalization can be taken too far. Normalization just for normalizations sake is not ideal, but I definitely agree that there needs to be a balance there, and I think that she explained this quite well.