Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2004-07-28 : 19:54:12
|
| I've got a relatively big table -- about 1,500,000 rows -- that tracks web application page loads. Thing is, 99.9% of the time, it's just sitting there collecting new rows. At the end of the day, it gets digested into more manageable stats.During the day, sometimes, someone needs to run queries against it. That's rare, but does happen. It seems like a waste to keep common indexes on the table all the time, but it's also ridiculous for both the querying user and the DB in general to have all management activity generate lots of table scans.This has to be a common problem. For grins, here's my idea of a solution. Can someone please tell me a more sane way to approach it?My idea: have a sp that creates all of the indexes that management reports need (if they're not there already), and another that deletes those indexes. Have a table that tracks the last time a report was run. Schedule a job to delete the indexes if it's been more than 30 minutes since a report. Preface every management report sp (and the end of day digest sp's) with a call to the generate indexes sp.Now, I love a good hack, and this definitely counts. But really, is there a more common way of doing this sort of thing?Thanks-b |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-28 : 20:27:24
|
| Why don't you create a mini data warehouse that will fulfill your reporting needs?It would be better than faffing about with creating/deleting indices on your production tables. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-28 : 20:54:54
|
| I'm with Timmy. Maybe you can setup a job or write an app to pull out data from the live un-indexed table at certain intervals and insert it into the Indexed table for reporting purposes.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-29 : 09:11:16
|
| 1.5?That's not to big...I like the warehouse....And do you have the code for the report...betcha it's doing a scan with the indexes anyway....Did you do a show plan?Brett8-) |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-07-29 : 21:08:00
|
| Thanks for the ideas. Using a mini warehouse sounds like a reasonable idea; the data does need to be current (past 10 minutes) for reporting, but the reporting sp's can easily ensure that it is before running.Cheers-b |
|
|
|
|
|
|
|
|
Part-time indexes?