October 17, 2005 at 8:25 am
Hi. We have an application that makes calls to SQL scripts instead of stored procedures, and the creator says the I/O is minimal. In addition to running Perfmon I am trying to find documentation to verify that this method is slow and causes bottlenecks but can't find anything at Microsoft. There are many articles on performance but nothing that directly addresses this. Does anyone have a link or any pertinent information? There is a definite performance issue. Thanks.
Linda
October 17, 2005 at 10:26 am
You mean the scripts are stored on the server and are called using xp_cmdshell? Or that they are send across the network as sql-statements?
1)Advantages using stored procedures:
Reduces roundtrips (network latency)
Managebility,resusability (only 1 place to change instead of various applications)
Less cpu used due lower amount of compilation
2)You can use profiler to catch the SQL statements sent.
(beware of select *, no where clause ...)
3)You can copy/open the captured statements/scripts in query analyzer to view an estimated query plan. (beware of table scans)
October 17, 2005 at 10:34 am
The scripts are not located on the database server, but a smaller server. The application calls the scripts that contain T-SQL code. We have run traces to capture, but it doesn't convince the vendor that it creates a bottleneck. What would be ideal, would be some kind of article from Microsoft 🙂
Linda
October 17, 2005 at 12:18 pm
Microsoft certainly has a best-practices stored in here:
http://www.microsoft.com/technet/prodtechnol/sql/default.mspx
Calling scripts doesn't seem to be a high I/O solution;
Assuming the scripts are stored on the computer from where the application runs (otherwise extra permission checking/network latency)
1.A new/same thread opens & executes the script (from disk instead of memory,sometimes even replacing some variable?) -> Open connection ,the whole script is send across the network medium instead of the name of the stored procedure + parameters (size), close connection
2.The scripts needs to be compiled by the query optimiser (permission checking, best plan) (some expections when using sp_executesql)
3.Integrity checks on the scripts due proper security?
October 18, 2005 at 10:58 am
Linda -
Is the application in question written specificially for SQL Server or does it support multiple database platforms?
You may have a situation where the application is doing exactly what it's supposed to do in order to support the widest possible number of database servers.
Joe
October 18, 2005 at 11:19 am
You guessed it. It's written for multiple platforms. However, they haven't seen performance issues at other sites like we have here. Of course our site is really stress-testing the application so maybe they will reconsider 🙂
Linda
October 18, 2005 at 11:38 am
What is your network connectivity ? The reason I ask is that I have been in a similar situation once before. The only things you may be able to do are those that help 'optimize' the architecture that you're dealing with. You need to check the following:
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 18, 2005 at 11:38 am
What is your network connectivity ? The reason I ask is that I have been in a similar situation once before. The only things you may be able to do are those that help 'optimize' the architecture that you're dealing with. You need to check the following:
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 18, 2005 at 11:50 am
Assuming that your vendor can't come up with a quick fix (e.g. rewrite the application to use stored procedures) you're probably stuck with tuning what you can.
The usual SQL performance tricks apply - determine where the problem lies, then see what you can do from there. Start at http://www.sql-server-performance.com/articles_audit.asp, the tasks/steps listed are pretty comprehensive.
Even if you're seeing a hardware bottleneck I would be sure to take a look at the SQL Trace for long running or high CPU queries. It's not unusual for applications that support multiple database platforms to be "bare bones" meaning that the developers are not well versed in a specific platform and do very little additional indexing, etc. Could be that a couple of additional indexes would make the application scream (wishful thinking).
Joe
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply