Best practices question 2

[k108]

Hi,

I have a question about where to put stored procedures...

For example, we have 2 databases, DB1 and DB2. There are some stored procedures in DB1 and some stored procedures in DB2.

However, the stored procedures on both DB's reference tables on the *other* DB. That is, there are selects/updates, etc across databases.

In the interest of simplifying maintanence, etc would it be a good idea to create a separate database altogether *just* for the purpose of storing stored procedures?

(This post is a very simplified example - we actually have many databases, and many many stored procedures that cross-reference tables on various databases - as it stands now, each DB has it's own set of sp's... but we are building a new machine and were wondering there's a better way to do this).

Anyhow, hope this isn't too strange a question, but it did come up...

Thanks!!

 

[DrSql]

What we done in our environment with similar situations to run all mainatnance stored procedures... we approched by dedicationg a server and db, with linked to all servers. This made us one sp to execute on all servers.

Dr.Sql
Good Luck.

 

[mrdenny]

I've seen it done both ways where the procs are spread out accross many databases, and where all procs are in a single database and they call the databases as needed.

As there isn't much maintaince to do with procs it really comes down to what works for your developers and the apps that have been written.

I like to keep the procs in the database for the app that the proc is used in.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

I wonder if there is any advantage to keeping all the procs in one database vs. spreading across many databases?

I am still getting used to the idea of multiple databases. In oracle, you use tablespaces to logically/physically separate data... I guess SQL Server databases are the equivalent of oracle tablespaces, in that they allow you to logically separate data, and the filegroups (where databases reside) allow you to physically separate data...

Same concept. So I would guess, if you're just using databases as a logical division, there should be no penalty, performance-wise, for having a separate database for stored proc's only...

On the other hand, if the stored procedure database is on a separate file, would there be a performance hit? Or no, because all sp's are stored in memory, once executed (or ideally, should be in memory if they're constructed properly). Or would it be advantageous if you're reading different disks? Or does it not matter???

Sheesh, this is so confusing sometimes...

 

[mrdenny]

This would need to be looked into, but I'm not sure how much execution plan caching would go on if the procs were kept in thier own database.

Loading procedures from disk should be almost nothing since procedures are very small (for the most part).

To make things a little more confussing, when SQL 2005 comes out we'll be intrudecing the concept of schemas within each database.

Think about the Oracle concept put scalled down to within each database, with each user in each database getting thier own schema. That's just going to make it more confussing.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

I just read that reusing execution plans (for stored procs) does not always result in the best performance. As a general rule, reusing plans *can* improve performance, but not always, especially if different parameter values are supplied.

 

[mrdenny]

Generaly resuing the plans will give you the better performance.

When your tables changes a lot, that can through off the stored plans, if you don't have auto update stats that can through off the plans.

If this becomes a problem you can have the SQL Server recompile the procedures which will force it do redo the plans.

The place when stored plans won't help you is if you have if statments within the stored procedure. If you've got if statements in there it will have to reginerate the plan each time since the path through the procedure changes each time.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief