Some tips for using indexed views in SQL Server 2016 Consider creating two or more smaller views instead of creating a single large indexed view. Sometimes it can greatly improve performance especially if the underlying tables are located in different databases. Add an index to a view very carefully. When the underlying tables of the view are modified, the clustered […]
Tag: SQL Server 2016
Some tips for using User-Defined Functions in SQL Server 2016
Some tips for using User-Defined Functions in SQL Server 2016 Use scalar user-defined functions when you need to do the same mathematical calculations at multiple places in code. For example, if you need to calculate the factorial for the given number, you can write appropriate scalar user-defined function to encapsulate code for reuse. Consider using CLR user-defined functions. The CLR […]
Some tips for using Merge Replication in SQL Server 2016 (Part 2)
Some tips for using Merge Replication in SQL Server 2016 (Part 2) 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. Set the “Maximize Throughput for Network Applications” […]
Some tips for using Merge Replication in SQL Server 2016 (Part 1)
Some tips for using Merge Replication in SQL Server 2016 (Part 1) Avoid replicating text, ntext and image columns. These data types require more storage space and processing than other column data types. Consider setting the -OutputVerboseLevel parameter of the Merge Agent to 0. This parameter specifies whether the output should be verbose. There are three available values: 0 – […]
Some tips for using Transactional Replication in SQL Server 2016 (Part 2)
Some tips for using Transactional Replication in SQL Server 2016 (Part 2) Set the “Maximize Throughput for Network Applications” option. This can increase SQL Server performance, because Windows will allocate more RAM to SQL Server than to its file cache. Consider locating both the publisher and the distributor on the same physical server. You can place the publisher and the […]
Some tips for using Transactional Replication in SQL Server 2016 (Part 1)
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 […]
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 […]