Hello folks, I'm operating from a position of insufficient knowledge. I'm trying to optimize some application generated queries and my only interface to the production box is SentryOne's web GUI.
I see queries on production that take 7-8 seconds (duration). When I copy the SQL to a non-production, but realistic data, box the execution takes less than a second in SSMS. It's doing a merge join on non-prod and a loop join on prod, but that doesn't seem to be the issue. Forcing a loop join on non-prod is still extremely fast. Likewise, I had the admins run the query in question on prod from SSMS and execution is fast. My question has to do with the metric "Duration" in Top SQL. Can someone explain exactly what that metric is? For example, is it the time it takes the query to execute, or is it execution time plus network IO to the app? This will hopefully help me determine if I've got a SQL optimization problem or an architecture issue.
Database: SQL Server 2016
Thank you in advance!
