Procedure Time out: oracle

Showing posts with label oracle. Show all posts

Friday, March 30, 2012

Profiler and SQL Express

I'm just wondering if I install SQL Express + Express Manger will this
include SQL Server profiler ?
If not is there any way to get SQL Server profiler without purchase a
complete version of SQL Server 2005 Standared or Entreprise editions ?
Thank you"SM" <sauvemark@.yahoo.com> wrote in message
news:%23fGkDyH8FHA.956@.TK2MSFTNGP10.phx.gbl...
> I'm just wondering if I install SQL Express + Express Manger will this
> include SQL Server profiler ?
> If not is there any way to get SQL Server profiler without purchase a
> complete version of SQL Server 2005 Standared or Entreprise editions ?
>
For development and testing purposes you can use SQL Server Developer
Edition which has the full compliment of tools.
David|||SQL Express Edition does not ship with SQL Profiler.
In order to use Profiler you need to have a license of SQL Server 2005.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"SM" <sauvemark@.yahoo.com> wrote in message
news:%23fGkDyH8FHA.956@.TK2MSFTNGP10.phx.gbl...
> I'm just wondering if I install SQL Express + Express Manger will this
> include SQL Server profiler ?
> If not is there any way to get SQL Server profiler without purchase a
> complete version of SQL Server 2005 Standared or Entreprise editions ?
> Thank you
>

profiler and showplan

I turn on profiler Performance/Execution Plan, and expect to see the
plan in the textdata field - but it's not there.
Is there some event class or other field or something that must also
be included that I've missed?
Thanks.
JoshWhich SQL profiler template have you selected? One of the template (standard)
displays TextData field.
Thanks
GYK
"JXStern" wrote:
> I turn on profiler Performance/Execution Plan, and expect to see the
> plan in the textdata field - but it's not there.
> Is there some event class or other field or something that must also
> be included that I've missed?
> Thanks.
> Josh
>|||On Wed, 27 Oct 2004 16:03:02 -0700, GYK
<GYK@.discussions.microsoft.com> wrote:
>Which SQL profiler template have you selected? One of the template (standard)
>displays TextData field.
I am capturing/displaying the textdata field, but it's empty on the
showplan lines, and in the statement lines I still see just the
statements. I used the SQLProfilerStandard template, and one of my
own, didn't seem to matter. I added all the TSQL events, that didn't
help either.
J.
>Thanks
>GYK
>"JXStern" wrote:
>> I turn on profiler Performance/Execution Plan, and expect to see the
>> plan in the textdata field - but it's not there.
>> Is there some event class or other field or something that must also
>> be included that I've missed?
>> Thanks.
>> Josh
>>|||You have to capture the Binary Data to see the Showplan output.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:9ta0o0d664jpr8ihme1kb2kngna64f721q@.4ax.com...
> On Wed, 27 Oct 2004 16:03:02 -0700, GYK
> <GYK@.discussions.microsoft.com> wrote:
>>Which SQL profiler template have you selected? One of the template
>>(standard)
>>displays TextData field.
> I am capturing/displaying the textdata field, but it's empty on the
> showplan lines, and in the statement lines I still see just the
> statements. I used the SQLProfilerStandard template, and one of my
> own, didn't seem to matter. I added all the TSQL events, that didn't
> help either.
> J.
>
>>Thanks
>>GYK
>>"JXStern" wrote:
>> I turn on profiler Performance/Execution Plan, and expect to see the
>> plan in the textdata field - but it's not there.
>> Is there some event class or other field or something that must also
>> be included that I've missed?
>> Thanks.
>> Josh
>>
>|||On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>You have to capture the Binary Data to see the Showplan output.
Wait a minute. I changed the template to capture BinaryData field,
and sure enough, now I got the Showplan tree - in the TextData field!
So I removed the BinaryData field, and now I *still* get the Showplan
tree in the TextData field. Even after I exit and restart Profiler!
Is this some weird bug in SQL2K, that you have to first ask for the
BinaryData, then you can stop asking for it, to get the Showplan tree?
Weird.
But it worked, so thanks!
Josh|||That IS weird. When I remove the binary data column I don't get the showplan
anymore.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ebf0o0pldmaluufjgic4lstkb6ovectqob@.4ax.com...
> On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>You have to capture the Binary Data to see the Showplan output.
> Wait a minute. I changed the template to capture BinaryData field,
> and sure enough, now I got the Showplan tree - in the TextData field!
> So I removed the BinaryData field, and now I *still* get the Showplan
> tree in the TextData field. Even after I exit and restart Profiler!
> Is this some weird bug in SQL2K, that you have to first ask for the
> BinaryData, then you can stop asking for it, to get the Showplan tree?
> Weird.
> But it worked, so thanks!
> Josh
>|||What event are you capturing... showplan or execution plan?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ebf0o0pldmaluufjgic4lstkb6ovectqob@.4ax.com...
> On Wed, 27 Oct 2004 16:27:33 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>You have to capture the Binary Data to see the Showplan output.
> Wait a minute. I changed the template to capture BinaryData field,
> and sure enough, now I got the Showplan tree - in the TextData field!
> So I removed the BinaryData field, and now I *still* get the Showplan
> tree in the TextData field. Even after I exit and restart Profiler!
> Is this some weird bug in SQL2K, that you have to first ask for the
> BinaryData, then you can stop asking for it, to get the Showplan tree?
> Weird.
> But it worked, so thanks!
> Josh
>|||On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>What event are you capturing... showplan or execution plan?
Execution plan.
J.|||On Wed, 27 Oct 2004 17:48:58 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>That IS weird. When I remove the binary data column I don't get the showplan
>anymore.
But when you get it, is it in the binary or text field?|||JXStern wrote:
> On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>> What event are you capturing... showplan or execution plan?
> Execution plan.
> J.
Execution Plan only requires TextData. Show Plan, if I'm not mistaken,
requires BinaryData.
--
David Gugick
Imceda Software
www.imceda.com|||I just found this... not that it gives a whole lot more info, but at least
it's documented:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OCy3cfKvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> JXStern wrote:
>> On Wed, 27 Oct 2004 17:58:04 -0700, "Kalen Delaney"
>> <replies@.public_newsgroups.com> wrote:
>> What event are you capturing... showplan or execution plan?
>> Execution plan.
>> J.
> Execution Plan only requires TextData. Show Plan, if I'm not mistaken,
> requires BinaryData.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||On Thu, 28 Oct 2004 22:46:23 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>I just found this... not that it gives a whole lot more info, but at least
>it's documented:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
Kinky (eg, a bug) but it doesn't explain the even weirder case I saw.
J.|||But we just have your word for it, right.
Have you been able to duplicate it?
;-)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:vjs7o0lvo1o2799j6033qlc1ibk6ab9uo9@.4ax.com...
> On Thu, 28 Oct 2004 22:46:23 -0700, "Kalen Delaney"
> <replies@.public_newsgroups.com> wrote:
>>I just found this... not that it gives a whole lot more info, but at least
>>it's documented:
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;272439
> Kinky (eg, a bug) but it doesn't explain the even weirder case I saw.
> J.
>|||On Sat, 30 Oct 2004 15:19:57 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>But we just have your word for it, right.
>Have you been able to duplicate it?
>;-)
Now that you ask, no.
I just tried it, and on a clean startup, this time I *did* get the
execution plan - *without* also getting the binary!
Looks random. Isn't that special?
:)
J.

Profiler and set fmtonly On issue

