Tips for using table hints in SQL Server 2019 If your query is very slow, try the following before using table hints: – rebuild indexes used in the query (or defragment them using the DBCC INDEXDEFRAG command), – update statistics on the relevant tables, – consider creating new indexes for this query, – rewrite your query to provide better execution...
Tag: snapshot
Tips for using table hints in SQL Server 2017
Tips for using table hints in SQL Server 2017 If you want to set any table hints, do not remember to test the query with hint and without the hint and compare results. Because SQL Server 2017 query optimizer usually proposes the best execution plan, you should test the query with table hint and use this hint only when it...
Tips for using Snapshot Replication in SQL Server 2017
Tips for using Snapshot Replication in SQL Server 2017 Run the Snapshot Agent as infrequently as possible. The Snapshot Agent bulk copies data from the Publisher to the Distributor, which results in some performance degradation. So, try to schedule it during CPU idle time and slow production periods. Consider specifying a simple or bulk-logged recovery model for the subscription database....
Troubleshooting SQL Server 2016 Merge Replication
Troubleshooting SQL Server 2016 Merge Replication If you have problems with SQL Server 2016 merge replication, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2016 service pack. Because some SQL Server 2016 Merge replication bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the time...
Troubleshooting SQL Server 2016 Transactional Replication
Troubleshooting SQL Server 2016 Transactional Replication If you have problems with SQL Server 2016 transactional replication, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2016 service pack. Because some SQL Server 2016 Transactional replication bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the time...
Troubleshooting SQL Server 2016 backup/restore problems
Troubleshooting SQL Server 2016 backup/restore problems If you have problems with SQL Server 2016 backup/restore, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2016 service pack. Because many SQL Server 2016 backup/restore bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the time this article...
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 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 […]
Troubleshooting SQL Server 2014 Snapshot Replication
Troubleshooting SQL Server 2014 Snapshot Replication If you have problems with SQL Server 2014 snapshot replication, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2014 service pack. Because some SQL Server 2014 snapshot replication bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the time […]