Big database

Our current application has a mammoth database containing a single schema with plenty of indexes. This structure poses a challenging job when it comes down to fine-tuning the performance:

• Single schema: All the entities in the database are clubbed under a single schema named dbo. This again hampers business due to the confusion with the single schema regarding various tables that belong to different modules; for example, Customer and Supplier tables belong to the same schema, that is, dbo.
• Numerous stored procedures: Currently, the database has a large number of stored procedures, which also contain a sizeable chunk of the business logic. Some of the calculations are being performed within the stored procedures. As a result, these stored procedures prove to be a baffling task to tend to when the time comes to optimize them or break them down into smaller units.

Whenever deployment is planned, the team will have to look closely at every database change. This again is a time-consuming exercise and many times would turn out to be even more complex than the build and deployment exercise itself.