Hi,
why do I have a lot of
set fmtonly On, the sql phrase and then set fmtonly Off
example :
SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
'Q2477A#401'
SET FMTONLY OFF
I know that turning it on means retuns columns info with no data back. So it
is called when c alling A SP that has a simple T-SQL phrase as the example
in my trace in profilee and how can I disable so it will not be included in
the trace results
set fmtonly off%
Most probably your API (probably ADO) that wants to retrieve meta-data for the query before the
actual data is returned. I.e., a matter of how you are using ADO. I suggest you post to an ADO
group, including a small comment or slimmed-down-code-sample showing your current coding technique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:FCE31D96-46D0-4659-B4C0-DF95EEA53EE2@.microsoft.com...
> Hi,
> why do I have a lot of
> set fmtonly On, the sql phrase and then set fmtonly Off
> example :
> SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
> 'Q2477A#401'
> SET FMTONLY OFF
> I know that turning it on means retuns columns info with no data back. So it
> is called when c alling A SP that has a simple T-SQL phrase as the example
> in my trace in profilee and how can I disable so it will not be included in
> the trace results
> set fmtonly off%
>
|||SalamElias wrote:
> Hi,
> why do I have a lot of
> set fmtonly On, the sql phrase and then set fmtonly Off
> example :
> SET FMTONLY ON Select PROD_VAT_CODE From Product Where PROD_MANF_SKU =
> 'Q2477A#401'
> SET FMTONLY OFF
> I know that turning it on means retuns columns info with no data
> back. So it is called when c alling A SP that has a simple T-SQL
> phrase as the example
> in my trace in profilee and how can I disable so it will not be
> included in the trace results
> set fmtonly off%
I've seen the problem in Delphi client applications that use ADO. When
you discover how to turn it off in your code (regardless of what you
wrote it in), please post the solution here so I can inform my
colleagues.
Thanks.
David Gugick
|||I never do any manipulation using the set fmtonly. It is a straight forward
asp ADO code
(open conn, give it a T-SQL or a stored procedure, populate parameters and
get back the result using a dataset or call execute methode.
For the T-SQL I gave as an example, her is the relevant piece of code :
objConn.Open "Provider=SQLOLEDB;Password=user;Persist Security Info=True; _
User ID=user;Initial Catalog=PETC;Data Source=myserver"
cmdSQL.ActiveConnection = objConn
Then I have a record set which I loop through, in the loopi Execute the
following
sstrsql = "Select PROD_VAT_CODE From Product Where PROD_MANF_SKU = '" &
OrderForm.items(i)("SKU") & "'"
cmdSQL.CommandText = sstrsql
Set rsSKUVat = cmdSQL.Execute
"Tibor Karaszi" wrote:

> Most probably your API (probably ADO) that wants to retrieve meta-data for the query before the
> actual data is returned. I.e., a matter of how you are using ADO. I suggest you post to an ADO
> group, including a small comment or slimmed-down-code-sample showing your current coding technique.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:FCE31D96-46D0-4659-B4C0-DF95EEA53EE2@.microsoft.com...
>
>
|||Just out of curiousity, why are you persisting security info? Also,
there are a number of properties that you can set on the resultset and
command objects. Can you provide that information as well?
David Gugick
Imceda Software
www.imceda.com
SalamElias wrote:[vbcol=seagreen]
> I never do any manipulation using the set fmtonly. It is a straight
> forward asp ADO code
> (open conn, give it a T-SQL or a stored procedure, populate
> parameters and get back the result using a dataset or call execute
> methode.
> For the T-SQL I gave as an example, her is the relevant piece of code
> : ---
> objConn.Open "Provider=SQLOLEDB;Password=user;Persist Security
> Info=True; _ User ID=user;Initial Catalog=PETC;Data Source=myserver"
> cmdSQL.ActiveConnection = objConn
> Then I have a record set which I loop through, in the loopi Execute
> the following
> sstrsql = "Select PROD_VAT_CODE From Product Where PROD_MANF_SKU = '"
> & OrderForm.items(i)("SKU") & "'"
> cmdSQL.CommandText = sstrsql
> Set rsSKUVat = cmdSQL.Execute
> "Tibor Karaszi" wrote:
|||What do you mean by persisiting scurity info?Secondly, can you you indicate
the different parameters that are usefull to use in commands or datasets
Thanks a lot
"David Gugick" wrote:

> Just out of curiousity, why are you persisting security info? Also,
> there are a number of properties that you can set on the resultset and
> command objects. Can you provide that information as well?
> --
> David Gugick
> Imceda Software
> www.imceda.com
> SalamElias wrote:
>
>
|||Profiler and set fmtonly On issue|||Thanks for the info, but would you please can you you
indicate the different parameters that are usefull to use in commands
or datasets Thanks a lot
"David Gugick" wrote:

> SalamElias wrote:
>
> What parameters are you setting for the recordset object. Ther are
> settings for cursor location and type in particular.
> The persist is in your connect string as you posted it:
> objConn.Open "Provider=SQLOLEDB;Password=user; Persist Security
> Info=True; _
> User ID=user;Initial Catalog=PETC;Data Source=myserver"
> You should read about that option if you're not sure why it's there.
> From MSDN:
> "Remarks
> If Persist Security Info is set to False at the time the data source is
> initialized, the data source cannot persist sensitive authentication
> information. Furthermore, a call to a property that contains sensitive
> authentication information, such as a password, returns a default value
> instead of the actual password.
> After the data source has been uninitialized, sensitive information that
> was set when the data source was initialized with the Persist Security
> Info property set to False still cannot be obtained from the Properties
> collection or by persisting the uninitialized data source object.
> However, new properties set after the data source object has been
> uninitialized can be persisted or obtained if Persist Security Info is
> set to True.
> Before the data source is initialized for the first time, sensitive
> information can be obtained from the Properties collection, and can be
> persisted, regardless of the setting of the Persist Security Info
> property. Therefore, sensitive applications should avoid passing
> uninitialized data source objects.
>
> Note The recommended method for connecting to an instance of Microsoft?
> SQL Server? 2000 is to use Windows Authentication mode."
>
>
> --
> David Gugick
> Imceda Software
> www.imceda
|||SalamElias wrote:
> Thanks for the info, but would you please can you you
> indicate the different parameters that are usefull to use in commands
> or datasets Thanks a lot
>
Can you post what you are using please.
Generally, with ADO, I like to use forward-only, read-only, server-side
cursors. They are the fastest when minimal row processing on the client
is required. If you need to perform a lot of processing on each row in
the results, then I might move over to a client-side cursor just to get
the rows off the server more quickly.
David Gugick
Imceda Software
www.imceda.com

Profiler and set fmtonly On issue

Hello Eliassal,
To turn off this feature, you may try to set Command.prepared in ADO to
False.
Since it appears that this is an ADO related request and would best be
address in the ADO newsgroups, I have provided the link below:
Microsoft.public.data.ADO
If you need further assistance on this request, you can post it in the ADO
newsgroups. I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
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.
So many thanks I will give it a go and let you know. Also, I posted the
question in the ADO group
Salam
"Sophie Guo [MSFT]" wrote:

> Hello Eliassal,
> To turn off this feature, you may try to set Command.prepared in ADO to
> False.
> Since it appears that this is an ADO related request and would best be
> address in the ADO newsgroups, I have provided the link below:
> Microsoft.public.data.ADO
> If you need further assistance on this request, you can post it in the ADO
> newsgroups. I hope above information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
> 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.
>

Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>sql

Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.c...ofiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>

Profiler and Perfmon data side by side

What should I save in perfmon and how do I save it so that I can import it
into Profiler and compare perfmon with events in Profiler ? I have seen it
in 2005...
Hassan
Save some counters in PerfMon and there si an option to import results into
Profiler
File--Import Perfomance Data
"Hassan" <hassan@.test.com> wrote in message
news:e69KfgPJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> What should I save in perfmon and how do I save it so that I can import it
> into Profiler and compare perfmon with events in Profiler ? I have seen it
> in 2005...
>

Profiler and Perfmon data side by side

What should I save in perfmon and how do I save it so that I can import it
into Profiler and compare perfmon with events in Profiler ? I have seen it
in 2005...Hassan
Save some counters in PerfMon and there si an option to import results into
Profiler
File--Import Perfomance Data
"Hassan" <hassan@.test.com> wrote in message
news:e69KfgPJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> What should I save in perfmon and how do I save it so that I can import it
> into Profiler and compare perfmon with events in Profiler ? I have seen it
> in 2005...
>

Profiler and Perfmon data side by side

What should I save in perfmon and how do I save it so that I can import it
into Profiler and compare perfmon with events in Profiler ? I have seen it
in 2005...Hassan
Save some counters in PerfMon and there si an option to import results into
Profiler
File--Import Perfomance Data
"Hassan" <hassan@.test.com> wrote in message
news:e69KfgPJIHA.3940@.TK2MSFTNGP05.phx.gbl...
> What should I save in perfmon and how do I save it so that I can import it
> into Profiler and compare perfmon with events in Profiler ? I have seen it
> in 2005...
>

Profiler and cpu percentage

Can anyone tell me if its possible to filter the results of the profiler
by the percentage of cpu being used. Using PerfMon I can see that there
are often times when the cpu on our server is using over 80% and I would
like to find out what this is.
regards,
Aaroni would think this is a matter of filtering on the start
time from profiler to match the time of the perfmon spikes
>--Original Message--
>Can anyone tell me if its possible to filter the results
of the profiler
>by the percentage of cpu being used. Using PerfMon I can
see that there
>are often times when the cpu on our server is using over
80% and I would
>like to find out what this is.
>regards,
>Aaron
>.
>sql

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

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

Profiler 2005 issue

Hi!

When I try to run profile 2005 ... as soon as i hit the connect button (authentication dialog) I get this error:

The procedure entry point lstrcpynI could not be located in the dynamic link library MSDART.DLL

any ideas?

Everything is up to date on my system. my configuration:

SQL Server 2000 enterprise (can't wait till next week to get EE for 2005)
WS 2003 w/SP1
.NET 1.1/2.0 (2.0 was installed with SQL 2005)
VS.NET 2003 (going to install 2005 later)

Office 2003 with SP2anyone?

Profiler 2000

I set up an audit trace while logged on to the server. After running it
for a while I logged out and found that the trace had stopped and
closed. I really had wanted to let it run for a couple of days -- we
are trying to figure out a SQL injection attack that defaced a website
this week. I am guessing that this will probably require running it
from a monitoring workstation while logged on, but is there perhaps a
way to leave a trace running while not logged on, whether on the server
or not? (because of other considerations w/ folder redirection and
profiles I can't leave the WS running overnight). BOL didn't help
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Don't use profiler, use Trace instead. Profiler is just a gui that uses the
built in TRACE functionality. You can use profiler to setup the events and
columns you wish to trace. Then start and immediately stop the trace. Then
go to the menu under File and then Script Trace. This will give you a
template on how to create a file that holds the trace event data by filling
in the missing info as shown in the header of the file. When you run this
it will create a trace that runs independently of that connection so you can
log off and it will still run. See BOL for more details.
Andrew J. Kelly SQL MVP
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.210fe7ec4cfeafbc989699@.msnews.microsoft.c om...
>I set up an audit trace while logged on to the server. After running it
> for a while I logged out and found that the trace had stopped and
> closed. I really had wanted to let it run for a couple of days -- we
> are trying to figure out a SQL injection attack that defaced a website
> this week. I am guessing that this will probably require running it
> from a monitoring workstation while logged on, but is there perhaps a
> way to leave a trace running while not logged on, whether on the server
> or not? (because of other considerations w/ folder redirection and
> profiles I can't leave the WS running overnight). BOL didn't help
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
|||Hi Pete,
You can use the following system SP's. This approach, however, requires a
bit of work.
sp_trace_create
sp_trace_generateevent
sp_trace_setevent
sp_trace_setfilter
sp_trace_setstatus
fn_trace_gettable
You can also try to use SQL Trace Analyzer developed by Lakeside SQL which
you can download from here:
http://www.lakesidesql.com/TaDownload/ToTest/1.1.2007.813/TASetup.zip. Trace
scheduling is just a supplementary feature of the aplication. It provides
very granular trace filtering which can help you find what you're looking
for.
HTH.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ow%23m9eizHHA.484@.TK2MSFTNGP06.phx.gbl...
> Don't use profiler, use Trace instead. Profiler is just a gui that uses
> the built in TRACE functionality. You can use profiler to setup the events
> and columns you wish to trace. Then start and immediately stop the trace.
> Then go to the menu under File and then Script Trace. This will give you
> a template on how to create a file that holds the trace event data by
> filling in the missing info as shown in the header of the file. When you
> run this it will create a trace that runs independently of that connection
> so you can log off and it will still run. See BOL for more details.
> --
> Andrew J. Kelly SQL MVP
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.210fe7ec4cfeafbc989699@.msnews.microsoft.c om...
>

Profiler [SQL Timeout using perfmon.msc]

After reading the information on the Poor man's monitor
http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
I could write an application that would immediately tell me when a timeout
was happening. Being that the server is a production server, I don't dare
test on it and the testing I did indicated that only the Server 2003 was able
to pull the data I was looking for (as the article indicated).
The problem is that when I set up the profmon.msc on the 2003 server to look
at the SQL timeouts on the 2000 server, it brings back nulls to the three
monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
Is there a way to coax the actual SQL Lock Timeout data back from the 2000
server to the 2003 server through PerfMon.msc?
Should I post this to a different user group?
--
Regards,
JamieAnswered my own question. The counterid will increment each time the table
is created. I believe my smallest counterid in the counterdetails was 191.
When I reset the query to point to the right counterid, it immediately
produced results.
--
Regards,
Jamie
"thejamie" wrote:
> After reading the information on the Poor man's monitor
> http://www.sqlmag.com/Articles/ArticleID/37468/pg/2/2.html I began to believe
> I could write an application that would immediately tell me when a timeout
> was happening. Being that the server is a production server, I don't dare
> test on it and the testing I did indicated that only the Server 2003 was able
> to pull the data I was looking for (as the article indicated).
> The problem is that when I set up the profmon.msc on the 2003 server to look
> at the SQL timeouts on the 2000 server, it brings back nulls to the three
> monitoring tables Counterdata, CounterDataDetails, and DisplayToID.
> Is there a way to coax the actual SQL Lock Timeout data back from the 2000
> server to the 2003 server through PerfMon.msc?
> Should I post this to a different user group?
> --
> Regards,
> Jamie

Profiler : Getting the memory usage of a query

Im using profiler to get information about query on the server here but i cant seem to be able to get the memory usage of a query. I can acess the cpu, I/O and duration but i cant seem to be able to get that last info i need.

Is there any way for me to obtain it trough profiler?

Not through profiler nosql

Friday, March 30, 2012

Profiler and SQL Express

profiler and showplan

profiler and showplan

profiler and showplan

Profiler and set fmtonly On issue

Profiler and set fmtonly On issue

Profiler and performance

Profiler and performance

Profiler and Perfmon data side by side

Profiler and Perfmon data side by side

Profiler and Perfmon data side by side

Profiler and cpu percentage

Profiler and alerts

Profiler and alerts

Profiler and alerts

Profiler always doing something

Profiler 2005 issue

Profiler 2000

Profiler [SQL Timeout using perfmon.msc]

Profiler : Getting the memory usage of a query

Procedure Time out

Blog Archive

About Me