Mysql – Best Method or Tool to Migrate from MySQL to SQL Server

To answer the actual question about tuning, rather than looking at internals, which can help you understand why SQL behaves in some ways, there's only 1 undocumented option I can think of.

You need to ensure that your transaction log file does not have too many VLFs inside it otherwise log operations will be slowed down. To view the number of VLFs you must use the undocumented DBCC LOGINFO command, and then take corrective action. Kimberly has a great blog post about it at http://sqlskills.com/blogs/Kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Apart from that ,there is no real magic to perf tuning. It's all about having the right design, the right indexing strategy, the right database maintenance, and re-evaluating it all over and over to make sure that your requirements are still met by all of the above. There's no /faster switch for SQL Server.

Hope this helps

From Microsoft :

"As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer."