SQL FileStream - Concurrency support

Question

SQL FileStream - Concurrency support

Mia2022

Old Hand

Points: 342
More actions
May 17, 2022 at 10:21 pm

#4034592

Hi team,
We have already enabled a SQL filestream for storing API request and response contents in one of our audit table. It works fine in PROD.
Now we want to add more traffic to it, earlier, the traffic comes from 1 API, now we want to enable Audit for other APIs with request content with more transactions which means request message will be quite large compared to previous ones..
Now the question we have ?. Will SQL filestream support concurrency (multiple concurrent request). Will there be any impact in terms of performance leading to Timeout for API ?.. Will there be any impact in nightly back up ?
Right now, we have single insert procedure to insert the records into that table.. if we feel, it will be a problem for concurrent request, then we can think of splitting the table into multiple for each API and have separate insert stored procedure ?
Also provide some view on Filestream vs Blob in terms of size
Thanks
May 19, 2022 at 9:41 am

This was removed by the editor as SPAM

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: 736193 More actions · Answer 1

It's not quite clear what you mean by API and what you're doing with FileStream (FS).

This is designed to give concurrent access to blob items at a higher rate than querying through T-SQL if you use the Win32 API to retrieve (or store) the blobs. Usually when the blob size is > 1MB, FS is much faster. Between 256kb and 1MB, it can go either way. Below 256kb, use T-SQL.

Mia2022 Old Hand Points: 342 More actions · Answer 2

ok.. The API I referred is ASP.Net API (Middleware) not FS API... We already using T-SQL to store the request & response in FS enabled columns using insert stored procedures... The question here is, will there be any concurrent issues if I extend this insert stored procedure to be used by other .NET API to store large request contents (> 1 MB) in FS enabled fields concurrently. ?

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

You're using some strange verbiage. Extend the API. Do you mean have more clients calling this stored procedure? However you call these isn't relevant, so saying you are extending your API elsewhere distracts and confuses. You are asking if you can have more pages/clients/sessions using this stored procedure.

Stored procedures can be run simultaneously by many clients. This is not really a problem. At some point, you might have scale issues, but that's the case with any single system and high workloads.\

Mia2022 Old Hand Points: 342 More actions · Answer 4

Do you mean have more clients calling this stored procedure? . Yes .. Will there be any concurrent issue if we try to perform concurrent inserts in File Stream enabled columns.. ?. i mean time out to client ? because SQL internally stores the contents in NAS for File stream.

This reply was modified 2 years, 6 months ago by Mia2022.

Steve Jones - SSC Editor SSC Guru Points: 736193 More actions · Answer 5

This is really no different than inserting into a SQL Table. You can have blocking/delays with concurrency with multiple people inserting to the same page.

With FS, you have to ensure you that your storage handles multiple threads writing to the device. That will be the limit. In general, however, the log matters more as the log has to write to disk. The writes to the "table" on the NAS can be delayed a bit until a checkpoint occurs. At least, I believe that's the case. I don't know that I've looked at if this is a write immediately/flush the buffer pool for FS stuff. If you use the Win32 API, it does, but in that case you will cause some blocking for reads of that particular blob.