One of the best feature of FileStream is : The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
I have encountered an issue that If I create a stored procedure for return file from my FileStream table, Will The SQL Server buffer pool be used? Is it possible to take advantage of benefits of FileStream with T-SQL and SPs?
Thanks
If you use GET_FILESTREAM_TRANSACTION_CONTEXT sql buffer pool not use.
Accessing FILESTREAM data with Managed API
Accessing FILESTREAM data using Win32 Streaming has a number of advantages over accessing it using TSQL. When accessing FILESTREAM data using TSQL, SQL Server reads the content of the FILESTREAM data file and serves it to the client. SQL Server memory is used for reading the content of the data file. Accessing FILESTREAM data using Win32 Streaming does not use SQL Server memory. In addition it allows the application to take advantage of the Streaming capabilities of the NT File System.
Though accessing FILESTREAM data using Win32 Streaming has a number of advantages, it is a bit complicated to use, compared to the syntax needed to access it from TSQL. Before a client application can access the FILESTREAM data, it needs to find out the logical path that uniquely identifies the given file in the FILESTREAM data store. This can be achieved by using the “PathName” method of a FILESTREAM column. Note that the PathName() function is Case Sensitive. The following example shows how to retrieve the PathName() associated with the FILESTREAM data of a column. https://www.red-gate.com/simple-talk/sql/learn-sql-server/an-introduction-to-sql-server-filestream/
for test using buffer pool while read data use counter
SQLServer:Buffer Manager\Extension page writes/sec
SQLServer:Buffer Manager\Extension page reads/sec