This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Integration Services-2”.
1.To increase this Rows / sec calculation in SSIS, you can do the following:
a) Improve drivers and driver configurations
b) Start single connections
c) Use single NIC cards
d) None of the mentioned
Explanation:Make sure you are using the most up-to-date driver configurations for your network, data source, and disk I/O.
2. Point out the correct statement :
a) If the network is your bottleneck, then a potential solution is to use single NIC card per server
b) To overcome limitations of drivers, you can try to start multiple connections to your data source
c) For 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 32-bit drivers
d) None of the mentioned
Explanation:As long as the source can handle many concurrent connections, you may see an increase in throughput if you start several extracts at once.
3. When you execute SQL statements within Integration Services, the following optimization can be made ?
a) If Integration Services and SQL Server run on the different server, use the SQL Server destination instead of the OLE DB destination to improve performance
b) In SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache
c) If you cannot use commit size 1, use the highest possible value of commit size to reduce the overhead of multiple-batch writing.
d) All of the mentioned
Explanation:When using parallel pipelines (see points #8 and #10 below), it provides a high-speed, shared cache.
4. Which of the the following steps should be carried out using data types in SSIS for efficient results ?
a) Make data types as wide as possible so you will allocate more memory for your transformation.
b) Do not perform excessive casting of data types
c) Do not watch precision issues when using the money, float, and decimal types
d) None of the mentioned
Explanation:Excessive casting of data typesit will only degrade performance. Match your data types to the source or destination and explicitly specify the necessary data type casting.
5. Point out the wrong statement :
a) Heap inserts are typically slower than using a clustered index
b) Use partitions and partition SWITCH command; i.e., load a work table that contains a single partition and SWITCH it in to the main table after you build the indexes and put the constraints on
c) Use the NOLOCK or TABLOCK hints to remove locking overhead
d) Use a commit size of <5000 to avoid lock escalation when inserting; note that in SQL Server 2008 you can now enable/disable lock escalation at the object level, but use this wisely [expand title="View Answer"]Answer: a Explanation:For using Heap inserts,you may want to drop indexes and rebuild if you are changing a large part of the destination table; you will want to test your inserts both by keeping indexes in place and by dropping all indexes and rebuilding to validate.[/expand] 6. Which of the following error message indicates that the package cannot run in SSIS ? a) DTS_E_CANTINSERTCOLUMNTYPE b) DTS_E_CACHELOADEDFROMFILE c) DTS_E_CANNOTCONVERTBETWEENUNICODEANDNONUNICODESTRINGCOLUMN d) DTS_E_CONNECTIONREQUIREDFORMETADATA [expand title="View Answer"]Answer: a Explanation:Cache Transform transformation is trying to write data to the in-memory cache.[/expand] 7. Which of the following is event logged by the Integration Services Service ? a) SQLISService_255 b) SQLISService_256 c) SQLISService_155 d) SQLISService_156 [expand title="View Answer"]Answer: b Explanation:SQLISService_256 event indicates that the service is about to start.[/expand] 8. Purpose of SQLISPackage_12546 event in SQLIS package is to : a) Indicate that a package started. b) Indicate that a task or other executable in a package has finished its work. c) Indicate that a warning message was raised in a package. d) All of the mentioned [expand title="View Answer"]Answer: b Explanation:SQLISPackage_12288 indicates that a package started.[/expand] 9. A basic package in SSIS includes the following elements a) T-SQL flow elements b) Control flow elements c) DataSet flow statements d) None of the mentioned [expand title="View Answer"]Answer: b Explanation:Control flow elements perform various functions, provide structure and control the order in which elements run.[/expand] 10. Which of the following evaluation operation is provided in SSIS ? a) A constraint that uses only the execution result of the precedence executable to determine whether the constrained executable runs b) An expression that is evaluated to determine whether the constrained executable runs c) An expression and a constraint that combines the requirements of execution results of the precedence executable d) All of the mentioned [expand title="View Answer"]Answer: d Explanation:The expression must be a valid SSIS expression, and it can include functions, operators, and system and custom variables.[/expand] Sanfoundry Global Education & Learning Series – SQL Server.
LinkedIn | Facebook | Twitter | Google+