10

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 table with a where clause, no joins. I would expect larger queries with joins to the empty tables might include updates to linked tables, but I'll check that and update this question asap.

Update: There are 1000 queries with an execution count of between 1043 - 4622614 (over 2.5 months). I'll have to dig more to find out when the cached plan originates from. This is just to give you an idea of the extent of the queries. Most are reasonably complex with more than 20 joins.

@srutzky- yes I believe there is a date column related to when the plan was compiled so that would be of interest, so I'll check that out. I wonder would thread limits be a factor at all when the SQL Server sits on a VMware cluster? Soon to be a dedicated Dell PE 730xD thankfully.

@Frisbee - Sorry for the late response. As you suggested, I ran a select * from the empty table 10,000 times over 24 threads using SQLQueryStress (so actually 240,000 iterations) and hit 10,000 Batch Requests/sec immediately. Then I reduced to 1000 times over 24 threads and hit just under 4,000 Batch Requests/sec. I also tried 10,000 iterations over only 12 threads (so 120000 total iterations) and this produced a sustained 6,505 Batches/sec. The effect on the CPU was actually noticeable, around 5-10% of total CPU usage during each testing run. Network waits were negligible (like 3ms with the client on my workstation) but the CPU impact was there for sure, which is pretty conclusive as far as I am concerned. It seems to boil down to CPU usage and a bit of needless database file IO. The total executions/second works out at just under 3000, which is more than in production, however I am testing only one of dozens of queries like this. The net effect of hundreds of queries hitting empty tables at a rate of between 300-4000 times per minute therefore would not be negligible when it comes to CPU time. All testing done against an idle PE 730xD with dual flash array and 256GB RAM, 12 modern cores. This is the output from SQLSentry

@srutzky- good thinking. SQLQueryStress seems to use connection pooling by default but I had a look anyway and found that yes, the box for connection pooling is checked. Update to follow

@srutzky- Connection pooling is apparently not enabled on the application - or if it is, it is not working. I did a profiler trace and found that the connections have EventSubClass "1 - Nonpooled" for Audit Login events.

RE: Connection Pooling- Checked the weblogics and found connection pooling enabled. Ran more traces against live and found signs of pooling not occurring correctly/at all: enter image description here

And here is what it looks like when I run a single query with no joins against a populated table; the exceptions read "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" Note the batch requests counter. Pinging the server during the time the exceptions are generated results in a successful ping response.

enter image description here

Update- two consecutive test runs, same workload(select*fromEmptyTable), pooling enabled/not enabled. Slightly more CPU usage and a lot of failures and never goes above 500 batch requests/sec. The tests show 10,000 Batches/sec and no failures with pooling ON, and about 400 batches/sec then a lot of failures due to pooling being disabled. I wonder if these failure are related to a lack of connection availability?

enter image description here

@srutzky- Select Count(*) from sys.dm_exec_connections;

  • Pooling enabled: 37 consistently, even after the load test stops

  • Pooling disabled: 11-37 depending on whether or not exceptions are
    occurring on SQLQueryStress i.e.: when those troughs appear on the
    Batches/sec graph, the exceptions occur on SQLQueryStress, and the
    number of connections drops to 11, then gradually back up to 37 when the batches start to peak and the exceptions are not occurring. Very, very interesting.

Maximum connections on both test/live instances set at the default of 0.

Have checked the application logs and cannot find connectivity issues however, there are only a couple of minutes worth of logging available due to the large number and size of errors i.e.: lots of stack trace errors. A colleague on app support advises that a substantial number of HTTP errors occur related to connectivity. It would seem based on this, that for some reason the application is not correctly pooling connections and as a result, the server is repeatedly running out of connections. I will look into app logs more. I wonder is there a way of proving this is happening in production from the SQL Server side?

@srutzky- Thank you. I'll check up on the weblogic config tomorrow and update. I was thinking though about the mere 37 connections - if SQLQueryStress is doing 12 threads at 10,000 iterations = 120,000 select statements non-pooled, shouldn't that mean each select creates a distinct connection to the sql instance?

@srutzky- Weblogics are configured to pool connections, so it should be working fine. Connection pooling is configured like this, on each of the 4 load-balanced weblogics:

  • Initial Capacity: 10
  • Maximum Capacity: 50
  • Minimum Capacity: 5

When I increase the number of threads executing the select from empty table query, the number of connections peaks around 47. With connection pooling disabled, I consistently see a lower max batch requests/sec (from 10,000 down to about 400). What will happen every time is that the 'exceptions' on SQLQueryStress occur shortly after the batches/sec goes into a trough. It is related to connectivity but I cannot make sense of exactly why this is happening. When no tests are running, #connections goes down to about 12.

With connection pooling disabled, I am having trouble understanding why the exceptions occur, but maybe that it a whole other stackExchange question/question for Adam Machanic?

@srutzky I wonder then why the exceptions occur without pooling enabled, even though the SQL Server is not running out of connections?

  • 1
    Peter, with the most recent updates in mind regarding connection pooling, it looks like you now need to re-run your tests with SQLQueryStress but with Connection Pooling turned off. That would be a more accurate reflection of the effects of how the app is working, and I believe it will show an increase in CPU usage and even RAM usage. – Solomon Rutzky Jan 7 '16 at 19:43
  • 1
    Peter, do you have a maximum number of connections set for the server? I am guessing that without the pooling you are running into an issue of too many connections. I wonder if your app ever gets that error. Also, if possible to re-run that last test one more time (both with and without pooling enabled), while the test is running for each of those two configurations, run a SELECT COUNT(*) FROM sys.dm_exec_connections; to see if the value is greatly different between having pooling enabled or not. Based on those errors, I think there would be many more connections when pooling is disabled. – Solomon Rutzky Jan 8 '16 at 15:46
  • 1
    Peter, 37 connections seems like an awfully low maximum. Given that the connection limit is set to 0 (i.e. unlimited), is the system memory bound? Also, connection pooling should be on by default, but is controlled by the client. Is the app a .NET app? Doesn't need to be in order to use connection pooling, but would help knowing in order to find the cause of this. And can you see what connection string is being used? Does it specify Pooling=false or Max Pool Size? – Solomon Rutzky Jan 8 '16 at 21:40
  • 1
    Peter, each of the 12 threads is creating its own connection per query, sequentially for the 10k iterations. So without pooling, the connection can be destroyed as soon as the code closes the connection. Pooling will keep the connection around for re-use. So it makes sense that the number of connections was consistent while using pooling. Not sure about why 37 without more info. How many connections are there when no test is running? Backing out that number will give a better indication of how many are created by the testing. – Solomon Rutzky Jan 8 '16 at 22:22
  • 1
    Connection pooling is maintained per-client, not server. So WebLogics and SQLQueryStress should each have their own connection pools (in terms of the min_pool and max_pool sizes, etc). Regarding "With connection pooling disabled, I see a lower max batch requests/sec": that makes sense since it takes more time for each connection from the app to authenticate and initialize the session, etc. This is exactly why connection pooling exists :-). – Solomon Rutzky Jan 26 '16 at 16:35
7

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 what I have been stating here, then it seems to me that, even if on a small scale, this is a type of DDoS attack on your system since it is flooding the network and your SQL Server with bogus requests, preventing real requests from either getting to SQL Server or being processed by SQL Server.

1

If the tables are getting hit 100-200 times a minute then they are (hopefully) in memory. The load on the server is very very low. Unless you have high CPU or memory on the database server this is likely a non-issue.

Yes the queries take shared locks but hopefully the are not blocking any update locks nor being blocked by any update lock. Do you you have any update, insert, or delete on these tables. If not I would just let it go - if you are having performance issues there have got to be bigger fish to fry from a database server perspective.

I ran a test on 100,000 select count(*) on an empty table and it ran in 32 seconds and the queries were over a network. So 1/3 millisecond. Unless your network is getting overloaded this is not even impacting the client. If you are having major performance issues these 1/3 milliseconds blank queries are not what is killing the app.

And these could be just part of a left join grabbing some static type data the is not part of the current application. It could be chained with with other queries so it is not an extra round trip. If so yes it is sloppy but it is not even causing more traffic.

So back to look at the actual statements. Are you seeing any updates, adds, or deletes on these tables?

Yes many empty tables and queries to empty tables are indication of sloppy coding. But if you are having major performance issues this is not the cause unless you have some really sloppy write operations also going on with these tables.

  • How many other users were on the SQL Server running queries when you did your 100k queries test? I am not saying that I am right and you are wrong, but if you were the only one on the system, or one of just a few, then naturally you wouldn't see much of an impact. The issue of the locking wasn't a matter of blocking, it was simply a matter of the resources it requires of SQL Server to lock and unlock those data pages, even if they are always in the Buffer Pool. It is still work that is being done. And schedulers are not unlimited. – Solomon Rutzky Dec 10 '15 at 20:39
  • And I am not saying you are wrong. Other users or not it is still a valid measure of how long it took and a measure of resources The stated load is 100-200 per minute. 100,000 from one client in 30 seconds exceeds that load by a factor of 200 to 400. If there are no update locks then if it came from one client or 100 makes no difference. Your answer assumes there is there is either an overloaded network or SQL server and based on the question you don't know that. If this was a DDoS attack there would be more like 100 / sec (not minute) and it would not be against an empty table. – paparazzo Dec 10 '15 at 20:54
  • Correct, based on the question we do not know enough to narrow it down, which is why I was saying that these things could be an issue, depending on circumstances. And the DDoS thing was just an analogy, mainly based on the wording of the original question which implied it was several getting hit at that rate and many others getting hit as well, just less frequently. – Solomon Rutzky Dec 10 '15 at 21:06
  • I consider this to be a valuable answer in the sense that the first paragraph sums it up very well: "Unless you have high CPU or memory on the database server this is likely a non-issue." In our case, we do have high cpu usage at certain times of the day and therefore the extra cpu pressure does seem to be a factor based on my testing. – Peter Jan 6 '16 at 16:54
  • Notably I only quoted queries executing 100-200 times/minute, when in actual fact there are around 50 queries to these empty tables with execution counts of between 200-4000/minute. Cumulatively, the effect of querying empty tables with this frequency does affect the CPU quite a lot, even in the best case scenario of non-parameterized queries executing repeatedly, so the plan, data etc are all in memory. – Peter Jan 6 '16 at 16:57
0

In general on each query the following steps are done:

  1. Request from Application.
  2. Database Parse the query.
  3. Database engine check if this query is stored already in the RAM. use execution plan if exists in Memory.
  4. if not existing in RAM , Database engine check for existing statistics on the objects in the query and determine the execution plan.
  5. Run the execution plan , use i/o to get data from the disk.
  6. response to application.

many queries as you mentioned might cause extra load on a system which is already heavy - extra load on connections , CPU , RAM and I/O.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.