May 19, 2009 at 7:59 am
A few users were reporting query timeouts, but only a small percentage of our overall users. I replicated a query for SSMS and just kept executing it. Most of the time, it would run in 1 second. However, every so often, it would take much longer to execute. I let a long execution run to completion at one point and it took over six minutes. Has anyone experienced this? How does a script go from 1 second to 6 minutes? Not to mention, the problem seems to just have corrected itself as I haven't been able to reproduce it for at least 16 hours.
The script does contain an INNER JOIN and an encrypted field. However, the tables are quite small. Table one has 5 columns and 120 records. Table two has 4 columns and 66478 records (this table has the encrypted field).
Please let me know if you need any other information. Thank you.
-John
May 19, 2009 at 8:40 am
The changes to the query could come from several sources, but I'll just address two. If you were changing the parameters, you may have hit upon a set of parameters that either performed badly with the existing plan in cache, or created a plan that didn't work well. If the parameters are the same, you probably hit blocking on the server. I'm leaning towards the second thing being the issue. You should set up some time of monitoring to observe the blocks and waits on the machine. Here's a great white paper from MS that can help.
You also might want to look at the query itself. Check out it's execution plan to be sure it's using the indexes on the tables correctly. Getting your queries performing well generally decreases blocking.
"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
May 21, 2009 at 3:47 am
On the timeout issue...
(1) Check indexes exist on the correct columns. There are many blogs out there that have scripts to look at the DMVs and report potential "missing indexes". Be careful as not all indexes report needs to be created.
(2) Use profiler (standard template) to trace the session. Look closely @ the "ExistingConnections". In the TextData look @ the SET commands in particular the arithabort. By default this is set to OFF via most ODBC/OLEDB connection, but within SSMS it is set to ON.
I had a similar issue with a SP where via ASP.NET timed out, but running the exact SQL via SSMS took 3 seconds. Had to get the Dev team to manually set the "CommandText" to SET arithabort ON. This can be done @ Server/DB level but as it was out Prod server I didnt want to take the jump (bloody 5 9's cr4p)
Hope this helps
JL
May 21, 2009 at 4:15 pm
In this case, indexes don't seem to be the problem. I was able to see the issue in action the other day. Users reported an issue with a page in my system. I replicated that statement in SSMS and kept running it. The results in SSMS seemed identical to what the users were experiencing. The script would execute in 2 seconds or less most of the time. However, every so often, it would take a long time to run. I canceled script many times but let it run once, as described above, and it ran for over six minutes.
I'm still poking around to try and find more information. I did a check on dm_os_wait_stats and SOS_SCHEULER_YIELD is, bay far, the wait_type that has occurred the most. Which is really odd considering how beefy the server is vs. the number of users we have. Even while experience the issue (while it was happening with my users and I was able to replicate) the overall CPU usage never went over 50%; across 8 processors. I also double checked to make sure SQL is using all eight processors. The system admin confirmed nothing else was taxing the server as it's a dedicated SQL server.
From here, I'm just trying to learn more about waits... If it happens again, maybe I'll have enough information to combat the issue.
May 22, 2009 at 6:12 am
Have you looked at the execution plans? It's possible it's doing lots of scans and you're seeing contention. Have you tried running a blocking monitor (do a search in the scripts area here on SSC, there are serveral available) to see which processes are possibly in contention?
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply