October 10, 2008 at 8:48 am
I'm working on a system with 4GB of memory, min/max values set to 2048/3096 respectively and the /3GB switch added to booti.ini. We have several tables without a clustered index and it appears that one of these, which is frequently accessed by stored procs, is consuming more than 60% of the buffer cache, thus leading to frequent flushing of pages from the cache when new data is needed by other apps. I'm taking a guess that without the clustered index, reading data from this table is very random and that the server is having to read pages/extents that contain a mixture of data (pages or extents with data other than what was requested). I'm considering adding a clustered index, but I want to fully understand what's happening for future reference. The table is relatively small with 4 nonclustered indexes and roughly 17 million rows. I've removed any page and extent fragmentation from the 4 NCK's
Additionally, I'm using Spotlight to track the memory on the server and though I've specified a min of 2048 and a max of 3096, the monitor reports only using 2.54 using at any time especially when the page life expectany drops to lower than 60 secs and the data cache runs out of space. I would think that the server would grab a portion of the remaining .5GB available.
Ex: Buffer Cache Size 1.83GB
Procedure Cache Size 608MB
Any feedback is welcome.
Thanks
October 10, 2008 at 2:50 pm
I'd say you're on the right idea that the queries on heap tables are causing numerous pages to be loaded because the records can be so spread out. Depending on your queries and how big a row is in the table, you may really only be intending to read a small portion of each page of the table. The best thing to do is understand how the data is queried, and figure out if there is some column or columns that are always or most frequently in your WHERE clauses, and have fairly good selectivity and make those the columns in your clustered index. It may be something as simple as queries are usually only on recent data so use an identity column or transaction date or something for the index.
October 10, 2008 at 3:15 pm
I'd listen to Chris, but I might look to add multiple indexes on these tables. Look at the queries and see what they're using for their WHERE clauses.
October 10, 2008 at 10:54 pm
Thanks for the confirmation. That's what I released into QA and we're testing. I'll post the results later.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply