Profiler always doing something

New to SQL Server.
How can I determine how much the Profiler is dragging down a server? I am
running a trace from my pc against a database server but I wish there was a
before and after report that would show how much the Profiler drags down a
server. I am also stroring the trace here on my pc.
I generally prefer to use a server side trace rather than using the Profiler
GUI for anything but short term troubleshooting to minimise the impact, also
be very specific about what events you are monitoring. In terms of imapct,
you would need to use perfmon to measure it but would obviously need to
capture stats before starting the trace as a baseline to compare to when
Profiler is running. For tips on using Profiler/Trace have a look at
http://www.sql-server-performance.co...filer_tips.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:B1302B2C-0292-462F-96AF-E2D3D1CFD49F@.microsoft.com...
> New to SQL Server.
> How can I determine how much the Profiler is dragging down a server? I am
> running a trace from my pc against a database server but I wish there was
> a
> before and after report that would show how much the Profiler drags down a
> server. I am also stroring the trace here on my pc.
|||Hello Jasper,
I got that server side down! I am using SP to capture my stats. I guess
you are right, use Perfmon first to get a baseline then run the SP to see how
hard to drives the server.
"Jasper Smith" wrote:

> I generally prefer to use a server side trace rather than using the Profiler
> GUI for anything but short term troubleshooting to minimise the impact, also
> be very specific about what events you are monitoring. In terms of imapct,
> you would need to use perfmon to measure it but would obviously need to
> capture stats before starting the trace as a baseline to compare to when
> Profiler is running. For tips on using Profiler/Trace have a look at
> http://www.sql-server-performance.co...filer_tips.asp
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:B1302B2C-0292-462F-96AF-E2D3D1CFD49F@.microsoft.com...
>
>
|||Just to add to this:
The overhead that Trace imposes on a server depends very much on what events
and columns you selected for tracing and how often those events occur. So it
is difficult to give any single number, the best way is to measure yourself.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:A88E6302-3DDE-4C16-B247-2CE9E377A550@.microsoft.com...[vbcol=seagreen]
> Hello Jasper,
> I got that server side down! I am using SP to capture my stats. I guess
> you are right, use Perfmon first to get a baseline then run the SP to see
> how
> hard to drives the server.
> "Jasper Smith" wrote:
|||Thanks Maciek,
I have the hang of making the trace into a stored procedure and letting the
SQL Agent call it as a job. I just do not want to "give" the SQL Agent the
permission to start a trace and then the trace slows the database server way
down. This could happen if the volume goes up on the server. I may need to
have a Alert set to Knock off the job if it becomes too busy...
"Maciek Sarnowicz [MSFT]" wrote:

> Just to add to this:
> The overhead that Trace imposes on a server depends very much on what events
> and columns you selected for tracing and how often those events occur. So it
> is difficult to give any single number, the best way is to measure yourself.
> Regards,
> Maciek Sarnowicz
> --
> SQL Server Tools SDE
> This posting is provided "AS IS" with no warranties, and confers no rights
>
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:A88E6302-3DDE-4C16-B247-2CE9E377A550@.microsoft.com...
>
>
|||Rich wrote:
> Hello Jasper,
> I got that server side down! I am using SP to capture my stats. I
> guess you are right, use Perfmon first to get a baseline then run the
> SP to see how hard to drives the server.
>
Profiler has a lot of overhead compared to using a server-side trace.
Server-side traces are quite efficient and generally pose little impact
on servers. And just so you know, running a server-side trace from the
Profiler client actually creates and runs two traces on the server: a
server-side and a normal Profiler trace.
David Gugick
Imceda Software
www.imceda.com

Procedure, know thyself...

Anybody got a code snippet that will allow a stored procedure to determine its own name at execution?It gets real ugly real fast. I think only admins can run this code with no error. Any problems with just teaching each sproc its own name by hard-coding? That's how I learned my name, anyway ;-)

create procedure testproc
as
create table #temp
(col1 nvarchar(20),
col2 int,
col3 varchar(255))

insert into #temp
exec ('dbcc inputbuffer(@.@.spid)')

select col3 from #temp
drop table #temp
go

exec testproc|||Pat pointed me to this: SELECT OBJECT_NAME(@.@.PROCID)

Works well:CREATE PROCEDURE testprocedure AS
SELECT OBJECT_NAME(@.@.PROCID) AS 'ProcName'
GO
EXEC testprocedure
GO
drop procedure testprocedure|||I gotta ask...why do you need to do this?

I do sproc loggin in some systems, with a nested logging call, and still don't see the need for this|||For event logging.
One less hard-code value.|||CREATE PROC <procname>

well I guess it can be a resusable piece of code...make it part of tql template I guess|||This code goes at the top of the procedures I want to monitor:
--Log Event------------------------------
declare @.EventText varchar(500)
declare @.ProcedureName varchar(50)

set @.ProcedureName = OBJECT_NAME(@.@.PROCID)
set @.EventText = @.ProcedureName + ' '
+ dbo.LogParam('@.Param1', @.Param1)
+ dbo.LogParam('@.Param2', @.Param2)
...
+ dbo.LogParam('@.ParamN' ,@.ParamN)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LogEvent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
exec LogEvent @.ProcedureName, @.EventText
-----------------------------------

If I get a chance, I'm going to look for a way to generate the parameter names and values automatically as well, though that would involve dynamic code.
This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.|||SELECT name FROM dbo.syscolumns WHERE (@.@.procid) = id-PatP|||Oh yeah, get chummy with QuoteName() too.

-PatP|||This logs a record that contains a complete execute statement to recreate in query analyzer whatever sproc and parameters are submitted by a user.

That has got to be a bad idea...if it's a low OLTP, then maybe...but why do you care

I mostly care about data changes with history...next would be performance|||This is for debugging. So when the developer comes to me and says "your stored procedure returns the wrong data", I can say "Your code submitted the wrong parameters".|||How about DECLARE @.func VARCHAR(50)
SELECT @.func = Object_name(@.@.Procid)as in ErrorHandler:
DECLARE @.func VARCHAR(50)
SELECT @.func = Object_name(@.@.Procid)
EXECUTE [dbo].[ErrorHandler] @.m_error, 'MyDBIndex sp', @.func
RAISERROR ('Stored Procedure %s failed with error number %d', 16, 1, @.func, @.m_error)|||Dang, just when I thought smugly "I know this one!", I get scooped. damn. Back to the mines I go...|||I'm too lazy to modify code... SQL Profiler will give me all the evidence I need to abuse the lazy, without requiring anything more on my part than simply turning SQL Profiler on and watching the fun.

-PatP|||I like my code because I am lazy.
I don't have to have profiler running constantly to catch the executed sprocs, and I can copy and paste the code directly from my eventlog table into query analyzer to reproduce the issue.|||I'm too lazy to like my code, which I am too lazy to write just to catch lazy developers.