April 14, 2008 at 9:56 am
we have a database where we dump security logs from domain controllers and a few selected servers. every server has a staging table and a real table.
data from the server or DC goes into a staging table and a SQL JOB than puts it into the real table and truncates the staging table. all tables have the exact same schema and index struture. when i created this db i set it up as a one huge file at the start and only 20% or so is being used.
what is happening is that i'm getting large differenced in time to import data from the staging tables to the real tables. in one case an 90,000 row table took 5 times as long as a 200,000 row table. i ran alter index on anything fragmented more than 5% before i started to rebuild those indexes. i use a fillfactor of 70% and pad_index on to try to minimize page splits.
only thing different is the table sizes. some tables have millions of rows and others a few hundred thousand. depending on how busy the DC is. only thing i can think of is on the less busy servers fillfactor of 70% is not low enough and that i'm wating on page splits
April 14, 2008 at 11:16 am
I'm sorry to say it, but you probably need to gather more data. Can you get Perfmon & Profiler in place to see what is occurring when you run these loads?
"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
April 15, 2008 at 7:52 am
will try it today
tried a test with one table. first i ran just a select on the data to be inserted and it came back in a few seconds. when i tried to insert that data it's running for a while. no blocking or deadlocks.
seems to be related to full text indexes. when i disable it for a table, it runs fast. on a table where FT is enabled it slows to a crawl
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply