August 9, 2024 at 6:02 am
I am debugging (sort of) a problematic query, that gives a timeout error (30 seconds) in our company application. I've captured the query + parameters using Extended Events and when I run the exact same query with the same parameters in SSMS, it takes about 1 second. Anyone got any ideas what's happening here?
August 9, 2024 at 10:37 am
Looks like "runs fast in ssms but slow in application"
check ms documentation https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-application-slow-ssms-fast
August 9, 2024 at 8:58 pm
Just to add to what Jo Pattyn said, there are a lot of reasons why a thing can be fast in SSMS and slow in applications. You could have some blocking, the query could be ad-hoc query and first run builds the plan in the cache and the second run (your run) has a pre-compiled plan. Could be permissions or settings in the application. The app may be overriding the default settings you have set in SSMS/SQL Server for things like NOCOUNT or XACT_ABORT.
Are you running the application and SSMS on the exact same machine? If not, is it possible for you to do so. The reason here is I know for me, if I am VPN'ed from home, everything is a bit slower than when I am on site. Plus, if I am running stuff on low-resource machines (4GB RAM or less), everything is just slower. Heck, even being on site, running a query from my local machine vs running the same query on the server itself gives performance differences.
What I would recommend is to first make sure you are comparing apples to apples in terms of performance (same physical/virtual machine running the app and running the query) so you can properly get execution of the query. If it is still slow in the app but fast in SSMS, I would check the environments are the same (settings being overwritten). If that's not the case, I'd look for blocking and the query execution time (via Profiler or Extended Events). If the query executes quick but the data to the application is slow, that could be a sign of network congestion or a poor link to the computer.
Might not hurt to also check is this happening for everyone or just one user?
Last ditch thing - increase the execution timeout in the application IF you have access to the source. That is a thing configured on the application side (not SQL Server side). Set it to a very large value and do a test while running a trace (profiler or XE) and also watch computer metrics of the machine running the query (CPU, memory, disk I/O, and network). If disk I/O starts spiking, you are likely low on memory and paging to disk which is a slow operation. If network spikes, you may just have a large amount of data to pull in and your link may be too slow.
Another thing to check in the application (and SQL Server) is does the query complete quickly according to extended events but never completes according to the application. If so, you MAY have turned on implicit transactions OR the application may be explicitly opening a transaction prior to running the query and never committing/rolling back the transaction. If I remember correctly, open transactions in .NET count towards your query timeout, but I could be mistaken on that... It's been a long time since I have needed explicit transactions inside my application layer. My apps 99% of the time are calling stored procedures and they open the connection, get or change the data, and close the connection as quick as they can. I know opening and closing the connection is a slow operation, but my apps aren't exactly database heavy applications. MOST do the heavy lifting on the client machine to keep the server free to do other work.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 12, 2024 at 5:52 am
The problematic (readonly) query only returns one row with the parameters used when captured with Extended Events so I don't think it's related to huge amounts of data(?) I'm trying to capture the executionplan in Extended Events but haven't managed to do that yet (I'm no expert in EE), not sure I picked the correct fields but all queryplan-ids (or what they were called) doesn't return any data.. :/
I'll investigate if I can run SSMS on the actual server instead of my machine.
The query is generated by NHibernate, not sure if that's related but still..
EDIT: The query sometimes is very slow and sometimes it runs very fast, in both application/SSMS. I can only guess it's cache-related.
August 12, 2024 at 8:35 am
As you mention it happens in ssms too
it also coud be "parameter sniffing" https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
August 12, 2024 at 7:39 pm
Older but definitive article...
https://www.sommarskog.se/query-plan-mysteries.html
--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