Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com
|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via droptable.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
>> Is there a way to set up an alert in SQL Server when a long running
>> query (say 30 seconds or greater) occurs in Profiler?
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Processing responsibilities - RS services vs. repository

Hi - Can someone please explain the split of processing that occurs (i.e.
what processing occurs where) in terms of between the Reporting Services
server itself vs. on the repository server (if different)? Specifically, we
see that there are many scheduled jobs in the SQL Agent on the repository
server, which potentially look related to scheduled/subscription jobs in RS,
but not sure. We'd like to understand this split of work from a workload
planning point of view.
thanksSomeone at MSFT will add more detail to this or correct me, but essentially
what I think happens is that when you schedule a job like a subscription, SQL
Server Agent adds an event to a table at the appointed times. [Look at the
job step details and you will see that they are all running a stored
procedure called AddEvent]. The table is constantly being polled by the
Report Server Windows Service. When there is an event that has been added,
Report Server then sees it and gets the job done, I think by making the
appropriate calls to the Report Server web service. The Report Server web
service and the Report Server Windows service are two separate things.
The Report Server obviously has the responsibility of performing the job,
and this might involve sending a query to the catalog to get the relevant
information. So really SQL Server Agent just adds an event to a table so that
the Windows service sees it.
Take a look also at the performance objects RS Web Service and RS Windows
Service in System Monitor or Performance Monitor.
Cheers
Charles Kangai, MCT, MCDBA
"ISGADMIN" wrote:
> Hi - Can someone please explain the split of processing that occurs (i.e.
> what processing occurs where) in terms of between the Reporting Services
> server itself vs. on the repository server (if different)? Specifically, we
> see that there are many scheduled jobs in the SQL Agent on the repository
> server, which potentially look related to scheduled/subscription jobs in RS,
> but not sure. We'd like to understand this split of work from a workload
> planning point of view.
> thanks|||Hi Charles thanks for the info. Do you know if the repository is also used
during other non-subscription operations like normal report rendering?
Clearly there must be an initial lookup in order to get report params,
security, etc. and anything else stored in the repository, but from a
capacity planning point of view I am interested to know how to plan for the
server holding the repository as a function of the load on my RS server
itself.
regards
"Charles Kangai" wrote:
> Someone at MSFT will add more detail to this or correct me, but essentially
> what I think happens is that when you schedule a job like a subscription, SQL
> Server Agent adds an event to a table at the appointed times. [Look at the
> job step details and you will see that they are all running a stored
> procedure called AddEvent]. The table is constantly being polled by the
> Report Server Windows Service. When there is an event that has been added,
> Report Server then sees it and gets the job done, I think by making the
> appropriate calls to the Report Server web service. The Report Server web
> service and the Report Server Windows service are two separate things.
> The Report Server obviously has the responsibility of performing the job,
> and this might involve sending a query to the catalog to get the relevant
> information. So really SQL Server Agent just adds an event to a table so that
> the Windows service sees it.
> Take a look also at the performance objects RS Web Service and RS Windows
> Service in System Monitor or Performance Monitor.
> Cheers
> Charles Kangai, MCT, MCDBA
> "ISGADMIN" wrote:
> > Hi - Can someone please explain the split of processing that occurs (i.e.
> > what processing occurs where) in terms of between the Reporting Services
> > server itself vs. on the repository server (if different)? Specifically, we
> > see that there are many scheduled jobs in the SQL Agent on the repository
> > server, which potentially look related to scheduled/subscription jobs in RS,
> > but not sure. We'd like to understand this split of work from a workload
> > planning point of view.
> >
> > thanks|||The ReportServer catalog is certainly queried during normal operations like
rendering, because that is where the report definitions are stored. Once you
deploy your reports or support files, they are stored in the Report Server
catalog. The Report Server catalog is Report Server database. Open your
ReportServer database using Enterprise Manager and take a look at some of the
tables. You will see your reports in the Catalog table. Whenever reports run
the reports also get temporarily cached in unrendered format in the databases
(there is another database used called ReportServerTempDB).
Check this information out in Books Online. It is all there.
cheers
Charles Kangai, MCT, MCDBA
"ISGADMIN" wrote:
> Hi Charles thanks for the info. Do you know if the repository is also used
> during other non-subscription operations like normal report rendering?
> Clearly there must be an initial lookup in order to get report params,
> security, etc. and anything else stored in the repository, but from a
> capacity planning point of view I am interested to know how to plan for the
> server holding the repository as a function of the load on my RS server
> itself.
> regards
> "Charles Kangai" wrote:
> > Someone at MSFT will add more detail to this or correct me, but essentially
> > what I think happens is that when you schedule a job like a subscription, SQL
> > Server Agent adds an event to a table at the appointed times. [Look at the
> > job step details and you will see that they are all running a stored
> > procedure called AddEvent]. The table is constantly being polled by the
> > Report Server Windows Service. When there is an event that has been added,
> > Report Server then sees it and gets the job done, I think by making the
> > appropriate calls to the Report Server web service. The Report Server web
> > service and the Report Server Windows service are two separate things.
> >
> > The Report Server obviously has the responsibility of performing the job,
> > and this might involve sending a query to the catalog to get the relevant
> > information. So really SQL Server Agent just adds an event to a table so that
> > the Windows service sees it.
> >
> > Take a look also at the performance objects RS Web Service and RS Windows
> > Service in System Monitor or Performance Monitor.
> >
> > Cheers
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "ISGADMIN" wrote:
> >
> > > Hi - Can someone please explain the split of processing that occurs (i.e.
> > > what processing occurs where) in terms of between the Reporting Services
> > > server itself vs. on the repository server (if different)? Specifically, we
> > > see that there are many scheduled jobs in the SQL Agent on the repository
> > > server, which potentially look related to scheduled/subscription jobs in RS,
> > > but not sure. We'd like to understand this split of work from a workload
> > > planning point of view.
> > >
> > > thanks