For the Max buffer size concern, increase the Maximum buffer size value in small increments will help to solve the issue. If this adjustment has worked, the number of round trips to the database, as measured in Performance Monitor by a decrease in the value of SQL Server statistics: batchrequestsPerSecond value. Stop increasing the value when the rate of improvement diminishes. But it is strongly recommended that you split your tables(s) into smaller units.

Maximum buffer size refers to the size of buffer the kernel allocates for holding input/output data to and from SQL Server. The buffer can be used to hold more than one row of the entire result set for output binding, and it should be allocated large enough to hold at least one row. The buffer size needed for one row depends on the number of tables joined (exist join excluded) and the size of the aggregated table columns. When large numbers of joins are used, or wide tables, a customer may encounter the issue that the maximum buffer size is less than the space needed to hold one row of the result set. Due to the way the kernel handles data binding, the aggregated table column size is always the width of the entire table. Therefore, attempting to resolve the issue by limiting the field projection list does not help.

Fixation

You can fix the issue by rewriting the join, or by increasing the Maximum buffer size value. Because large joins and wide rows may lead to performance issues, we use this limit to catch performance issues and ask customers to rethink their joins. Actually, Microsoft don’t suggest to increase the number substantially. Because it will lead to lots of instable issue. You could refer to the article here:

http://blogs.msdn.com/b/emeadaxsupport/archive/2009/05/25/random-clients-crashes.aspx

To wrap up the thoughts here, it is strongly recommended that you could optimize your SQL query and minimize the join clause instead of changing the Max Buffer Size.

Leave a Reply

Recent Comments

    Archives

    Categories