Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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
Instance
Database
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 between environments. When developing a new query, additional tuning is often needed as it 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.
Final note: lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.
More Resources:
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.
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?
