This is fairly easy to test yourself. Let's create two very simple procedures:
CREATE PROCEDURE dbo.sp_mystuff
AS
SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
SELECT 'x';
GO
Now let's build a wrapper that executes them a number of times, with and without the schema prefix:
CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.sp_mystuff;
SET @i += 1;
END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
SET NOCOUNT ON;
DECLARE @i INT = 1;
WHILE @i <= 1000
BEGIN
EXEC dbo.mystuff;
SET @i += 1;
END
END
GO
Results:
Conclusions:
- using sp_ prefix is slower
- leaving out schema prefix is slower
The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.
Also I performed some pretty extensive testing of this pattern in the following blog post:
http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Yes is a good idea, if the DB is spread across different devices. ( that ideally would be on different volumes ). The percentage of the volume of corruption would be less. However to restore you still have to restore the whole DB, which in separate devices and volumes can be faster. ( I/O is spread, less concurrency )
No. You have to recover the Db(s) that the corrupted device holds. So if you have a DB that has 1 corrupt device and you have those logical connections (good practice), you just need to change on OS, the connection to the new physical device. Then restore DB.
How is you replication ? Repserver or disk mirroring? For repserver, i do not think so, your source ASE would stop functioning, and you would have to stop replication to restore the db. Disk mirroring I don't know.
Response to updated question:
My point 2 was:
- You will need to recover the whole db, unless you know exactly which objects where present on that device (and segments of it) that got corrupted, if yes then you can rebuild them manually( tables, procs, views... ). If you don't know or the labour to do it manually is too great, complete db recover.
What I mentioned about the logical connections was this, for example:
You have DB called TEST1 on the the following devices:
data01
data04
log02
data03
When you create the device with disk init, you have to give the path. But the path is not directed to the device itself, it goes to a soft link. Your data01 for example:
disk init.... physname='..sybase/data01.dat" ...
On the OS that sybase/data01.dat will be pointing to /dev/data01.dat. This way if you need on the OS to replace a corrupted device, you won't have to rebuild the database, you just create a new raw file, point data01.dat to it, and on DB restore data (LOAD). Faster process, than drop old DB, create new, load.
Well I'm pretty sure your errorlog would have some information about that corrupted device and also about your dumps had issues.
If your dumps were successfully dumped you should be able to use them on your restore (good devices). So I don't really get why you had to go back so many days. The thing is for instance if you have a corrupted device, it will manifest whenever some action tries to use it, (I/O). So your dumps would have failed i believe.
Best Answer
This is the usual reason. While the concept of compiling a stored procedure is implementation specific, it usually involves producing a query plan for the procedure and/or the individual statements it is formed from, and storing it for later reuse (to save that part of the process being repeated every time the procedure is called).
As your data grows, the balance of data in each table/index may change in such a way that the optimal plan when the procedure was last compiled is now much more inefficient than other options so the procedure will no longer be performing as well as it could. If you had just a few rows in a table when the procedure was created (or last compiled) a scan might have been more efficient than one or more seeks for instance, but that may no longer be the case later. Also which index it is most efficient to touch first can change over time especially for queries on wide tables and those that join in several tables.
The database engine will usually have some heuristics built in to cause a recompile after massive data changes, as well as after structural changes, but these are usually quite conservative in their action so manually kicking off a recompile is sometimes necessary. Much the same as the heuristics involved in deciding when to resample index statistics histograms (which in tern feeds into the query plan decisions generally and in your stored procs).
Sometimes procs with parameters will need different plans to be efficient for different inputs - sometimes there are circumstances where it is beneficial to always for a recompile because the difference is so high that you don't want to risk using a slow cached plan (in MS SQL Server the
WITH RECOMPILE
hint is there to deal with such circumstances, or the per-statementOPTION (RECOMPILE)
for a finer grained approach). Procedures complex enough that this is a major issue are often "code smells" which mean your design needs a tweak, though they are not always easily avoidable.A final reason to recompile stored procedures, functions, views, and other programmatic objects, is when changes elsewhere are made in a way that breaks dependency checking, meaning an automatic recompile does not happen when it is actually needed. For instance:
ALTER VIEW
).The dependency information proc->view is now lost.
As the dependency chain is broken, the engine does not know that the statements in the procedure may need to be recompiled meaning that procedure may start failing completely or giving incorrect results.