Procedure Time out: March 2012

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

Profiler / sql trace

There are several ways to capture/view sql activities. Can
you tell me what the best way is ?
Run Profiler from a client machine or
Use sql trace procs from a client machine using QA or
Run sql trace procs at the server (which I've seen MS PSS
has always used)
thanksFor a quick look at a server I'll use Profiler from my PC and if I want to
keep it I'll save to a file (which gives me the ability to save to a table
later if I want to do more analysis) For longer traces I'll run a server
side trace on the server and generally import the resulting files into a
table for analysis. I tend to use a rollover file size of 50 MB to keep them
manageable and generally define a stop time or set up a job to stop it at a
future date.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dba" <Dba@.Dba1.Com> wrote in message
news:021001c3d61e$e5e79bf0$a401280a@.phx.gbl...
> There are several ways to capture/view sql activities. Can
> you tell me what the best way is ?
> Run Profiler from a client machine or
> Use sql trace procs from a client machine using QA or
> Run sql trace procs at the server (which I've seen MS PSS
> has always used)
> thanks|||Hello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Based on my experience, this kind of problem does not have an easy and
clear answer. It is hard for me to tell you which the best way is to
capture/view sql activities; what a good method is for you may not be a
good method for other people. The way in which we choose to monitor SQL
Server depends on specific and particular concerns. I can help you find
the most appropriate way for you to monitor SQL server based on your needs,
but, please understand that there is no "best way" to do this.
When people use stored procedures to monitor SQL Server, the information we
are able to get is limited. For example:
Sp_who stored procedure provides information about current Microsoft SQL
Server users and processes.
sp_lock stored procedure reports information about locks.
SQL Server Profiler (SQL Trace) is a graphical tool that allows system
administrators and database developers to monitor engine events on
computers running Microsoft SQL Server. Using SQL Server Profiler on the
client machine to monitor SQL Server will increase network traffic.
Monitoring on the server will increase the workload of the server machine.
For more information regarding this issue, please refer to the following
article.
325263 Support WebCast: SQL Server 2000 Profiler: What's New and How to
http://support.microsoft.com/?id=325263
I hope the above explanation is clear. Please let us know if you need
further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long
>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for
using Microsoft
>newsgroup.
>Based on my experience, this kind of problem does not
have an easy and
>clear answer. It is hard for me to tell you which the
best way is to
>capture/view sql activities; what a good method is for
you may not be a
>good method for other people. The way in which we choose
to monitor SQL
>Server depends on specific and particular concerns. I
can help you find
>the most appropriate way for you to monitor SQL server
based on your needs,
>but, please understand that there is no "best way" to do
this.
>When people use stored procedures to monitor SQL Server,
the information we
>are able to get is limited. For example:
>Sp_who stored procedure provides information about
current Microsoft SQL
>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that
allows system
>administrators and database developers to monitor engine
events on
>computers running Microsoft SQL Server. Using SQL Server
Profiler on the
>client machine to monitor SQL Server will increase
network traffic.
>Monitoring on the server will increase the workload of
the server machine.
>For more information regarding this issue, please refer
to the following
>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's
New and How to
>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know
if you need
>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup. My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to perform the
best practices for their benefits. However, we never prohibit customers from applying their
easier way to do anything. It all depends on customer's specific issues and detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to run the
Profiler at the server side is that it guarantees you can trace all the information.
In the GUI trace setup, you can click the checkbox for: "server processes sql server trace
data"
The stored procedures to create/exec profiler traces will guarantee events will not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace information is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trace information is
received as a result set, in which case if there is a large volume of trace information, the
performance will definitely be poorer than tracing on the server side. That's why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on the client side. I
fully understand that running the Profiler is convenient and also efficient for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesitate to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Profiler / sql trace

Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long

quote:

>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for

using Microsoft

quote:

>newsgroup.
>Based on my experience, this kind of problem does not

have an easy and

quote:

>clear answer. It is hard for me to tell you which the

best way is to

quote:

>capture/view sql activities; what a good method is for

you may not be a

quote:

>good method for other people. The way in which we choose

to monitor SQL

quote:

>Server depends on specific and particular concerns. I

can help you find

quote:

>the most appropriate way for you to monitor SQL server

based on your needs,

quote:

>but, please understand that there is no "best way" to do

this.

quote:

>When people use stored procedures to monitor SQL Server,

the information we

quote:

