Hi,
Im noticing a performance diff when i execute a proc from the application
the duration shown in the profiler is higher than when i execute the same
proc from query analyze.
Does anyone have any clue on why this difference is occuring. What could be
causing the overhead.
Thanks.
Arzan wrote:
> Hi,
> Im noticing a performance diff when i execute a proc from the
> application the duration shown in the profiler is higher than when i
> execute the same proc from query analyze.
> Does anyone have any clue on why this difference is occuring. What
> could be causing the overhead.
> Thanks.
Duration is sometimes an application issue. The duration for a query is
determined by how long a query takes to execute plus how long the
application takes to fetch all the results in the result set. QA fetches
rows immediately and performs little to no processing on the returning
data, so durations are as fast as they can be. OTOH, an application may
be performing row processing on the result set or not fetching all at
once causing delays in the final duration figure.
You can test this from SQL EM and Profiler.
Run Profiler with the SQL:StmtStarting and SQL:StmtCompleted events.
Find a table with a lot of rows and right-click and select Return All
Rows.
You'll see the SQL:StmtStarting event with the Select * From...
Go back to SQL EM and CTLR + END to move to the end of the result set
Now you see the SQL:StmtCompleted in Profiler.
I'm not saying this is your problem. Can you provide more details about
your app and what's it's doing with the results, if any.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label profiler. Show all posts
Showing posts with label profiler. Show all posts
Monday, March 26, 2012
Performance diff when sp executed from apps and from query analyze
Hi,
Im noticing a performance diff when i execute a proc from the application
the duration shown in the profiler is higher than when i execute the same
proc from query analyze.
Does anyone have any clue on why this difference is occuring. What could be
causing the overhead.
Thanks.Arzan wrote:
> Hi,
> Im noticing a performance diff when i execute a proc from the
> application the duration shown in the profiler is higher than when i
> execute the same proc from query analyze.
> Does anyone have any clue on why this difference is occuring. What
> could be causing the overhead.
> Thanks.
Duration is sometimes an application issue. The duration for a query is
determined by how long a query takes to execute plus how long the
application takes to fetch all the results in the result set. QA fetches
rows immediately and performs little to no processing on the returning
data, so durations are as fast as they can be. OTOH, an application may
be performing row processing on the result set or not fetching all at
once causing delays in the final duration figure.
You can test this from SQL EM and Profiler.
Run Profiler with the SQL:StmtStarting and SQL:StmtCompleted events.
Find a table with a lot of rows and right-click and select Return All
Rows.
You'll see the SQL:StmtStarting event with the Select * From...
Go back to SQL EM and CTLR + END to move to the end of the result set
Now you see the SQL:StmtCompleted in Profiler.
I'm not saying this is your problem. Can you provide more details about
your app and what's it's doing with the results, if any.
David Gugick
Imceda Software
www.imceda.com
Im noticing a performance diff when i execute a proc from the application
the duration shown in the profiler is higher than when i execute the same
proc from query analyze.
Does anyone have any clue on why this difference is occuring. What could be
causing the overhead.
Thanks.Arzan wrote:
> Hi,
> Im noticing a performance diff when i execute a proc from the
> application the duration shown in the profiler is higher than when i
> execute the same proc from query analyze.
> Does anyone have any clue on why this difference is occuring. What
> could be causing the overhead.
> Thanks.
Duration is sometimes an application issue. The duration for a query is
determined by how long a query takes to execute plus how long the
application takes to fetch all the results in the result set. QA fetches
rows immediately and performs little to no processing on the returning
data, so durations are as fast as they can be. OTOH, an application may
be performing row processing on the result set or not fetching all at
once causing delays in the final duration figure.
You can test this from SQL EM and Profiler.
Run Profiler with the SQL:StmtStarting and SQL:StmtCompleted events.
Find a table with a lot of rows and right-click and select Return All
Rows.
You'll see the SQL:StmtStarting event with the Select * From...
Go back to SQL EM and CTLR + END to move to the end of the result set
Now you see the SQL:StmtCompleted in Profiler.
I'm not saying this is your problem. Can you provide more details about
your app and what's it's doing with the results, if any.
David Gugick
Imceda Software
www.imceda.com
Tuesday, March 20, 2012
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Performance baseline report
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
The following articles should help you going:
http://www.sql-server-performance.com/articles/audit/perform_performance_audit_p1.aspx
http://www.sql-server-performance.com/articles/per/10_baselining_tips_p1.aspx
-Sue
|||Thanks the articles are really helpfull. Do you think the SqL Server Performance Monitoring and Management Tools such as SQL Diagnostic Manager or Spotlight on SQL Server Enterprise are helpful? If I design to buy those tools, do I need to create my own performance baseline report.
|||The decision on purchasing those types of tools often depends on how many servers, databases you need to monitor per DBA and the nature of the databases, how critical they are to the business. Sometimes a "lightweight" shop can get by just buy rolling their own monitoring systems. But if you can get the money for the purchase, it certainly won't hurt! And some of them can really help a more junior level person get up to speed.
Personally, I liked Diagnostic Manager when I used it. I don't think you would really need to baseline separately if using DM. It has a repository to store the data captured and reporting functionality. It has built in reports and you can also write your own. So you can do trend reporting, analysis from a certain point in time and view changes, variances, etc. I know you can download a demo and try it out. Their support was good, company was helpful when evaluating this product - as well as others.
It's been too many years since I used Spotlight and years ago I didn't care for the performance overhead and all the junk in put in the database. I didn't like it, didn't feel it added much value and didn't like cleaning up from the install when we got rid of it. I know they've changed a lot since so I can't really give a decent opinion.
Not exactly the same thing but somewhat along the same lines and as long as we're on the general subject, have you looked at the Performance Dashboard reports from Microsoft? They are more to provide information at a given point in time in terms of performance. But you can export the reports out to files if needed. They are lightweight and do a good job of assessing performance issues, allowing drill through into various areas. You need to be on SQL 2005 SP2 to use them. No other requirements and they are free. Check the following link for a brief overview and a link to the download: http://blogs.msdn.com/sqltips/archive/2007/03/29/sql-server-2005-performance-dashboard-reports.aspx
-Sue
Wednesday, March 7, 2012
Performace Monitor and SQL Profiler
Hi,
I was running Performace Monitor and SQL Profiler to keep track of
changes on the server for period of a week. And for some reason this ONE
table keeps getting locked out. The third party vendor suggested that SQL
Profiler creates locks again the users database.
I though the SQL Profiler acquire any locks against user databases, it is
getting information form SQL API directly?
Also the performace counters I read had no affect on the SQL Server...
Is my understanding wrong? Can someone clearify it for me? Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005Well... I'm not sure if SQL Profiler may cause locks or not however
Microsoft recommends using these kind of tools (SQL Profiler, Performance
Monitor) from another machine. Because they effect performance on the
machine they work. However, in this case they also recommend using a
dedicated connection between those two machines to avoid network traffic
because of using these tools if possible.
So, in your case, yes, they have affect on the SQL Server somehow.
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> Hi,
> I was running Performace Monitor and SQL Profiler to keep track of
> changes on the server for period of a week. And for some reason this ONE
> table keeps getting locked out. The third party vendor suggested that SQL
> Profiler creates locks again the users database.
> I though the SQL Profiler acquire any locks against user databases, it is
> getting information form SQL API directly?
> Also the performace counters I read had no affect on the SQL Server...
> Is my understanding wrong? Can someone clearify it for me? Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem Ã?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > Hi,
> >
> > I was running Performace Monitor and SQL Profiler to keep track of
> > changes on the server for period of a week. And for some reason this ONE
> > table keeps getting locked out. The third party vendor suggested that SQL
> > Profiler creates locks again the users database.
> >
> > I though the SQL Profiler acquire any locks against user databases, it is
> > getting information form SQL API directly?
> >
> > Also the performace counters I read had no affect on the SQL Server...
> >
> > Is my understanding wrong? Can someone clearify it for me? Thanks!
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
>|||Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
> > Well... I'm not sure if SQL Profiler may cause locks or not however
> > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > Monitor) from another machine. Because they effect performance on the
> > machine they work. However, in this case they also recommend using a
> > dedicated connection between those two machines to avoid network traffic
> > because of using these tools if possible.
> >
> > So, in your case, yes, they have affect on the SQL Server somehow.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > Hi,
> > >
> > > I was running Performace Monitor and SQL Profiler to keep track of
> > > changes on the server for period of a week. And for some reason this ONE
> > > table keeps getting locked out. The third party vendor suggested that SQL
> > > Profiler creates locks again the users database.
> > >
> > > I though the SQL Profiler acquire any locks against user databases, it is
> > > getting information form SQL API directly?
> > >
> > > Also the performace counters I read had no affect on the SQL Server...
> > >
> > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > >
> > > --
> > > Mohit K. Gupta
> > > B.Sc. CS, Minor Japanese
> > > MCTS: SQL Server 2005
> >|||Thanks Guys ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
> > I see, I will have to check that out on if it has any affect on user database
> > or not. But today I did a trace that logged about 250,000 records on
> > another database, and that server didn't notice any deadlocks or lock
> > timeouts. But I did take your advice and this time ran the SQL Monitor on a
> > different computer. So maybe that had some affect.
> >
> > So I do suspect me running it on the serer caused locks but might have
> > caused some slow down. So I think the application is either timing out too
> > quickly or something else is a miss. Thank-you for your reply.
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> > > Well... I'm not sure if SQL Profiler may cause locks or not however
> > > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > > Monitor) from another machine. Because they effect performance on the
> > > machine they work. However, in this case they also recommend using a
> > > dedicated connection between those two machines to avoid network traffic
> > > because of using these tools if possible.
> > >
> > > So, in your case, yes, they have affect on the SQL Server somehow.
> > >
> > > --
> > > Ekrem Ã?nsoy
> > >
> > >
> > >
> > > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > > Hi,
> > > >
> > > > I was running Performace Monitor and SQL Profiler to keep track of
> > > > changes on the server for period of a week. And for some reason this ONE
> > > > table keeps getting locked out. The third party vendor suggested that SQL
> > > > Profiler creates locks again the users database.
> > > >
> > > > I though the SQL Profiler acquire any locks against user databases, it is
> > > > getting information form SQL API directly?
> > > >
> > > > Also the performace counters I read had no affect on the SQL Server...
> > > >
> > > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > > >
> > > > --
> > > > Mohit K. Gupta
> > > > B.Sc. CS, Minor Japanese
> > > > MCTS: SQL Server 2005
> > >
I was running Performace Monitor and SQL Profiler to keep track of
changes on the server for period of a week. And for some reason this ONE
table keeps getting locked out. The third party vendor suggested that SQL
Profiler creates locks again the users database.
I though the SQL Profiler acquire any locks against user databases, it is
getting information form SQL API directly?
Also the performace counters I read had no affect on the SQL Server...
Is my understanding wrong? Can someone clearify it for me? Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005Well... I'm not sure if SQL Profiler may cause locks or not however
Microsoft recommends using these kind of tools (SQL Profiler, Performance
Monitor) from another machine. Because they effect performance on the
machine they work. However, in this case they also recommend using a
dedicated connection between those two machines to avoid network traffic
because of using these tools if possible.
So, in your case, yes, they have affect on the SQL Server somehow.
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> Hi,
> I was running Performace Monitor and SQL Profiler to keep track of
> changes on the server for period of a week. And for some reason this ONE
> table keeps getting locked out. The third party vendor suggested that SQL
> Profiler creates locks again the users database.
> I though the SQL Profiler acquire any locks against user databases, it is
> getting information form SQL API directly?
> Also the performace counters I read had no affect on the SQL Server...
> Is my understanding wrong? Can someone clearify it for me? Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem Ã?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > Hi,
> >
> > I was running Performace Monitor and SQL Profiler to keep track of
> > changes on the server for period of a week. And for some reason this ONE
> > table keeps getting locked out. The third party vendor suggested that SQL
> > Profiler creates locks again the users database.
> >
> > I though the SQL Profiler acquire any locks against user databases, it is
> > getting information form SQL API directly?
> >
> > Also the performace counters I read had no affect on the SQL Server...
> >
> > Is my understanding wrong? Can someone clearify it for me? Thanks!
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
>|||Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
> > Well... I'm not sure if SQL Profiler may cause locks or not however
> > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > Monitor) from another machine. Because they effect performance on the
> > machine they work. However, in this case they also recommend using a
> > dedicated connection between those two machines to avoid network traffic
> > because of using these tools if possible.
> >
> > So, in your case, yes, they have affect on the SQL Server somehow.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > Hi,
> > >
> > > I was running Performace Monitor and SQL Profiler to keep track of
> > > changes on the server for period of a week. And for some reason this ONE
> > > table keeps getting locked out. The third party vendor suggested that SQL
> > > Profiler creates locks again the users database.
> > >
> > > I though the SQL Profiler acquire any locks against user databases, it is
> > > getting information form SQL API directly?
> > >
> > > Also the performace counters I read had no affect on the SQL Server...
> > >
> > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > >
> > > --
> > > Mohit K. Gupta
> > > B.Sc. CS, Minor Japanese
> > > MCTS: SQL Server 2005
> >|||Thanks Guys ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
> > I see, I will have to check that out on if it has any affect on user database
> > or not. But today I did a trace that logged about 250,000 records on
> > another database, and that server didn't notice any deadlocks or lock
> > timeouts. But I did take your advice and this time ran the SQL Monitor on a
> > different computer. So maybe that had some affect.
> >
> > So I do suspect me running it on the serer caused locks but might have
> > caused some slow down. So I think the application is either timing out too
> > quickly or something else is a miss. Thank-you for your reply.
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> > > Well... I'm not sure if SQL Profiler may cause locks or not however
> > > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > > Monitor) from another machine. Because they effect performance on the
> > > machine they work. However, in this case they also recommend using a
> > > dedicated connection between those two machines to avoid network traffic
> > > because of using these tools if possible.
> > >
> > > So, in your case, yes, they have affect on the SQL Server somehow.
> > >
> > > --
> > > Ekrem Ã?nsoy
> > >
> > >
> > >
> > > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > > Hi,
> > > >
> > > > I was running Performace Monitor and SQL Profiler to keep track of
> > > > changes on the server for period of a week. And for some reason this ONE
> > > > table keeps getting locked out. The third party vendor suggested that SQL
> > > > Profiler creates locks again the users database.
> > > >
> > > > I though the SQL Profiler acquire any locks against user databases, it is
> > > > getting information form SQL API directly?
> > > >
> > > > Also the performace counters I read had no affect on the SQL Server...
> > > >
> > > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > > >
> > > > --
> > > > Mohit K. Gupta
> > > > B.Sc. CS, Minor Japanese
> > > > MCTS: SQL Server 2005
> > >
Performace Monitor and SQL Profiler
I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem ?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
>
Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
|||Thanks Guys ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem ?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
>
Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
|||Thanks Guys ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
Saturday, February 25, 2012
Perfomance monitor
Hi all,
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.com/performance_monitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.com/performance_monitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
> > Hi all,
> > I am thinking how bad it can be if I have perfomance monitor and sql
> > profiler running for my production SQL server? do they affact the perfomance
> > a lot? If there a better way for me to do the samilar thing?
> >
> > Thanks.
I am thinking how bad it can be if I have perfomance monitor and sql
profiler running for my production SQL server? do they affact the perfomance
a lot? If there a better way for me to do the samilar thing?
Thanks.See if this helps:
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Tips On Using the SQL Server Profiler
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
Tips for Using Performance Monitor
http://www.sql-server-performance.com/performance_monitor_tips.asp
AMB
"Catelin Wang" wrote:
> Hi all,
> I am thinking how bad it can be if I have perfomance monitor and sql
> profiler running for my production SQL server? do they affact the perfomance
> a lot? If there a better way for me to do the samilar thing?
> Thanks.|||Very good infomation. Thanks a lot .
"Alejandro Mesa" wrote:
> See if this helps:
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> Tips On Using the SQL Server Profiler
> http://www.sql-server-performance.com/sql_server_profiler_tips.asp
> Tips for Using Performance Monitor
> http://www.sql-server-performance.com/performance_monitor_tips.asp
>
> AMB
> "Catelin Wang" wrote:
> > Hi all,
> > I am thinking how bad it can be if I have perfomance monitor and sql
> > profiler running for my production SQL server? do they affact the perfomance
> > a lot? If there a better way for me to do the samilar thing?
> >
> > Thanks.
Perfmon/Profiler confusion
I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>
|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).
|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>
|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Perfmon/Profiler confusion
I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a proc
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Perfmon/Profiler confusion
I am trying to troubleshoot crazy values for SQL Statistics/SQL
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a pro
c
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Compilations/sec in Perfmon. So I started up the Profiler,removed all
the default events and added Stored Procedures/SP:Recompile event class.
I am seeing numbers like 20 SQL Compilations/sec average, however, the
Profiler only records a few over a 10 minute period. What am I missing
here? Could it be that SQL Server is not really doing recompilations,
but actual compilations (as if it sees the stored proc for the first
time)? Anyway, I am lost.
I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).Hi Frank
These counters are measuring completely different things.
Although I don't know everything that is included in the Perfmon SQL
Statistics/SQL
Compilations/sec value, I know it is much more than just stored procedure
compilations.
Also, the SP:Recompile event in Profiler does not measure ALL
recompilations. It only counts those that occur after the procedure has
already started executing, and something in the proc forces SQL Server to
stop and compile the proc again. It does not count those cases where a proc
is recompiled before it even starts executing.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:OiS5lc05GHA.2104@.TK2MSFTNGP06.phx.gbl...
>I am trying to troubleshoot crazy values for SQL Statistics/SQL
>Compilations/sec in Perfmon. So I started up the Profiler,removed all the
>default events and added Stored Procedures/SP:Recompile event class.
> I am seeing numbers like 20 SQL Compilations/sec average, however, the
> Profiler only records a few over a 10 minute period. What am I missing
> here? Could it be that SQL Server is not really doing recompilations, but
> actual compilations (as if it sees the stored proc for the first time)?
> Anyway, I am lost.
> I am on SQL Server 2000 (v. 8.00.2039 - i think, sp4).|||Kalen Delaney wrote:
> Hi Frank
> These counters are measuring completely different things.
> Although I don't know everything that is included in the Perfmon SQL
> Statistics/SQL
> Compilations/sec value, I know it is much more than just stored procedure
> compilations.
I've looked around but I can't find a good definition of what SQL
Statistics/SQL Compilations/sec in PerfMon measures. Any idea of where
I can find it?
Also, is it generally a bad thing to have a high number (like 30-40) in
SQL Statistics/SQL Compilations/sec value?
Regards.
> Also, the SP:Recompile event in Profiler does not measure ALL
> recompilations. It only counts those that occur after the procedure has
> already started executing, and something in the proc forces SQL Server to
> stop and compile the proc again. It does not count those cases where a pro
c
> is recompiled before it even starts executing.
>|||On Wed, 04 Oct 2006 10:15:53 -0700, Frank Rizzo <none@.none.com> wrote:
>Also, is it generally a bad thing to have a high number (like 30-40) in
>SQL Statistics/SQL Compilations/sec value?
Well they're not free.
J.
Subscribe to:
Posts (Atom)