Some tips for using Transactional Replication in SQL Server 2016 (Part 1) Try to keep transactions as short as possible. Because SQL Server send changes from the Publisher to Subscriber as INSERT, UPDATE, and DELETE statements you should keep transactions as short as possible to help the Distribution Agent to transfer transactions through the network. By the way, keeping transactions […]
Author: Alexander Chigrik
Some tips for using Snapshot Replication in SQL Server 2016
Some tips for using Snapshot Replication in SQL Server 2016 Avoid publishing unnecessary data. Try to restrict the amount of published data. This can results in good performance benefits, because SQL Server will publish only the amount of data required. This can reduce network traffic and boost the overall replication performance. Do not configure the Distribution Agent to run continuously. […]
Some tips for using SQL Server 2016 file and filegroups
Some tips for using SQL Server 2016 file and filegroups Do not set the autoshrink feature. Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to ON. Place log files on RAID 1+0 (or RAID 1) disks. By […]
Tips for using XML in SQL Server 2016
Tips for using XML in SQL Server 2016 Consider using the RAW mode of FOR XML queries, instead of AUTO or EXPLICIT modes. The RAW mode transforms each row in the result set into an XML element with a generic identifier as the element tag. Using this mode provides the best overall performance, in comparison with the AUTO and EXPLICIT […]
Tips for using jobs in SQL Server 2016
Tips for using jobs in SQL Server 2016 Run the scheduled jobs during periods of low database access. To reduce the total SQL Server overhead, try to schedule SQL Server 2016 jobs during CPU idle time and slow production periods. For example, you can schedule the job to run every night at midnight. Specify the understandable job name. Try to […]
Some tips for using backup and restore in SQL Server 2016
Some tips for using backup and restore in SQL Server 2016 Consider using the simple recovery model to minimize the backup/restore time and minimize administrative overhead for the transaction log. Under the simple recovery model the transaction log is not backed up. So, the data is recoverable only to the most recent backup of the lost data and the point-in-time […]
Tips for using SQL Server 2016 cursors
Tips for using SQL Server 2016 cursors Reduce the number of rows to process in the cursor. To reduce the cursor result set use the WHERE clause in the cursor’s select statement. Do not forget to close SQL Server 2016 cursor when its result set is not needed. To close SQL Server cursor, you can use CLOSE {cursor_name} command. This […]
Some tips for using bulk copy in SQL Server 2016
Some tips for using bulk copy in SQL Server 2016 Consider using minimally logged bulk copy whenever possible. The minimally logged bulk copy is much faster than other bulk copy methods, but to use it you must provide all the following conditions: 1. The database option ‘select into/bulkcopy’ is set to true (by the way, using this option causes the […]
SQL Server 2016 Reporting Services Optimization Tips
SQL Server 2016 Reporting Services Optimization Tips Use Reporting Services web portal. The Reporting Services web portal introduces in SQL Server 2016 and replaces Report Manager from previous releases. This is a modern portal which incorporates KPIs (Key performance indicators), Mobile Reports, Paginated Reports, Excel and Power BI Desktop files. Consider using the Chart data region. SQL Server 2016 supports […]
Tips for using constraints in SQL Server 2016
Tips for using constraints in SQL Server 2016 Try to create a single column constraint. The more columns the constraint will have, the slowly it will work and the more stored space it will require. Use default constraints instead of DEFAULT objects. DEFAULT object are provided for backward compatibility and has been replaced by default definitions (default constraints) created using […]