Sql-server – the date mentioned in @@version SQL Server

Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called only when necessary. This way, most of the times, the table variables are not created (obtained from cache to be more precise). By doing this, we were able to significantly reduce the perf index Active Temp Tables, and SQL Server returned to normal behavior.

For those that did not know this: if an ad-hoc query, PROC or UDF declare a table variable (or a temporary table), SQL Server creates the tables, before starting execution. So, an early return does not prevent temp tables from being created. This completely fooled this accidental DBA. I was trying to find the culprit by changing queries one by one, putting a return in the beginning. Because of this behavior (which is understandable), I was failing to find the root cause of the contention on the DB. Live and learn every day!

For those who are now saying that it does not make sense to have a query called so many times, you are right! We are already changing the application to cache the results outside SQL Server.

Thank you Aaron and RTL for your help.

Renato Ferreira

Due to your comments on the main answer, I would write my suggestions below. You described that your neighbor can still connect to the machine but you not. This doesn't seem like a normal behavior of an SQL Server. This just brings me to another conclusion. It seems to be more likely a network error. In fact, you also described that you could connect to the server too, just before an hour.

I would suggest the following solution approaches:

1. Check if your neighbor still can connect to the server.
2. Check if you can connect to all other systems your normally used to connect with.
3. Logon on the PC of your neighbor, try to connect to Server X. If this will work, it's nothing with your permissions or with your user.
4. Due to the fact, that it seems to be network related, I would suggest to check the network in your company. Check at your neighbor's PC which IP is assigned to Server X. You can do this by using the cmd and typing nslookup serverx. Compare it with the IP which was provided after you tried to ping serverx. Do they match? If not, you probably have a DNS error. Which means that Server X has another IP, but your PC hasn't recognized it in his ARP. You may want to flush your your dns-cache. You can do this using ipconfig /flushdns on the command line.
5. If this 4 doesn't apply or help, you can try an tracert to server x from your neighbor using tracert serverx. Check which hops occur. Try the same on your machine. Do they match or is there any difference? If you found a timeout on a special ip, you can try to ping the corresponding ip from your pc and from the pc of your neighbor. If you can't reach the ip, but your neighbor can - it looks like a port error on a specific switch between you and your neighbor. By the way, the whole story at all seems to be a defect port in my opinion. Just due to the fact that this just occurs from one minute to the next is kind of specific for an port error. Even the fact that just you cannot connect to the server. You should try this!
6. If all of the above doesn't help, you still check the firewalls between you and server x. Some companies use smart switches or IDS application (Intrusion detection system) which will detect misbehavior and close specific ports. If your company use such a system and it's not proper configured it may cause such a port close. I've had a similar solution in my old company in the first days of our IDS. You can also try to connect to server x from your neighbors PC using RDP and try to ping your PC from the side of the Server.

If nothing else helps, you need to contact your system administrator (if your not the one :-D) and try to figure out which cable or port is broken.

I really don't think that this is an SQL Server specific error. I would guess that this is some hardware or maybe software error in your network/pc.