@Zikato Don't retry too quickly. SQL Server prefers to keep incoming connection requests on the same scheduler if they arrive within a small time window. A 3-5 second delay between attempts ought to work.
If you need something guaranteed, you might have to do something daft with Resource Governor and one scheduler per group/pool.
@Zikato Describe exactly what you're doing then, because that works reliably for me from sqlcmd
You need to be sure you're actually creating a new connection each time and not, for example, just being told that's what you're getting from a caching layer or connection pooling etc
Could somebody with Full Text Search set up on their SQL Server instance please try the following for me exec sp_describe_first_result_set N'select RANK from CONTAINSTABLE(someTableHere, *, N''someSearchTerm'') c'also the same for FREETEXTTABLE and please tell me the result of the system_type_name in the result.
Great thanks. Interesting, I thought it was a float. And what do you get for exec sp_describe_first_result_set N'select * from semantickeyphrasetable(someTableHere, *) c'
Trying to write a EF Core add-in and too much to get round to setting all that up at the moment.
keyphrase just says nvarchar in the docs, doesn't say length.
EF Core is horrendous to work with if you're trying to extend it. Major API surface changes in minor builds even, and someone has really the wrong idea of what DI is supposed to do.
Also would have made life a lot easier if the whole thing had just been a series of ExpressionVisitor pipelines which you could replace or plug in to, but no, there's loads of stuff where you basically have to reimplement half the framework just to change one tiny detail.
I'm adding Full text functions to it, along with XQuery, and table/query hints, and INSERT... SELECT and MERGE, and TVPs, so got my work cut out.
I've started to use the QueryStore to monitor my App and one of the things I noticed is the unexpected high memory usage for what I thought a simple operation:
Which would mean the statement uses almost 600MB of RAM each time it is executed?
The RowCount for each execution in that timeframe is a...
Does the weird internal insert bulk statement ever produce a memory grant?
bulk copy is weird like that, it uses a completely separate insert path, and I suspect the query plan is not cached and is basically made up when you query it off the DMVs.
@Yano_of_Queenscastle Interesting that those columns are not nullable but the earlier ones you showed are.
I had a thing like that, and I don't think it relates to COLUMNSTORE
Nvm, Ireviewed the code and it was actually the Gzip compressor before the bulkcopy
We had a Powershell script to zip up some data and archive it and it had to be changed to manually call dispose on the memory stream and force the .NET garbage collector to run after compressing data and before the SqlBulkCopy.
@CadeRoux Sounds like someone doesn't know how to make it fully streaming...
Depending on what you're trying to compress though. If you're compressing or decompressing the CSV itself then you need something like CsvReader and GZipStream which can fully stream it through.
If it's the per-column data which is being compressed then you want to look at a reusable ArrayPool for the backing arrays of those MemoryStreams.
Powershell is often particularly bad at this, if you are not using a built-in cmdlet. Eg something like Get-Content will cache the whole thing in a byte array, rather than streaming it. Sometimes it's worth just doing it in C#.
