Xp_userlock showing in SQL Server for version 1.7

question
3

I believe we are using a cloud based environment from Azure, unfortunately due to the nature of the business I don’t know the pricing plan that is used.

However the server specs is 16 core Windows Server 2012 with 32GB of RAM, we have SQL Server SQL Analysis Server and a Web Server running from the same machine, after running you code to get the max worker count it shows as 704, I don’t believe this figure to be modified.

Also for you information below in the configuration we have for Hangfire. Which we use a dashboard and two instances of Hangfires servers one for job from users and one for internal jobs.

For the dashboard here is the global config settings we use:

        public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero,
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true
                });
            
            app.UseHangfireDashboard("", new DashboardOptions
            {
                Authorization = new[] { new HangfireAuthorizationFilter() }
            });
        }

Fore the jobs coming for users we have the following config settings

        public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero,
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true,
                    EnableHeavyMigrations = true
                });

            var options = new BackgroundJobServerOptions
            {
                Queues = new[] { "critical", "high", "default", "low" },
                SchedulePollingInterval = new TimeSpan(0, 0, 30),
                ServerCheckInterval = new TimeSpan(0, 0, 30),
                ServerTimeout = new TimeSpan(0, 5, 0),
                WorkerCount = 2,
                StopTimeout = TimeSpan.FromSeconds(10)
            };

            app.UseHangfireServer(options);
        }

        protected void Application_Start(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Start();
        }

        protected void Application_End(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Stop();
        }

For the internal jobs server we have the following settings

 public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true,
                    EnableHeavyMigrations = true
                });

            var options = new BackgroundJobServerOptions
            {
                Queues = new[] { "critical_io", "high_io", "default_io", "low_io" },
                SchedulePollingInterval = new TimeSpan(0, 0, 30),
                ServerCheckInterval = new TimeSpan(0, 0, 30),
                ServerTimeout = new TimeSpan(0, 5, 0),
                WorkerCount = Environment.ProcessorCount * 5,
                StopTimeout = TimeSpan.FromSeconds(10)
            };

            app.UseHangfireServer(options);
        }

        protected void Application_Start(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Start();
        }

        protected void Application_End(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Stop();
        }

Then in the active expensive queries within SSMS shows as follows:
image

Hope this helps.