February 1, 2011 at 8:15 am
Hi
I am wondering about the accuracy of placing a copy of production into a test environment. If I do this will the query plans also be copied or are they all stored in a system database?
I am obviously wanting to replicate inefficient plans and tune them in a test environment but if I include actual plans in prod will they be the same as if I include the actual plan in test for a given query? If not whats the best way to ensure my test environment matches prod for tuning plans?
Hope I have made sense
Thanks
February 1, 2011 at 10:23 am
Kwisatz78 (2/1/2011)
HiI am wondering about the accuracy of placing a copy of production into a test environment. If I do this will the query plans also be copied or are they all stored in a system database?
I am obviously wanting to replicate inefficient plans and tune them in a test environment but if I include actual plans in prod will they be the same as if I include the actual plan in test for a given query? If not whats the best way to ensure my test environment matches prod for tuning plans?
Hope I have made sense
Thanks
No, the query plans will not be copied to the new environment. Execution plans are stored in the cache memory of the server, not the database. What is stored in the database is all the statistics, indexes and constraints that the optimizer uses to decide what execution plan to create. As long as you have a system that roughly approximates production (multiple processors if they exist, the same cost threshold for parallelism, same version of SQL Server, etc.) you will get mostly identical execution plans. Sometimes it won't work out that way, but most of the time it will.
"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
February 1, 2011 at 10:26 am
And the times it won't work out are times the plan is likely to change anyway.
It's usually good enough to copy the database over to parallel hardware.
The thing to avoid is trying to tune on significantly different hardware. What works well on a "test server" with 8 CPU cores may not work well on a VM "production server" with 8 "virtual CPUs".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2011 at 10:48 am
Absolutely.
I thought of another one too, you need to make sure you're using the same parameters in the test environment that you do in production. That will help to ensure you get the same execution plan in the event you're dealing with bad parameter sniffing.
"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
February 2, 2011 at 2:26 am
Ok great stuff thanks guys.
February 2, 2011 at 2:50 am
Would you also need to simulate a similar server load? Could SQL Server choose a different plan under different load conditions?
February 2, 2011 at 4:17 am
rjohal-500813 (2/2/2011)
Would you also need to simulate a similar server load? Could SQL Server choose a different plan under different load conditions?
No, the optimizer is not monitoring the server. It performs its work independently from what is going on in the machine.
"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
February 2, 2011 at 4:18 am
rjohal-500813 (2/2/2011)
Would you also need to simulate a similar server load? Could SQL Server choose a different plan under different load conditions?
But, thinking about it a bit more, of course, being able to simulate a server load does make for more powerful and meaningful testing. It really depends on what you're trying to get out of it.
"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
February 2, 2011 at 6:35 am
rjohal-500813 (2/2/2011)
Would you also need to simulate a similar server load? Could SQL Server choose a different plan under different load conditions?
It won't choose a different plan, but the plan may work better/worse under different load conditions.
If, for example, a plan works beautifully if it's the only thing accessing I/O, but works horribly if it has to contend for a heavily overused I/O channel, then different loads may reveal that the query still needs tuning.
That's what load testing is for. There are tools available for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply