Query Performance Is Awful

  • 5-SQL

    SSC Enthusiast

    Points: 112

    Hi,

    I have a database on a SQL Server 2016 running in Compatibility Level 110. The problem is the query speed is awful!

    The largest table holds about 70k rows of data, and when selecting the whole table in Management Studio it makes around 3 minutes to result everything. Interestingly, if I run the same query in dbForge Studio, the query returns in about 8 seconds.

    Also if I run the query against the master database, and explicitly add the database name into the query, the query return quicker but in in about 30 seconds.

    What is the difference between running a query in Management Studio, and dbForge Studio that makes then so different? Also why does the query result faster when run against another database (such as master). And finally, why does it take so long to return 70k rows of simple data?

  • Grant Fritchey

    SSC Guru

    Points: 395510

    With nothing to go on but the description you've provided, I have no idea. It could be so many different things that it's hard to say. Differences in the ANSI settings between dbForge & SSMS. dbForge could be displaying the data set before it all gets returned faster than SSMS does, but the query is the same. It's hard to know.

    To really understand this, you need to do a couple of things. First, don't measure performance from the client. Instead, use extended events to capture the performance metrics so that you can compare like to like and then figure out what the differences are. Next, start getting the execution plans from each of these queries to understand how the optimizer is resolving 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

  • Andrey

    SSChasing Mays

    Points: 645

    it's worth to read although it's a long read

    "Slow in the Application, Fast in SSMS?"

    http://www.sommarskog.se/query-plan-mysteries.html

    Regarding master db :   it has another compatibility level (130) than your db (110 = sql2012) and thus Query optimizer may/will behave accordingly.

     

  • dbaforever

    Hall of Fame

    Points: 3738

    That's a good point, Andrey. Also, if the original poster just recently created the table in master, its' stats and query plan got rebuilt in master. Maybe they need to update stats/rebuild indexes in the user database. But without more info, it's hard to say more.

  • TRACEY-320982

    SSChampion

    Points: 13489

    We had this, we set the database properties, options, legacy cardinality estimation to ON it was OFF before, it went from hours to secs.

  • Jeff Moden

    SSC Guru

    Points: 994667

    TRACEY-320982 wrote:

    We had this, we set the database properties, options, legacy cardinality estimation to ON it was OFF before, it went from hours to secs.

    Same here except backwards for us.  We had to turn it off.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

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