SQL Server Performance

Need more speed

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by simondm, Jun 13, 2006.

  1. simondm New Member

    Hi All,

    I have the following query:



    SELECT Vertical, COUNT(*)/7 as [Pages], SUM(Views)/7 as [Views]
    FROM CONTAINSTABLE([PagesContents], [Body], '"wind in the willows"') CT
    JOIN ViewsCurrent PV (nolock) ON PV.PageID = CT.[Key]
    JOIN Feeds ITF (nolock) ON ITF.IPID = PV.IPID
    GROUP BY Vertical


    This query completes in about 20 seconds, return about 90 rows - I need to get it down to < 2 seconds. The query plan looks good. All the joins are NESTED LOOPS. ViewsCurrent and Feeds have clustered indexes (PK's) on the fields they are joinging on. The query plan performs a clustered index seek.

    The full text search on its own completes very quickly < 1 second. And in the query plan shows as 4% of the workload. The remaining workload is split evenly between the two joins.

    ViewsCurrent is fairly large with about 20 million rows. But it is on a dedicated RAID10 array of 4x15,000rpm SCSI drives. The full text index is on a similar array (and for the record a separate channel).

    All tables in the query have a no fragmentation (<1%).

    Running perfmon during the query I can see a disk queue of 1 on both the Full Text drive and the ViewsCurrent drive. The disk bytes/sec counter shows about 10MB/s on the full text drive and about 6 MB/sec on the ViewsCurrent drive. The CPU is almost idle (4x3ghz). The machine does appear to be paging with about 7,000 pages/sec.

    I think that the query is IO bound - but the perfom stats don't look bad to me. How can I diagnose the problem better? Is that paging counter bad?

    For the Records
    SQL 2005 SP1
    Windows 2003 SP1
    Dell 6850 - 4x3.0Ghz, 8GB RAM

    Thanks for any help!
    Simon

  2. joechang New Member

    a properly configured server should not be paging

    disk queue depth and latency are important, see the list of counters in the /qdpma sub site
  3. Haywood New Member

    quote:Originally posted by joechang

    a properly configured server should not be paging

    IMO, it's probably your small raid 10.

    Re disk: You need more spindles to effectivly search the objects. A four disk Raid 10 is not a high i/o volume.


    Edit:

    Saw the memory config, memory _shouldnt_ be an issue here...
  4. simondm New Member

    Thanks all. I haven't got to the bottom of the paging issue yet adn I really would like to understand it before I ask for more disks (again!).

    The paging only happens when the server is performing full text queries. The rest of the time I never see any paging.

    Would it be possible that my full text index has heavy fragmentation at OS level? The index has been reorganised with SQL which is basically SQL's way of optimising it. The queue counter is low, yet the throughput is low. I can only assume that reads are taking some time, fragmentation would explain this?

    I'm sure if I ran the SQLIO tool on that drive I could get a much higher throughput for sequential reads.

    Does anyone think I'm missing anything here??

    Thanks again
    Simon

Share This Page