cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

This product reached the end of support date on March 31, 2021.

Remote vs local MS SQL Server instance for a chatty application

Tomasz_Szreder
Advisor

@Shakti Sareen has another question on tuning a database-intensive application with DNA:

Hi,

Attached trace showing performance overview graph as client and server
processing time almost same. But if we check the CNS breakdown then 82% from
server side and network time too showing, which is surprising.

Also if we check thread
level analysis then there are too many MSSQL:PreLogin with fix app turns as 5
and bytes involved 2,400. is there anything we can suggest here because on
predicting the trace response time increasing from 4 seconds to 80 seconds which
is too high. Also Server payload bytes for these threads are zero, but for many
we can see payload bytes, so I guess with this MSSQL:PreLogin no payload
required.

Now finally if you can
review the trace and suggest some great tuning and bottleneck will be good. Also please
review the trace and share recommendation since right now client and server
locally residing and after migration over latency we have to make sure
performance should not be impacted. Review MTU size also from both end if
required? I am not sure if this can make any value addition here

mako-00052-login-copy.zip

10 REPLIES 10

Tomasz_Szreder
Advisor

Hello Shakti,

1) Performance Overview and CNS Breakdown are very different views. I don't
think Performance Overview is a frequently used view, because it's
calculated from the perspective of each node without taking other
factors into account. CNS breakdown is usually more practical because it
shows the whole transaction as it happened and combines times of
simultaneous activities. So it doesn't make sense to compare these views
as far as I know.

2) You
basically already made the correct analysis with Response Time Predictor – it obviously
predicts a large increase in the transaction time because it is extremely
latency sensitive. If you take a look at the Thread Analysis, you’ll notice a
large number of very short threads, each initializing and closing another
connection with SQL Server instance. It’s hard to determine what your
application is supposed to do, because it keeps on repeatedly throwing the same
simple selects over and over again at your DBMS, opening and closing a network
connection each time. The optimizations have to be implemented by software
engineers and not network administrators, because even very small increase in
latency after moving your SQL Server instance to a remote area will contribute significantly
to application performance.

So:

- Make sure
your application connects and disconnects from the SQL Server only when
necessary (ideally once only)

- Try to group
selects into as few queries as possible, and run them as a transaction. Let the
DBMS do the optimization job. Note: full Microsoft SQL Server instance handles
optimizations better than the freeware SQL Server Express, if performance is
key.

- There are
many database-related optimizations that can be implemented on the SQL
Server-side, such as creating indices on frequently accessed data. I’m not sure
they would matter much for such simple queries as in your application.

Try to work with its designers and explain why
it keeps on accessing database in such a chatty way. There are a lot of
resources on how to improve a database-intensive application, see for instance:
http://www.codeproject.com/Articles/34372/Top-steps-to-optimize-data-access-in-SQL-Server
or other articles.

Thanks for the valuable info here. My reply
as follows:

- Make sure your application connects and
disconnects from the SQL Server only when necessary (ideally once only)

Reply: I guess you mean avoid below trend which is happening many times

MSSQL:Prelogin
MSSQL:BATCH SET NO_BROWSETABLE ON
MSSQL:PREP(1) 'SELECT * FROM xxfdic'
MSSQL:BATCH select count(*) from xxfdic
where data_elem_name = 'sys_installed' and entry_type = 'T' and value = 'ED'

- Try to group
selects into as few queries as possible, and run them as a transaction. Let the
DBMS do the optimization job. Note: full Microsoft SQL Server instance handles
optimizations better than the freeware SQL Server Express, if performance is
key.

Reply: From the
traces does it look like we having freeware SQL Server express and can
recommend full Microsoft SQL Server instance? How we can check this?

From the trace you can read
that server is proposing TDS 7.1 SP1 (look at Packet Trace, packet #11
description):

