September 26, 2011 at 6:25 am
Hi all,
I have a TSQL case I cannot explain. The problem is that the same SQL statement from the same application for some clients is executed as batch and from other clients as RPC.
To test this, we have created a very simple test app that executes this statement only:
set dateformat MDY
The application is installed on several client machines and all connect to the same SQL Server.
Using the SQL profiler we see for most clients:
EventClass: SQL:BatchStarting/SQL:BatchCompleted
TextData: set dateformat MDY
but, for some clients we see this:
EventClass: RPC:Starting/RPC:Completed
TextData:
declare @p1 int
set @p1=0
exec sp_prepexec @p1 output,NULL,N'set dateformat MDY'
select @p1
So for some clients the statement is executed as RPC and from other clients is it executed as SQL batch.
Since the scope of the set command in the first statement is different from the scope of the second statement, the same application behaves differently when run from one client or another.
How is it possible that this statement sometimes gets executed as RPC and sometimes as SQL batch? What can cause this difference? My first thought was that it had be the client application, but it is the same compiled application (Win32 exe) running on all clients.
clients: Win32 application + sql native client (10.50.1600.1)
server: SQL Server 2008 R2
Maybe someone can give me some more insight on this? Thanks!
September 26, 2011 at 6:47 am
Are these clients using ODBC data source to connect to SQL server? I suspect it is a connection configuration issue.
The probability of survival is inversely proportional to the angle of arrival.
September 26, 2011 at 7:05 am
No ODBC connections. The client is a Delphi application and uses the DBExpress framework for the database connection. In the test app there is a connection component and a query component. We hardcoded all settings for the database connection in the test app. The only dependency is a MS SQL driver dll from the DBExpress framework which talks to the sql native client (sqlncli10.dll).
September 26, 2011 at 7:31 am
I suspect there is a configuration setting within the application or (probably) DB express that is different on some of the clients. Are the clients all using TCP/IP or Named Pipe connections to the server?
The probability of survival is inversely proportional to the angle of arrival.
September 26, 2011 at 8:29 am
The clients *should* be using TCP/IP, but we didn't check that so far. Will check this now for all clients. To be continued...
September 26, 2011 at 8:37 am
The difference is how they're called. If you called a procedure through an execute SQL statement in your code, it'll come in as a TSQL batch. If you explicitly call the procedure through the DBLIB, you'll get the RPC call. The RPC call is considered to be the more efficient of the two, so I'd check that client code to see which one is doing what and change the TSQL Batch to match the RPC code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2011 at 2:15 am
We checked the clients and all are using tcp/ip.
The problem is now tracked down and it appears to be the Delphi DBExpress framework on the client side. There is a driver dll for the SQL Server connection that causes the weird behavior. Since we don't have the sources of this dll we cannot see under what conditions it uses RPC or Batch. We are contacting the vendor about this to solve this issue.
Thanks all.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy