while investigating an unrelated deadlocking issue, I discovered SSC3 is causing massive deadlocking on our development database with literally hundreds of deadlocks logged from just the last couple of weeks.

I have captured the deadlock information as XML using a query on sys.dm_xe_sessions which shows either PAGE or KEY locks causing deadlocks.

This is happening in TempDB and caused by the procedure \"RG_WhatsChanged\".

It happens continuously for all users whether they are actively using SSC3 or not.

I think that you're most likely seeing this problem as a result of our polling query, which regularly runs for each user to look for changes to your database objects. This runs for two purposes:

- to display the 'blue blob' next to an altered object
- to display the name of the user who made the change, if you're working in shared mode.

It sounds like in your case you've either got a fair few users on a single instance, or maybe several databases that are source controlled. Unfortunately, once a user clicks on a linked DB, the poller will continue to run for that DB until SSMS is closed.

We are looking to improve the performance of the polling query (and I'll alert the development team to this post) but in the meantime, things you can try are:

Reducing the polling interval - this will mean the blue blobs will take longer to appear but a refresh of the commit tab will bring them right up.

We have about 30 Databases under source control in a shared DB environment between 10 developers, although 90% of usage is across 2-3 of these only.

Since posting above I have found details about the DefaultTraceMinimumInterQueryTimeInMillis option to add to the RedGate_SQLSourceControl_Engine_EngineOptions config file. We've greatly increased this above the default which has improved the situation for most users.

Interesting your comment about it being used to display the username of who made a change in shared mode - this has never worked for us, we sometimes see the odd name pop up but 99% of changes are made by "unknown".

If you're seeing 'unknown' a lot, try the option to log changes to a separate database. If you don't do that, it relies on reading them from the default trace and on a busy-ish server (or with lots of users) the trace file can roll over in a matter of a few minutes. This means that the information gets lost quite quickly

The bottom of the page that tells you how to set those up displays a list of change types that we cannot track- is it possible those account for a lot of the missing entries?
There's also an issue (or at least *was*) where table changes made using the designer, rather than T-SQL, weren't picked up.

Also ensure all users have made the same config file change, otherwise it might be that for many users the changes are still using the old method.

Yes certainly some of our activity will involve items from the list of objects shown as not [currently] logged.

However we've just done another quick test:

>User A, create a table using t-sql
>Refresh the commit changes tab, new table shows up and Changed by column shows "user A"
>User B create a proc using t-sql
>Refresh the commit changes tab, new proc shows up and Changed by column shows "user B"
> User B alters the table just created by user A and adds a column
> Refresh the commit changes tab, new table now shows changed by "Unknown"

I'm assuming (hoping) this is not the expected behaviour?

Thanks!

Last edited by sttu on Mon Feb 10, 2014 4:24 pm, edited 1 time in total.

It's definitely one possible cause- there's currently a couple of possibilities we'll be investigating shortly:

- new object in the repository, but amended after it's created before it's committed (which is what you're seeing in this example)
- existing object in the repository that is dropped and recreated in the DB
- trace file has rolled over before the polling query runs.

We're hoping to work on all these fairly soon, so things should improve.