Query Performance Checking.

Question

Query Performance Checking.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 5, 2009 at 8:23 am

#134029

Hello Experts
For the last few days, I have noticed that during the afternoon time (between 12 - 2p..m), queries take long time to eexecute the results. But the same query executes well in other time. The batch jobs which runs by the time on the Prod server is taking long time.
Below are the activities done by my end:
1. Checked for blocking running sp_who2
2. Executed Profiler to check what are people running the queries.
3. Checked the tempdb whether the mdf and ldfs are full. Everything was normal.
4. Checked the SP...and found that the tables are having indexes.
5. Killed the users running select statments.
==================================================================
Most strange thing is, the same query runs great and having good perf in other time.
Could you please suggest me whether any good steps are there to check?
I asked one of my senior colleagues (DBA), he asked me to check the network performance(byte transfer\sec). What do I want from you is, if you can let me know for this type of situation where I get user requests from users about the sudden slow down of query performance, the Actual steps which I need to check in sequence to "Quickly" resolve the issue.
We have SLAs defined andfor sudden high priority requests I need to provide the resolution within 3\4 hrs.
Can you please suggest me?
Regards
Sourav
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

Sourav Mukherjee (7/5/2009)
1. Checked for blocking running sp_who2
2. Executed Profiler to check what are people running the queries.
3. Checked the tempdb whether the mdf and ldfs are full. Everything was normal.
4. Checked the SP...and found that the tables are having indexes.
5. Killed the users running select statments.

You should use profiler effectively to find out what processes are taking more cpu time and IO subsystem. You can then fine-tune the queries that are affecting the system. Also, check for fragmentation of indexes, you might need to rebuild or reorganize indexes. Also update statistics on the tables.

The following two articles by Gail will help you find out the culprits and help you resolve the issue.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/">

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

You should run profiler as suggested in these articles during peak time for around half an hour or so and then analyse the situation. Try out the processes/tricks mentioned in the articles and let us know the results.

Edit : Fixed the Quote

Pradeep Singh

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thanks for providing the links. Quite useful.

However, What did I find from the link-1 is: it is a complete analysis by running profiler which might take couple of days to troubleshoot.

- Other than that is there any quick reference available?

- How to check the network performance of the server? Is there any tools available? Can it happen due to sudden network issue?

- What exactly can be check from the 'Activity Monitory'. Does this help in this type scenario?

Any other suggestions?

Regards

Sourav

Thanks.

ps. One Orange Chip Points: 29422 More actions · Answer 3

Sourav Mukherjee (7/5/2009)
- Other than that is there any quick reference available?

Performance issues are perennial issues and have to be dealt with carefully to have them sorted permanently, so what it takes 2 days of yours.

- How to check the network performance of the server? Is there any tools available? Can it happen due to sudden network issue?

Use system monitor, check out for %Network Utilization under Network Segment counter. The value should be less than 30%.

Pradeep Singh

Nadrek SSC-Insane Points: 20039 More actions · Answer 4

Use Perfmon on the server, look at disk statistics (Avg disk sec/read and write, disk queue lengths), CPU statistics, and memory statistics (Page Life Expectancy, among others). Check for an anti-virus scan or other non-SQL Server activity on any shared resource.

Also, check on whether the physical spindles are shared with other apps/servers (particularly on a SAN).

For the network, is something else using up shared bandwidth?

shiv-356842 SSCarpal Tunnel Points: 4312 More actions · Answer 5

Hi Thank you your reply

How can we check disk statics & other

we really appreciate

Thanks

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

Go to a command prompt.

perfmon

In the second group of three icons, you'll see a line graph, a bar graph, and a report. Click on the "View Report" (third) icon.

Click the + icon, just to the right of the "View Report" icon.

Under "Performance Object", select LogicalDisk or PhysicalDisk, as you prefer (Logical Disk is not always present).

Select "All Instances" on the right hand option button pair.

Go through the "Select counters from list" list and select the important counters (multi-select works on this list), then hit "Add".

Repeat for other "Performance Object" types.

Watch the values.