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

    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

    Mr or Mrs. 500

    Points: 550

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

    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.

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

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