How to Force all Stored Procedures to "recompile"?

Is there a way to force all stored procedures to "recompile" or create a new
execution plan? What we are trying to do is find a way to quickly identify
all stored procedures that are invalid because of schema changes like table
s or columns dropped or alt
ered.
Thanks,
BLGYou are asking two different things. AFAIK, there's no way to know which pla
ns are invalidated (a plan can be
invalidated for several reasons).
If you want a proc to recompile at next execution, you can use sp_recompile
on any of the tables that the proc
is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a new execu
tion plan? What we are
trying to do is find a way to quickly identify all stored procedures that ar
e invalid because of schema
changes like tables or columns dropped or altered.
> Thanks,
> BLG|||Hi,
Run the command DBCC FREEPROCCACHE to remove all compile plans from the
procedure cache.
Karl Gram, BSc, MBA
http://www.gramonline.com
"blg" <anonymous@.discussions.microsoft.com> wrote in message
news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> Is there a way to force all stored procedures to "recompile" or create a
new execution plan? What we are trying to do is find a way to quickly
identify all stored procedures that are invalid because of schema changes
like tables or columns dropped or altered.
> Thanks,
> BLG|||D'oh. Why didn't I think of that? :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:uqRIwlqEEHA.4080@.TK2MSFTNGP09.
phx.gbl...
> Hi,
> Run the command DBCC FREEPROCCACHE to remove all compile plans from the
> procedure cache.
> --
> Karl Gram, BSc, MBA
> http://www.gramonline.com
>
> "blg" <anonymous@.discussions.microsoft.com> wrote in message
> news:1C9AE715-5303-4FC6-ABD2-6ADCA7B6FF87@.microsoft.com...
> new execution plan? What we are trying to do is find a way to quickly
> identify all stored procedures that are invalid because of schema changes
> like tables or columns dropped or altered.
>

How to find the size of all databases at once

Hi everyone.

I'm very new to SQL and need to move databases from an old server to a new one that is attached to a SAN. I'm trying to plan LUN sizes.

Is there a way to show the sizes of all databases on the server at once?

Thank you.

Try running the following: "exec sp_helpdb". This will give you the size in megabytes of each database. If you pass the database name to sp_helpdb you will get file level information which may assist in your planning. Thanks.

|||

When I execute this command I get this error:

Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Do I need to add something else?

|||

It seems that one of the databases does not have an owner. Check the following link:

http://www.fogcreek.com/FogBugz/KB/errors/CannotinsertthevalueNULLi.html

I hope this solves your problem.

Best regards,

Sami Samir

How to find query plan for a stored procedure using temporary tables

This post is related to SQL server 2000 and SQL Server 2005 all
editions.
Many of my stored procedures create temporary tables in the code. I
want to find a way to find the query plan for these procs
Repro

--***********************************
use pubs
go
CREATE PROCEDURE Test @.percentage int
AS
SET Nocount on
--Create and load a temporary table
select * into #Temp1 from titleauthor

--Create second temporary table
create table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ord
int, rolaylityper int)

--load the second temporary table from the first one
insert into #Temp2 select * from #Temp1

go

set showplan_Text ON
go
EXEC Test @.percentage = 100
GO
set showplan_Text OFF
go
**************************************

I get the following error
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp2'.
Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
Invalid object name '#Temp1'.

I do understand what the error message means. I just want to know a
better way of finding the query plan when using temp objects.
My real production procs are hundreds of lines with many temp tables
used in join with other temp tables and/or real tables.

RegardsOn 25 Oct 2006 15:41:52 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>This post is related to SQL server 2000 and SQL Server 2005 all
>editions.
>Many of my stored procedures create temporary tables in the code. I
>want to find a way to find the query plan for these procs

(snip)

Quote:

Originally Posted by

>I get the following error
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp2'.
>Server: Msg 208, Level 16, State 1, Procedure Test, Line 10
>Invalid object name '#Temp1'.

Hi comp_databases_ms-sqlserver,

You get these errors because SET SHOWPLAN_TEXT ON tells SQL Server to
generate a plan INSTEAD OF executing the SQL. As a result, your temp
tables are not generated.

The only way to get execution plans is to allow SQL Server to execute
the statements as well as outputting the plan. You do this by issuing
the command
SET STATISTICS PROFILE ON;
Note that this includes other (run-time) info as well as the plan.

Of course, you can also decide to use CREATE TABLE for all temp tables
at the start of your procs instead of using INSERT INTO.

--
Hugo Kornelis, SQL Server MVP|||SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
troubleshoot performance problems of a proc with 2000+ lines of code
that is using 10+ temp tables?
I am not able to configure SQLDebugger from a client. I have to be on
the server to use it. This applies to SQL2000.|||On 26 Oct 2006 11:29:57 -0700, comp_databases_ms-sqlserver wrote:

Quote:

Originally Posted by

>SET STATICS PROFILE ON is a good solution. Thanks for that. How do you
>troubleshoot performance problems of a proc with 2000+ lines of code
>that is using 10+ temp tables?
>I am not able to configure SQLDebugger from a client. I have to be on
>the server to use it. This applies to SQL2000.

Hi comp_databases_ms-sqlserver,

That's a pretty broad question!

Some of the things I'd look into if I was assigned this task would be
(in random order):

* Try to combine some or even all steps of the procedure into one single
query. Procs like this are often the result of procedural thinking. New
SQL coders with a background in procedural languages often tend to think
in the steps required to get somewhere. They will then code a sequence
of steps, with temp tables to hold intermediate results. A truly
set-based and declarative solution gives the optimzer more freedom to
rearrange steps and reduces the amount of moving data around. This can
yield huge benefits in performance.

* Create temp tables at the start of the stored proc. This reduces the
number of recompiles (and at 2000+ lines, recompiling the proc will
probably take a noteable amount of time). It also gives you the
opportunity to declare indexes on the temp tables BEFORE data is put
into them - this will reduce the number of recompiles even further and
it may speed up execution. However, it can also sometimes be better to
postpone index creation until after the temp table is populated, even
though this means accepting a recompilation. Test various strategies to
find out.

* Copy the code from the stored procedure to Query Analyzer and run it
one step at a time, using BEGIN TRAN, ROLLBACK and COMMIT as needed to
be able to repeat each step multiple times. This gives you the option to
get an execution plan for each step, and also to try different versions
of the query and/or different indexes to see how they change the
execution speed of that particular part of the proc.

* Setup a profiler trace, run the stored proc, then use the output from
the profiler trace to identify which part(s) of the stored proc are
responsible for the largest portion of the execution time.

* Check if your procedure might be subject to parameter sniffing (google
for it if you've never heard of the term).

* If you really can't combine the steps, consider breaking the procedure
in several smaller parts. This reduces compilation time when
recompilations are needed and can be leveraged to solve parameter
sniffing problems.

There are probably more things you can do, but these are the ones I can
think of at the top of my head.

Good luck!

--
Hugo Kornelis, SQL Server MVP

how to find media name of the last successful backup via SMO

Would you show me how to use SMO to find the name of the media of the last
succesful backup via SMO?
I use maintenance plan to do backup. I need to find the name of the media
(backup file) that was created for the last backup.
thanks,
ktm
I did not get a response to my question. I am not sure if my question was
not clear enough, or SMO has no support for what I need. Anyway below is the
query in SQL 2005 that i think can provide the correct device name of the
last backup of a database. I need an easier way via SMO to find the device
name.
select top 1 database_name, backup_set_id, type, physical_device_name
'DeviceName'
,backup_start_date Start, backup_finish_date Finish
,is_damaged 'Damaged', has_incomplete_metadata 'Incomplete'
FROM msdb..backupmediafamily f, msdb..backupset s
where upper(database_name) = 'ADVENTUREWORKS'
and f.media_set_id = s.media_set_id
order by backup_finish_date desc
thanks,
ktm
"ktmd" wrote:

> Would you show me how to use SMO to find the name of the media of the last
> succesful backup via SMO?
> I use maintenance plan to do backup. I need to find the name of the media
> (backup file) that was created for the last backup.
> thanks,
> ktm
>