Some tips for SQL Server 2016 clustering Avoid running other software on the SQL Server 2016 cluster nodes. To increase SQL Server 2016 performance, SQL Server clusters should be dedicated to SQL Server only. Consider using Distributed Availability Groups (DAG) if you have two availability groups residing on different Windows Server Failover Clusters (WSFC). DAG enables you to associate two […]
Author: Alexander Chigrik
Some tips for using table hints in SQL Server 2016
Some tips for using table hints in SQL Server 2016 Use the INSERT … SELECT statement with the TABLOCK hint. In SQL Server 2016, the insert in an INSERT … SELECT statement is multi-threaded or can have a parallel plan. To get a parallel plan, you can use the compatibility level 130 and the INSERT … SELECT statement must use […]
Some tips for using query hints in SQL Server 2016
Some tips for using query hints in SQL Server 2016 If your query is very slow, try the following before using query hints: – rebuild indexes used in the query (or defragment them using DBCC INDEXDEFRAG), – update statistics on the relevant tables, – consider creating new indexes for this query, – rewrite your query to provide better execution plan. […]
Some tips for using full-text search in SQL Server 2016
Some tips for using full-text search in SQL Server 2016 Use a full-text query instead of the LIKE Transact-SQL predicate if you need to query formatted binary data or query a large amount of unstructured text data. A full-text query against millions of rows of text data can take only seconds; whereas a LIKE query against the same data can […]
Tips for using stored procedures in SQL Server 2016
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 […]
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 […]