Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Unanswered: 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?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2004
    Posts
    2

    sorry - more specific information

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Can you post the query and the table(s) DDL?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •