We have recently moved our database to a SAN and I am trying to monitor performance. Does anyone know of a good way to monitor performance going to the SAN? I have been watching things like %Disk time and Avg Disk Queue Length, but does anyone know if these are acurrate when referring to a SAN? My %Disk Time is going up to numbers as high as 1200. The Disk queue length seems kind of low for seeing that high disk time as well.
Any help would be great.
Thanks much.I a junior DBA here and the senior dba in charge of our sql cluster monitors disk write bytes/sec, disk read bytes/sec, and average disk queue length among others things and I'm pretty sure our stuff's on a SAN. He says anything approaching 1 or above for queue length is cause for alarm. I think read & write throughput are specific to the hardware.
Wish I could give you a better answer...|||So what the performance monitor is telling you is that your disk is writing twelve times as fast as it can (not real likely using conventional hardware, but it happens frequently using a SAN). That would tend to lead to really short disk queue lengths.
I'd suggest that you talk to your SAN vendor. They should be able to give you some really good ideas on what you can monitor, especially within the SAN drivers themselves (if you are using SAN specific drivers).
You obviously have to adjust any counters delivered with the OS, since they are tuned for a very different world than a SAN. A good first guess is to scale the stock values by about a factor of 20, but you'll probably have to adjust that once you get a better feel for how your particular configuration performs.
-PatP|||We already did ask the SAN vender and they told us to purchase this software package they created to monitor performance. Personally I don't think that is necessary, so I was hoping I could monitor performance through these counters. I just don't know how they should be adjusted for the SAN.
Showing posts with label monitors. Show all posts
Showing posts with label monitors. Show all posts
Saturday, February 25, 2012
Performance Monitors Start/Stop 7AM
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
command_logman.mspx" target="_blank">http://www.microsoft.com/resources/...and_logman.mspx
command_relog.mspx" target="_blank">http://www.microsoft.com/resources/...mand_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx
.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
command_logman.mspx" target="_blank">http://www.microsoft.com/resources/...and_logman.mspx
command_relog.mspx" target="_blank">http://www.microsoft.com/resources/...mand_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx
.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
Performance Monitors Start/Stop 7AM
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.
There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/d...nd_logman.mspx
http://www.microsoft.com/resources/d...and_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.
There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/d...nd_logman.mspx
http://www.microsoft.com/resources/d...and_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
Performance Monitors Start/Stop 7AM
I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx
--
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx
--
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>
performance monitors on the SQL 2005
Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScript
Take a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScript
Take a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
performance monitors on the SQL 2005
Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
--
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
--
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
performance monitors on the SQL 2005
Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_read
s)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.c...
p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_read
s)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.c...
p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
Performance Monitors on 64bit OS
I am using a 3rd party tool to bind to the perf monitors, with no success. I
n
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks
n
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks
Performance Monitors on 64bit OS
I am using a 3rd party tool to bind to the perf monitors, with no success. In
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
Thanks
OK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
Thanks
OK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks
Performance Monitors on 64bit OS
I am using a 3rd party tool to bind to the perf monitors, with no success. In
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks
Monday, February 20, 2012
Performance Monitor log files
For the SQL Server running here we have setup a log file that monitors all the activities while the server is running, such as processor percentage being used over the day, CPU percentage taken by SQL Server etc. The problem I'm facing is that the log files (which are in PERFMON format) are recorded over a period of a business day for 30 days. Now I want to get the measurements for all the 30 days combined into one file where i can analyze all the measurement over 30 days.
Thanks in advance.HI
I dont know of any way to combine log files. The best you can do is establish numeric values then manually put these into a spreadsheet if you want a graph.
In future, just run the perfmon monitoring for 30 days......
Hope this helps,
SG
Thanks in advance.HI
I dont know of any way to combine log files. The best you can do is establish numeric values then manually put these into a spreadsheet if you want a graph.
In future, just run the perfmon monitoring for 30 days......
Hope this helps,
SG
performance monitor lifetime
from what i've read so far, the performance monitor's data is not
instantaneous, rather collected historically since the last reboot. is
this correct? i've also read (though i can't remember where), that
there was some win2k/service pack combination that allowed for
instantaneous monitoring. i've got a feeling the monitors values are
getting skewed by past (good) performance and would like to see
instantaneous values (w/out having to reboot). thanks,
arthur
There's no one setting that control all counters. You have to read the counter definition about how
it is measured. The ones that you are probably thinking of is Buffer Cache counters that now
averages from about 2000 to 3000 last samples (changed in sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1107372326.151669.237260@.f14g2000cwb.googlegr oups.com...
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
>
|||arthur wrote:
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
Performance monitor values vary by counter type. You can read about them
in MSDN. If you are dealing with a counter that does not display an
instantaneous value, all you need to do is compare it to the previous
value. That's essentially what sp_monitor does (as does perfmon). That
is, it keeps track of previous values and capture date/time in order to
perform the necessary calculations. Rebooting the server won't help
because once you do, you're back to have to calculate each time.
David Gugick
Imceda Software
www.imceda.com
instantaneous, rather collected historically since the last reboot. is
this correct? i've also read (though i can't remember where), that
there was some win2k/service pack combination that allowed for
instantaneous monitoring. i've got a feeling the monitors values are
getting skewed by past (good) performance and would like to see
instantaneous values (w/out having to reboot). thanks,
arthur
There's no one setting that control all counters. You have to read the counter definition about how
it is measured. The ones that you are probably thinking of is Buffer Cache counters that now
averages from about 2000 to 3000 last samples (changed in sp3).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"arthur" <alangham@.gmail.com> wrote in message
news:1107372326.151669.237260@.f14g2000cwb.googlegr oups.com...
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
>
|||arthur wrote:
> from what i've read so far, the performance monitor's data is not
> instantaneous, rather collected historically since the last reboot. is
> this correct? i've also read (though i can't remember where), that
> there was some win2k/service pack combination that allowed for
> instantaneous monitoring. i've got a feeling the monitors values are
> getting skewed by past (good) performance and would like to see
> instantaneous values (w/out having to reboot). thanks,
> arthur
Performance monitor values vary by counter type. You can read about them
in MSDN. If you are dealing with a counter that does not display an
instantaneous value, all you need to do is compare it to the previous
value. That's essentially what sp_monitor does (as does perfmon). That
is, it keeps track of previous values and capture date/time in order to
perform the necessary calculations. Rebooting the server won't help
because once you do, you're back to have to calculate each time.
David Gugick
Imceda Software
www.imceda.com
Subscribe to:
Posts (Atom)