sp_MSsub_check_identity performance -- sql server replication

Jason Jahn
6/12/2006 2:48:01 PM

Hello,

I have a transactional w/ updating publication with about 150 tables. A
bunch of these have identity fields. Using the SQL Server profiler on the
subscriber, I can see that very often (varying from 1-5 seconds), there is an
"exec sp_MSsub_check_identity @lower_bound_id = 0" being run, which causes
431 CPU and 8665 Reads (varies very slightly). Is this something to worry
about, or expected behavior? The subscriber is a test machine with no client
activity whatsoever. I ask about this because the CPU and Read values seem
quite high for something running that often. Replication seems to be working
fine, with no errors and no backed up transactions.

Both the distributor/publisher (one machine) and the subscriber are running
SQL Server 2005 with SP1 applied. The distributor/publisher is 64-bit SQL on
Windows 2003 SP1 x64, and the subscriber is 32-bit on Windows 2003 SP1 x32,
if that makes a difference.

Please let me know if any other information is required.

Thanks,

Hilary Cotter
6/12/2006 10:05:41 PM

This is the replication process fixing the subscriber table for the identity
element. Its normal.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

sql server replication : sp_MSsub_check_identity performance