SUMMARY:
MSSQL:Response[8]{ENVCHANGE Database='Expandable_DB'}{INFO InfoNumber=5701
Class=0 Message='Changed database context to 'Expandable_DB'.'
Server=MKSQLPD-01 LineNumber='1}{ENVCHANGE SQLCollation=Default}{ENVCHANGE
Language='us_english'}{INFO InfoNumber=5703 Class=0 Message='Changed language
setting to us_english.' Server=MKSQLPD-01 LineNumber='1}{LOGINACK
Interface='Transact-SQL ServerTDSVersion=7.1,SP1 ServerProgram='Microsoft SQL
Server'{ENVCHANGE PacketSize=Default}{DONE Status=Success}

This means the app is
likely connecting to MS SQL 2000 based on

http://www.freetds.org/userguide/tdshistory.htm.

I don't think there is a
direct way to find out MS SQL version or type from the trace.

@Tomasz Szreder Ok, so as
per packet trace we can see backend as MS SQL 2000 and it's quite old. So may
be one of the suggestion we can give to application team to move on latest
server such as TDS 7.3 i.e. SQL Server 2008. Also anything else we can see
as bottleneck after reviewing packet capture such as MTU size which is showing
453. In Client trace shared with you we did not see any error/warning related
to MTU size still wanted to make sure. In server trace we can see Frame larger
than MTU that may be because of TCP offloading which I guess reason for this warning.

The table I shared does not include later SQL Server products, such as SQL Server 2012, 2014 and 2016 - I'd consider one of these if you're upgrading. The upgrade alone won't help much here, the problem is in how the application is using database, see my answers below.

I don't know if it makes sense in your case, but sometimes switching to a local file-based database may be a better idea than solving the latency problem (I'm thinking of SQLite 3 here). If the application doesn't have multiple simultaneous users and does not make use of advanced SQL Server features, then getting rid of latency because of a local database may be the most reasonable optimization of all.

See:

https://www.sqlite.org/whentouse.html

http://charlesleifer.com/blog/five-reasons-you-sho...

Regarding the multiple repeated MSSQL Prelogin/Select statements, it's unclear what you want to profile here. It seems rather aimless to repeat the same query several times in a row and profile it as a whole. If you repeat this block 20 times instead of 10 times, it will run 2 times longer in the same environment - but what for? You need to define your goals more clearly before approaching performance optimizations.

DNA lets you find out which transactions contribute the most to the total response time, but it's difficult to proceed if you don't specify what is the actual transaction you want to analyze. In this case the application is latency sensitive and any degradation of latency will slow down your application significantly.

@Tomasz Szreder : We are the performance team and need to tell application team where exactly the bottleneck and how they can tune the application if possible. Set of queries I mentioned earlier are the one's you referring to correct? Login is the specific transaction till now which is showing huge increase in response time from 4 sec to 80 seconds hence we are focusing on this transaction for now. I will be asking the application team why this trend being followed which look's very chatty. Why you mentioned application is latency sensitive? application having this sort of communication protocol what recommendation we can give to have less latency impact?

The protocol is latency-sensitive because the latency affects how long it takes the client to request data from the server and the server to respond, and the application asks the server and waits for response many times.

As you can see in the picture, I selected 4 threads related to a transaction and split the view with Bounce Diagram. If you select individual threads and see the corresponding Bounce Diagram arrows, you'll notice that the Prelogin phase takes the most time. The Prelogin sequence involves 10 application turns and the major optimization effort should go towards avoiding it. The other selects take usually 1-2 app turns and are sent simultaneously, so the latency effect is less observable than in the Prelogin sequence.

So, the recommendations would be to

- try to minimize the number of subsequent client-server talks by reusing one SQL Server connection (or a pool of opened connections)

- combine queries into one and then extract data application-side

- run your queries in a transaction

- set the session options once (SET NO_BROWSETABLE ON) instead of repeatedly resetting it in each transaction

- use stored procedures to avoid sending complex queries to the server in multiple packets

You can see another latency-sensitive transaction example in the demo traces database in Flowchart Examples:

This illustrates conversations with another database, Oracle, over a larger latency link. Note that here it takes short for the client and server to process data, but the packets travel a long time through the wire - this is similar to your case, only your trace is captured in a local network, so the latency problem may not be very visible from the your app users' perspective.

Tomasz_Szreder
Advisor

Hi Alex, that sounds like an error on your side, would you agree do delete the comment or did you mean something else? 🙂