in my previous workplace we used oracle enterprise manager for viewing statistics and bottlenecks of queries that are running against oracle10g. In my new workplace we use Microsoft SQL Server (2005). Is there any tool like enterprise manager of oracle in SQL Server - I need to see all the jdbc SQL queries i make and how long they are taking in the db..
thanks
Use SQL Server Profiler for tracing DB queries and such.
If you are looking for costs of queries and how they break down, you should use the Query Analyzer that is built into SSMS.
For SQL 2005 SP2 and later, you can download the Performance Dashboard Reports from MS (free).
Quote:
Common performance problems that the dashboard reports may help to resolve include: - CPU bottlenecks (and what queries are consuming the most CPU) - IO bottlenecks (and what queries are performing the most IO). - Index recommendations generated by the query optimizer (missing indexes) - Blocking - Latch contention
The information shown in the reports is from the dynamic management views which you could query yourself if you didn't want to download this addon.
A combination of 'SQL Server Profiler', 'SQL Server Management Studio' and 'Database Engine Tuning Advisor' will be your friend. Have a look at showing the estimated and actual execution plans using SSMS, or create trace files with SQL Server Profiler (using the Tuning template) to feed to the Database Engine Tuning Advisor