#StackBounty: #sql-server #sql-server-2012 #query-performance #azure-vm Advice on diagnosing a "sometimes" slow query

Bounty: 150

I have a query that “uses” an indexed view in a database running on SQL Server 2012. “Sometimes” it runs slow (3-7 seconds), but most of the time it’s instantaneous.

Here’s an example query from SQL Profiler:

exec sp_executesql N'
SELECT      [a].[Field1] -- other columns
FROM        [dbo].[ListingSearchView][a] WITH (NOEXPAND)
WHERE       [a].[LocationId] IN (@locationIds1,@locationIds2,@locationIds3,@locationIds4,@locationIds5,@locationIds6)
AND         [a].[StatusType] = @statusType',N'@statusType int,@locationIds1 int,@locationIds2 int,@locationIds3 int,@locationIds4 int,@locationIds5 int,@locationIds6 int',@statusType=1,@locationIds1=5714947,@locationIds2=5714884,@locationIds3=5714901,@locationIds4=5715034,@locationIds5=5715052,@locationIds6=5714867

Note: I execute it via a Dapper query. There’s no stored procedure here…just executing raw SQL via an abstraction over ADO.NET.

The underlying view looks like this:

CREATE VIEW [dbo].[ListingSearchView]
WITH SCHEMABINDING
AS
    SELECT     [a].[Field], [b].[Field], [c].[Field], [d].[Field]
    FROM       [dbo].[Listings][a]
    INNER JOIN [dbo].[ListingFeatures][b] 
    ON         [a].[ListingId] = [b].[ListingId]    
    INNER JOIN [dbo].[ListingBuildingDetails][c] 
    ON         [a].[ListingId] = [c].[ListingId]
    INNER JOIN [dbo].[ListingLandDetails][d] 
    ON         [a].[ListingId] = [d].[ListingId]
    WHERE      [a].[IsVisible] = 1
    AND        [a].[IsLive] = 1
    AND        [a].[AgencyCompanyId] IS NOT NULL

GO

Here’s the execution plan:
enter image description here

I haven’t been able to replicate this slowness through load testing. We do have various components doing writes to tables in the indexed view.

What I’ve done:

  • Defrag’d indexes
  • Checked statistics

What I’m planning to do next:

  1. Setup a SQL profiler trace capturing SHOWPLAN XML (high overhead)
  2. Install SQL Monitor.

My current inkling is that the issue is related to blocking/locking.. and my instinct tells me to simply add WITH (NOLOCK) hints to everything. However, I want to be sure this is the issue before I apply the fix.

Can someone point me to other directions I can look at? Note: SQL Server is hosted on a VM in Azure, so network issues could be the case, but the slowness happens so often (few times every hour), that I’m ruling that out.

Also – other queries are sometimes running slow too (non indexed view), but I wanted to focus on this one…as it should be a pretty simple one.

I captured 2x slow query plans. One took 4.8 seconds, the other 3.0 seconds. Note, they executed around the same time as each other – maybe that’s a hint?

Is it because I’m passing multiple locationIds in the query? When I run the same query again, the execution plan is the same, but the response is instant. As I said – these two slow queries were running at the same time… is it due to locking?

I’m looking into a tool like SQL Monitor because I’m a dev, not a DBA. I don’t really have the knowledge of the data e.g. DMV queries return (yes, I could learn…but time/learning curve is the issue). I am hoping that someone more skilled can give me some pointers/advice.

As requested, I’ve also scripted out the schema + indexes for the indexed view and underlying tables (note: I didn’t script out FK’s/constraints, as I didn’t think that would matter. Let me know if it does)
https://pastebin.com/wh1sRcbQ

One thing I noticed, is when I run the query behind the indexed view, it’s doing lots of table scans, instead of seeks…

So, I’m wondering if when the data behind the indexed view is being updated, the view is trying to update itself via table scans (e.g at a less-than-optimal rate), and the read queries are waiting for the index update to finish? Is that possible?


Get this bounty!!!

#StackBounty: #windows-server-2008-r2 #ssl #windows-server-2012-r2 #iis-8 #sql-server-2012 TLS Error 70 issues

Bounty: 50

I have a web application (IIS 8) on one server (Windows Server 2012) connecting to SQL Server Reporting Services 2012 on another server (Windows Server 2008) that until recently was working fine. About a week ago, it stopped working, and since then, I have not been able to get the system working again. The premise is that my code calls to SSRS to retrieve a report, then serves it to the user (so that the user never has view or access to the report directly for security reasons).

The error message in my logs states “The request was aborted: Could not create SSL/TLS secure channel.”
The web server is reporting “A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 70. The Windows SChannel error state is 105.” which says that it’s trying to negotiate an unsupported protocol.

Fixes tried (and failed):

  1. Updated code to force TLS 1.2
  2. Fully patched both servers, and software that doesn’t automatically get patched
  3. Checked firewall on both servers (it is disabled)
  4. Examined and updated the allowed protocols, etc. with IIS Crypto to disable insecure options on both servers.
  5. And out of desperation (because it came up in search results and Windows Update notes):

  6. Added the LdapEnforceChannelBinding registry entry per MS at https://support.microsoft.com/en-us/help/4034879/how-to-add-the-ldapenforcechannelbinding-registry-entry.
  7. Followed the directions at https://community.spiceworks.com/topic/1794963-schannel-event-id-36888-microsoft-no-help-at-all re security check exemptions

So far nothing has worked, and the only other ideas that I have (but am not sure I want to try except at last resort) are

  • Remove the most recent set of Windows patches
  • Disable or delete encryption ciphers (MS recommended this a few years ago per http://searchsecurity.techtarget.com/news/2240234856/Microsofts-Schannel-security-patch-affecting-TLS-connections
  • Disable Data Execution Prevention (it has been working for years this way)

Before I take any of these steps, does anyone have any ideas on anything else I can try to get these two servers talking again?


Get this bounty!!!

#StackBounty: #sql-server #sql-server-2012 #ssrs SSRS and Managed Service Account – impossible to configure?

Bounty: 150

We are trying to configure Reporting Services to use a Managed Service Account. The environment is:

Server: Windows 2008 R2 SP1
Reporting Services: SQL Server 2012 (version 11.0.6567.0)

Currently, SSRS runs as a domain service account, but we would like to change to running as an MSA. The SQL Server instance and agent have both been successfully changed to use an MSA.

When I try to change the service account to an MSA, via Reporting Services Configuration Manager, I receive an error:

Microsoft.ReportingServices.WmiProvider.WMIProviderException: The account name is not valid. Specify an account in the form domainalias.

—> System.Runtime.InteropServices.COMException (0x8004021D): Exception from HRESULT: 0x8004021D
— End of inner exception stack trace —
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWindowsServiceIdentity(String accountName, SecureString password, Boolean useBuiltinAccount)

My question is simple:

Has anyone successfully changed the SSRS service account to use a Managed Service Account? If so, how?!


Get this bounty!!!

#StackBounty: #sql-server #sql-server-2012 #performance-tuning #multi-thread Identify threads with maximum difference in time

Bounty: 50

I have many parallel executing queries in my SQL Server database. I need to find out queries where the difference in time for the longest thread and the shortest thread is high (excluding co-ordinator thread). In fact, I need to find top 10 such queries with highest difference (from the queries that are getting exeuted in the span of 1 hour). How to track this information in SQL Server?

Note: I am using SQL Server 2012.

enter image description here


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!

#StackBounty: #sql-server-2012 #c# #powershell #smo Why can't C# SMO see extended properties on a column but Powershell SMO can?

Bounty: 200

I am attempting to read extended properties on tables and columns in a winforms C# application. I am using SQL Server SMO to do so. When I execute the application it does not see the extended properties, but when I read the extended properties using PowerShell, it does see the extended properties.

The C# code:

var x = col.ExtendedProperties.Count;
var NPI = col.ExtendedProperties["NPI"].Value;
bool npi = bool.Parse(NPI.ToString());

The PowerShell code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $env:COMPUTERNAME
$server.Databases["<db name>"].Tables["<table name>"].Columns["<column name>"].ExtendedProperties | Select Name, Value, State

I have checked and both Visual Studio and PowerShell are using the same version of SMO (11.0.0.0). When I execute the C# code the col.ExtendedProperties.Count = 0, but when I execute the PowerShell code I get:

Name Value    State
---- -----    -----
NPI  False Existing

Does anyone have any ideas as to why this could be happening?


Get this bounty!!!