asked on

Query Performance Tuning Tips

I have a query that takes 1.5 minutes to run (depending on how much data is returned in the query) and I want to performance tune the database for the query.

I have run the "Display Estimated Execution Plan" in SQL Server Management studio and added all the keys suggested.

Now I am adding indexes for any of the fields used in the where clauses and JOIN clauses.

The Client.ClientId is already a primary Index.
There is a PK Key and a PK Index on that field (for all my tables same idea).

But other tables: For instance if it says:
INNER JOIN Client on Client.ClientId = Employee.ClientId

I created an Index on the Employee table, ClientId.

So anything used in a where clause I add another index on that column.

Is that right? Any other ideas?

thanks.

SOLUTION

edtechdba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

lcohan

Did you looked at your query execution plan in SSMS? At least estimated plan - under Query menu option "Display Estimated query plan"

Starr Duskk

ASKER

Only 2 LEFT OUTER JOINs compared to 8 INNER JOINs

Starr Duskk

ASKER

Icohan,

>>Did you looked at your query execution plan in SSMS? At least estimated plan - under Query menu option "Display Estimated query plan"

Yes, I said:
>>I have run the "Display Estimated Execution Plan" in SQL Server Management studio and added all the keys suggested.

Any thing else I should do with that? Unless it errored, I didn't know what else to do with it.

Kent Olsen

Can you post the query?

Starr Duskk

ASKER

I will look at my joins and orders...
meanwhile, is adding all the indexes heading down the right track? Or is that a waste of time?

thanks!

SOLUTION

Kent Olsen

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

lcohan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

>>check your logical/physical reads

Where do I check this?

SOLUTION

Scott Pletcher

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

I have checked that my field names are sargable... thanks for that suggestion. Didn't know about that. They are though.

Also, with adding indexes and switching my one left outer join to the bottom with the other left outer join, I have lowered the query from 1:32 to 53 seconds.

Still cleaning it up and adding a few more indexes. I've read the criteria on adding indexes, and I'm pretty certain the ones I'm adding will be important and won't hinder the CUD operations enough to make it a detriment.

I'll get back to ya'll when I'm entirely done.

thanks!

Starr Duskk

ASKER

Got it down to 36 seconds from 1:48!

SOLUTION

lcohan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Starr Duskk

ASKER

Should I have an index on the group by clauses?

thanks.

Starr Duskk

ASKER

Icohan,

Does the Configure Data Collection Wizard Progress last a LONG time? Because it's been going for many minutes. And I don't know if something's wrong with it.

thanks!

Starr Duskk

ASKER

Also, Icohan, I installed the first thing you suggested and my results were like below (tons more)... nothing reporting any problems or missing indexes.

Table 'Client'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JobType'. Scan count 6, logical reads 74, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Employee'. Scan count 6, logical reads 2288, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UserProfile'. Scan count 6, logical reads 178, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'UserRole'. Scan count 6, logical reads 1118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Open in new window

Is the above what I should see? No fancy graphs or anything? It's when I click the Message tab after a query is run.

thanks.

Kent Olsen

>> Should I have an index on the group by clauses?

Probably not. The group by function will be applied after the last join and won't read any of the base tables.

Starr Duskk

ASKER

I have cut the query down quite a bit, so I am going to award points.

However, I may be back and show my actual query, because I'd like to tune it better, because it is still taking a long time to run. But I'll open a new ticket for that.

thanks all!

Starr Duskk

ASKER

Thanks all! Every suggestions was very valuable! I hope everyone is happy with the scoring results. thanks!