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.
Good Luck. : http://www.simple-talk.com/sql/learn-sql-server/management-studio-improvements-in-sql-server-2008/ : http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx : http://www.microsoft.com/downloads/en/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en2.1.2. : http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!7121.entry
answered Oct 26 '10 at 07:33 AM
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.
answered Oct 26 '10 at 06:48 AM
Grant Fritchey ♦♦
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.
answered Oct 26 '10 at 06:41 AM