Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Post Unanswered: Basic Performance Question

    I am running a pretty big select on multiple tables using Sybase 11.0.3.
    The first time I run this after restarting the server it takes about 30 minutes the next time it takes only 7 minutes. Why the difference?

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    This is probably due to caching of part of the rows in the dataserver's memory cache. Accessing cached rows is *way* faster than fetching them from the file system.

    Michael

  3. #3
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Use sp_sysmon to monitor the performance.

    sp_sysmon 'begin_sample'
    go
    -- Your select
    go
    sp_sysmon 'end_sample'

    You'll see loads of output, you want to look for anything to do with data cache and data cache hit rates.

  4. #4
    Join Date
    Jan 2003
    Posts
    7
    Is there away to clear the cache using a trace flag or something (without rebooting the server, which I don't have physical acess too). I need to tune some queries but the cache is giving me incorrect results.


    Thanks,
    Coop

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Use "set statistics IO on", and then count the number of logical IOs that your query generates. Then try to find the query plan that will generate the least number of logical IOs.

    Michael

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Originally posted by coobr01
    Is there away to clear the cache using a trace flag or something (without rebooting the server, which I don't have physical acess too). I need to tune some queries but the cache is giving me incorrect results.


    Thanks,
    Coop
    A neat trick is to unbind the table(s) from the default data cache. It should then use physical I/O every time (note that depending on your device config and OS the OS may do some caching).

    The best way to ensure the machine is cold is to reboot your machine!!
    Thanks,

    Matt

Posting Permissions

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