July 10, 2019 at 6:19 pm
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?
July 11, 2019 at 11:08 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2019 at 12:21 pm
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.
July 17, 2019 at 2:05 am
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.
July 17, 2019 at 5:21 pm
We had this, we set the database properties, options, legacy cardinality estimation to ON it was OFF before, it went from hours to secs.
July 17, 2019 at 5:47 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply