Optimizing SQL Server for an app that repeatedly hits the database with the exact same query?

I am dealing with performance issues with a Windows app that uses SQL Server Express (2014) on the back-end.

I have managed to get this running a lot better primarily by reviewing the indexing SQL Server side but there is one particular report that is still running quite slowly.

Looking into what it is doing, it appears to be looping in the app and querying out thousands of very simple SELECT * queries against one table WHERE = Primary Key, so only retrieving one record in each case. And when I say IDENTICAL, I mean identical, it's not even varying the primary key to get different stuff, it is asking for exactly the same record back from the database apparently each time it needs it, up to a hundred times in only a few seconds.

This is an example report that takes about 10-15 seconds to run when the server is quiet- how many times the query runs I've added as a comment:

```
SELECT * FROM "Patient" WHERE "_Recno" = 35051 -- (runs 106 times)
SELECT * FROM "Client" WHERE "_Recno" = 15607 -- (99 times)
SELECT * FROM "SpeciesEntry" WHERE "_Recno" = 180 -- (97)
SELECT * FROM "Table" WHERE "_Recno" = 9 -- (97)
SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2615 -- (96)
SELECT * FROM "Table" WHERE "_Recno" = 34 -- (96)
SELECT * FROM "DefaultEntry" WHERE "_Recno" = 2562 -- (84)
SELECT * FROM "Table" WHERE "_Recno" = 33 -- (84)
SELECT * FROM "Treatment" WHERE "_Recno" = 1682 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 1819 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 927 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 934 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 935 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 940 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 942 -- (33)
SELECT * FROM "Treatment" WHERE "_Recno" = 944 -- (33)
SELECT * FROM "OptionWP" WHERE "_Recno" = 103 -- (3)
SELECT * FROM "OptionWP" WHERE "_Recno" = 54 -- (1)
SELECT * FROM "PatientEstimate" WHERE "_Recno" = 8928 -- (1)
SELECT * FROM "Phr

The queries being identical is not the problem, and in some ways helps since the Execution Plan is cached and the data pages needed for the query should still be cached. The issue would then tend to be the per-connection overhead of authentication and initializing the session.

The first thing to look into is: is "connection pooling" being used? You can test for this by using SQL Server Profiler, select the "RPC:Completed" event in the "Stored Procedures" category, make sure that "TextData", "ClientProcessID", and "SPID" are checked for that event (at the very least, you can select other columns if you like). Then, go to "Column Filters", select "TextData", and in the "Like" condition add the following condition: exec sp[_]reset[_]connection. Now run that trace. If you see instances of exec sp_reset_connection coming through, then that is due to connection pooling being used. But that doesn't necessarily mean that it is this app that is using it. So look at one of the "SPID"s in the Profiler trace and try to match it to the output of the following query:

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*,
       sssn.[program_name],
       sssn.host_process_id,
       sssn.client_interface_name,
       sssn.login_name,
       qry.[text]
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) qry
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

The field on the far-right/end has the most recent query executed. That should confirm that the session is the app in question (by what it is doing).

IF you cannot find any evidence of connection pooling be used in general, or at least used for this app, then the Connection String being used by the app needs to be updated to enabled connection pooling.


...in most cases the client app and SQL Server are on the same machine, and multiple instances of the client are accessed through RDP. In a few cases the client is on a different machine on the LAN...

Based on the above info from the question, it appears that there are multiple clients connecting, correct? If so, then connection pooling, while probably still a good idea and helpful, will be less effective since each client maintains its own pool of connections. Meaning, 5 instances (or whatever number of them) will still make 5 separate pools for connections, and each one will reduce the connection startup overhead for its respective app, but cannot reduce the overhead beyond that / down to a single shared connection). Also keep in mind that even with connection pooling, if an app does not properly close its connections before attempting to open new ones, you will still have multiple connections / sessions coming from a given instance of the app.

In this case, and in the case of a single app that is not using connection pooling and cannot have its Connection String updated in order to use connection pooling, then, if possible to update the app at all, it would be quite helpful to implement a caching layer such as Redis or memcache (and I believe AWS and Azure both offer cloud-based caching solutions). These repetitive hits, can often be cached and bypass the RDBMS altogether (for a specified amount of time, of course), which is a large part of why these things exist.

Now that I have just caught up with the recent comments on the question, it appears that most likely neither the Connection String nor any part of the app can be modified. In which case there is not much one can do, outside of possibly checking to see if the connections made from the app that is running on the same server as SQL Server is using Shared Memory or TCP/IP to connect, and if it is TCP/IP for the same-server connections, then check to see if the Shared Memory protocol is enabled for SQL Server, and if not, then enable it. This is not a guaranteed improvement since it is possible that the connection string forces the protocol to be TCP/IP (e.g. using the syntax of: server=tcp:{something}), but still worth trying as this syntax is likely not being used.

UPDATE

from comment on this answer:


[the query above] gives me nothing, but looking at sys.dm_exec_connections.connect_time for the app in question is several hours to days back. ... in every case ... the difference between connection and session start is always under 50ms (between 3 and 16)

This could very well indicate the problem, or at least a large part of it. If a connection was made several hours to days back, and the session started immediately thereafter, then either the app is a desktop app that makes a single connection and session that it executes all queries against (similar to how SSMS query tabs work), or the app c

StackExchange Database Administrators Q#153159, answer score: 4