Simple Select * very slow
-
Hello,
I have an issue where a database is extremely slow on an instance on a server. I have installed a new instance and then took a backup of the database and restored it to the new instance and its fast.
I downloaded the Contoso BI database and these ran fast on both instances.
any ideas why the DB on instance 1 is slow but the identical DB on instance 2 is fast, remember contoso is fast on both so i dont think its the instace, but then its an identical db....hope this all makes sense.
example of speed:
select * on table on DB1 = 7 mins
select * on table on DB2 = 4 mins
-
"select *" asks for all data from table and thus the whole table has to be taken to Buffer Cache
assuming that both instances are on the same host, there can be many reasons including the following:
- difference in configuration of INSTANCE1 & INSTANCE2 (max memory, for example)
- difference in versions/builds of INSTANCE1 & INSTANCE2
- different underlying disks for DB1 & DB2
- active sessions running in parallel on "slow" instance
- active processes on OS level during the tests
- network issues between you (client) and the host during the tests
-
Just piling on here.
When comparing the performance between two systems, we have to know that every possible thing is identical before we can say "See, SystemA is sucky and SystemB is not". The same database (from a restore, not rebuilt from imports or anything) with the same data and the same query is barely the start. How much memory does each machine have? How many processors does each machine have? How fast are those processors? Assuming absolutely identical hardware (in every possible regard, disks, controllers, etc.), then, server settings, database settings, all must be taken into account. Then, let's toss another wrinkle on the stack. Are the active loads on these systems the same, usually to mean, you're the only resource on both systems. Otherwise, we have to add in contention on any of the given resources.
In short, everything Andrey said and more must be compared, not just two runs of the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
thats the point though, its 2 instances on the same server, so hardware and load is the same...
migrated the live DB off so the load is minimal
-
The thing is, SELECT * from a table without a where clause is just a test of server settings, database settings, load, contention, and hardware. It's not anything else. There's not going to be two different execution plans, two different paths to the data or anything else. So, it's down to, what's the differences between those two instances? There are differences. You just have to identify them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
thanks even though the contoso DB on both instances select query is fast on both instances?
- iwatson wrote:
thanks even though the contoso DB on both instances select query is fast on both instances?
are the tables the same in Contoso and your database?
what if you select * from small table in case of Contoso?
-
Identical database. Check?
Identical query. Check?
If either of those is wrong, one database is older than another or something, statistics updates were run, whatever, then everything changes. However, assuming identical databases and identical queries, what other things are different? Are they on the same disk or different? Do both instances have the same memory allocation? Is that allocation within the memory of the system or are they fighting for it? CPU priority? Something has to be different. Something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi Andrey, yes same database and tables download from MS website.
small or large tables perform almost identical on Contoso
- iwatson wrote:
small or large tables perform almost identical on Contoso
Ah, but "almost" is not identical. If everything is equal, performance should be identical (within a small margin of error, again, contention for resources, waits, sunspots). Capture more metrics. What are the wait statistics for each query when you run them. What resources are they waiting on? That can give you an indication. What about the I/O of each, same? That can give you an indication.
I believe that the database itself and the query are the same. So... where are the differences? They must exist.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - iwatson wrote:
Hi Andrey, yes same database and tables download from MS website.
small or large tables perform almost identical on Contoso
by the way, compare subsequent runs (durations) of the same query , let's say 5 cycles on each db
-
Hi Andrey yes been doing that and clearing cache
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
before each query.
the annoying thing is the contoso DB's are fast on both instances...
- iwatson wrote:
the annoying thing is the contoso DB's are fast on both instances...
Grant is absolutely right - there should be something different between databases/instances/whatever.
let's compare output of sp_spaceused
exec sp_spaceused 'slow table'
exec sp_spaceused 'fast table' -
Have you tried restoring the database on instance 1?
-
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply