SQL Server Performance Forum – Threads Archive

Replication Performance with Multiple Indexes

Experiencing a performance problem using SQL Server replication. When looking at the article found at http://www.sql-server-performance.com/q&a71.asp, I read the following excerpt: "It is not a good idea, from a performance perspective, to accept the default of a clustered index on a primary key, as it may not be the best choice for the use of a clustered index. In addition, it is not a good idea to "double up" on indexes. In other words, don’t put a Primary key non-clustered index on a column, and a clustered index on the same column (this is possible, although never a good idea)." We currently have a situation where a published table ("T1") in our main publishing database ("DB1") has several indexes including a Clustered index ("I1") on the PK column ("C1"). Once the table is replicated, "I1" is being transferred to our subscribing database ("DB2"), but "C1" (on "DB2") loses its constraint as the primary key. Since "DB2" is also serving as a publisher (in another replication set to a third database ("DB3")), the "C1" in "T1" on "DB2" must have a PK constraint in order for the column to be included in the replication set. So on "DB2", "C1" was altered to be a PK — which created a new non-clustered index ("I2"). This is the situation described in the excerpt above as being undesirable. Can you give me a technical reason as to why this is bad? Could this be causing the performance issues we are experiencing? If so, why? Do you have any ideas as to possible ways around this? Perhaps not including the clustered index in the original replication? FYI: We have several other instances of replication that are subscribing from the main publisher. These, however, are not also serving as publishers and, therefore, did not need to have the PK set. We are using these instances as a baseline for measuring performance. Thanks in advance for any help or suggestions you might provide, S Corbin

As explained in that Q&A :

By itself, a primary key does not have a direct affect on performance. But indirectly, it does. This is because when you add a primary key to a table, SQL Server creates a unique index (clustered by default) that is used to enforce entity integrity. But as you have already discovered, you can create your own unique indexes on a table, which has the same affect on performance. So, strictly speaking, a primary index does not affect performance, but the index used by the primary key does.
which is explained in detalied in some other tips :
If you are inserting new rows into a table that has a clustered index as its primary key, and the key monotonically increases, these means that each INSERT will physically occur one after another on the disk. Because of this, page splits won’t occur, which in itself saves overhead. This is because SQL Server has the ability to determine if data being inserted into a table has a monotonically increasing sequence, and won’t perform page splits when this happens. If you are inserting a lot of rows into a heap (a table without a clustered index), data is not inserted in any particular order onto data pages, whether the data is monotonically or not monotonically increasing. This results in SQL Server having to work harder (more reads) to access the data when requested from disk. HTH
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

So what would your recommendation be to fix the problem?

Not to create CI on primary keys. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>