>are able to get is limited. For example:
>Sp_who stored procedure provides information about

current Microsoft SQL

quote:

>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that

allows system

quote:

>administrators and database developers to monitor engine

events on

quote:

>computers running Microsoft SQL Server. Using SQL Server

Profiler on the

quote:

>client machine to monitor SQL Server will increase

network traffic.

quote:

>Monitoring on the server will increase the workload of

the server machine.

quote:

>For more information regarding this issue, please refer

to the following

quote:

>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's

New and How to

quote:

>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know

if you need

quote:

>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and

confers no rights.

quote:

>.
>

Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup.
My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to
perform the
best practices for their benefits. However, we never prohibit customers from
applying their
easier way to do anything. It all depends on customer's specific issues and
detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to
run the
Profiler at the server side is that it guarantees you can trace all the info
rmation.
In the GUI trace setup, you can click the checkbox for: "server processes sq
l server trace
data"
The stored procedures to create/exec profiler traces will guarantee events w
ill not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file
creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result
set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace infor
mation is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trac
e information is
received as a result set, in which case if there is a large volume of trace
information, the
performance will definitely be poorer than tracing on the server side. That'
s why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on
the client side. I
fully understand that running the Profiler is convenient and also efficient
for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesita
te to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Profiler ....duration

In SQL Server 2000, "Duration" in profiler (which is in milliseconds) taken by the event; does it include time for the network transfer of the results to the client as well ; or its just the duration taken by SQL Server to complete the SQL Statement.All the measurements are serverside only, so no network time. If you want to see the network time then run the query in query analyser but before you start right click and select client side statistics.|||

thanks for the info.

I have a 3rd party application & the queries are generated from the application.

So I wanted to see the total duration. Queries are very dynamic, so running from Query Analyzer may not yield same result w.r.t. time/ network load when the app runs/ etc etc ... So i am trapping it in profiler.

|||Then there is no easy way to get this with the built in tools.

Profiler (GUI) uses SA account

HI,
When I start MS Sql server 7.0 / Profiler (GUI), it uses SA account.
Is it possible to start Profiler (GUI) with another account ?
thank you
dannyDanny,
In SQL 7.0 you can just grant exec permission on xp_trace*.* series
extended stor procs for a user account to run a trace using profiler.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danny Presse" <dpresse@.congresmtl.com> wrote in message
news:eufEH0eXDHA.1680@.tk2msftngp13.phx.gbl...
> HI,
> When I start MS Sql server 7.0 / Profiler (GUI), it uses SA account.
> Is it possible to start Profiler (GUI) with another account ?
> thank you
> danny
>
>

Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
Thanks,
Osk
Osk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
David Gugick
Imceda Software
www.imceda.com
sql

Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
Thanks,
OskOsk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
David Gugick
Imceda Software
www.imceda.com

Profiler -> Duration

Hi,
What does a zero value in the Duration column of SQL
Profiler trace mean for SQL:BatchCompleted and
RPC:Completed events?
--
Thanks,
OskOsk wrote:
> Hi,
> What does a zero value in the Duration column of SQL
> Profiler trace mean for SQL:BatchCompleted and
> RPC:Completed events?
It means the duration was so fast SQL Server recorded it as 0. The
granularity of the timer is probably about 15 ms in most cases.
--
David Gugick
Imceda Software
www.imceda.com

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
sql2k sp3
TIA, ChrisR
ChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
David Gugick
Imceda Software
www.imceda.com

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
--
sql2k sp3
TIA, ChrisRChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
--
David Gugick
Imceda Software
www.imceda.com

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
sql2k sp3
TIA, ChrisRChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
David Gugick
Imceda Software
www.imceda.comsql

Profiler - just view Writes

Hello,
Is there any way to use profiler and just show the 'Writes' it does.
I do not want to display anything else for a specific DB.
Thanks,
TmuldSet the filter for Writes > 0
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1188592028.966774.60190@.z24g2000prh.googlegroups.com...
> Hello,
> Is there any way to use profiler and just show the 'Writes' it does.
> I do not want to display anything else for a specific DB.
> Thanks,
> Tmuld
>

Profiler - details of SP

How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||That gives me the duration of each Insert, Updaet and Select in the SP?
Thanks Karen, you are a blessing to the SQL Server community
--
Thanks,
Thomas
"Kalen Delaney" wrote:
> Hi Thomas
> You can trace SP:StmtStarting and SP:StmtCompleted
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> > How can I setup Profiler to track/watch the execution of the lines in a
> > stored procedure?
> >
> > It looks like I just see exec sp_DoWork, as TextData.
> >
> > I want to see the sql statements in the SP and how long they each take.
> > --
> > Thanks,
> > ThomasLL
>
>|||SP:StmtCompleted can give you the duration of each statement in the SP.
Thanks for your kind words. Karen thanks you too. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:CC7D6F54-4DE7-4DE8-B7CC-7904572FDF90@.microsoft.com...
> That gives me the duration of each Insert, Updaet and Select in the SP?
>
> Thanks Karen, you are a blessing to the SQL Server community
> --
> Thanks,
> Thomas
>
> "Kalen Delaney" wrote:
>> Hi Thomas
>> You can trace SP:StmtStarting and SP:StmtCompleted
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "Thomas.LeBlanc@.NoSpam.Com"
>> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
>> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
>> > How can I setup Profiler to track/watch the execution of the lines in a
>> > stored procedure?
>> >
>> > It looks like I just see exec sp_DoWork, as TextData.
>> >
>> > I want to see the sql statements in the SP and how long they each take.
>> > --
>> > Thanks,
>> > ThomasLL
>>

Profiler

I have a trace file that I captured from my production server that I want to replay on my dev box. The database on dev is a restore of the production backup, but the DB_ID of the two is different, which I believe is causing these errors:
No matching connection to execute this on. Will auto-create one.
and
Failed to set proper user name ('empireapi') for the connection
the empireapi username is in the Dev database. I have also dropped and re-created it.
Question:
Is the different databaseid the source of the problem? can I change it somehow? Do you have enough info to determine?
Thanks,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
Yes. Yes. And Yes.
In books online there is a topic:
Replaying Traces
in the Administering SQL Server section. You can find the
requirements for replay traces listed there. Included is
that the database ids need to be the same.
To correct this, you can restore the master database from
the production server to the dev box.
The other work around would be to remove database ids from
the trace. Then set all the users captured in the trace to
have a default database of the target database. The commands
will then be executed in the default database so the
database id isn't an issue.
-Sue
On Thu, 10 Jun 2004 11:16:31 -0500, "Kevin3NF"
<KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:

>I have a trace file that I captured from my production server that I want to replay on my dev box. The database on dev is a restore of the production backup, but the DB_ID of the two is different, which I believe is causing these errors:
>No matching connection to execute this on. Will auto-create one.
>and
>Failed to set proper user name ('empireapi') for the connection
>the empireapi username is in the Dev database. I have also dropped and re-created it.
>Question:
>Is the different databaseid the source of the problem? can I change it somehow? Do you have enough info to determine?
>Thanks,
|||Thanks Sue...I will get to one of your two excellent suggestions right away.
Fortunately, the DEV box is completely under my control for at least another
week, then it becomes a production box, so I can do anything I need to with
it :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:d87ic0tqi2vj8g4vti340ukfa80corr6dj@.4ax.com... [vbcol=seagreen]
> Yes. Yes. And Yes.
> In books online there is a topic:
> Replaying Traces
> in the Administering SQL Server section. You can find the
> requirements for replay traces listed there. Included is
> that the database ids need to be the same.
> To correct this, you can restore the master database from
> the production server to the dev box.
> The other work around would be to remove database ids from
> the trace. Then set all the users captured in the trace to
> have a default database of the target database. The commands
> will then be executed in the default database so the
> database id isn't an issue.
> -Sue
> On Thu, 10 Jun 2004 11:16:31 -0500, "Kevin3NF"
> <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:
to replay on my dev box. The database on dev is a restore of the production
backup, but the DB_ID of the two is different, which I believe is causing
these errors:[vbcol=seagreen]
re-created it.[vbcol=seagreen]
somehow? Do you have enough info to determine?
>

Profiler

Where can I change the length or data type (preferrably to nvarchar 4000) of
the textdata field in profiler? My intent is to capture as much of a stored
proc script as possible & save as a trace table, but the TextData column type
of ntext (16) is not sufficient. Or is there a way to capture the body of
text in the lower pane?
JDArsenault wrote:
> Where can I change the length or data type (preferrably to nvarchar
> 4000) of the textdata field in profiler? My intent is to capture as
> much of a stored proc script as possible & save as a trace table, but
> the TextData column type of ntext (16) is not sufficient. Or is there
> a way to capture the body of text in the lower pane?
NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
in length. it's possible the Profiler UI has limitations on the amount
of text it displays. If you use a server-side trace, you will see
everything.
David Gugick
Imceda Software
www.imceda.com
|||Can you briefly describe a server side trace & what I need to do?
"David Gugick" wrote:

> JDArsenault wrote:
> NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
> in length. it's possible the Profiler UI has limitations on the amount
> of text it displays. If you use a server-side trace, you will see
> everything.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||JDArsenault wrote:
> Can you briefly describe a server side trace & what I need to do?
Profiler is just a GUI for the SQL Trace API, with some major
disadvantages. Profiler uses traces that produce rowsets so the UI can
display those results to the user in a streaming fashion. It's a good
option for seeing a short amount of activity on a server. Profiler is
not intended for use, nor is it recommended for use, in an enterprise
fashion because of the increased overhead and no guarantee of seeing all
results.
A server-side trace is a programmatic method to create, start, stop, and
manage traces. The API (accessed through T-SQL) is somewhat verbose, but
does provide a guarantee of seeing all traced information and causes
much less overhead on the server.
You use the sp_trace_* system procs and fn_trace_* system functions to
do this.
The easiest way to see the code for server-side trace is to set one up
using Profiler. Select the options to save the results to a file and
have the server manage the trace. Select the events you want and columns
you need to see. Start the trace. Stop it. Select the File - Script
Trace menu option and Profiler generates much of the code for you.
You can then modify the code as you see fit. You'll also need to
manually stop the trace, so getting the trace handle when the trace
starts is important. You sp_trace_setstatus to start/stop traces.
You always want to trace to a flat file on one of the server's local
drives. Never to a network share or a NAS device. The tempdb/log device
is usually a good place (RAID 5 arrays are not ideal for traces).
When the trace is stopped, you can query the information from the file
using fn_trace_gettable(), import the information into a table using
that function, or open the trace file in Profiler.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for the straight-forward help...
"David Gugick" wrote:

> JDArsenault wrote:
> Profiler is just a GUI for the SQL Trace API, with some major
> disadvantages. Profiler uses traces that produce rowsets so the UI can
> display those results to the user in a streaming fashion. It's a good
> option for seeing a short amount of activity on a server. Profiler is
> not intended for use, nor is it recommended for use, in an enterprise
> fashion because of the increased overhead and no guarantee of seeing all
> results.
> A server-side trace is a programmatic method to create, start, stop, and
> manage traces. The API (accessed through T-SQL) is somewhat verbose, but
> does provide a guarantee of seeing all traced information and causes
> much less overhead on the server.
> You use the sp_trace_* system procs and fn_trace_* system functions to
> do this.
> The easiest way to see the code for server-side trace is to set one up
> using Profiler. Select the options to save the results to a file and
> have the server manage the trace. Select the events you want and columns
> you need to see. Start the trace. Stop it. Select the File - Script
> Trace menu option and Profiler generates much of the code for you.
> You can then modify the code as you see fit. You'll also need to
> manually stop the trace, so getting the trace handle when the trace
> starts is important. You sp_trace_setstatus to start/stop traces.
> You always want to trace to a flat file on one of the server's local
> drives. Never to a network share or a NAS device. The tempdb/log device
> is usually a good place (RAID 5 arrays are not ideal for traces).
> When the trace is stopped, you can query the information from the file
> using fn_trace_gettable(), import the information into a table using
> that function, or open the trace file in Profiler.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Just to comment on that:
Profiler limits amount of data displayed in TextData column in the grid,
however entire content of TextData is visible in the bottom pane once the
event is selected. It can be copied from there to clipboard using standard
Ctrl-C menu.
Excellent write-up on server side trace.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:B5F43559-6464-4463-9A13-5BC1C2164E5F@.microsoft.com...[vbcol=seagreen]
> Thanks for the straight-forward help...
> "David Gugick" wrote:

Profiler

Is there any way I can give user permission to run SQL profiler witout giving
them sysadmin access
thx
stoney wrote:
> Is there any way I can give user permission to run SQL profiler
> witout giving them sysadmin access
> thx
No. It is a sysadmin function. SQL 2005 will have other options.
David Gugick
Imceda Software
www.imceda.com
sql

profiler

I stsrted a trace on one of my bds and it is creating mulitple 5 mb files. I
thought it would stop once I closed the profiler. WRONG!!!
How can I stop this?
You can use the following TSQL statement to identify the TraceID of any
Traces that are currently running:
SELECT DISTINCT traceid FROM :: fn_trace_getinfo(default)
You can then stop any running traces by setting the status of the TraceID to
stopped (0).
ie.
EXEC sp_trace_setstatus @.traceid = 1,
@.status = 0
HTH
- Peter Ward
WARDY IT Solutions
"Andre Gibson" wrote:

> I stsrted a trace on one of my bds and it is creating mulitple 5 mb files. I
> thought it would stop once I closed the profiler. WRONG!!!
> How can I stop this?
|||thanks so much Peter,
I will try that
"P. Ward" wrote:
[vbcol=seagreen]
> You can use the following TSQL statement to identify the TraceID of any
> Traces that are currently running:
> SELECT DISTINCT traceid FROM :: fn_trace_getinfo(default)
> You can then stop any running traces by setting the status of the TraceID to
> stopped (0).
> ie.
> EXEC sp_trace_setstatus @.traceid = 1,
> @.status = 0
> HTH
>
> - Peter Ward
> WARDY IT Solutions
>
> "Andre Gibson" wrote:

Profiler

Hi,
I'm usin g profiler to trace TSQL (stmtstarting etc) and
there are 100s of rows with empty values for the textdata
column - any ideas why?
TIA
Not all events return data for every column. What events are these that have
the blank textdata values?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:ac8801c4d6fe$3192d480$a601280a@.phx.gbl...
> Hi,
> I'm usin g profiler to trace TSQL (stmtstarting etc) and
> there are 100s of rows with empty values for the textdata
> column - any ideas why?
> TIA
|||sp:stmtStarting, sp:stmtCompleted

|||Is it possible that the procedure is encrypted?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bob" <anonymous@.discussions.microsoft.com> wrote in message
news:187c01c4d707$1473e8e0$a501280a@.phx.gbl...
> sp:stmtStarting, sp:stmtCompleted

|||Kalen Delaney wrote:[vbcol=seagreen]
> Is it possible that the procedure is encrypted?
>
> "Bob" <anonymous@.discussions.microsoft.com> wrote in message
> news:187c01c4d707$1473e8e0$a501280a@.phx.gbl...
What I generally see with an encrypted SP in Profiler for
SP:StmtStarting/Completed is something like:
-- <sp_name> Encrypted Text
Strange.
David Gugick
Imceda Software
www.imceda.com

PROFILER

Hello,
What would be the best place to run profiler, on the production Server or on
another client Machine?
Thanks,
QA
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"OA" <omrana@.verizon.net> wrote in message
news:%23GQ7oPukIHA.1052@.TK2MSFTNGP05.phx.gbl...
> Hello,
> What would be the best place to run profiler, on the production Server or
> on another client Machine?
>
> Thanks,
>

profiler

Hi
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
Mark
You can create and start traces using TSQL scripts. You get a handle back with which you can stop
and close a trace. Easiest way to get started is to define the trace in Profiler, and from the
profiler menu save the trace definition as a TSQL script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Mark

Profiler

Hey,
I want to run the profiler in certain times during the
day. Instead of me opening and closing sessions of
profiler, i extracted the script and run it through query
analyser (then schedula it as a job and run at the times i
want). I am testing this now and there seems to be a
problem. I run the script i got and when i execute it in
query anaylser the profiler file is created in the
location i want but no data is being written to it. I
cannot delete it because "the file is being used"... does
anyone know why this happenes? any work arrounds?
Help please!!Hi Claudia
This is normal.
Data is only written to the trace file in 128K chunks. So as soon as there
is 128K worth of events, you'll see some size for the file. If you stop and
close the trace, it will also write all the remaining data to the trace
file, or if the server is stopped.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:03dd01c39954$e85764d0$a001280a@.phx.gbl...
> Hey,
> I want to run the profiler in certain times during the
> day. Instead of me opening and closing sessions of
> profiler, i extracted the script and run it through query
> analyser (then schedula it as a job and run at the times i
> want). I am testing this now and there seems to be a
> problem. I run the script i got and when i execute it in
> query anaylser the profiler file is created in the
> location i want but no data is being written to it. I
> cannot delete it because "the file is being used"... does
> anyone know why this happenes? any work arrounds?
> Help please!!sql

Friday, March 30, 2012

Blog Archive

About Me