Tips for using stored procedures in SQL Server 2016 Try to avoid using temporary tables and DDL (Data Definition Language) statements inside your stored procedure. Using temporary tables or DDL statements inside stored procedure reduces the chance to reuse the execution plan. Consider using user-defined table type as a parameter for the stored procedures. SQL Server 2016 supports a user-defined […]
Tag: tips
Some tips for using indexed views in SQL Server 2016
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 […]
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 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 […]
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 […]