Monitoring timeouts?

What's the best way to monitor the causes of timeouts on SQL 2005?
Michael MacGregor
Database Architect
Michael
In most cases bad written queries cause timeouts. I'd indentify a long
running queries and try to optimize them
select i.name
from sys.indexes i
where i.object_id=object_id('<table_name>') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
--Index usage
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
All indexes which havent been used yet can be retrieved with the following
statement:
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OaRypwmlHHA.2552@.TK2MSFTNGP06.phx.gbl...
> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
>
|||Hi Michael
"Michael MacGregor" wrote:

> What's the best way to monitor the causes of timeouts on SQL 2005?
> Michael MacGregor
> Database Architect
What kind of timeout? For instance
http://msdn2.microsoft.com/en-us/library/ms190181.aspx or you may want to
look at SQL Profiler and lock escallations/deadlocks/query plans etc... to
narrow down what is causing the timeouts possibly with Performance counters
if the machine is generally underperforming e.g.
http://support.microsoft.com/kb/224587/EN-US/
Other resources:
http://blogs.msdn.com/sqlcat/archive/tags/Performance+and+Scalability/default.aspx
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
John
|||Thanks guys.
Really helpful.
Filed for future reference.
MTM