Buffer Tuning Event Logs aren't showing up in the Log Events Window in VS 2005

Question

Post reply

Buffer Tuning Event Logs aren't showing up in the Log Events Window in VS 2005

dndaughtery

SSChampion

Points: 11290
More actions
March 7, 2011 at 8:55 am

#388993

I have been able to tune my buffers in BIDS 2008 at home but when at work I use VS 2005 and I went through the steps of enabling the BufferTuning checkbox in the logging dialog box for the dataflow and have the Log Event Window open during debugging but there aren't any entries being written into the log. Does anyone know why?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

Not sure, perhaps different permissions from the domain at work?

Could there be different versions of software somewhere? Is this the same OS?

dndaughtery SSChampion Points: 11290 More actions · Answer 2

I have noticed one thing. I switched to another dataflow and the buffertuning messages stating overflow appeared. I tuned the DefaultMaxBufferRows and Maxed the DefaultMaxBufferSize and they did not appear. Is it the scenario that the statements only appear when a buffer is over flowed? That if the one buffer is all thats needed there are no buffer events logged?

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

Out of my area, but I'll ask a few people if they know and get you an answer. Glad it's working.

dndaughtery SSChampion Points: 11290 More actions · Answer 4

I think I was correct. I have found in the following article statement showing the messages when the buffer overflows the DefaultMaxBufferSize as well as when the buffer is beneath the MinBufferSize but it never shows any log events for when the buffer is between the MinBufferSize and MaxDefaultBufferSize.

http://technet.microsoft.com/en-us/library/cc966529.aspx#EHAA

If anyone finds info to contradict this please let me know.

dndaughtery SSChampion Points: 11290 More actions · Answer 5

After some thought on Buffer Tuning I think I've come up with a good way to configure DefaulMaxBufferRows and DefaultMaxBufferSize. The thing that irritates me is I haven't found a way to get the Estimated Row Length, if someone knows how to do that please let me know. But here are my thoughts:

Imagine you use the default settings (DefaulMaxBufferRows = 10,000 and DefaultMaxBufferSize = 100 MG (10485760)).

If the your rows * RowLength < DefaultMaxBufferSize , you won't get an error in your logging but you could have more room in each buffer because of the possibility you didn't reach the max size of DefaultMaxBufferSize = 100 MG (10485760).

This means that as soon as the buffer recieved 10,000 rows another buffer would be created with nothing written to notify you in the event log that another buffer was created.

So in order to get as many rows as possible, keep adjusting your DefaulMaxBufferRows setting until you get a log event stating that the buffer size would be overflowed. The adjust until you don't recieve that log event.

Am I thinking correctly on this?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

dndaughtery (3/7/2011)
The thing that irritates me is I haven't found a way to get the Estimated Row Length, if someone knows how to do that please let me know.

I answered that in your other topic:

it is the sum of the column sizes when all the columns contain their possible largest values.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP