Ok, let me say this is my 1st proper technical blog, so please gentle with any comments ;-)
The other day I was looking into an issue in our development environment which was causing timeouts. The dev's usual complain, but normally is their code causing the issue (dont get me started)... In this instance the query was pretty well written, CPU on the host was low, memory usage at the OS and SQL Level was all within an acceptable range and disk usage was also minimal for this environment. So, what was going on? One thing to also mention is that SSMS also used to time-out on the odd occasion.
Using a recent post by Paul Randal (blog) for a waittypes survey, I checked what the top waits were on the dev server. Some of the common one's appeared in there like WRITELOG, CXPACKET etc but one that stood out was PREEMPTIVE_OS_LOOKUPACCOUNTSID.
I knew a lot of the PREEMPTIVE_% waittype are new to 2008/R2, but this I had no idea what it was. I did however take an educated guess it was something to do with AD, and referring to the "master of all" google I stumbled across a post by Steve Hindmarsh (blog).
After reading the post with "hopeful" interest I found that this wasn't the cause of the problem, but it did however point me in the right direction...
I had an idea from this post that it has to be something with AD or Network routing, as the post covers issues with untrusted domains within mssql.
Well, after a 15 minute debate with the networking "commander in chief" we found the issue! DNS... Yes, the server was setup by the "techies" with 1 invalid DNS server and the other severely overload. Therefore when SQL Server was trying to authenticate the Windows user(s) it was causing SSMS and application to time-out.
Another lesson learnt; don’t always assume that it is an issue with SQL Server!!!
Hope this post help anyone with this issue and I look forward to your comments..
Thanks
