Timeline for Optimizing SQL Server for an app that repeatedly hits the database with the exact same query?
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 4, 2017 at 9:39 | vote | accept | Ivan McA | ||
| Oct 25, 2016 at 8:17 | comment | added | Ivan McA | I'd just add, from talking to the client last night, while the report is slow with the client app on same box, where they are really seeing slowness (1.5 mins) is with a remote client app. Which is not surprising given the number of queries and that the issue seems to be overhead. Just to expand on the issue- I do know this particular client had an issue before with network backups that came down in the end to a wonky ethernet cable, so really "any" suggestions appreciated as to things they could look at to speed up remote access (will suggest to test network connectivity and latency). | |
| Oct 25, 2016 at 8:06 | comment | added | Ivan McA | 9 rows- 4 client app (same box), 5 SSMS. 2 SSMS are me, 3 someone else. For client app entries, ms between connection and session start 16, 16, 16, 13. For SSMS 6, 6, 3, 3, 3. ms between session start and last activity is 18843, 766, 1813, 2960 for the client app and 43, 40, 46600, 483, 140296 for SSMS. Last activity was at least 10h ago for everything but my SSMS connection. SSMS uses default values for the GUI connection, using Windows Authentication with SERVERNAME\SQLEXPRESS. There aren't any additional connection string params. Protocol default but I can see in the DMVs Shared Memory. | |
| Oct 25, 2016 at 7:56 | comment | added | Ivan McA | For the client app, right now, quiet time (middle of the night) I'm getting a total of only 9 rows- 4 for the client app and ms between connection and session start | |
| Oct 24, 2016 at 23:17 | comment | added | Solomon Rutzky |
@IvanMcA I have updated my answer -- the UPDATE section -- with more info and adapted query. Please run it and report the results. Also, for SQL Server Express, you can't use (local), but that shouldn't be the issue since SERVERNAME\SQLEXPRES shouldn't force TCP. What connection string are you using in SSMS? and what does sys.dm_exec_connections show for how SSMS is connected vs the app? That is the net_transport field for rows having <local machine> in the client_net_address field.
|
|
| Oct 24, 2016 at 23:14 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
Added UPDATE section
|
| Oct 24, 2016 at 19:37 | comment | added | Ivan McA | Just to add, it was giving me nothing because of the WHERE clause- in every case session_id = most_recent_session_id and the difference between connection and session start is always under 50ms (between 3 and 16) Do you think- is there any way to force it to use shared memory, or would this require editing the connection string in the source code? There is an .ini file for the client with DB connection parameters in it but from looking at it I don't think there is an option to connect using other than TCP- I tried changing SQLHost= to (local) instead of SERVERNAME\SQLEXPRESS but didn't work | |
| Oct 24, 2016 at 19:05 | comment | added | Ivan McA | @srutzky- I think it is using connection pooling. Your query gives me nothing but loking at sys.dm_exec_connections connect_time for the app in question is several hours to days back. Shared Memory IS enabled on the SQL Server and SSMS uses it but looking at sys.dm_exec_connections the app is using TCP to connect- I presume there is no way I can change this? | |
| Oct 24, 2016 at 16:35 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added clarification
|
| Oct 24, 2016 at 15:07 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added 549 characters in body
|
| Oct 24, 2016 at 15:01 | history | answered | Solomon Rutzky | CC BY-SA 3.0 |