This question already has answers here:
Increasing the Command Timeout for SQL command (5 answers)
Closed 16 days ago.
The query works in the C# program (no timeouts) if I first run that same query in SSMS. It seems like the database is not initialized, loaded, etc. until I run the query in SSMS.
This only happens when the database just restored or if a database snapshot was restored. Same thing happens if the server/database is restarted, etc.
The query is somewhat complex with five temp tables and joins to these temp tables and three other table joins with many where clauses. Again, the query works so I do not suspect a problem with the query itself.
However, the query currently has no options or parameters added to it. It is complex but only uses basic T-SQL commands. I cannot post it here due to security issues.
Here is the message in C#. The timeout in the connection string is set to zero.
... ;Connect Timeout=0;System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I can only get the C# Program to work if I first run the query in SSMS. Sometimes if I run the C# program more than once, it works. We are seeing similar timeouts in other applications as well. The database size is 433000 MB. The C# Program does use threading to execute sub-queries later on after this base query executes.
1条答案
按热度按时间vsnjm48y1#
This only happens when the database just restored or if a database snapshot was restored. Same thing happens if the server/database is restarted, etc.
It sounds like the issue is an empty cache (and slow drives). SQL Server always wants to load data from RAM cache when it can, rather than disk, but it doesn't pre-emptively load data into RAM for caching. Rather it preserves data in RAM as it's used, so later access to the same data can be more efficient.
Therefore when you restart a database, it's normal for things to be a little slower at first, as the cache warms up. Thankfully, this is a rare event in any well-designed system.
I've seen environments where they'll build a stored procedure using
sp_msforeachtableto loop through each table and do a select query for the data. They run this at startup, so the SQL Server has a reason to fill the cache as soon as it starts.This can work for certain workloads, or if you really have enough RAM available to hold all your data and indexes, but more often this strategy fails to include indexes in the proper way and pollutes the cache with data that won't really be needed, leaving less available for indexes, temp tables, query plan cache, and working sets.
The strategy can even make things worse if, for example, you run a query where a certain index would be useful, but the optimizer sees the index is on disk, the cache is full, and the data pages are already in RAM. Then it might opt not to use the index, and this can continue over time such that the index never really loads into RAM.
In your case, it sounds like a targeted approach to pre-load data from certain tables might be useful.
The query is somewhat complex with five temp tables and joins to these temp tables and three other table joins
Five temp tables is pathologic. Temp tables are a feature to avoid. Especially the JOINs between them, because temp tables are commonly heaps with no meaningful indexes to make the JOINs efficient, and any index for the source data that might have helped is no longer useful. I've been able to use a temp table here and there on occasion to help solve a query issue, but they should be one of the last places you look when solving complex data issues, not the first.
If you can consolidate this down to fewer (or even a single) queries — and you almost always can — you'll typically find that things run much more efficiently, and you may not even need to worry about warming the cache.