March 15, 2010 at 7:37 pm
Hi,
I’ve got this query:
SELECT * FROM [MyDB].[MySchema].[GetHashTable] (
'SomeValue'
,1)
running on Test and Dev servers. The server versions are the same. The Dev server is a little two logical CPU’s virtual machine. The test server is a physical machine with four CPU cores dedicated to the instance.
To make everything equal, I’ve even restored the database from the Dev server onto the test server.
On Dev server the query takes 3 seconds to run, on Test – 24 seconds.
The plans are the same, the database is the same, the server version is the same, etc.
There are two interesting facts however. If I change the ‘SomeValue’ to a value which has only 5 occurances in the table, the time for both servers is about the same. If the value happens to be in the table 240 times, the timing on test server is 2.5 times longer, if the value happens to be 11895 times, the timing is different by 8 times (the original test: 3 and 24 seconds accordingly).
Also when I run this query on both servers (somewhere inside the function, it calls fn_varbintohexstr):
SELECT execution_count, total_worker_time, total_worker_time/execution_count avg_worker_time
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
WHERE p.query_plan.exist(
'declare default element namespace ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'';
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan//MissingIndexes') = 0
and cast(query_plan as nvarchar(max)) like '%fn_varbintohexstr%'
Dev Server
execution_counttotal_worker_timeavg_worker_time
118952558592215
118952148439180
Test Server
execution_counttotal_worker_timeavg_worker_time
11895219101571841
11895209111331757
So, the average time it takes to run fn_varbintohexstr is nearly ten times longer on the test server compared to the tiny virtual machine, which is the dev server.
Yet another interesting fact is, if I run dbcc freeproccache on the test server and then run the query with the parameter value which occurs 5 times in the table, I still get long avg_worker_time on the test server even though the query takes the same time (about 55ms) as on the dev box.
execution_counttotal_worker_timeavg_worker_time
5117192343
5107432148
This confuses me even more.
I’ve tried everything, but could not find the cause for this. Any ideas?
Thanks.
March 15, 2010 at 8:40 pm
Sorry, I got one thing wrong here, the execution time is about the same for 5 records on both servers if I use:
SET STATISTICS TIME ON
SET STATISTICS IO ON
If I remove these lines, then the execution time on the test server is greater and corresponds with the avg_worker_time
March 15, 2010 at 9:06 pm
It looks like the below query has the same kind of performance between the dev and the test servers:
declare @count int
declare @val nvarchar (100)
set @count = 1
While @count <= 1000
begin
SELECT @val = sys.fn_varbintohexstr(HashBytes('SHA1', 'abcdefghijklmnopqrstuvwxyz')
set @count = @count +1
end
March 18, 2010 at 7:34 pm
Up! 🙂
March 19, 2010 at 1:34 am
Is a trace running on the TEST server?
March 19, 2010 at 12:22 pm
You say the execution plans are the same. Is that the estimated or the actual exec plans?
How many rows difference is there between the environments?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 19, 2010 at 9:17 pm
Roust_m (3/15/2010)
Sorry, I got one thing wrong here, the execution time is about the same for 5 records on both servers if I use:SET STATISTICS TIME ON
SET STATISTICS IO ON
If I remove these lines, then the execution time on the test server is greater and corresponds with the avg_worker_time
The reason I asked about the trace is because calling a function causes extra events to be raised - which correspond to setting these options on.
March 21, 2010 at 4:12 pm
Paul White NZ (3/19/2010)
Roust_m (3/15/2010)
Sorry, I got one thing wrong here, the execution time is about the same for 5 records on both servers if I use:SET STATISTICS TIME ON
SET STATISTICS IO ON
If I remove these lines, then the execution time on the test server is greater and corresponds with the avg_worker_time
The reason I asked about the trace is because calling a function causes extra events to be raised - which correspond to setting these options on.
Paul, thanks a lot!
March 21, 2010 at 4:13 pm
CirquedeSQLeil (3/19/2010)
You say the execution plans are the same. Is that the estimated or the actual exec plans?How many rows difference is there between the environments?
Both estimated and actual, no row difference at all, it is the same database backup restored.
Problem solved anyway.
March 21, 2010 at 5:36 pm
Roust_m (3/21/2010)
CirquedeSQLeil (3/19/2010)
You say the execution plans are the same. Is that the estimated or the actual exec plans?How many rows difference is there between the environments?
Both estimated and actual, no row difference at all, it is the same database backup restored.
Problem solved anyway.
Would you mind sharing what the problem was?
March 21, 2010 at 6:14 pm
Roust_m (3/21/2010)
CirquedeSQLeil (3/19/2010)
You say the execution plans are the same. Is that the estimated or the actual exec plans?How many rows difference is there between the environments?
Both estimated and actual, no row difference at all, it is the same database backup restored.
Problem solved anyway.
Please let us know what the issue was and what you did to resolve it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 21, 2010 at 11:48 pm
lmu92 (3/21/2010)
Roust_m (3/21/2010)
CirquedeSQLeil (3/19/2010)
You say the execution plans are the same. Is that the estimated or the actual exec plans?How many rows difference is there between the environments?
Both estimated and actual, no row difference at all, it is the same database backup restored.
Problem solved anyway.
Would you mind sharing what the problem was?
Trace running on the server
March 22, 2010 at 12:16 am
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 2:14 am
Roust_m (3/21/2010)
Paul White NZ (3/19/2010)
Roust_m (3/15/2010)
Sorry, I got one thing wrong here, the execution time is about the same for 5 records on both servers if I use:SET STATISTICS TIME ON
SET STATISTICS IO ON
If I remove these lines, then the execution time on the test server is greater and corresponds with the avg_worker_time
The reason I asked about the trace is because calling a function causes extra events to be raised - which correspond to setting these options on.
Paul, thanks a lot!
No worries - it was pretty obvious once you mentioned the difference made by the SET statements.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply