I'm setting up a website on new app and database servers. Server details are:
Web pages are running slow so I've dug in to try and find the cause. What I ended up finding and can't explain is that if I have a simple query, e.g. (this query has the problem but I don't run it on the website):
SELECT 1
The query on its own has no problem, but if I pad it out with spaces so the query text is 676 characters or more (up to 675 runs fine) the execution will magically gain 500ms. If I keep adding spaces to around 1500 characters the performance is mostly slow with a random boost here and there. Adding more spaces to around 2000 characters the query becomes consistently fast again.
Running the query on the SQL server itself there is no problem, only when running remotely. I have tried a simple PowerShell script using SqlCommand on the app server and I've tried SQL Server Management Studio on another machine, both of these are slow. SQL Profiler shows the queries running instantly, Duration is 0 (CPU, Reads and Writes are also 0).
Here are some sample runs, this comes from SQL Management Studio client statistics with the slower instances where the query size are above 675 and the quicker instance below 676.
Client processing time 0 0 0 15 0
Total execution time 15 62 531 546 531
Wait time on server replies 15 62 531 531 531
Getting back to the website, I'm not running "SELECT 1" or padding queries with spaces in the website code. The actual queries being executed from the website are mainly from Entity Framework and because of the way those queries are constructed, the columns listed in the select clause, some queries have joins and where clauses, all those things result in the length of query text reaching that magical 676 character limit and the query runs slow. Here is an actual query from the website:
exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ID] AS [Id1],
[Extent1].[Name] AS [Name],
[Extent1].[Code] AS [Code],
[Extent1].[DisplayOrder] AS [DisplayOrder],
[Extent1].[ScreenTypeId] AS [ScreenTypeId],
[Extent1].[Exclude] AS [Exclude],
[Extent1].[Author] AS [Author],
[Extent1].[Editor] AS [Editor],
[Extent1].[Created] AS [Created],
[Extent1].[Modified] AS [Modified],
[Extent2].[Id] AS [Id2],
[Extent2].[Name] AS [Name1],
[Extent2].[ScreenUrl] AS [ScreenUrl],
[Extent2].[Author] AS [Author1],
[Extent2].[Editor] AS [Editor1],
[Extent2].[Created] AS [Created1],
[Extent2].[Modified] AS [Modified1]
FROM [dbo].[ProfitCentre] AS [Extent1]
INNER JOIN [dbo].[ScreenType] AS [Extent2] ON [Extent1].[ScreenTypeId] = [Extent2].[Id]
WHERE [Extent1].[ScreenTypeId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=16
Why are the queries taking so long when the length of the query text changes and how can I fix it?
The issue in this case is due to the way the virtual machines were set up on the host environment. I don't deal with the infrastructure so I'm not sure the specific details but the infrastructure people shifted the VMs around into the appropriate cluster and that resolved the performance.