Indexing Join Columns
In my last Sync/Contention post, I beat up on a select group of SAN administrators who aren’t willing to go the extra mile to optimize the very heart of their organization, SQL Server. You guys know who you are.
All DBAs know that Joining tables on non-indexed columns is the most expensive operation SQL Server can perform. Amazingly, I run into this problem over and over with many of my customers. Sync technologies like the Sync Framework, RDA and Merge Replication allow for varying levels of server-side filtering. This is a popular feature used to reduce the size of the tables and rows being downloaded to Silverlight Isolated Storage or SQL Server Compact.
It’s also a performance killer when tables and columns participating in a Join filter are not properly indexed. Keeping rows locked longer than necessary creates undue blocking and deadlocking. It also creates unhappy slate and smartphone users who have to wait longer for their sync to complete.
Do yourselft a favor and go take a look at all the filters you’ve created and makes sure that you have indexes on all those Joined columns.
Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany