SQL Server Performance

Performance Issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Grant50, Jan 17, 2007.

  1. Grant50 New Member

    The following query is sent by six clients about every 30 seconds on a 30 MB database. The 1.8Ghz server w/1GB of ram is getting killed. Anyone have any suggestions? Thanks!

    (@TeeTimeDate datetime,@CourseId int)SELECT ReservedCellCount *100.0/ FullCellCount, TeeTimeDate
    FROM tblCourseTimes_v8
    WHERE TeeTimeDate BETWEEN @TeeTimeDate AND DATEADD(dd, 6,@TeeTimeDate)AND CourseId=@CourseId
    AND FullCellCount != 0 AND ReservedCellCount!=0 AND ParentCourseTimeId is null
  2. joechang New Member

    what are the indexes on tblCourseTimes_v8?

    should probably have a clustered index on: CourseId, TeeTimeDate
  3. dineshasanka Moderator

    check the execution plan. it will give you better suggestions

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  4. satya Moderator

    1GB RAM, why don't you add another 1GB for performance if the data is accessed frequently.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. MohammedU New Member

    Make sure you have the right indexes and check the I/O stats and execution plan for optimization.

    I will be interested to add a coputed column for "ReservedCellCount *100.0/ FullCellCount" instead of computing for every execution to improve this query.

    Check BOL for (persistent) computed coulumns..


    Mohammed U.
  6. dkp New Member

    Try this, add also make sure that you have the required indexes:


    DECLARE @TeeTimeDate datetime
    DECLARE @TeeTimeDate_to datetime --- New variable
    DECLARE @CourseId int

    SET @TeeTimeDate = GetDate() -- You can ignore this

    SET @TeeTimeDate_to = DATEADD(dd, 6,@TeeTimeDate) -- New

    SELECT ReservedCellCount * 100.0/ FullCellCount,
    TeeTimeDate
    FROM tblCourseTimes_v8
    WHERE TeeTimeDate BETWEEN @TeeTimeDate AND @TeeTimeDate_To --Modified
    AND CourseId=@CourseId
    AND FullCellCount > 0 --Modified
    AND ReservedCellCount > 0 --Modified
    AND ParentCourseTimeId is null



Share This Page