Query Tuning
-
Thanks for all your replies.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Dear Sugesh,
FYI..
http://support.microsoft.com/kb/243589/EN-US/ this links is good.
Minaz
"More Green More Oxygen !! Plant a tree today"
-
Minaz,
I shall read the link and follow suggestion given there. Is it poosible to see exactly which part in a query needs to be tuned.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Just I wanted to know if the joined column are indexed ?? the plan shows there are nested loop joins, and according to BOL :
"A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice."
Minaz
"More Green More Oxygen !! Plant a tree today"
-
All the columns used in the join are indexed. Infact the execution plan was showing a hash join and i have forced it to be a nested join using inner loop hint in the join statement.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
OK..
1. What I see is lots of cluster index seek. that tells me correct indexes are not in used. Have you use any index hints ? If not then once the right indexes are used then update the index statistics.
3. check for PARALLELISM by running sp_configure, if yes then you can try forcing a non-parallel plan by using the OPTION (MAXDOP 1) .
4. Clean the procedure cache
Try running the query...
Minaz...
"More Green More Oxygen !! Plant a tree today"
-
May I know what "INNER LOOP JOIN" is?
Regards,
K. Matsumura
-
INNER LOOP join is same as the JOIN but it forces a nested loop join to occur instead of a hash/merge join. I read this in a blog and used it in my query.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
well I'd take the join hints out for a start. I also have to say that what you're asking is pretty basic and adding MCDBA after your name will probably not get you a lot of sympathy, sorry.
A bookmark lookup is effectively using pointers from a secondary index to return data from the underlying table, if it's a practical proposition I'd try replacing that part of the query with a covered index thus eliminating the table from the join.
Sorry, now I'm posting I can't see your original post! Read up on Kimberley Tripp and Kalen Delaney blogs/web sites for articles on query tuning.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
off the top of my head and if this were a super critical query I'd be trying to use covered indexes where I could.
In no particular order these are the columns which should make up those indexes:-
PaymentType = IsAutomatic
,[Description],PaymentTypeCode,NODB
ExternalRelationAccount = PaymentTypeCode
, AccountID,NODB, PaymentTypeNODB
ExternalRelationAddress = Name1Line1
,Name1Line2, ExternalRelationNumber, NODB, IsActive, AddressTypeCode
[ORDER] = ExternalRelationNumber
, ExternalRelationNumberNODB, AccountID, AccountNODB, NODB, IsActive, PickListGenerationDate
+
maybe OrderNumber
You're always going to do a bookmark on Order unless you build a very wide covered index, if order has 300 columns then maybe. For the first three tables there should be one index containing all the columns, you'll have to figure out the best order for the columns. For the Order table you'll have to test if one or several indexes give best performance.
I only did a quick scan so if i missed a column, apologies, but you should get the idea.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
Some clarifications from myside.
1. I have already said that all the columns in the join are indexed. I accept that there is no covering index but the columns included in where clause has an single index on each table.
2. I know its not good to force a query hint but with out the query hint the query took almost 3 mintues to run and there was a hash/merge join. I read some websites and tried to force the query to use a nested loop join. There by my query executed within a minute. But there was a book mark lookup.
I was puzzled by this because one of the sites that i read said that book mark lookup would be bad for a query.
So i was not aware what to do. I suggested for a covering index but the developement manager says that he is not happy with the suggestion.
Atlast i came up to the forums and posted my question here. I know sometimes people forget basic things and question others.
But your words were too ......... and remember that microsoft does not grant a certification for a user with sympathy. They do keep tests to check our knowledge and then certify us.
This forum is to share the knowledge what we have and learn from others experience. Its all about learning and learning and more learning.
If you say that you answer to questions raised by forum members out of sympathy everytime then request you not to answer my questions if that's the case.
I am very sorry if my words have hurt you. I can't avoid writing this.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
1) When you want help tuning a query, ESPECIALLY a complex one such as this, you simply MUST include table definitions (to include indexes and keys, etc) and table data counts.
2) Perhaps your statistics are out of date and that is why the optimizer is choosing hash/merge joins.
3) The slowness from non-loop-forced query plan could be due to having very fat tables (definitions woulda helped here). This can be overcome with either faster hardware (especially disk system) or covering index(es). Tell the dev mgr he may have to suck it up if he wants a fast running query. Note that there will be overhead on inserts/updates/deletes if you create large indexes to cover this query.
4) Again, if we had table defs/counts we could possibly consider alternatives such as intermediate tables, forcing plan order, etc.
5) If all tables are under 100K rows and you are getting 160K rows back, are you getting a cartesian somewhere?
6) You are joining 4 tables, potentially each with 100K rows, bringing back 160K rows. Sounds like you are hitting a very large fraction of the data in each table in which case the optimizer will likely ignore indexes in favor of sequential I/O from table scans.
7) Is your data/indexes fragmented? Being an MCDBA I would hope you do regular maintenance on your structures and fragmentation is minimal.
8) Typically when queries of this nature are presented, they are reports that do not NEED to be run in <10 seconds because they are not run often. This is especially true when they hit such a large percentage of the total data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I did some tuning in the server side than the query and achieved something beeter than what it was before. The things i did.
1. Enabled /3GB switch to make the server use more memory
2. Made the antivirus software not to scan the database files and scheduled them to get executed on weekends.
3. separated the database data and log files.
This tuning did help to me but was not able to achieve the target. By the way i am new to this project environment. The earlier DBA dint check the RAM usage, server performance, etc.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply