Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    5

    Question Unanswered: Setting performance options

    Hello everyone,
    we are trying to tune our database, therefore we are researching possible tunable parameters that would enhance performance. It strikes to me as weird that I do not seem to find a way to vary the following in SQL Server:
    1) Sort buffer size:
    I cannot find any mention to sort buffers in BoL. Is it possible that they do not exist? Sybase's SQL Server has a "number of sort buffers" option (SQL Server Reference Manual) and most of the times they seem to agree with MS SQL Server's options.
    2) Page size:
    According to BoL (Understanding Pages and Extents), the page size is fixed to 16K. Is that true? Why is that?
    3) Fill factor:
    Reading through BoL (Understanding Pages and Extents), I was left with the impression that we can only set a value for the fill factor of the indexes and not the tables. Is that so? Is there any other way to control the free space in our tables?
    4) Read-ahead:
    I found this entry (SET STATISTICS IO (Transact-SQL)) but it seems to me that "read-ahead reads" is only an informative indication of how many read-aheads SQL Server decided to initiate and not a tunable parameter of the desirable amount of pages. Am I right? And, once again, if this is true, why is that?
    Thank you very much
    Last edited by annathanop; 05-28-10 at 11:47.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1) no
    2) actually, 8k. Yes. I can't say why but I know luminaries such as Joe Chang are campaigning for this to change
    3) If by table you mean heap, no (what do you think would be achieved by this?). If by table you mean leaf level of clustered index then yes.
    4) Yes. Why would you want to configure that?

    Sybase and SQL Server and Oracle and DB2 are different beasts. I'm not sure why people are sometimes so astonished that they differ.

  3. #3
    Join Date
    May 2010
    Posts
    5
    Hello again,
    thank you very much for your answer!
    1) what do you mean by 'no'? that there is no sort buffer indeed?
    Thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're asking the wrong question. In Microsoft SQL Server. sort buffers are not static and they are almost never an issue that needs any kind of tuning (I've seen two cases in many tens of thousands of problems over more than fifteen years).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2010
    Posts
    5
    Ok, thank you both very much.

Tags for this Thread

Posting Permissions

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