April 12, 2010 at 4:32 pm
Run SQLIO to benchmark disk performance.
Adjust Queue Depth setting on HBA to at least 64.
Stripe disks on SAN frame, do not use host based striping. Do not concatenate large quantities of disks at the host.
Add SQL Service account to "Perform volume maintenance tasks" security policy.
Adjust MAXDOP setting as high as possible without blocking other processes.
Grow the data file to the expected size before doing the import.
Dan
April 13, 2010 at 8:22 am
1) what are the IO stalls on your disk system, especially writes? see sys.dm_io_virtual_file_stats in BOL
2) as others have mentioned, indexes/triggers. also unindexed FKs maybe?
3) growth rates on the file?
4) network latency if loading from different machine?
5) IO config in general - sector alignment, ntfs cluster size, number of spindles, controller/SAN cache ratios and queue depths, etc?
6) what stuff have you already done or checked on? If you aren't a pretty skilled DBA I will recommend you get a professional on board for a bit of tuning/mentoring. TB sized databases take special handling and knowledge to make them efficient
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply