SQL Server Performance Forum – Threads Archive

DTS Performance Ideas

Each night, I copy our production ERP system into a reporting system (datawarehouse). The job is starting to creep up there in time. I just want to do a straight dump from production into reporting. I truncate the reporting table and then copy all the information from the ERP. I do not use queries, lookups or transformations. I am using Fast Load as the option on the transfers and no transactions for the package properties. I assume no transactions is grabbing a dirty read of all the data. Would it be better to use transactions in case a process is trying to read the table I am inserting into? Process is done at 3am so no other processes should be running at that time but being a reporting server, if someone can’t sleep there is the potential that they could be on it. I was wondering what I can do to speed up performance with SQL server. One thing I am uncertain of is the Fetch Buffer Size. I have increased this playing around, but not sure how much it adds in performance and how high I can go. There are 4 tables that I copy with over 3 million rows in them. I’m also open to changing the job from a reload all data. Just not sure how I can verify I’m getting all the changed data for there is no update date field on many of the tables. Any advice is appreicated, thank you. Thanx
Adam

http://www.databasejournal.com/features/mssql/article.php/1499481
http://www.databasejournal.com/features/mssql/article.php/3095511
http://msdn.microsoft.com/library/en-us/dtssql/dts_usage_7703.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Few more
http://www.sql-server-performance.com/dts.asp
vyaskn.tripod.com/sql_server_dts_best_practices.htm
www.quepublishing.com/articles/article.asp?p=23571 —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

Thank you for the links. But there are a few things that I do not know or cannot find. Was wondering about personal experience. I have Googled on the Fetch Buffer and can’t really find anything more than to just increase the size. How does the fetch buffer work and will it eat up lots of RAM if I say fetch 10,000 rows. If I do that will I see a lot of performance improvement than fetch 1000 rows. Should I be using transactions, I just need a dirty read and don’t want to tie up the system while dumping data. Thanx
Adam

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dblibc/dbc_pdc05_1y20.asp abotu fetch buffer information, also confirm the memory settings & service pack levels on your SQL Server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>