Friday, February 17, 2012

CXPACKET, NETWORKIO, PAGELATCH_EX on server

I have a server with 300Gb+ of disk space on a SAN, with a database 55Gb for data and 15Gb for tranx log. SQL Server 2000 w/SP3 is installed. Whenever I run any query (simple, complex, short, long, using indexes or not) the server current activity shows many instances of NETWORKIO, PAGELATCH_EX, CXPACKET and other wait types on several processes. In Profiler, 500,000+ events were recorded within a 60 second timeframe. Someone else is responsible for "infrastructure" so before I go with recommendations to fix my issues, I would like as much accurate info as possible about the cause. This is supposed to be a relatively low-activity server. My understanding is serious disk subsystem issues would cause all these wait types together. But why would this happen when running a "select x, y, z from table" where the table contains < 250000 records and x,y,z make up a covering index. Additionally, I ran a relatively simple query, two tables, inner join, with properly defined indexes and went home to let it run. I returned and it had been running for 18 hours - I finally ran out of patience and stopped it. Any ideas?And you just went home? 2 possibilities:

1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)

the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.

there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.

Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.

No comments:

Post a Comment