Application querying empty tables

My company uses an application which has pretty major performance issues. There are a number of problems with the database itself which I am in the process of working through, but many of the problems are purely application related.

In my investigation I found that there are millions of queries hitting the SQL Server database which query empty tables. We have about 300 empty tables and some of those tables are queried up to 100-200 times per minute. The tables have nothing to do with our business area and are essentially parts of the original application which the vendor did not remove when they were contracted by my company to produce a software solution for us.

Aside from the fact that we suspect our application error log is being flooded with errors related to this issue, the vendor assures us that there is no performance or stability impact for either the application or the database server. The error log is flooded to the extent that we cannot see more than 2 minutes worth of errors to do diagnoses.

The actual cost of these queries is obviously going to be low in terms of CPU cycles etc. But can anyone suggest what the effect would be on SQL Server and the application? I would suspect that the actual mechanics of sending a request, confirming it, processing it, returning it and acknowledging the receipt by the application would itself have an impact on performance.

We use SQL Server 2008 R2, Oracle Weblogic 11g for the app.

@Frisbee- Long story short, I created a table containing the querytext which hit the empty tables in the app's database, then queried it for all the tablenames I know are empty and got a very long list. The top hit was at 2.7 million executions over 30 days of uptime, bearing in mind the app is generally in use 8am-6pm so those numbers are more concentrated to operational hours. Multiple tables, multiple queries, probably some relavent via joins, some not. The top hit (2.7million at the time) was a simple select from a single empty tabl

I would suspect that the actual mechanics of sending a request, confirming it, processing it, returning it and acknowledging the receipt by the application would itself have an impact on performance.

Yes, and there are even some additional factors, but the degree to which any of these are actually affecting your system is impossible to say without analyzing the system.

That being said, you are asking for what could be an issue, and there are some things to mention, even if some of these are not currently a factor in your particular situation. You say that:


We have about 300 empty tables and some of those tables are queried up to 100-200 times per minute.

• Empty tables that are not being queried are not an issue. But I guess you could also be meaning that they are all being queried, just that some are getting hit a lot more than others.

• Query parsing & execution plan generation shouldn't be much of an issue if the query text being submitted remains the same across calls. SQL Server will hash the text of the query and look it up in the plan cache. If found, then it won't do either the parsing or compilation steps again (until the plan is removed from the cache).

• Any table, empty or not empty, will require at least a "shared" lock to indicate that the resource is being used. This prevents operations that require exclusive locks (add / changing / removing columns, etc) from making the change while the resource is in use. Locking and unlocking, even if accomplished in less than 1 millisecond since there is no data, still requires system resources (memory and CPU) to manage those lock operations.

• Even with no result sets coming back to the app from SQL Server, there is still the same amount of network traffic going to SQL Server whether the query yields results or not. The text of the query or name of the stored procedure needs to be sent. And even if no results come back, SQL Server still has to send some network packets containing the result set structure in addition to packets telling the client that a result set is starting (even if no rows are found) and then that the result set is ending and should be closed. And there could be additional messages from print statements and/or row counts.

• Connecting to SQL Server requires some amount of system resources. It takes CPU and memory to handle the authentication (as well as network packets back and forth) and this also takes time. This is why Connection Pooling exists: to cut down on this expense.

• Even with Connection Pooling reducing system resource usage, SQL Server still needs to maintain those connections and that requires memory and some minimal CPU.

• Even with no rows and hence a very quick execution time, the query was still executed. Even if there were 10 or 10,000 rows and those were pulled from the Buffer Pool (i.e. memory) since they were used frequently, a thread still needs to do that work. And a thread that is working on this useless query is not working on an actual useful query.

There might even be more, but this should help get a sense of things. And keep in mind that like most performance issues, it's all a matter of scale. All of the items mentioned above are non-issues if being hit once per minute. It's like testing a change on your workstation or in the development database: it always works with only 10 - 100 rows in the tables. Move that code to production and it takes 10 minutes to run, and someone is bound to say: "well, it works on my box" ;-). Meaning, it is only due to the sheer volume of calls being made that you are seeing an issue, but that is the situation that exists.

So, even at 1 million useless, 0 row queries, that amounts to:

• an extra 2 million lock operations (every lock must be unlocked, right?). this is mostly a cost of time spent on a useless operation instead of on a useful operation.

• more network traffic that could be getting you closer to saturation (not sure how likely this is, but still)

-
more connections being maintained which take up more memory. How much unused physical RAM do you have? that memory would be better used for running queries and/or query plan cache. Worst case would be that you are out of physical memory and SQL Server has to start using virtual memory (swap), as that slows things down (check your SQL Server error log to see if you are getting messages about memory being paged).

And just in case anyone mentions, "well, there is connection pooling". Yes, that definitely helps reduce the number of connections needed. But with queries coming in at up to 200 times per minute, that is a lot of concurrent activity and connections still need to exist for the legitimate requests. Do a SELECT * FROM sys.dm_exec_connections; to see how many active connections you are maintaining.

• regardless of anything else, this is still at least 1 million times during each day that a thread that could have been doing something useful was instead unavailable.

If I am not incorrect about

StackExchange Database Administrators Q#123370, answer score: 7