September 30, 2021 at 12:09 pm
Getting a lot of raw, non-sproc queries run which is causing plan cache bloat, any ways to prove they are generated by an ORM?
September 30, 2021 at 12:55 pm
By 'generated', do you mean 'written'?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 30, 2021 at 1:09 pm
Well, I'd say the only real way you can suggest, you can't prove it, that they're probably ORM tools is the fact that, assuming they're using the ORM tool even remotely correctly, the queries will be coming through RPC_COMPLETED in Extended Events, not BATCH_COMPLETED. Combine this with the fact that you do not have a stored procedure that they're calling, and it's pretty strong evidence that it's some type of ORM tool. I've worked with three different ones. They all used prepared statements by default. They look, and act, a lot of stored procedures, but there's no code on the server. Still, you can code these manually, hence, you can't say definitively that it is an ORM tool.
Also, it's possible, they list the tool in the connection string. You might see it there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2021 at 2:09 pm
I don't know about prove, but ORMs often have a telltale signature.
I can easily recognize Entity Framework queries, which alias tables as ExtentN, and alias parameters as @p__linq__N. e.g.,
SELECT TOP (1)
[Extent1].[StudentId] AS [StudentId],
[Extent1].[Name] AS [Name]
FROM [dbo].[Student] AS [Extent1]
WHERE ([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL)
AND (@p__linq__0 IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Bill'
September 30, 2021 at 3:23 pm
Also, if you're capturing lots of queries that declare the same variable for the same column all different sizes based on the data passed
@Name(3) for 'Dog'
@Name(8) for 'Elephant'
Not only are you very likely seeing an ORM in use, you're also seeing it used incorrectly. Again, prove beyond a reasonable doubt... maybe, maybe not. Preponderance of evidence? Absolutely. And since this is a civil case, not a criminal one (yeah, yeah, ORMs), I think you can win the argument.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply