Analyzing Stored Procedures - does a tool exist?

My boss wants me to go through the many stored procedures that are on our many SQL servers and generate a list of how often they are used, and then use that info to retire any stored procs that are not being used.
I did a search for tools and came across the SPUD tool at http://www.hybridx.com but the only problem with that tool is that i need to modify each stored procedure before it will start logging information on the stored proc.
Does anyone know of or use any tools that generate this sort of information in a non-invasive way? Any help would be greatly appreciated. Thanks.
There really isn't any tool that will do this sort of thing for you other
than running a constant profiler trace with sp_completed and see which ones
are called. Of coarse just because it isn't called for a day or so does not
mean it isn't used but it will give you a start and then you can narrow down
the list.
Andrew J. Kelly SQL MVP
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
> My boss wants me to go through the many stored procedures that are on our
many SQL servers and generate a list of how often they are used, and then
use that info to retire any stored procs that are not being used.
> I did a search for tools and came across the SPUD tool at
http://www.hybridx.com but the only problem with that tool is that i need to
modify each stored procedure before it will start logging information on the
stored proc.
> Does anyone know of or use any tools that generate this sort of
information in a non-invasive way? Any help would be greatly appreciated.
Thanks.
|||<<
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the[vbcol=seagreen]
> stored proc.
That sounds like a horrible idea to me. That creates too many opportunites
for thier code to cause problems with your code.
Andrew's suggestion of running Profiler is probably the best thing to do.
Brian
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGTvDp$cEHA.1764@.TK2MSFTNGP10.phx.gbl...
> There really isn't any tool that will do this sort of thing for you other
> than running a constant profiler trace with sp_completed and see which
ones
> are called. Of coarse just because it isn't called for a day or so does
not
> mean it isn't used but it will give you a start and then you can narrow
down[vbcol=seagreen]
> the list.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:6EBFC2B2-6FCC-4826-BCBA-9FFA351F2EF7@.microsoft.com...
our
> many SQL servers and generate a list of how often they are used, and then
> use that info to retire any stored procs that are not being used.
> http://www.hybridx.com but the only problem with that tool is that i need
to
> modify each stored procedure before it will start logging information on
the
> stored proc.
> information in a non-invasive way? Any help would be greatly appreciated.
> Thanks.
>

Analysis Services, msmdpump.dll and IIS.

Hello:

I have two servers, one hosting Analysis Services and the other just serves as webserver.

I have configured msmdpump.dll on the Analysis Services server succesfully using basic authentication. But now I need the webserver to provide Analysis Services data. I thought that I would be sufficient to repeat on the webserver the same configuration steps that I folowed on the Analysis Services server and change the <ServerName> tag in the msmdpump.ini file to point to the last one.
Well, this isn't working.

What am I missing? Is there any tutorial or resource to accomplished?

I guess the problem you are having has to do with configuring permissions.

First make sure you can connect from your web server to Analysis Server using some client application. Then try and run your client application with the credentials of the user configured for your vitutual directory in IIS. Make sure you can connect in this case.

Some help with troubleshooting connectivity problems is found here: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Some files inetinfo.exeRandomNumber.mdbp or .hdmp (c:\windows\pchealth\errorrep\userdumps) and RadomNumber.cab or.txt (c:\windows\pchealth\errorrep\qsignoff) are eating away my Disk Space and I do not know how to stop it. Please help me. TIA. Gilda

PS: I have ISS 6.0 installed in a Windows Server 2003 in which is installled the exchange 2003.

Analysis Services, msmdpump.dll and IIS.

Hello:

I have two servers, one hosting Analysis Services and the other just serves as webserver.

I have configured msmdpump.dll on the Analysis Services server succesfully using basic authentication. But now I need the webserver to provide Analysis Services data. I thought that I would be sufficient to repeat on the webserver the same configuration steps that I folowed on the Analysis Services server and change the <ServerName> tag in the msmdpump.ini file to point to the last one.
Well, this isn't working.

What am I missing? Is there any tutorial or resource to accomplished?

I guess the problem you are having has to do with configuring permissions.

First make sure you can connect from your web server to Analysis Server using some client application. Then try and run your client application with the credentials of the user configured for your vitutual directory in IIS. Make sure you can connect in this case.

Some help with troubleshooting connectivity problems is found here: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Some files inetinfo.exeRandomNumber.mdbp or .hdmp (c:\windows\pchealth\errorrep\userdumps) and RadomNumber.cab or.txt (c:\windows\pchealth\errorrep\qsignoff) are eating away my Disk Space and I do not know how to stop it. Please help me. TIA. Gilda

PS: I have ISS 6.0 installed in a Windows Server 2003 in which is installled the exchange 2003.

Analysis Services Processing Freezes or Hangs

Currently I have multiple Analysis Services databases on multiple servers. On occassion, when processing these databases it will freeze or hang. Sometime the problem is the database structure, sometimes it is the back end source tables being locked, etc. The only way I can terminate the processing is to kill the dev environment or management studio. This causes problems because the next time the database is opened, it will not process or save. The only solution I found is restarting Analysis Services. Is there some way to force stop processing without damaging the database and leaving connections open?

It might be possible to open a second instance of SSMS and use XMLA to discover the sessions or connections and then cancel the one that it stuck. But sometimes the server can take a while to respond to these commands if it is "stuck".

You can also use the xmlaDiscover class from the Analysis Services Stored Procedure project http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover&referringTitle=Home which lets you manage this a bit simpler by running a command like:

CALL ASSP.DiscoverConnections()

which displays the current connections in a grid, and then:

CALL ASSP.CancelConnection(<ConnectionID>)

You should probably be aware that there is an issue with the Cancel command in SP2 that I read about in a KB article and sometimes it does not function correctly. Apparently there is a hotfix available if this issue affects you.

Analysis Services NLB

Hi everyone, I'm setting up two Analysis Services servers in NLB
following the directions in the link
http://www.microsoft.com/technet/pro.../olapsite.mspx
After some tries, both servers are working fine alone (trough each
server name), but not trough the NLB. Any Ideas?, Anyone has any other
step-by-step guide to setup a NLB configuration for Analysis Services?
Thanks in advance.
Miguel.
How are you testing that the servers are working?
Are you using Analysis Manager or a client like MDX Sample? It is not
recommended that you use Analysis Manager in and NLB environment (it
will only work against the NLB IP address)
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1139507155.262121.310690@.f14g2000cwb.googlegroups .com>,
mi.balle@.gmail.com says...
> Hi everyone, I'm setting up two Analysis Services servers in NLB
> following the directions in the link
> http://www.microsoft.com/technet/pro.../olapsite.mspx
> After some tries, both servers are working fine alone (trough each
> server name), but not trough the NLB. Any Ideas?, Anyone has any other
> step-by-step guide to setup a NLB configuration for Analysis Services?
> Thanks in advance.
> Miguel.
>

Analysis Services NLB

Hi everyone, I'm setting up two Analysis Services servers in NLB
following the directions in the link
http://www.microsoft.com/technet/pr...n/olapsite.mspx
After some tries, both servers are working fine alone (trough each
server name), but not trough the NLB. Any Ideas?, Anyone has any other
step-by-step guide to setup a NLB configuration for Analysis Services?
Thanks in advance.
Miguel.How are you testing that the servers are working?
Are you using Analysis Manager or a client like MDX Sample? It is not
recommended that you use Analysis Manager in and NLB environment (it
will only work against the NLB IP address)
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1139507155.262121.310690@.f14g2000cwb.googlegroups.com>,
mi.balle@.gmail.com says...
> Hi everyone, I'm setting up two Analysis Services servers in NLB
> following the directions in the link
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/olapsite.mspx[/ur
l]
> After some tries, both servers are working fine alone (trough each
> server name), but not trough the NLB. Any Ideas?, Anyone has any other
> step-by-step guide to setup a NLB configuration for Analysis Services?
> Thanks in advance.
> Miguel.
>