Hi Everyone! I have finally got around to answering all the questions from my performance tuning webinar from last week. I couldn?t answer all of them, since some of them were in fragments, but I got as many as possible. So, here is what I have.
Q: Is there any way to speed up a lookup transformation without using Full cache? memory pressure
A: You may want to try partial caching. If there are still performance issues, you can use the cache transform if you are using SQL Server 2008. That will allow you to cache the records you are looking up somewhere else, such as a flat file.
Q: Can you explain how TableLock works?
A: Table Lock simply prevents anyone else from accessing a table while your SSIS package is loading data to it.
Q: When you use ORDER BY in TSQL, don't you need to set IsSorted to True in your source?
A: Yes. In the Advanced Editor, on the Input and Output Columns tab, you will see the column properties. Find the column you want to sort on and set that property to a value of that property to 'True'. Then set its sort value to 1 for ascending and -1 for descending.
Q: Shawn - In Connection Manager Properties, is there any harm in always setting RetainSameConnection=TRUE as standard developer practice? It must default to FALSE for a reason.
A: There can be some problems with that. You will find that in scenarios when you have a source and destination using the same connection manager, while using transactions, it will override the transaction. There are a few other issues as well, but they mainly stem from using transactions.
Q: What occurs if the Packet Size is set to default in the SSIS package, but the ssis job has it set to 32767?
A: The settings in the job will override the package properties.
Q: Shawn, would you talk for a minute about using RAW destination, is this a good thing or should we keep away from it?
A: The RAW file destination is a good component to use. It can write data more quickly that the OLE DB destination and the flat file destination. No conversions or parsing is required.
Q: what is the difference b/w table load and fast load?
A: Table or View will load data in a row by row approach, whereas using the Fast Load option will load data in batches and gives you some flexibility with various options.
Q: What decision criteria do you use for enabling Fast Parse ON or OFF?
A: Think about where the data is coming from. If you know that it is formatted properly then use Fast Parse. If you are getting data from end users or sources that have been known to give unclean data, the don't use Fast Parse.
Q: Is there a difference in performance between OLEDB and ADO.NET?
A: Yes. Don't use ADO.NET if you don't have to. ADO.NET is required for certain types of connections because it enables different options. If it is not necessary, than stick with OLE DB.
Q: if you store the data source on the same machine sql is stored on, does the packet size make any difference at all? I understand it only matters when you going across the network.
A: You are correct. If you are pulling data across a network, then you will see a performance gain by adjusting the packet size. If you are working on a local machine, then there is no need to adjust the packet size.
Q: You mentioned the "union all" was a partially blocking transform... why do you distinguish this as "partially"?
A: This is because of how it handles the data. It depends on the input of multiple sources while it tries to merge them together. Also, the output is copied into new memory buffers which, in turn, could bring another thread into your data flow.
Q: Will the package run faster if we have the source flat file on the server and package also runs locally on the SQL server v/s source file on the server but package runs on the network machine?
A: Yes you will. The amount of performance will vary of course, depending on the size of the dataset you are working with, but if everything is on the same machine, then the package doesn?t have to pull data across the network. Cutting out that step will help with execution speed.
Q: On Table View Fast Load what is the best Max InsertCommit Size?
A: It depends on the size of the data set. Typically, you won't have to change this, but if you are working with a large data set, then you may want to play with this setting to get a better execution time. This will require some trial and error. Just remember, this is the amount of rows that SQL Server will commit after each load.
There you have it. I hope I was able to answer everyone?s questions thoroughly enough. If you would like to expand on these, feel free to do so in the comments! Thanks again everyone!