Simple Select * very slow

  • iwatson

    SSC Enthusiast

    Points: 190

    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

     

  • Andrey

    Right there with Babe

    Points: 723

    "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:

    1. difference in configuration of INSTANCE1 & INSTANCE2 (max memory, for example)
    2. difference in versions/builds of INSTANCE1 & INSTANCE2
    3. different underlying disks for DB1 & DB2
    4. active sessions running in parallel on "slow" instance
    5. active processes on OS level during the tests
    6. network issues between you (client) and the host during the tests

     

  • Grant Fritchey

    SSC Guru

    Points: 396622

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • iwatson

    SSC Enthusiast

    Points: 190

    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

  • Grant Fritchey

    SSC Guru

    Points: 396622

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • iwatson

    SSC Enthusiast

    Points: 190

    thanks even though the contoso DB on both instances select query is fast on both instances?

  • Andrey

    Right there with Babe

    Points: 723

    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?

  • Grant Fritchey

    SSC Guru

    Points: 396622

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • iwatson

    SSC Enthusiast

    Points: 190

    Hi Andrey, yes same database and tables download from MS website.

    small or large tables perform almost identical on Contoso

  • Grant Fritchey

    SSC Guru

    Points: 396622

    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 Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Andrey

    Right there with Babe

    Points: 723

    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

     

  • iwatson

    SSC Enthusiast

    Points: 190

    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...

  • Andrey

    Right there with Babe

    Points: 723

    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'
  • tripleAxe

    SSCertifiable

    Points: 5605

    Have you tried restoring the database on instance 1?

     

     

  • iwatson

    SSC Enthusiast

    Points: 190

    2019-07-29_14-24-36

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply