SQL Profiler to monitor which SPs are run

16 Feb 2007 5:30 PM

Adrian

SQL Server 2000 Standard Edition

I would like to monitor which stored procedures are being used so I can
delete (archive) ones that are no longer being used. I was thinking of
running SQL Profiler and having it monitor just the Event Class SP:
Completed - putting the results in a table. This seems to give me the
information I require.

However I will need to run it for at least a month to ensure any stored
procedures that are rarely uses are captured. Is this likely to have any
significant effect on SQL Server, or is there a better way of doing it.

Thanks for any help.

Adrian

19 Feb 2007 12:01 PM

Qi Ding [MSFT]

Hello Adrian,

Thank you for using newsgroup!

I am afraid your issue seems a bit complex and in order to give you a
detailed explanation, we need to do more research. I believe that you will
get our reply soon.

Regards,

QiDing

Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

62.31.35.21
Show quoteHide quote

| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:7983
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| SQL Server 2000 Standard Edition
|
| I would like to monitor which stored procedures are being used so I can
| delete (archive) ones that are no longer being used. I was thinking of
| running SQL Profiler and having it monitor just the Event Class SP:
| Completed - putting the results in a table. This seems to give me the
| information I require.
|
| However I will need to run it for at least a month to ensure any stored
| procedures that are rarely uses are captured. Is this likely to have any
| significant effect on SQL Server, or is there a better way of doing it.
|
| Thanks for any help.
|
| Adrian
|
|
|

2 Mar 2007 2:07 AM

Qi Ding [MSFT]

Hi Adrian

Thank you for using newsgroup!

You can select profiler as it shall be used under the situation. Since only
one event is selected, the performance effect will not be so heavy.
However, check applications on the server is better option.

Regards,

QiDing

Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

5 Mar 2007 3:18 AM

Qi Ding [MSFT]

Hello Adrian,

I am just writing to see how everything is going. If you have any updates
or need any further assistance on this issue, please feel free to let me
know. I am glad to be of assistance.

Have a nice day!

Regards,

QiDing

Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

8 Mar 2007 6:19 AM

Qi Ding [MSFT]

Hello Adrian,

As you mentioned in your article, it is a good choice to use SQL Profiler
to capture SQL Server event from server. Since only one event is selected
in SQL Profiler, it will not affect on SQL Server markedly. SQL Server
profiler has visible interface that can let you get SQL Server event
easily. Please note that SQL Server profiler can not be performed under the
situation that hard drive space less than 10MB.

Let¡¯s try to use Transact-SQL language to view which active transaction
maintains the longest.
DBCC OPENTRAN
GO
Then we can get the active transaction information of current database.
This T-SQL language can set to be performed everyday so we know which
stored procedure is being used.

However, if the amount is not too huge, check applications on the server is
a better option.

Regards,

QiDing

Microsoft Online Support
Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Thread options