Page 226 - Open Soource Technologies 304.indd

Unit 13: Stored Procedure

13.2.12 Benefits of Stored Procedures Notes

Why should you use stored procedures? Let’s take a look at the key benefits of this technology:
• Precompiled execution. SQL Server compiles each stored procedure once and then
reutilizes the execution plan. This results in tremendous performance boosts when stored
procedures are called repeatedly.
• Reduced client/server traffic. If network bandwidth is a concern in your environment,
you’ll be happy to learn that stored procedures can reduce long SQL queries to a single
line that is transmitted over the wire.
• Efficient reuse of code and programming abstraction. Stored procedures can be used by
multiple users and client programs. If you utilize them in a planned manner, you’ll find
the development cycle takes less time.
• Enhanced security controls. You can grant users permission to execute a stored procedure
independently of underlying table permissions.

Load Balancing High Transaction Volume Databases

ecently, while working on a SQL server optimization project, we had the opportunity
to look into one interesting problem. We had a huge database (to the tune of 800GB)
Rwhich was being hammered with approximately 30000 transactions per second.
Database load was expected to grow by a factor of 100 in coming days and the idea was
to devise a solution which could handle that load. This was a SQL server 2005 enterprise
edition database hosted on an 8 processor fifth Generation server. We wouldn’t say this
server was on its knees with this load but yes there were wait times longer than expected
and to add to that there were times when data traffic suddenly went up significantly and
in those times DB was not able to keep up.

Though this is not a very common scenario in many of the modern day applications out
there but this definitely is a hallmark of databases handling loads from specific industries
like banking. Applications intended for these industries normally have huge volume of
small database transactions. In this article, we present one of the approaches you can take
to handle a scenario such as this.
Introducing Broker Hub
To demonstrate the problem which has these type of database requirements, let’s use the
example of a Broker Hub – a stock broking hub. Stock broking applications have very high
volume of small database transactions and also there are spurts in database activity depending
on market conditions. For simplicity sake, let’s assume that we were at a point when database
design and usage pattern for Broker hub database was in the most optimal state.
First Choice–Scale Up
So to optimize Broker Hub further, we had a number of ideas and first choice was obviously
to increase the hardware capacity. Increasing the hardware capacity did help the case. We
could handle upwards of 50000 transactions a second by moving to a better system with 16
processors and a SAN array of high speed disks. But above 50000 in our load environments,
we could still see the database to be the bottleneck.

Next obvious idea was to try SQL Server 2008 which has support for performance optimization
features like advanced compression (reducing the overall disk IO) and support for virtually
Contd...

LOVELY PROFESSIONAL UNIVERSITY 221