x

sql server 2005 performance

hi team, i am in need to identify the slow running query in the sql server 2005 standard edition. how can i do it so
more ▼

asked Oct 26, 2010 at 06:27 AM in Default

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

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
more ▼

answered Oct 26, 2010 at 07:33 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 26, 2010 at 07:25 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 26, 2010 at 06:48 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 26, 2010 at 06:41 AM

KenJ gravatar image

KenJ
20k 1 3 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x249

asked: Oct 26, 2010 at 06:27 AM

Seen: 632 times

Last Updated: Oct 26, 2010 at 12:18 PM