CXPACKET and PAGEIOLATCH_SH

Question

CXPACKET and PAGEIOLATCH_SH

RS-298333

SSC Veteran

Points: 206
More actions
February 13, 2006 at 9:41 am

#152759

Hi Guys,
I m running couple of queries at on sql server 2000 (SP 4) on Windows 2000. The queries used to run in 45 minutes. But these days the same queries are running forever. Each SP ID will have multiple Context IDs and the wait type will immediately be CXPACKET or PAGEIOLATCH_SH. What could be the problem? What should we do to run them faster?
Thanks,
RS

Viewing 4 posts - 1 through 3 (of 3 total)

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Without looking at the code my only question is whether the machine this is running on is a multi processor box. If so I would look at using the maxdop option to limit the degree of parelism (cpu's used) for the query. The wait resources could be the result of threads waiting on other threads to finish on a multi cpu box.

RS-298333 SSC Veteran Points: 206 More actions · Answer 2

Yes, we are using 4 CPUs. In EM when i right click on server and then properties and the select the processor and the table parallelism the selected option is "Use all available processors". Should i limit that number and try? We have not changed anything from the original query which took only 30-45 min running time.

Thanks

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 3

Try the query like this:

select whatever from table OPTION (MAXDOP 1)

The number in the maxdop hint is the number of procesors the query will use, I would also look at when the indexes and stats on the tables this query hits were last updated.