What's the best way to monitor the causes of timeouts on SQL 2005?
Michael MacGregor
Database ArchitectMichael
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/archiv...lt.aspx
http://download.microsoft.com/downl...otPerfProbs.doc
http://download.microsoft.com/downl...aits_Queues.doc
John|||Thanks guys.
Really helpful.
Filed for future reference.
MTM
Wednesday, March 21, 2012
Monitoring timeouts?
Labels:
2005michael,
causes,
database,
macgregordatabase,
microsoft,
monitor,
monitoring,
mysql,
oracle,
server,
sql,
timeouts
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment