Author: Alexander Chigrik

Pro Members SQL Server Standard Members

Tips for using Merge Replication in SQL Server 2017 (Part 1)

Tips for using Merge Replication in SQL Server 2017 (Part 1) Avoid using join filters with five or more tables. Because join filters with five or more tables can significantly degrade merge replication performance, you should avoid join filter for the small lookup tables, or denormalize the database design instead of using join filters with five or more tables. Use...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using Transactional Replication in SQL Server 2017 (Part 2)

Tips for using Transactional Replication in SQL Server 2017 (Part 2) Consider using transactional replication to memory-optimized tables. In SQL Server 2017, tables acting as transactional replication subscribers, excluding peer-to-peer transactional replication, can be configured as memory-optimized tables. To configure the subscriber database for supporting replication to memory-optimized tables, you should set the @memory_optimized property to true by using sp_addsubscription...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using Transactional Replication in SQL Server 2017 (Part 1)

Tips for using Transactional Replication in SQL Server 2017 (Part 1) 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. Increase the -MaxBcpThreads parameter of the Distribution Agent....

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

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....

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using SQL Server 2017 file and filegroups

Tips for using SQL Server 2017 file and filegroups Consider placing the log files on other physical disk arrays than those with the data files. Because logging is usually more write-intensive, it’s important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance. Do not create many data and log files on the same physical...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Some tips for using tempdb database in SQL Server 2017

Some tips for using tempdb database in SQL Server 2017 Set the reasonable size for the tempdb database and transaction log. First of all you should estimate how large your tempdb database will be. To estimate the reasonable database size, you should estimate the size of each table individually, add some additional space (10-20%) and then add the values obtained....

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using SQL Server 2017 cursors

Tips for using SQL Server 2017 cursors Use READ ONLY cursors, whenever possible, instead of updatable cursors. Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set. Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can results in some...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using backup and restore in SQL Server 2017

Tips for using backup and restore in SQL Server 2017 Consider storing the backup files on physical disks on another computer. Storing the backup files on the same computer where the databases stores may cause problem with restoring databases if the physical disks were damaged. Try to separate your database to different files and filegroups to backing up only appropriate...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Some tips for using XML in SQL Server 2017

Some tips for using XML in SQL Server 2017 Consider using the XML data type. This data type is used to store XML documents in table columns or Transact-SQL variables. The XML data type can be used in variables, columns, or in stored procedure and function parameters. Use the XQuery value() method instead of the query() method when you want...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using SQL Server 2017 triggers

Tips for using SQL Server 2017 triggers Try to use CHECK constraints instead of triggers whenever possible. Constraints are much more efficient than triggers and can boost performance. Constraints are also more consistent and reliable in comparison with triggers, because you can make errors when you write your own code to perform the same actions as the constraints. So, you...

This content is for Pro, Pro Member, Pro Member Annual - Valentine's, Pro Member Monthly - Valentine's, Standard, Standard Member, Standard Member Annual - Valentine's and Standard Member Monthly - Valentine's members only.
Log In Register