Tips for Getting the Most of SQL Server Performance Tuning

Some users may face issues during bulk copying. These problems are easy to resolve with all the SQL Server performance tuning tips we will be mentioning here.

Opt for the BULK INSERT query rather than DTS to add information into the database
This option is far quicker in comparison with the bcp or DTS when it comes to carrying out file import tasks. But this command is not capable of bulk copying information from the database to a file.

Try to apply non-logged bulk copy
This type of copy proves quicker than its logged counterpart. However, it requires meeting the following conditions:
● The table in question is not undergoing duplication
● One of the settings (select into) is toggled to true
● The presence of the TABLOCK hint
● The indexes are either absent or empty at the start of bulk copy.

Increase the use of native mode
Using more native mode bulk copy is helpful from the perspective of query tuning in SQL as it boosts performance.

Give preference to bcp over dts
This provides an advantage when a user has to export information from a table to a file. This is because bcp provides rapid results in comparison with dts.

Be more specific about row quantity in case of selective bulk copying
In particular, mention the first and last rows to gain an advantage in terms of performance. This is because the overall data requirement decreases.

Mention the exact row quantity for each batch you copy
This is especially important in case the t-log fills up prior to copy completion. That’s due to the SQL Server’s committing and rolling back of transactions for each batch since every batch goes to the server in the form of a single transaction.
Bulk copying big files can result in quick filling up of the t-log, even before the bulk copy is over. You can simply enlarge the log or enable it to grow automatically.

Raise the quantity of packet sizes
This section mentions the bytes that each network packet consists of that go to and from the server. The default packet size is 4096 but it may range from 4096-65535 bytes. Bigger sizes can improve the speed which is why experts recommend starting with 8192 bytes.

Apply the ORDER hint for better performance
This is beneficial during SQL Server performance tuning when the table contains a clustered index as per which the data is sorted. This way, the database will load information without reordering it, saving time and resources in the process.

Make an index after bulk loading is complete
In case of new tables, this action order helps performance as the information loading takes place without the need to build index pages while copying is going on.

Keep non-clustered indexes out of the bulk copy operation
This means it is better to drop them in case you have to fill information in an empty table. Once bulk loading is over, you can create those nonclustered indexes again. Again, the resulting effect is a positive one on bulk copy performance as index pages need not be created as the operation carries out.

Likewise, if the table isn’t empty as you attempt to bulk copy information into it, there could be any type and number of indexes already present. When the data quantity is huge in such cases, dropping the indexes from the table before you begin the operation is a better idea.

You can bring back the indexes once the task is complete to help with query tuning in SQL. Just make sure you pay attention to the time it takes to load information in both the cases- in the presence of clustered indexes and after you drop/rebuild said indexes. Try this out on a test server before going through with the final task.

Certain situations account for the presence of multiple CPUs. In such cases, the user can attempt to split the data into at least 2 sources. They can proceed to execute different instances of bcp for parallel tasking on multiple clients.

This boosts SQL Server performance tuning since the database enables bulk copying information from several clients into one table. Parallel operation is possible through both- bcp and BULK INSERT which speeds up performance. In other words, you should try to take advantage of this facility, and to do so, you will have to have a specific TABLOCK hint and set the bul copy option to true.