Mixing
Why does my query run fast in Environment A, but slow in Environment B?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
6
down vote
favorite
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
sql-server query-performance execution-plan
edited Sep 7 at 6:09
Andriy M
15.3k53470
asked Sep 6 at 14:05
LowlyDBA
6,27352241
 |Â
show 3 more comments
up vote
6
down vote
favorite
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
sql-server query-performance execution-plan
edited Sep 7 at 6:09
Andriy M
15.3k53470
asked Sep 6 at 14:05
LowlyDBA
6,27352241
5
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
3
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
2
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
1
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
1
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17
 |Â
show 3 more comments
up vote
6
down vote
favorite
up vote
6
down vote
favorite
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
sql-server query-performance execution-plan
edited Sep 7 at 6:09
Andriy M
15.3k53470
asked Sep 6 at 14:05
LowlyDBA
6,27352241
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
sql-server query-performance execution-plan
edited Sep 7 at 6:09
Andriy M
15.3k53470
asked Sep 6 at 14:05
LowlyDBA
6,27352241
edited Sep 7 at 6:09
Andriy M
15.3k53470
edited Sep 7 at 6:09
Andriy M
15.3k53470
edited Sep 7 at 6:09
Andriy M
15.3k53470
15.3k53470
asked Sep 6 at 14:05
LowlyDBA
6,27352241
asked Sep 6 at 14:05
LowlyDBA
6,27352241
asked Sep 6 at 14:05
LowlyDBA
6,27352241
6,27352241
5
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
3
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
2
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
1
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
1
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17
 |Â
show 3 more comments
5
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
3
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
2
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
1
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
1
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17
5
5
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
3
3
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
2
2
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
1
1
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
1
1
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17
 |Â
show 3 more comments
3 Answers
3
active
oldest
votes
up vote
11
down vote
accepted
There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.
Server
- Is the hardware across environments the same (disks, memory, CPU, etc.) ?
- If VMs are being used, could noisy neighbors affect overall VM performance?
- If in the cloud, do autoscaling and other configurations have parity?
- Are environments mixed between physical/virtual/cloud?
- Do the OS versions match?
- Are environments in different datacenters?
Instance
- Are the SQL Server versions the same?
- A CU or SP can make a world of a difference even if the major versions are the same.
- Is the active workload during the query execution(s) comparable?
- Are the same volume of queries present in all environments?
- Is the nature of the workload the same across all environments?
- Do all environments participate in the same HA/DR setup?
- Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.
- Do the same maintenance jobs get run on the same schedules in all environments?
- Are trace flags equivalent across all environments?
- Are the same backup jobs running in all environments?
- Impact from backups should be minimal, but often they are not run at all on lower environments.
- Are the sys configurations the same?
Database
- Are the schemas / indexes / statistics / objects all equal across environments?
- Does the exact same data exist across environments?
- Amount of data
- Distribution of data
- Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)
- Are database level configs the same?
- Are compatibility levels the same?
With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies and additional tuning is needed as a query moves towards production.
Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.
Generally, lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
Different Plans for "Identical" Servers by Aaron Bertrand
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
add a comment |Â
up vote
8
down vote
The other answers are good, but I would add that you should consider the amount of data in Environment B, and any contention with other queries.
Some SQL queries show no performance problems in isolation (e.g. 1000 rows in table, no other queries running) , but can be horror-show with 10,000,000 rows in table (e.g. parameter sniffing issues), and/or other queries potentially writing to, updating or locking the tables involved.
I agree with the other answers on checking hardware/environments/configurations match first, but if nothing obvious comes up, start looking at query execution plans, running SQL Profiler etc.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
2
down vote
In short, you need to isolate whether the db itself is slow compared to the other, or its environment is slower. Rule out the easiest things first.
This has happened to me on a few occasions. Each time it turned out to be environment: someone else was hammering away and starving the db of IOPS on one server.
Run a top(1) on the slower server and see if the CPU is experiencing high amounts of wait states, or cpu stealing if you're in a virtual environment.
This will also help point to missing indexes that cause execution plans to perform full table scans instead of index scans (but that's easy to spot with slow query logging). This will also show up in ps as procs in a D state.
Once you've ruled that out, it's time to dig deeper into the hardware: is the work being spread out across all the CPUs, has a network port renegotiated itself to 100Mb. Run vmstat and/or iostat on both machine and compare the differences.
If the datasets are identical, does the same query generate the same execution plan on both? Do tables contain the same numbers of rows? Are the index definitions identical? Do the tables have similar levels of fragmentation? Similar numbers of active connections?
answered Sep 6 at 14:40
dland
1516
add a comment |Â
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
11
down vote
accepted
There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.
Server
- Is the hardware across environments the same (disks, memory, CPU, etc.) ?
- If VMs are being used, could noisy neighbors affect overall VM performance?
- If in the cloud, do autoscaling and other configurations have parity?
- Are environments mixed between physical/virtual/cloud?
- Do the OS versions match?
- Are environments in different datacenters?
Instance
- Are the SQL Server versions the same?
- A CU or SP can make a world of a difference even if the major versions are the same.
- Is the active workload during the query execution(s) comparable?
- Are the same volume of queries present in all environments?
- Is the nature of the workload the same across all environments?
- Do all environments participate in the same HA/DR setup?
- Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.
- Do the same maintenance jobs get run on the same schedules in all environments?
- Are trace flags equivalent across all environments?
- Are the same backup jobs running in all environments?
- Impact from backups should be minimal, but often they are not run at all on lower environments.
- Are the sys configurations the same?
Database
- Are the schemas / indexes / statistics / objects all equal across environments?
- Does the exact same data exist across environments?
- Amount of data
- Distribution of data
- Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)
- Are database level configs the same?
- Are compatibility levels the same?
With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies and additional tuning is needed as a query moves towards production.
Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.
Generally, lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
Different Plans for "Identical" Servers by Aaron Bertrand
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
add a comment |Â
up vote
11
down vote
accepted
There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.
Server
- Is the hardware across environments the same (disks, memory, CPU, etc.) ?
- If VMs are being used, could noisy neighbors affect overall VM performance?
- If in the cloud, do autoscaling and other configurations have parity?
- Are environments mixed between physical/virtual/cloud?
- Do the OS versions match?
- Are environments in different datacenters?
Instance
- Are the SQL Server versions the same?
- A CU or SP can make a world of a difference even if the major versions are the same.
- Is the active workload during the query execution(s) comparable?
- Are the same volume of queries present in all environments?
- Is the nature of the workload the same across all environments?
- Do all environments participate in the same HA/DR setup?
- Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.
- Do the same maintenance jobs get run on the same schedules in all environments?
- Are trace flags equivalent across all environments?
- Are the same backup jobs running in all environments?
- Impact from backups should be minimal, but often they are not run at all on lower environments.
- Are the sys configurations the same?
Database
- Are the schemas / indexes / statistics / objects all equal across environments?
- Does the exact same data exist across environments?
- Amount of data
- Distribution of data
- Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)
- Are database level configs the same?
- Are compatibility levels the same?
With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies and additional tuning is needed as a query moves towards production.
Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.
Generally, lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
Different Plans for "Identical" Servers by Aaron Bertrand
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
add a comment |Â
up vote
11
down vote
accepted
up vote
11
down vote
accepted
There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.
Server
- Is the hardware across environments the same (disks, memory, CPU, etc.) ?
- If VMs are being used, could noisy neighbors affect overall VM performance?
- If in the cloud, do autoscaling and other configurations have parity?
- Are environments mixed between physical/virtual/cloud?
- Do the OS versions match?
- Are environments in different datacenters?
Instance
- Are the SQL Server versions the same?
- A CU or SP can make a world of a difference even if the major versions are the same.
- Is the active workload during the query execution(s) comparable?
- Are the same volume of queries present in all environments?
- Is the nature of the workload the same across all environments?
- Do all environments participate in the same HA/DR setup?
- Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.
- Do the same maintenance jobs get run on the same schedules in all environments?
- Are trace flags equivalent across all environments?
- Are the same backup jobs running in all environments?
- Impact from backups should be minimal, but often they are not run at all on lower environments.
- Are the sys configurations the same?
Database
- Are the schemas / indexes / statistics / objects all equal across environments?
- Does the exact same data exist across environments?
- Amount of data
- Distribution of data
- Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)
- Are database level configs the same?
- Are compatibility levels the same?
With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies and additional tuning is needed as a query moves towards production.
Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.
Generally, lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
Different Plans for "Identical" Servers by Aaron Bertrand
There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.
Server
- Is the hardware across environments the same (disks, memory, CPU, etc.) ?
- If VMs are being used, could noisy neighbors affect overall VM performance?
- If in the cloud, do autoscaling and other configurations have parity?
- Are environments mixed between physical/virtual/cloud?
- Do the OS versions match?
- Are environments in different datacenters?
Instance
- Are the SQL Server versions the same?
- A CU or SP can make a world of a difference even if the major versions are the same.
- Is the active workload during the query execution(s) comparable?
- Are the same volume of queries present in all environments?
- Is the nature of the workload the same across all environments?
- Do all environments participate in the same HA/DR setup?
- Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.
- Do the same maintenance jobs get run on the same schedules in all environments?
- Are trace flags equivalent across all environments?
- Are the same backup jobs running in all environments?
- Impact from backups should be minimal, but often they are not run at all on lower environments.
- Are the sys configurations the same?
Database
- Are the schemas / indexes / statistics / objects all equal across environments?
- Does the exact same data exist across environments?
- Amount of data
- Distribution of data
- Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)
- Are database level configs the same?
- Are compatibility levels the same?
With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies and additional tuning is needed as a query moves towards production.
Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.
Generally, lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
Different Plans for "Identical" Servers by Aaron Bertrand
edited Sep 7 at 13:29
community wiki
LowlyDBA
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
add a comment |Â
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Willing to make this a community wiki?
– Joe Obbish
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
Sure! Sounds good to me
– LowlyDBA
2 days ago
add a comment |Â
up vote
8
down vote
The other answers are good, but I would add that you should consider the amount of data in Environment B, and any contention with other queries.
Some SQL queries show no performance problems in isolation (e.g. 1000 rows in table, no other queries running) , but can be horror-show with 10,000,000 rows in table (e.g. parameter sniffing issues), and/or other queries potentially writing to, updating or locking the tables involved.
I agree with the other answers on checking hardware/environments/configurations match first, but if nothing obvious comes up, start looking at query execution plans, running SQL Profiler etc.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
8
down vote
The other answers are good, but I would add that you should consider the amount of data in Environment B, and any contention with other queries.
Some SQL queries show no performance problems in isolation (e.g. 1000 rows in table, no other queries running) , but can be horror-show with 10,000,000 rows in table (e.g. parameter sniffing issues), and/or other queries potentially writing to, updating or locking the tables involved.
I agree with the other answers on checking hardware/environments/configurations match first, but if nothing obvious comes up, start looking at query execution plans, running SQL Profiler etc.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
8
down vote
up vote
8
down vote
The other answers are good, but I would add that you should consider the amount of data in Environment B, and any contention with other queries.
Some SQL queries show no performance problems in isolation (e.g. 1000 rows in table, no other queries running) , but can be horror-show with 10,000,000 rows in table (e.g. parameter sniffing issues), and/or other queries potentially writing to, updating or locking the tables involved.
I agree with the other answers on checking hardware/environments/configurations match first, but if nothing obvious comes up, start looking at query execution plans, running SQL Profiler etc.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
The other answers are good, but I would add that you should consider the amount of data in Environment B, and any contention with other queries.
Some SQL queries show no performance problems in isolation (e.g. 1000 rows in table, no other queries running) , but can be horror-show with 10,000,000 rows in table (e.g. parameter sniffing issues), and/or other queries potentially writing to, updating or locking the tables involved.
I agree with the other answers on checking hardware/environments/configurations match first, but if nothing obvious comes up, start looking at query execution plans, running SQL Profiler etc.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered Sep 6 at 15:33
Phil S
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered Sep 6 at 15:33
Phil S
811
answered Sep 6 at 15:33
Phil S
811
811
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Phil S is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
add a comment |Â
up vote
2
down vote
In short, you need to isolate whether the db itself is slow compared to the other, or its environment is slower. Rule out the easiest things first.
This has happened to me on a few occasions. Each time it turned out to be environment: someone else was hammering away and starving the db of IOPS on one server.
Run a top(1) on the slower server and see if the CPU is experiencing high amounts of wait states, or cpu stealing if you're in a virtual environment.
This will also help point to missing indexes that cause execution plans to perform full table scans instead of index scans (but that's easy to spot with slow query logging). This will also show up in ps as procs in a D state.
Once you've ruled that out, it's time to dig deeper into the hardware: is the work being spread out across all the CPUs, has a network port renegotiated itself to 100Mb. Run vmstat and/or iostat on both machine and compare the differences.
If the datasets are identical, does the same query generate the same execution plan on both? Do tables contain the same numbers of rows? Are the index definitions identical? Do the tables have similar levels of fragmentation? Similar numbers of active connections?
answered Sep 6 at 14:40
dland
1516
add a comment |Â
up vote
2
down vote
In short, you need to isolate whether the db itself is slow compared to the other, or its environment is slower. Rule out the easiest things first.
This has happened to me on a few occasions. Each time it turned out to be environment: someone else was hammering away and starving the db of IOPS on one server.
Run a top(1) on the slower server and see if the CPU is experiencing high amounts of wait states, or cpu stealing if you're in a virtual environment.
This will also help point to missing indexes that cause execution plans to perform full table scans instead of index scans (but that's easy to spot with slow query logging). This will also show up in ps as procs in a D state.
Once you've ruled that out, it's time to dig deeper into the hardware: is the work being spread out across all the CPUs, has a network port renegotiated itself to 100Mb. Run vmstat and/or iostat on both machine and compare the differences.
If the datasets are identical, does the same query generate the same execution plan on both? Do tables contain the same numbers of rows? Are the index definitions identical? Do the tables have similar levels of fragmentation? Similar numbers of active connections?
answered Sep 6 at 14:40
dland
1516
add a comment |Â
up vote
2
down vote
up vote
2
down vote
In short, you need to isolate whether the db itself is slow compared to the other, or its environment is slower. Rule out the easiest things first.
This has happened to me on a few occasions. Each time it turned out to be environment: someone else was hammering away and starving the db of IOPS on one server.
Run a top(1) on the slower server and see if the CPU is experiencing high amounts of wait states, or cpu stealing if you're in a virtual environment.
This will also help point to missing indexes that cause execution plans to perform full table scans instead of index scans (but that's easy to spot with slow query logging). This will also show up in ps as procs in a D state.
Once you've ruled that out, it's time to dig deeper into the hardware: is the work being spread out across all the CPUs, has a network port renegotiated itself to 100Mb. Run vmstat and/or iostat on both machine and compare the differences.
If the datasets are identical, does the same query generate the same execution plan on both? Do tables contain the same numbers of rows? Are the index definitions identical? Do the tables have similar levels of fragmentation? Similar numbers of active connections?
answered Sep 6 at 14:40
dland
1516
In short, you need to isolate whether the db itself is slow compared to the other, or its environment is slower. Rule out the easiest things first.
This has happened to me on a few occasions. Each time it turned out to be environment: someone else was hammering away and starving the db of IOPS on one server.
Run a top(1) on the slower server and see if the CPU is experiencing high amounts of wait states, or cpu stealing if you're in a virtual environment.
This will also help point to missing indexes that cause execution plans to perform full table scans instead of index scans (but that's easy to spot with slow query logging). This will also show up in ps as procs in a D state.
Once you've ruled that out, it's time to dig deeper into the hardware: is the work being spread out across all the CPUs, has a network port renegotiated itself to 100Mb. Run vmstat and/or iostat on both machine and compare the differences.
If the datasets are identical, does the same query generate the same execution plan on both? Do tables contain the same numbers of rows? Are the index definitions identical? Do the tables have similar levels of fragmentation? Similar numbers of active connections?
answered Sep 6 at 14:40
dland
1516
answered Sep 6 at 14:40
dland
1516
answered Sep 6 at 14:40
dland
1516
answered Sep 6 at 14:40
dland
1516
1516
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216919%2fwhy-does-my-query-run-fast-in-environment-a-but-slow-in-environment-b%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
5
How do you expect anyone to know when you haven't included any information about your schema, data, queries, or the environments in which they run? This question is either Too Broad or Unclear.
– jpmc26
Sep 6 at 20:44
3
@jpmc26: Given that the question was submitted together with a self-answer, I believe the intention was to make it generic. I've tried to make that clearer in the wording of the actual question, cheers.
– Andriy M
Sep 7 at 6:44
2
@jpmc26 Andriy is correct. I really enjoyed Erik's generalized question about query performance changing and felt this topic benefited from similar treatment. My end point was basically that even with all that information you asked for, the answer for any question of this ilk will probably be "that's why they're not the same, so don't expect them to act the same 100% of the time".
– LowlyDBA
Sep 7 at 13:32
1
That is what the vote to close / flag button is for :) I'm not sure what in my answer will become out of date, or wasn't relevant for the past 10 years, however.
– LowlyDBA
Sep 7 at 15:25
1
@jpmc26: You are raising good points in general. At the same time, not all of them are applicable here IMHO. We could discuss this further in chat, if you like.
– Andriy M
Sep 7 at 16:17