May 8, 2019 at 10:18 pm
Hello
there are 2 servers A and B with the same settings. However when I try to run a select count(*) in server A it takes 6 seconds whereas in the other it takes 18-28 seconds
can anyone let me know how or where to check for the problem
May 9, 2019 at 10:18 am
hi
compare the target objects you're running the query against - they can have different structure, amount of data, type, etc
also, (sql?) settings can be the same, but resources are different (amount of RAM, CPU, disk subsystem)
+ buffer cache is shared and due potential parallel activity (existing/missing) on the servers you get different results
do the subsequent runs last the same time?
May 9, 2019 at 11:29 am
There are multiple things when you are comparing the performance of a query from 1 server to another server. If considering all the measures like CPU, MEMORY, IO, schema etc. are same. You can check the query plan.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 9, 2019 at 12:11 pm
First, and I can't emphasize this enough, double, triple, quadruple check that yes, really, these servers are the same. Frequently they are not.
Then, of course, the next question is, are the databases the same? Again, double check that the same structures and the same code exists on both. Then, of course, assuming that the servers are actually identical and the databases are actually identical, is the data? Different data volumes leads to differences in index distribution, statistics, row estimates, and therefore, different execution plans leading to different performances. Also, different data volumes alone can account for differences in performance. Then toss in that different data volumes means different contention in different places. Speaking of contention, is the load on both machines identical or are there differences here? Are the statistics different? Are you capturing the behavior the queries the same way on both? Are you using a method of capture that doesn't itself interfere with or report bad data (best solution here is Extended Events)?
Differences lead to differences. Something is different. Identify that and you'll be on the way to figuring out why one is slower than the other and what to do about 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
May 9, 2019 at 12:14 pm
Oh, and doggone it, database settings. ANSI settings especially, but all the rest too. Differences there can lead to different performance characteristics.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply