Query Performance

Question

Query Performance

trivedi.nikesh

Right there with Babe

Points: 715
More actions
April 22, 2010 at 3:52 am

#146598

Hi All,
Last week we designed a query to generate a report. Initially this query was running fine fetching all records in 10 sec, but this week it is running quite slow on the system and taking more than 2 min to fetch same count of records. We run this query on our test servers on same data, there it is fetching the results in 11 seconds.
Please advice me that, where do I look to improve the performance for this query. What software and hardware factors are responsible for this situation.
Nikesh

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

trivedi.nikesh (4/22/2010)
Hi All,
Last week we designed a query to generate a report. Initially this query was running fine fetching all records in 10 sec, but this week it is running quite slow on the system and taking more than 2 min to fetch same count of records. We run this query on our test servers on same data, there it is fetching the results in 11 seconds.
Please advice me that, where do I look to improve the performance for this query. What software and hardware factors are responsible for this situation.
Nikesh

It could be any number of things. The first thing that leaps to mind is parameter sniffing. Check the execution plans when the query runs fast and when it runs slow. Also, you could have statistics that are out of date due to data changes. Indexes could be fragemented. You might be seeing contention for resources on the server... The list goes on.

In addition to looking at the execution plans, I'd suggest gathering wait and queue statistics on the server. Here's an excellent article from Microsoft on how to do this.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

trivedi.nikesh Right there with Babe Points: 715 More actions · Answer 2

Dear Sir,

I am heartily thankful to you. I will apply your suggested measures.

Please correct me if I am wrong

Reason behind Query is not performing good due to (in case it is tuned):

1. Fragmentation of Pages

2. Statistics are not updated

3. No sufficient Memory

4. More I/O

5. Blockings

6. Huge CPU utilization

Please update this list if some thing is missing here.

Thanks again.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

trivedi.nikesh (4/23/2010)
Dear Sir,
I am heartily thankful to you. I will apply your suggested measures.
Please correct me if I am wrong
Reason behind Query is not performing good due to (in case it is tuned):
1. Fragmentation of Pages
2. Statistics are not updated
3. No sufficient Memory
4. More I/O
5. Blockings
6. Huge CPU utilization
Please update this list if some thing is missing here.
Thanks again.

That's the basic list, but based on that list, I wouldn't assume the query was tuned. I would assume there were tuning opportunities available.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

trivedi.nikesh (4/23/2010)
Dear Sir,
I am heartily thankful to you. I will apply your suggested measures.
Please correct me if I am wrong
Reason behind Query is not performing good due to (in case it is tuned):
1. Fragmentation of Pages
2. Statistics are not updated
3. No sufficient Memory
4. More I/O
5. Blockings
6. Huge CPU utilization
Please update this list if some thing is missing here.
Thanks again.

You missed two...

7. Data in tables increased

8. Different hardware either for the server or the pipe.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

trivedi.nikesh Right there with Babe Points: 715 More actions · Answer 5

Thank you for update in this list, I will work on this also.