March 13, 2015 at 7:57 am
I was just going through the default trace files and see full of sort warnings, missing join predicates and hash warnings. The server behaved weirdly last night with queries longer than usual time and the server started choking. I didn't find any info from error logs or event viewer. any suggestions?
March 13, 2015 at 9:24 am
muthyala_51 (3/13/2015)
I was just going through the default trace files and see full of sort warnings, missing join predicates and hash warnings. The server behaved weirdly last night with queries longer than usual time and the server started choking. I didn't find any info from error logs or event viewer. any suggestions?
Sort warnings and hash warnings can be an indication that you ran out of memory, or had queries that were consuming huge amounts of memory, and so things were spilling to the disk. If you have query monitoring in place, I'd go and look to see what was running and what was running long or using lots of memory.
The join predicate warnings, that's an indication, usually, of bad code. Read through this blog[/url] post and the comments.
If you don't have monitoring in place, you might be able to at least see the queries that are in cache that have high amounts of memory use by query sys.dm_exec_query_stats.
"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
March 13, 2015 at 1:05 pm
Thanks Grant. The problem is almost all the queries ran slowly and started taking more time to finish around that time. I scanned to third party tool to find the queries around that time but as said they are running very fast now. I think i need to keep track of queries running every hour or so in order to resolve this issue.
March 16, 2015 at 2:42 am
Bear in mind that it's not at all uncommon for sorts to spill. The sort operation requires a lot of memory and, as such, most sorts that affect larger row sets will spill.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply