new DM Views in 2005, which one to identify bottleneck?

Hi,
in SQL 2000 I'm using the sp_who1 storedprocedure to identify my longest
running queries and identify what is causing the delay.
sp_who1 is not a standard SP, its one found on the net which display more
info then the sp_who or sp_who2 SP.
but with 2005, what are the new options and information we can gather to
identify bottlnecks?
I want to know if my longest queries wait for the CPU, disk, network etc...
in my SQL 2000 servers, the most common waiting issue I have is the
PAGEIOLATCH wait type.
So I want to monitor my new servers and see the same queries and identify if
the same behavior appears. (and any other good information too)
any guide/recommandations?
thanks.
Jerome.Take a look at
http://www.microsoft.com/technet/pr...fprb.mspx#EYBAG
Regards
Amish Shah|||Or
The following DMV query can be used to find
which batches/requests are generating the most I/O
select top 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by
(total_logical_reads + total_logical_writes) Desc
--Getting text
select text
from sys.dm_exec_sql_text(
0x02000000134BE5072AFF3BDC70CF7C550689BA
DAD0E18154)
"Jeje" <willgart@.hotmail.com> wrote in message
news:OaPjQ59cGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> in SQL 2000 I'm using the sp_who1 storedprocedure to identify my longest
> running queries and identify what is causing the delay.
> sp_who1 is not a standard SP, its one found on the net which display more
> info then the sp_who or sp_who2 SP.
> but with 2005, what are the new options and information we can gather to
> identify bottlnecks?
> I want to know if my longest queries wait for the CPU, disk, network
> etc...
> in my SQL 2000 servers, the most common waiting issue I have is the
> PAGEIOLATCH wait type.
> So I want to monitor my new servers and see the same queries and identify
> if the same behavior appears. (and any other good information too)
> any guide/recommandations?
> thanks.
> Jerome.
>|||thanks.
its a good starting point.
"amish" <shahamishm@.gmail.com> wrote in message
news:1147240359.431408.226910@.e56g2000cwe.googlegroups.com...
> Take a look at
> http://www.microsoft.com/technet/pr...fprb.mspx#EYBAG
> Regards
> Amish Shah
>

new DM Views in 2005, which one to identify bottleneck?

Hi,
in SQL 2000 I'm using the sp_who1 storedprocedure to identify my longest
running queries and identify what is causing the delay.
sp_who1 is not a standard SP, its one found on the net which display more
info then the sp_who or sp_who2 SP.
but with 2005, what are the new options and information we can gather to
identify bottlnecks?
I want to know if my longest queries wait for the CPU, disk, network etc...
in my SQL 2000 servers, the most common waiting issue I have is the
PAGEIOLATCH wait type.
So I want to monitor my new servers and see the same queries and identify if
the same behavior appears. (and any other good information too)
any guide/recommandations?
thanks.
Jerome.Take a look at
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Regards
Amish Shah|||Or
The following DMV query can be used to find
which batches/requests are generating the most I/O
select top 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by
(total_logical_reads + total_logical_writes) Desc
--Getting text
select text
from sys.dm_exec_sql_text(
0x02000000134BE5072AFF3BDC70CF7C550689BADAD0E18154)
"Jeje" <willgart@.hotmail.com> wrote in message
news:OaPjQ59cGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> in SQL 2000 I'm using the sp_who1 storedprocedure to identify my longest
> running queries and identify what is causing the delay.
> sp_who1 is not a standard SP, its one found on the net which display more
> info then the sp_who or sp_who2 SP.
> but with 2005, what are the new options and information we can gather to
> identify bottlnecks?
> I want to know if my longest queries wait for the CPU, disk, network
> etc...
> in my SQL 2000 servers, the most common waiting issue I have is the
> PAGEIOLATCH wait type.
> So I want to monitor my new servers and see the same queries and identify
> if the same behavior appears. (and any other good information too)
> any guide/recommandations?
> thanks.
> Jerome.
>|||thanks.
its a good starting point.
"amish" <shahamishm@.gmail.com> wrote in message
news:1147240359.431408.226910@.e56g2000cwe.googlegroups.com...
> Take a look at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
> Regards
> Amish Shah
>