December 16, 2009 at 8:49 am
Hi all.
We have the following problem:
In a windows 2003 server with sql server 2008 SP1, with 512 Mb RAM memory, take 1 second to execute a query.
Past month, we add more RAM memory to this server, and now it have 2 Gb, and the same query take more than 1 minute to show same data, and we realized that the execution plan differs between both configurations.
We are testing that in virtual machines changing the amount of RAM memory, and it confirmed that I explain before.
Somebody have any idea about that?
Thanks in advance.
December 16, 2009 at 9:33 am
Everything else is absolutely equal, identical databases, identical indexes on the databases, identical statistics on the indexes, identical queries, identical parameters... I've moved databases between servers before without the execution plans changing, so I'm wondering what else might have changed to affect this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2009 at 12:48 pm
In addition to what Grant said, was there anything else that was running at execution time?
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
December 16, 2009 at 1:10 pm
And... Same number of processors, same cost threshold for parallelism, same ANSI settings on the connections...
Just a few more things that can result in execution plans changing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2009 at 12:14 am
Hi. Thank you very much for your responses.
After a lot of tests in production and test environments, the result is that when we change the amount of RAM memory (only this variable, in a VMWare virtual machine, that is a clon of the production environment) the execution plan changes... and the time to obtain the result changes too.
I know that it haven't got much sense... When you increase the server amount of RAM memory, the performance decrease...
In the test environment, we have changed several times the amount of memory, and we only have obtained the best result with 512Mb.
December 17, 2009 at 5:43 am
castillo.sergio (12/17/2009)
Hi. Thank you very much for your responses.After a lot of tests in production and test environments, the result is that when we change the amount of RAM memory (only this variable, in a VMWare virtual machine, that is a clon of the production environment) the execution plan changes... and the time to obtain the result changes too.
I know that it haven't got much sense... When you increase the server amount of RAM memory, the performance decrease...
In the test environment, we have changed several times the amount of memory, and we only have obtained the best result with 512Mb.
Something must be up with your configuration or the query. This behavior is counter to what you should expect. There's not straight line correlation to increasing memory and increasing performance, but throttling memory to increase performance is indicative of a serious problem.
x32 or x64 servers? Do you have dynamic memory set on the server? Something is going on there that is absolutely abnormal. Problem is, I don't have enough information to make a suggestion.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2009 at 8:27 am
Hi Grant, thank you for your answer.
I give you some information about de problem:
Virtual machine name: CLON-DCDB02
The following query is one of the problematic queries:
select * from
( select qAM.id , qAM.ref , qAC.name , qAM.concepto , qAM.cantidad , qAM.fecha
from descAccountMoves qAM INNER JOIN descAccount1 qAC on qAC.id = qAM.id_a
where qAM.id_state = 1 and qAM.id not in
( select qD.idObject
from descDiary qD inner join descPeriod qP on qP.id = qD.id_a
where qD.idObjectType = 10 and qD.asiento < > 5580 AND ( ( qD.id_a = 9 ) or ( IsNUll ( qP.idPeriodGrp , -1 ) = IsNull (
( select idPeriodGrp
from descPeriod
where id = 9 ) , -1
) ) ) )
and not ( ( qAM.id_type = 3 and qAM.Cantidad > 0 ) ) ) as qMain order by ref
I've executed it with SET STATISTICS IO ON, alternatively in two RAM configurations, and I've obtained the following results (I have remarked significative data):
CLON-DCDB02. 2Gb.
Table 'descPeriod'. Scan count 4937, logical reads 21476, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descDiary'. Scan count 1, logical reads 9068219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descAccountMoves'. Scan count 1, logical reads 999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descAccount1'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
=======================================================================
CLON-DCDB02. 512Mb.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descDiary'. Scan count 2, logical reads 5970, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descAccountMoves'. Scan count 1, logical reads 999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descAccount1'. Scan count 1, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'descPeriod'. Scan count 10680, logical reads 32039, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I've attached execution plans for both configurations too. Differs a lot between them!!!
December 17, 2009 at 1:04 pm
Interesting stuff. The bad plan, on the higher memory machine is using less memory than the good plan on the more memory machine. I'm thinking though that the statistics on the machines are different. The "good" plan has estimated and actual row counts that match more frequently than not, but the "bad" plan as a lot of mismatched rows. I'd try updating the statistics on both machines.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2009 at 2:35 pm
There aren't two machines... It's the same virtual machine with 512Mb, then shutdown, reconfigure with 2Gb and execute de same query...
Thank you.
December 17, 2009 at 6:22 pm
And after you reconfigure for the 2GB, what happens when you update stats?
Also, is the bad plan consistent? Is it slow on repeated executions of the query?
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
December 17, 2009 at 11:32 pm
1.- I will test that now.
2.- Yes, it is consistent. Always shows the results in same time lapse.
December 18, 2009 at 12:40 am
1.- I will try that now.
2.- Yes, it is. Several executions of the query has same time to obtain results.
Sorry, I've duplicated the previous post because I haven't seen the second page...
December 18, 2009 at 4:55 am
Hi all, we have resolved the problem using your suggestions.
The solution have appeared updating statistics with each RAM memory amount configuration. We thought that the update statistics process is necesary only once for DB, but obviously, we need to update statistics when we change the RAM memory configuration in our virtual machine. Now, the query runs in less than a second in all configurations after update the statistics.
The executions plans are different from the previous.
Thank you very much for your help.
December 18, 2009 at 6:31 am
Glad that it worked out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2009 at 8:38 am
Thanks for the feedback.
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply