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 which stored procs and UDFs reference a column

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

Which version of sql server are you using ?|||SQL Server 2000.|||You can try the sql server specific system table "sysdepends". Look it up in Books Online. I am not aware of an INFORMATION SCHEMA view that will help you in determining such references.|||

The sysdepends table seems helpful, but it only gives the table, not the columns. Here's the query I used:

SELECT sp.name as StoredProc, dep.name AS DependentObject
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id AND sp.xtype = 'P'
INNER JOIN sysobjects dep ON dep.id = sd.depid

Any further clues to linking the columns? (I suppose I could query for occurrences of the column name within the stored procedures depending on the column's table, but that's an approximation, since column names in the procedure text could be from a different table.)

|||

The depnumber column in sysdepends should give you the column id of the table that the procedure/function references. You can modify your query above to also join the depnumber column in sysdepends with the id column in syscolumns to get the name of the column.

Let me know if that works for you

|||

Seems I'm close, but the following query doesn't always yield complete results:

SELECT sp.name as StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

I'm trying to verify it as follows:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

Thank you for your consideration.

|||

If you are using dynamic sql to create the procedures/functions then sql server may not be able to track the references. Also, if you are using deferred name resolution [i.e create the procedure first and then create the table that is being referenced by the procedure] then sql server will not be able to track the references.

By any chance, is this the case?

|||

There are many cases under which the dependency tracking in SQL Server will not work. Here are the common cases:

1. Creation of dependent stored procedures in out-of-order fashion for example

2. Use of temporary tables or table variables in SELECT/DML statements will defer compilation of the statement so there will be no dependency information saved

3. Use of dynamic SQL

4. In case of permanent tables, if the object doesn't exist then deferred name resolution / compilation will kick-in at run-time and there will be no dependency information for this case also

Please take a look at the blog entry below for some queries on how to do this in SQL Server 2005 assuming that the dependency information is present.

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

So given the various restrictions it is probably unlikely that you have dependency information for most objects other than references, schema bound objects etc. You will have to mostly maintain this information manually or use your source code control system to scan your scripts assuming you use some keywords mechanism to tag scripts for example.

|||

I take it back -- after carefully comparing results, it looks like it's working great. Thanks so much for all your help!!

SELECT sp.name AS StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

Confirming/cross-checking query:

SELECT obj.[name], cmt.[text]
FROM syscomments cmt
INNER JOIN sysobjects obj ON obj.id = cmt.id
WHERE text like '%MyTable%'
AND [text] LIKE '%MyColumn%'

Reverse query:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

|||I've discovered that the sysdepends table is not reliable/complete unless the stored procedure is created (or recreated) after the table(s) that it references. [Or at least, I can say that querying for columns referenced by a proc may not show up any, but then if the proc is dropped and recreated, the same query can now show its column dependencies.]|||

As Umachandar pointed out, there are instances where sysdepends will not work correctly.

|||Recreating SPs or altering SPs are costly operations since they will block access to the SP metadata. So you have to probably schedule this during a maintenance window. And if you don't have a mechanism to know which ones to alter then you will have to do this for all the SPs and this can take considerable time depending on the number of SPs/UDFs in the database. So there are several issues if you rely completely on the server dependency information.

How to find the table used in Stored Procedure By Query

Hi,

I need to a find table which is used by list of stored procedures.

Can you please send me the query which is used?

Thanks and Regards

Abdul M.G

There is no built-in function in SQL Server that can do this. You need to search sysobjects table for matching strings.

Here's a piece of code I found that will list all stored procedures that reference a certain table.

SELECT DISTINCT so.name FROMsyscomments scINNERJOINsysobjects soon sc.id=so.idWHERE sc.textLIKE'%tablename%'

[Original source here]

|||

A much simpler way is to get the output of the sp_depends system stored procedure. This will give you any tables, views, stored procedures, user-defined functions or triggers used by the given stored procedure. Usage is sp_depends 'YourSPName'. Required tables will have a value of 'user table' in the type field in the output.

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 owner of objects

I need to find the owner of jobs and stored procedures that have been
created and change all of them to be owned by sa. I have recently been
getting a randomly occuring error in some job execution due to SQL not being
able to verify if an object owner really has access.
This the notification message I receive:
STATUS: Failed
MESSAGES: The job failed. Unable to determine if the owner (NCN\dbohannon)
of job Restore DukeEDI_TLog has server access (reason: Could not obtain
information about Windows NT group/user 'NCN\dbohannon'. [SQLSTATE 42000]
(Error 8198)).
I don't understand what would cause this since my account is a sysadmin and
it is the same security that these jobs have always run under.
Thanks in advance,
DeborahThe problem you're running into is not actually a SQL issue rather a error
returning information from the DC.
See the following kb article;
241643 PRB: 8198 Error Message Returned from Job Owned by Windows NT
http://support.microsoft.com/?id=241643
Changing the job ownership to a standard SQL account forces a change in
code path, which doesn't require a query to the DC.
Each job can be changed to make the job owner someone other than the
domain\user in Enterprise Manager.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Can you please tell me why this error would be intermittent? The scheduled
job has run successfully for weeks, and then I get this error. I then ran
the job again and it ran with no problem. I'm thinking if it were truly an
account problem then it would be so every time it is run.
Plus, the server it is running on was never anything besides Windows 2000
Server.
Thanks
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:vvkveL72DHA.2588@.cpmsftngxa08.phx.gbl...

quote:

---

> The problem you're running into is not actually a SQL issue rather a error
> returning information from the DC.
> See the following kb article;
> 241643 PRB: 8198 Error Message Returned from Job Owned by Windows NT
> http://support.microsoft.com/?id=241643
> Changing the job ownership to a standard SQL account forces a change in
> code path, which doesn't require a query to the DC.
> Each job can be changed to make the job owner someone other than the
> domain\user in Enterprise Manager.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||What I have seen in the past is that the calls we make to the Domain
Controller to enumerate the groups may fail intermittantly. So, it is not
the OS that SQL Server is running on, rather the communication between SQL
and the DC. So, if the DC is having problems or is busy, the query may
start to fail.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to find out parameter sniffing problem in SQL server??

Hi,
The database on which I am working, have 220 stored procedures.
I have to find out the procedures those have parameter sniffing problems. How I can find out?
Have any methode to find out this parameter snifffing problem.Look for procedures that use optional parameters, or use parameters to filter columns that have uneven data distribution.
Or, just look for procedures that have inconsistent execution times.

How to find inactive objects..

I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?

SQL 2005 Enterprise Edition SP 2

Thanks in advance.

Kay

You could start by running sp_depends for each table in the database. Unfortunately sp_depends is not always 100% accurate, and it will not pick up things like inline SQL being called from applications, but it is a start.

The DMV query below can also help find unused tables.

-- Unused tables & indexes. Tables have index_id’s of either 0 = Heap table or 1 = Clustered Index

DECLARE @.dbid int

SELECT @.dbid =db_id()

SELECT objectname=object_name(i.object_id), indexname=i.name, i.index_id

FROMsys.indexes i,sys.objectsAS o

WHEREobjectproperty(o.object_id,'IsUserTable')= 1

AND i.index_id NOTIN(SELECT s.index_id

FROMsys.dm_db_index_usage_statsAS s

WHERE s.object_id=i.object_id

AND i.index_id=s.index_id

AND database_id = @.dbid )

AND o.object_id= i.object_id

ORDERBY objectname,i.index_id,indexname ASC

How to find inactive objects

I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?

SQL 2005 Enterprise Edition SP 2

Thanks in advance.

Kay

There is a dynamic management view that will give you what you want since the last restart:

select object_name(i.object_id) as object_name
, case when i.is_unique = 1 then 'UNIQUE ' else '' end + i.type_desc as index_type
, i.object_id
, i.name as index_name
, i.index_id
, coalesce(user_seeks,0) as user_seeks
, coalesce(user_scans,0) as user_scans
, coalesce(user_lookups,0) as user_lookups
, coalesce(user_updates,0) as user_updates
from sys.indexes i
left outer join sys.dm_db_index_usage_stats s
on i.object_id = s.object_id
and i.index_id = s.index_id
and database_id = db_id()
where objectproperty(i.object_id , 'IsUserTable') = 1
and i.index_id in (1,0) --clustered index or heap
order by user_seeks + user_scans + user_lookups + user_updates asc

It tells how many times the index has been accessed in queries, changed, etc.

How to find cause of time-outs?

I am getting some .NET application exceptions saying the request to SQL
Server 2005 timed out. It is not always the same procedures that time out.
It is also very intermittent. How can I find out which SQL tasks are
running slow or to blame? I don't know where to look, what to log, etc.
.NET can't tell me more from its end.You could start by taking a look at this article on how to monitor
blocking.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509.

How to find cause of time-outs?

I am getting some .NET application exceptions saying the request to SQL
Server 2005 timed out. It is not always the same procedures that time out.
It is also very intermittent. How can I find out which SQL tasks are
running slow or to blame? I don't know where to look, what to log, etc.
.NET can't tell me more from its end.You could start by taking a look at this article on how to monitor
blocking.
http://support.microsoft.com/defaul...b;en-us;271509.

How to find an inserted value in a table

Hello,

I have 2 tables, and use objectdatasource and stored procedures, with sql server.

Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table.

How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one.

Thanks for your help.

In your stored proc use @.@.IDENTITY to get the ID for the newly inserted row.

SET NOCOUNT ONINSERT INTO...SELECT @.@.IDENTITY as IDCustomer

Now in your code execute the proc as if it were a SELECT one and you'll get a single-row result set back. Or if using the SqlCommand object use ExecuteScalar.

|||

Perfect!

Thanks for the help.

|||Please use SCOPE_IDENTITY() rather than @.@.IDENTITY to retrieve the ID of the last inserted row. Check out books on line to understand why.