November 4, 2008 at 11:30 am
balbirsinghsodhi (11/4/2008)
SQL Guru.. I know this query is not good for optimizer so I did modify and used in a following manner but still going in a suspended mode and the wait type is PAGEIOLATCH_SH. I think it is something related to multi processor. Looks like SQL is not good in multiprocessor.
It's got nothing to do with multi-processor and SQL is very good at using multiple processors.
A pageIOlatch, as its name implies, has to do with IO. It typically occurs when a page is moving from disk to memory or memory to disk. Frequent Page IO Latches with high wait times usually signifies that your IO system is not coping with the requests issued, possible as a result of a query that is requiring a lot more data that is actually necessary.
You have a large number of index scans in your query, both clustered and non clustered. That means that SQL is not able to do a seek, but must, for some reason, scan the entire table or index. On large tables, that's very, very expensive. If those tables aren't in cache, it's going to put a lot of load on your IO system.
Can you perhaps post the execution plan of the revised query you posted above, plus the table and index definitions of the following tables:
DN200
I_INV_FSC_INV_CRE_DT
time_external_period
DIM_FSC
DN100
Also, please post the definition of the views that you are using in the from clause. From the exec plan, I can see that not all of those are tables.
Please post the actual query plan, not the estimated. The estimated is lacking some information that is really useful. Run the query with the Show Execution Plan option enabled, right click the plan, save to disk. Save as a .sqlplan file, zip it and attach it.
Please run it with the option MAXDOP 1, as the parallelism just confuses things when trying to read the exec plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 1:13 pm
Thanks GilaMonster for taking so much of pain to solving this query.
Attached is the table and index defination.
I cannot do the Acutal execution paln because this query never finishes or takes 7 hr to compete. I don't see lots of scan going on because all the tables are indexes and also I see the estimated plan and it is doing index seek.
one more thing to mention that the view we are using is a partitioned views.
November 4, 2008 at 1:47 pm
What do you mean, you don't see lots of scans? Are you looking at the same execution plan I am? See attached. There are only two seeks in the entire plan, the rest are scans.
If you included all of your indexes in that last script then you definitly don't have enough. I saw 2 nonclustered indexes total.
Try adding indexes on DN200 (CLIENT_NAME, RECORD_NUMBER), DN100 (RECORD_NUMBER), DIM_TIME (date_id) INCLUDE (external period). That's just a first guess, there may be more indexes that could be added.
Rebuild all of your clustered indexes as well. See if that helps at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 2:31 pm
There are scans but cost is 0%, do you think we should consider those too. I am sending you the same query plan which I am looking at right now.
DN100 and DN200 tables are very small table and those contains only 500 or 600 rows but Transac view is a very huge table contains 227519978 rows, invoice table contains 41811502 rows and patient table contains 3701894 rows.
I don't think creating index on small table will affect much in the performance. I also checked the fragmentaion on those big tables and are none.
appreciated your help.
November 4, 2008 at 2:35 pm
1) you will need parallelism for this monster. I would try 4 for max
2) try using a OPTION (HASH JOIN) hint for query and see how that goes. nested loops are ugly for larger numbers of rows
3) check out the virtual file stats DMV and check for io stalls. What is your IO configuration, btw? Also take a look at perf mon Physical Disk: avg disk sec/read and avg disk sec/write for all drives separately
4) what is tempdb situation? the hashing will be beating the snot out of tempdb
5) how many rows per table in the query?
6) on the schema, you should not use varchar for anything under 4 characters - less efficient and wastes space. 🙂
7) it may be helpful to refactor the query and use interim storage for some sections
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2008 at 2:50 pm
Just thought of something else. Change the select to a simple select count(*) and run the query to see how many rows hit for each section please. Maybe provide the actual query plan here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2008 at 3:14 pm
Wow, I put maxdop 4 and hash join and the query return in less than five minute. Guys you are great.
You really deserve for big award.
Guru is the real guru. One more question. Putting hash join will affect anything.?
November 4, 2008 at 3:32 pm
balbirsinghsodhi (11/4/2008)
Wow, I put maxdop 4 and hash join and the query return in less than five minute. Guys you are great.You really deserve for big award.
Guru is the real guru. One more question. Putting hash join will affect anything.?
The problem here (one of many I suspect) is that the optimizer can't really get good numbers on making a plan due to complexity of query (and possibly due to stats age, lack of stats, lack of indexes, etc, etc). Your particular parameters this time probably hit a bunch of rows (which is why I asked for the count(*)) but it estimated some smaller number. It picked nested loops for some joins and went out to lunch on the actual execution. hash joins are typically much faster for large numbers of rows.
The downside is that forcing hash join will lead to lower performance if your parameters really DO hit just a few rows. But his is often a hit that is acceptable to avoid the queries that run all day. 😀
Glad things worked out for you this time.
I will now give you my standard spiel about getting a professional in to give your stuff a performance review and mentor you on how to troubleshoot things yourself in the future. This issue probably could have been resolved in a matter of minutes (instead of FOUR DAYS) if someone had been able to connect up to your system. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2008 at 4:18 pm
Sure, that will be great if you send me some kind of document which will trouble shoot the performance issue.
November 4, 2008 at 4:51 pm
Hmm, what we have here is a failure to communicate. 🙂
I said you need a PERSON, not a DOCUMENT, to help you get your systems working well and to mentor you on how to troubleshoot perf issues yourself.
Here are a few docs you can search for though: Troubleshooting Performance Problems in SQL Server 2005 and SQL Server 2005 Waits and Queues.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2008 at 5:29 pm
balbirsinghsodhi (11/4/2008)
There are scans but cost is 0%, do you think we should consider those too.
Yes, because they are still scans, ie a read of all the pages in the table/index. That's causing excessive IOs and your wait times are IO related.
Wow, I put maxdop 4 and hash join and the query return in less than five minute.
Great. Please can you post the actual execution plan. I have a suspicion that the costings in the estimated are off and that's why the bad plan was picked. If I can find the cause, you won't need the join hint (with its attendant risks).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 8:20 pm
SQLGURU.. so you are saying that I should hire a person in my company to troubleshoot the issue or you will help me by charging money. Is that right.
November 4, 2008 at 8:27 pm
Hi Gail..
Here is the attached actual execution plan.
Thanks again for helping me. what are the main thing to consider in the query execution plan. ?
sometimes I see that there is a proper index on the table but it is still taking index scan. for example in the execution plan there is transac_live table using an index scan . I don't know why it's taking index scan, table is already indexed.
November 4, 2008 at 9:09 pm
balbirsinghsodhi (11/4/2008)
SQLGURU.. so you are saying that I should hire a person in my company to troubleshoot the issue or you will help me by charging money. Is that right.
I am saying that you, and your company, would get a LOT of benefit out of engaging a professional tuning expert to come in and give your systems a review and train you on how to detect, investigate and resolve performance problems such as this using your own databases and systems. I was not in any way saying that person should be me. I am a VERY direct person - if I wanted you to hire me you would not need to ASK me if I wanted you to hire me. 😀 I hardly have time to make the occassional forum post these days I am so busy - - knock on wood!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2008 at 11:45 pm
Sure, I will consider your advise in future. I think it's not a bad idea to start some kind of web site and help people by charging some money.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply