May 20, 2009 at 9:51 am
Hi folks,
Currently working on reindexing a database of a third party monitoring application. Essentially what it does is monitor a whole bunch of servers for various parameters and uploads them into various tables. What then happens is those tables are summarized for each hour, and placed in a corresponding table with extension _H. For example, if a table is called logical_disk then the hourly version is logical_disk_H. This continues with tables of _D, _M, and _Q extensions for daily, monthly, quartery (there's even yearly, but you get the idea).
What should happen is each table is summarized and the data is inserted into the table "above", so hourly -> daily -> monthly, etc., and the old data is deleted. Eventually when it gets to yearly, that data is pruned when a year (or two or three) has passed.
My problem is that the company that designed this application may not have designed the database portion in the most efficient way. Each of the 250+ tables has NO primary key and NO clustered index. Instead, they only have two non-clustered indices, one a subset of the other. For example, NC Index 1 is on [server, write_time, diskname, timezone] while NC Index 2 is on [server, write_time]. I am currently changing the larger indexes on the biggest tables (index 1 above) to clustered, thus forcing a rebuild of index 2.
Without tinkering with the application and second guessing it's choices, is this the most transparent way to increase performance of the pruning of the data?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 20, 2009 at 1:30 pm
Changing as little as possible... yeah, that's probably a good way to go. I assume that the larger index is the most common access path? If the smaller index is, a better approach might be to make it the clustered index and then modify the larger index by dropping the first two columns because the fact of the matter is, those two indexes, as currently structured, cancel each other out. Once you cluster the larger index, I suspect that most queries will never use the smaller index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2009 at 1:34 pm
Grant Fritchey (5/20/2009)
Changing as little as possible... yeah, that's probably a good way to go. I assume that the larger index is the most common access path? If the smaller index is, a better approach might be to make it the clustered index and then modify the larger index by dropping the first two columns because the fact of the matter is, those two indexes, as currently structured, cancel each other out. Once you cluster the larger index, I suspect that most queries will never use the smaller index.
True, in which case I'll start to monitor the most frequently run queries and if necessary, drop the smaller Nonclustered index.
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply