Hi, I need to perform analysis on TomCat app Server for Cognos. My Cognos report are running very slow. I was wondering what procedure can we follow and/or which tools can we use to perform analysis, what are the things metrics we are intersted in observing, I am new to DBA so your recommendation would be really helpful. Thank you in advance :) Rgds, SqlHungry
Performance monitoring of SQL Server is a huge topic. I have two chapters on just monitoring in my book, SQL Server 2012 Query Performance Tuning:
http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1340361503&sr=8-1&keywords=fritchey . To get you started, one thing you can do is look at the wait statistics to see what is causing things to run slow on your system, sys.dm_os_wait_stats: SELECT * FROM sys.dm_os_wait_stats ORDER BY max_wait_time_ms DESC You'll have to look up what each of the waits means online. The other thing you can do for a quick look at what might be running slow on the system is use sys.dm_exec_query_stats. Run a select * against it and you'll see all the data available. You can combine it with sys.dm_exec_sql_text to get the query statements and sys.dm_exec_query_plan to see the execution plan in order to understand what's running slow. SELECT * FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp Those are rough metrics that can get you going, but there's just tons & tons more information on how to interpret this stuff and what to do about it.