sql server 2005 performance

Ramesh, it doesn't hurt to write a little bit more detail here if you want to get help. I dont know what kind of 'slow query' you are after from your question. Having said that if you are not comfortable using profiler you have other options to explore before you dive in with profiler.

1. You can use [Activity Monitor][1] to get a feel of whats going on on your instance. This feature is shipped only with 2008 but it is backward compatible with 2005 and works when you connect to 2005 instances from a SQL Server 2008 Client.
2. You can also use [performance dashboard][2] to troubleshoot performance problems. You can download it [here][3].
3. you can use DMV's. There are bunch of scripts out there contributed by the awesome community members. In fact Glenn Berry released revised version of his [SQL Server 2005 Diagnostic Information Queries][4] yesterday.

Good Luck. [1]: http://www.simple-talk.com/sql/learn-sql-server/management-studio-improvements-in-sql-server-2008/ [2]: http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx [3]: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en2.1.2. [4]: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7121.entry

How deeply do you need to investigate?

You can open Management-> Monitor from the SSMS GUI...

Or right-click on the instance and drill down through Reports->Standard Reports->... There are a plethora of graphical reports there to get you started.

You can run sp_who also.

You can also use Windows Performance Monitor to grab some metrics.

Until you have an idea of what you're trying to solve it is hard to predict the best tool for the job.

Absolutely, slow compared to what?

But, to identify long running queries, you can do one of two things. First, and usually best, set up a server-side trace to capture RPC:Complete and SQLBatch:Complete events. This should be captured to a file and then you can load it into a database, clean it, and run queries against it to identify the longest running procs.

Another option, if the procs are currently in cache, is to use sys.dm_exec_query_stats DMO. This DMO has aggregate information about the queries in cache, so you can see the longest running, most frequently called, etc. You can also combine it with other DMOs to see the SQL being called and the execution plan used.

Slow compared to what? If you don't know what the normal execution time for a query is, you'll have trouble determining if it's running "slowly"

To obtain query run times, you can set up a Profiler Trace to monitor query duration. You can then go through the results and look at queries that appear to be slow (by whatever measure you wish to apply).

If you gather these execution times periodically and save the results, you will then have a performance baseline for your queries and can better establish what "slow" means in relation to normal execution times.