November 6, 2013 at 2:53 am
Hi,
Is there any away of knowing the memory a specific procedure is taking in memory when compiled?
I have some procedures with dynamic SQL written by developers that use a programmatic "approach"..
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT '
SET @SQL = @SQL + ' Id,'
SET @SQL = @SQL + ' Name,'
....
and goes on like that for a very long number of rows....
I'd like to compare the memory with
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT
Id,
Name,
....
No need to end on each row the '... SQL isn't like VB or C#..
Thanks,
Pedro
November 6, 2013 at 3:59 am
You can query sys.dm_exec_cached_plans, however I can tell you now there should be minimal difference. The differences you describe are in the T-SQL. The first phase of parsing converts T-SQL into a query tree, that's further converted by the algrabriser and optimiser. What is cached is an execution plan, not the original SQL text.
p.s. C# doesn't need to end the line on a ' either.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 4:22 am
Thanks..
Made the changes on an existing procedure to remove the SET @SQL = @SQL ... and the result was, at least, surprising...
With concat: 393216 bytes (size_in_bytes column from sys.dm_exec_cached_plans).
Without concat: 262144 bytes..
That's a big difference... The execution plan stores each line separately and the XML for each line takes lots of space, it has 70 lines... And there are bigger procedures like this..
Pedro
November 6, 2013 at 4:27 am
The plan isn't stored in XML, that's just how SQL gives it to you, not its internal representation.
You're talking about 120kB that will be stored in cache once per procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 4:33 am
But size_in_byte gives the size the procedure takes in memory, right?
120K bytes per procedure when you have 600 procedures can be a lot when memory is a problem... some sql "servers" from our customers only have 4GB RAM (80% of them).
120K bytes are 15 data pages to store in memory 🙂
Thanks,
Pedro
November 6, 2013 at 4:44 am
sys.dm_exec_cached_plans
size_in_bytes int Number of bytes consumed by the cache object.
So yes.
See whether you have memory pressure, see whether you have cache memory pressure before you spend lots of time making changes. And maybe see if the dynamic SQL can come out entirely, that'll probably give you a better outcome as dynamic SQL is cached separately.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 4:58 am
GilaMonster (11/6/2013)
So yes.
Thanks
GilaMonster (11/6/2013)
See whether you have memory pressure, see whether you have cache memory pressure before you spend lots of time making changes. And maybe see if the dynamic SQL can come out entirely, that'll probably give you a better outcome as dynamic SQL is cached separately.
PLE is very low, some cases less than 400...
Unfortunately the dynamic SQL in most of the cases has to stay...
Some parameters that can be NULL (not to use col1 = ISNULL(@col1, col1) or (@col1 IS NULL or col1 = @col1) - read your blog on this one 🙂 ) and the worst of then all.... some where clauses are sent from the interface!!! I have to concatenate to the WHERE clause some variables sent from the interface :w00t:
It's better to use sp_executeSQL even without parameters than EXEC or it depends?
sp_executesql "forces" a plan and EXEC is like using WITH RECOMPILE to force a new plan...
If we change the parameterization to FORCE they behave the same way?!
Thanks,
Pedro
November 6, 2013 at 5:15 am
Low PLE != plan cache memory pressure. The balance between the plan cache and the data cache is complex, but they are two separate caches each with their own aging and removal process.
It's better to use sp_executeSQL even without parameters than EXEC or it depends?
Should be much the same
sp_executesql "forces" a plan and EXEC is like using WITH RECOMPILE to force a new plan...
If we change the parameterization to FORCE they behave the same way?!
No and no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 5:21 am
I'll look into the sp_executesql and EXEC deeper and the parameterization database option...
Our software is an ERP with loads of ad-hoc queries and the interface allows almost every combination of where clauses..
The only WHERE clauses, to build indexes, I can control are those inside the SPs and FKs...
Thanks,
Pedro
November 6, 2013 at 5:31 am
Have you considered enabling 'optimise for adhoc workloads'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 5:41 am
GilaMonster (11/6/2013)
Have you considered enabling 'optimise for adhoc workloads'?
That's already been done...
Memory taken by the ad-hoc queries sure went down a lot... I also tried the parameterization forced but noticed no different neither on performance nor memory usage..
Don't know if it has to do with how the users use the ERP...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply