December 2, 2016 at 6:18 am
We migrated an application several weeks ago and all appears to be running ok, apart from a couple of queries that are using the same set of parameter values when executing.
We have noticed that the query runs absolutely fine 99% of the time, then it literally stops working and just hangs when it attempts to execute against a very specific set of values (this doesn’t happen everytime the SQL code is executed, but when there is a problem its always the same parameter value). However, if we update statistics on one of the tables In the query (agldimvalue table), the query runs fine again regardless of what values are passed into the ‘sp_executesql’.
The query usually takes less than 3 seconds to run.
When we have the problem, the query doesn’t finish executing and creates millions of reads when they should be in the low thousands.
I’ve looked into the query execution plans and I’m aware its not the greatest query (returns 67k records to the user) in the first place and ive raised this with the development company. However, due to the query not having issues in our previous SQL server and apparently it worked exactly the same way. They arent interested in re-writing it.
THIS IS THE PROBLEM QUERY….
exec sp_executesql N'SELECT a.dim_value dim_value , isnull ( d.description , a.description ) description FROM agldimvalue a LEFT JOIN agldescription d ON a.attribute_id = d.attribute_id AND a.dim_value = d.dim_value AND d.language = @language AND d.client = @client WHERE a.client = @client AND a.status IN (N''N'' ) AND a.attribute_id = @attribute_id ORDER BY 2 ',N'@client nvarchar(2),@attribute_id nvarchar(2),@language nvarchar(2)',@client=N'23',@attribute_id=N'A4',@language=N'EN'
I believe the ORDER BY 2 is the problem area for the query. Something I’ve found interesting is, when the query fails to run with the above execution code if I change ORDER BY to 1 rather than 2. It works fine. If I change ORDER BY 2 to ORDER BY DESCRIPTION works fine (so column name reference rather than list number of the column). As soon as I re-run with ORDER BY 2, it hangs again and doesn’t complete.
The query plan is showing when it executes successfully we have spill to tempdb happening….
execution plan: Warning
operator used tempdb to spill data during execution with spill level 2 and 1 spilled thread. hash wrote 864 pages to and read
864 pages from tempdb with granted memory 11136KB and used memory 6208KB
The Query plan also shows me that when it does execute successfully, the ‘estimated number of rows’ are usually miles out compared to the ‘actualnumber of rows’ (approx. between 30 and 40k rows),(ive had a further read into parameter sniffing). I’ve also had a look at the XML show execution plan to see the values ‘ParameterCompiledValue’ and ‘ParameterRuntimeValue’ for the parameters being passed into the query are when the query executes.
We ran the query through a query tuner and added a couple of indexes to the 2 tables involved and it helped massively on the number of reads to the database. Also added 2 new statistics.
We have found a way of fixing the problem when it happens by running the following, but we are having to run this too often.
update statistics dbo.agldimvalue;
We have multiple tempdb files, could this be an area of concern for whats happening with the query ‘order by’ clause ?
I’m not sure why I can run the query successfully when it fails if I change the ORDER BY 2 to ORDER BY DESCRIPTION.
I don’t suppose theres any suggestions you could fire at me is there ?
Any help would be greatly appreciated.
p.s we’ve not been able to reproduce the error in our non-prod environment which from what I can see so far, the main difference between installations is number of application processes actually running against the databases and the tempdb file is only a single file, as it was in the SQL server prior to migration.
I’ve also tried doing full scan updates for statistics.
thanks
December 2, 2016 at 6:21 am
What versions of SQL Server are you migrating from/to?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 2, 2016 at 6:25 am
SQL 2012 Standard Edition original SQL Instance
Migration of the SQL is to a new instance on the same VM Server.
The migration was due to application upgrades requiring this, not DB related migration.
So we have a SQL 2012 std edition server on latest SP and CU which is in read only mode and has run this query fine.
We have our new instance on the same server, SQL 2012 std edition server on latest SP and CU. This new instance has been having the query issue. Not noticed any other problems as yet other than this.
SQL server version is 11.0.6540
December 2, 2016 at 3:08 pm
What are the data types of the columns a.client, a.status, and a.attribute_id??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 2, 2016 at 4:43 pm
Data Types:
a.client = nvarchar(25), not null
a.status = nvarchar(1), not null
a.attribute_id = nvarchar(4), not null
December 3, 2016 at 6:34 am
So it doesn't look like you have mismatched data types. That is actually a shame, because it could have been a magic-bullet fix. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 4, 2016 at 10:13 am
This could be a problem with heaps and too many forwarded records. See what numbers are produced by:
DECLARE @YourDB sysname = 'YourDB';
SELECT OBJECT_NAME(O.object_id) AS TableName, COALESCE(S.forwarded_record_count, 0) AS forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(@YourDB), DEFAULT, DEFAULT, DEFAULT, DEFAULT) S
JOIN sys.objects O
ON S.[object_id] = O.[object_id]
WHERE S.index_type_desc = 'HEAP'
AND OBJECT_NAME(O.object_id) IN ('agldimvalue', 'agldescription');
If the count is over 50, it might be worth running something like the following, out of hours before index optimization, once a week:
DECLARE @YourDB sysname = 'YourDB'
,@sql nvarchar(1000) = ''
,@TableName nvarchar(255);
DECLARE HeapCursor CURSOR
FOR
SELECT
N'[' + SCHEMA_NAME(O.[schema_id]) + N'].[' + OBJECT_NAME(S.[object_id]) + N']' AS TableName
FROM sys.dm_db_index_physical_stats(DB_ID(@YourDB), DEFAULT, DEFAULT, DEFAULT, DEFAULT) S
JOIN sys.objects O
ON S.[object_id] = O.[object_id]
WHERE S.index_type_desc = 'HEAP'
AND S.forwarded_record_count > 5
ORDER BY S.avg_fragmentation_in_percent DESC;
OPEN HeapCursor;
FETCH NEXT FROM HeapCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE ' + @TableName + ' REBUILD;';
EXEC (@sql);
FETCH NEXT FROM HeapCursor INTO @TableName;
END
CLOSE HeapCursor;
DEALLOCATE HeapCursor;
December 5, 2016 at 2:51 am
Thanks for that advice & info.
I've checked our db server this morning and the results for the HEAP query have come back as '0' for both columns.
However, there was a statistics update run last night, so I'm waiting for the problem to re-occur and then I'll check what those values are showing as.
Part of the problem with this, its only happening intermittently.
will keep you posted.
December 5, 2016 at 4:38 am
Okay, no problem with forwarded records.
At least it seems the tables in your query are heaps which might jog someone else's memory.
December 5, 2016 at 9:15 am
paul.biltcliffe 32759 (12/2/2016)
Data Types:a.client = nvarchar(25), not null
a.status = nvarchar(1), not null
a.attribute_id = nvarchar(4), not null
On an unrelated note, why are you using nvarchar(1) for your status instead of nchar(1)? It takes two extra bytes to store and it only makes a difference if you need to differentiate between '' and ' '.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2016 at 10:17 am
In regards nchar and nvarchar, its purely down to the 3rd party design for this.
There's a lot of this throughout their table designs, however they have very little willingness to want to make their application better. Frustrating to say the least.
I've even raised issues regarding bringing back TOP 99999 back to user front end. Makes zero sense to bring back so much data.
On a further note of the problem query, I have today noticed that the query causing the problem seems to hang for what I believed to be a specific parameter 'A4'. What I've since found following further SQL traces is that at some point during the query being repeatedly processed, all parameter values passed into the stored procedure start to show larger reads against the table at some point. It just happens 'A4' value reads 67K rows. Most other parameter values return under 1K rows. Usually no 'heap' appears in the execution plan, however, it appears a 'heap' does start to appear around the build up of the problem.
If the 'A4' value runs as expected it shows as follows in a SQL trace:
query executed with A4 value, 780 CPU, 3780 Reads,1 Writes
However, this morning I started to see the same query produce following output in a SQL trace file.....
10:42
A4 executed with, 84366 CPU, 4906036 Reads, 110014 Writes
10:45
A4 executed with, 80091 CPU, 5284791 Reads, 110007 Writes
10:46
A4 executed with, 97812 CPU, 6179117 Reads, 110012 Writes
I'm running further analysis to see what happens in the database leading up to the problem.
Every time statistics update on the 'agldimvalue' table is executed, it resolves the problem.
regards
Paul
December 5, 2016 at 11:07 am
paul.biltcliffe 32759 (12/5/2016)
In regards nchar and nvarchar, its purely down to the 3rd party design for this.There's a lot of this throughout their table designs, however they have very little willingness to want to make their application better. Frustrating to say the least.
I've even raised issues regarding bringing back TOP 99999 back to user front end. Makes zero sense to bring back so much data.
On a further note of the problem query, I have today noticed that the query causing the problem seems to hang for what I believed to be a specific parameter 'A4'. What I've since found following further SQL traces is that at some point during the query being repeatedly processed, all parameter values passed into the stored procedure start to show larger reads against the table at some point. It just happens 'A4' value reads 67K rows. Most other parameter values return under 1K rows. Usually no 'heap' appears in the execution plan, however, it appears a 'heap' does start to appear around the build up of the problem.
If the 'A4' value runs as expected it shows as follows in a SQL trace:
query executed with A4 value, 780 CPU, 3780 Reads,1 Writes
However, this morning I started to see the same query produce following output in a SQL trace file.....
10:42
A4 executed with, 84366 CPU, 4906036 Reads, 110014 Writes
10:45
A4 executed with, 80091 CPU, 5284791 Reads, 110007 Writes
10:46
A4 executed with, 97812 CPU, 6179117 Reads, 110012 Writes
I'm running further analysis to see what happens in the database leading up to the problem.
Every time statistics update on the 'agldimvalue' table is executed, it resolves the problem.
regards
Paul
This is known as the data value skew problem. And when you have it (and most databases do to some degree - often severe) you simply MUST do some things differently or the standard and expected behaviors of the SQL Server optimizer and query execution engine will simply CRUSH your server. I have come across clients that literally couldn't buy big enough hardware, but a few fixes in the app and perhaps some indexing improvements and all of a sudden they didn't need bigger hardware.
Feel free to give my contact information to the ISV and see if they will be willing to have a free chat with me. I have been consulting on just the SQL Server relational engine for 20 years. I also have multiple successes under my belt with software vendors and I can promise that yours won't be any different from them. EVERYONE does the same suboptimal stuff!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply