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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-10-15 : 17:12:32
|
| Jesse writes "I'm new to the data warehousing concept. People don't want to fork up money for a data warehouse system but reporting is slow and bogs down server for other users. So I decided to make the beginnings of a data warehouse on the same server. I made some fact tables and some dummy dimension tables (queries wanting dimension data join to the orignal OLTP tables for the data) and reporting from these fact tables is EXTREMELY FASTER.But everytime the updater script runs it crates shared / intent shared table locks on the OLTP tables which stay up for a while becuase table scans are being performed on heavy-insert poorly indexed tables that have >10,000,000 rows. The fact table updater makes heavy use of derived tables and there is no temptable or cursor usage.I'm thinking about using read uncommitted but some of the source tables receive update statments, although the big monster tracking tables reacieve mostly inserts and a few selects (about 90%/10%).The fact table updater takes about 1 1/2 hours to run all modules. It can be instructed to run one at a time so segments can be run at different itnervals (most of the run time is on search tracking, clickstream tracking is next slowest, with event tracking the quickest).There is only 1 server, at a distant co-lo. 2x800 P3, 512MB, 9GB scsi RAID 1 (software) Plus 36GB scsi single drive (core data, master DB, MSDB is on RAID, tracking, tempDB, 2 secondary DBs, and Main DB Trans log on non-raid). Server also has web server and full-text indexing. Buffer cache hit ratio is 98% during day (normal ops), drops to around 90% at night when batch processes run. CPU usage averages 60% during the day (its always 100% when a batch process runs, which is usually 3 hours of tiem between 12am and 6am) and I/O usage is below 10% when something batch isn't running. The DB is 2.5GB, about 1.6GB of it is tracking. The site averages about 50,000 page views, 5000 searches, 2000 unique sessions (session cookie, not ASP or webtrends), and 1000 unqiue visitors/machines (persistant cookie) per day.Is ReadCommited the best way? Is there any other way to deal with the massive blocking that this updater is causing?" |
|
|
|
|
|
|
|
Populating fact tables creates intrusive table locks, blocking queries from website