Disk Organization Can Significantly Impact Database Performance
There are a lot of techniques for organizing your database files that impact the performance of your database. For some databases, there is no measurable gain; for others, your implementation can determine if the database will perform at all.
At issue are keeping statistics up to date, reducing the amount of data fragmentation, and adequate amounts of memory for data cache. For this reason, smaller databases have less benefit from optimization, because much of the work can be done real-time and often in cache. Frequently for small databases you can leave the default settings for auto-create statistics, and to automatically update statistics real time. This keeps the query performance optimized as the statistics are up to date.
For large databases, or those databases where a large percentage of the data changes frequently, it is often impossible to maintain performance and statistics at the same time. Some systems will use the old federated view technique, or the newer partitioned tables allowing data to be added and removed in large chunks.
This technique provides the benefit of being able to drop large amounts of obsolete data in a few lightweight commands. It also has the added benefit that only new data requires updating of statistics and/or defragmenting.
For example, having a partitioned table of 31 days, with a separate partition for each day, you would simply add new data to a specific day of the month. Indeed, you could have a process that removes and deletes the oldest partition, and creates a new partition for the next batch of data. The old is removed in a very lightweight set of commands. The new is added, and able to be defragmented once the following day. Since all new data is only added to the newest partition daily, only that partition will be fragmented, and require updated statistics.
Perhaps you would like to share some of your expertise in this area? We’ll be touching on how this can be optimized when you are using a SAN, SSD, Azure, or direct attached storage. Perhaps we will get to RAID and the impact it has on performance, as well as the availability and use of RAM. Add your comments below, or drop an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Should I Give Discounts on My Products and Services?
I have been approached by non-profits as well as other small start-up companies to give them a discount on my products and services. Should I give them a discount?
Featured White Paper(s)
Harness Your Data for Better, Faster Decision-Making
read more)