December 4, 2017 at 2:45 pm
I allow my instances to auto-parameterize, so if it's that valuable to the plan, presumably SQL will do so anyway.
Again, with dynamic SQL specifically -- which this code must be -- the longevity in the cache is going to be minimum anyway. If the plan is still there quite a while later when you need it, then regenerating it shouldn't be a strain anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 4, 2017 at 2:58 pm
ScottPletcher - Monday, December 4, 2017 2:45 PMI allow my instances to auto-parameterize, so if it's that valuable to the plan, presumably SQL will do so anyway.Again, with dynamic SQL specifically -- which this code must be -- the longevity in the cache is going to be minimum anyway. If the plan is still there quite a while later when you need it, then regenerating it shouldn't be a strain anyway.
YMMV
It has helped with many of the dynamic queries I have had to modify because of the extensive use of EXEC (@sql) that has been used here.
Do what you want, just know when you post EXEC (@sql) answers and I see them I will add answers using EXEC sp_executesql @sql[, @sqlparm, @parm1 = @var1[,...]] type answers so that the OP and others will see the alternative when appropriate (i.e. not when you are executing dynamic SQL against a linked server).
December 4, 2017 at 3:33 pm
ScottPletcher - Monday, December 4, 2017 12:59 PM"Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway. So it's removed from the cache the moment there's pressure, and is left if not. So I don't see any real net difference for that. Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.
I don't know about the "dynamic SQL gets a zero value in the plan cache". If you have the "Optimize for ad-hoc workloads" option set on the instance, the first time a query runs, it gets a plan stub stored in the plan cache. The next time it runs, it'll get the full plan stored, overwriting the stub. If the option isn't set, the full plan gets written to the plan cache. However, ad-hoc SQL gets around this setting because if it isn't parameterized, the query hash will be different from run to run even if there's only a slight (read: text case, spacing, value, etc.) difference. The result of ad-hoc SQL is lots of single-use plans in the plan cache, which still occupy space. The single-use plans will, of course, be flushed out as space is needed in the cache, but if it's done properly, it won't even be necessary because a parameterized query generates a plan that can be reused.
December 4, 2017 at 3:36 pm
Ed Wagner - Monday, December 4, 2017 3:33 PMScottPletcher - Monday, December 4, 2017 12:59 PM"Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway. So it's removed from the cache the moment there's pressure, and is left if not. So I don't see any real net difference for that. Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.I don't know about the "dynamic SQL gets a zero value in the plan cache". If you have the "Optimize for ad-hoc workloads" option set on the instance, the first time a query runs, it gets a plan stub stored in the plan cache. The next time it runs, it'll get the full plan stored, overwriting the stub. If the option isn't set, the full plan gets written to the plan cache. However, ad-hoc SQL gets around this setting because if it isn't parameterized, the query hash will be different from run to run even if there's only a slight (read: text case, spacing, value, etc.) difference. The result of ad-hoc SQL is lots of single-use plans in the plan cache, which still occupy space. The single-use plans will, of course, be flushed out as space is needed in the cache, but if it's done properly, it won't even be necessary because a parameterized query generates a plan that can be reused.
Given that the query string is computer generated, and the only values from variables are numbers, the query should have exactly the same hash every time it runs except for the first time it runs on the 5th day of a month. And when the month changes, any plan would have to be regenerated, since the table name will change as well.
I got the 0 value from BOL (for SQL 2008, since that's the thread). I'm not 100% sure about it either, although it makes perfect sense:
Since ad-hoc plans are initialized with a current cost of zero, when the database engine examines the execution plan, it will see the zero current cost and remove the plan from the procedure cache. The ad-hoc execution plan remains in the procedure cache with a zero current cost when memory pressure does not exist.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 4, 2017 at 3:44 pm
ScottPletcher - Monday, December 4, 2017 3:36 PMEd Wagner - Monday, December 4, 2017 3:33 PMScottPletcher - Monday, December 4, 2017 12:59 PM"Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway. So it's removed from the cache the moment there's pressure, and is left if not. So I don't see any real net difference for that. Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.I don't know about the "dynamic SQL gets a zero value in the plan cache". If you have the "Optimize for ad-hoc workloads" option set on the instance, the first time a query runs, it gets a plan stub stored in the plan cache. The next time it runs, it'll get the full plan stored, overwriting the stub. If the option isn't set, the full plan gets written to the plan cache. However, ad-hoc SQL gets around this setting because if it isn't parameterized, the query hash will be different from run to run even if there's only a slight (read: text case, spacing, value, etc.) difference. The result of ad-hoc SQL is lots of single-use plans in the plan cache, which still occupy space. The single-use plans will, of course, be flushed out as space is needed in the cache, but if it's done properly, it won't even be necessary because a parameterized query generates a plan that can be reused.
Given that the query string is computer generated, and the only values from variables are numbers, the query should have exactly the same hash every time it runs except for the first time it runs on the 5th day of a month. And when the month changes, any plan would have to be regenerated, since the table name will change as well.
The larger issue is that non-parameterized SQL garbages up the plan cache. It always has and always will. It also gets around a configuration setting that helps address the problem of single-use plans.
Add in the SI issue and it's a bad idea from more than one angle.
December 4, 2017 at 3:46 pm
Ed Wagner - Monday, December 4, 2017 3:44 PMScottPletcher - Monday, December 4, 2017 3:36 PMEd Wagner - Monday, December 4, 2017 3:33 PMScottPletcher - Monday, December 4, 2017 12:59 PM"Dynamic SQL ... plan cache": As I understand it, for SQL 2008, dynamic SQL gets a zero value in the plan cache anyway. So it's removed from the cache the moment there's pressure, and is left if not. So I don't see any real net difference for that. Besides which, SQL can often produce a better plan for a specific, known value rather than a variable value.I don't know about the "dynamic SQL gets a zero value in the plan cache". If you have the "Optimize for ad-hoc workloads" option set on the instance, the first time a query runs, it gets a plan stub stored in the plan cache. The next time it runs, it'll get the full plan stored, overwriting the stub. If the option isn't set, the full plan gets written to the plan cache. However, ad-hoc SQL gets around this setting because if it isn't parameterized, the query hash will be different from run to run even if there's only a slight (read: text case, spacing, value, etc.) difference. The result of ad-hoc SQL is lots of single-use plans in the plan cache, which still occupy space. The single-use plans will, of course, be flushed out as space is needed in the cache, but if it's done properly, it won't even be necessary because a parameterized query generates a plan that can be reused.
Given that the query string is computer generated, and the only values from variables are numbers, the query should have exactly the same hash every time it runs except for the first time it runs on the 5th day of a month. And when the month changes, any plan would have to be regenerated, since the table name will change as well.
The larger issue is that non-parameterized SQL garbages up the plan cache. It always has and always will. It also gets around a configuration setting that helps address the problem of single-use plans.
Add in the SI issue and it's a bad idea from more than one angle.
If a query is not being re-used, I can't see any difference to the cache between a parameterized query and non-parameterized one.
And parameterized queries can suffer from SQL injection as well, particularly those that dynamically construct table/column names. Yes, parameterization reduces the chances, as do proficient developers.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 4, 2017 at 4:15 pm
ScottPletcher - Monday, December 4, 2017 3:46 PMIf a query is not being re-used, I can't see any difference to the cache between a parameterized query and non-parameterized one.And parameterized queries can suffer from SQL injection as well, particularly those that dynamically construct table/column names. Yes, parameterization reduces the chances, as do proficient developers.
If you're worried about the dynamic object(s) being used to inject SQL, you could always use an IF and an EXISTS to check those objects exist first. Yes, it's an extra call, but, I'd rather an extra call to a sys table than the server having SQL injected.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 4, 2017 at 4:33 pm
Thom A - Monday, December 4, 2017 4:15 PMScottPletcher - Monday, December 4, 2017 3:46 PMIf a query is not being re-used, I can't see any difference to the cache between a parameterized query and non-parameterized one.And parameterized queries can suffer from SQL injection as well, particularly those that dynamically construct table/column names. Yes, parameterization reduces the chances, as do proficient developers.
If you're worried about the dynamic object(s) being used to inject SQL, you could always use an IF and an EXISTS to check those objects exist first. Yes, it's an extra call, but, I'd rather an extra call to a sys table than the server having SQL injected.
I have written a lot of dynamic SQL here and because of id ten ts I have gotten to the point that column names, table names, view names, schema names, function and procedure names, index names, constraint names, data type names, etc. are always quoted using [ and ]. And all of these are derived directly from the system tables but when code that has worked for years suddenly breaks because someone created an index name that includes special characters like <, spaces, punctuation, etc. I have to go to the extreme to ensure the code doesn't break the next time someone does something stupid because they don't bother to consult with me before doing something.
December 5, 2017 at 1:54 am
Lynn Pettis - Monday, December 4, 2017 4:33 PMI have written a lot of dynamic SQL here and because of id ten ts I have gotten to the point that column names, table names, view names, schema names, function and procedure names, index names, constraint names, data type names, etc. are always quoted using [ and ]. And all of these are derived directly from the system tables but when code that has worked for years suddenly breaks because someone created an index name that includes special characters like <, spaces, punctuation, etc. I have to go to the extreme to ensure the code doesn't break the next time someone does something stupid because they don't bother to consult with me before doing something.
We might as well go the full hog then and use that sys table...:
Then, if for what ever reason, the table doesn't exist, the query will return the value NULL to @sql, and then sp_executesql will fail.
I've also changed the alias' to something more representative. It bugs the hell out of me when people alias their tables things like T1, T2, T3 in the order that the declare them. They don't help you.
Edit: Added N to start of strings.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 5, 2017 at 8:38 am
Thom A - Tuesday, December 5, 2017 1:54 AMLynn Pettis - Monday, December 4, 2017 4:33 PMI have written a lot of dynamic SQL here and because of id ten ts I have gotten to the point that column names, table names, view names, schema names, function and procedure names, index names, constraint names, data type names, etc. are always quoted using [ and ]. And all of these are derived directly from the system tables but when code that has worked for years suddenly breaks because someone created an index name that includes special characters like <, spaces, punctuation, etc. I have to go to the extreme to ensure the code doesn't break the next time someone does something stupid because they don't bother to consult with me before doing something.
We might as well go the full hog then and use that sys table...:
DECLARE @date DATETIME;DECLARE @sql NVARCHAR(MAX),@sqlparm NVARCHAR(MAX) = N'@pdate datetime';SET @date = GETDATE() - 5;SELECT YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];SELECT @sql = 'INSERT INTO [Check].[dbo].[Reconciliation] ( DocDate, Batch, Material)SELECT Date, --Don''t know what table this comes from, so couldn't alias itM.Batch,IV.DocumentIDFROM [Check].[dbo].[Master] MLEFT JOIN [DB2017].[dbo].' + QUOTENAME(t.[name]) + ' IV ON M.Batch = IV.TextValue COLLATE Latin1_General_CI_ASLEFT JOIN [DB2017].[dbo].[Documents] D ON IV.DocumentID = D.IDWHERE date >= @pdate;'FROM sys.tables tWHERE t.[name] = 'IndexValues-' + CONVERT(VARCHAR(2), @date, 1);EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;Then, if for what ever reason, the table doesn't exist, the query will return the value NULL to @sql, and then sp_executesql will fail.
I've also changed the alias' the something more representative. it bugs the hell out of me when when alias their tables things like T1, T2, T3 in the order that the declare them. They don't help you.
Thank you, saves me from my SQL OCD which I was trying hard not to give into here.
December 5, 2017 at 8:48 am
Thom A - Tuesday, December 5, 2017 1:54 AMLynn Pettis - Monday, December 4, 2017 4:33 PMI have written a lot of dynamic SQL here and because of id ten ts I have gotten to the point that column names, table names, view names, schema names, function and procedure names, index names, constraint names, data type names, etc. are always quoted using [ and ]. And all of these are derived directly from the system tables but when code that has worked for years suddenly breaks because someone created an index name that includes special characters like <, spaces, punctuation, etc. I have to go to the extreme to ensure the code doesn't break the next time someone does something stupid because they don't bother to consult with me before doing something.
We might as well go the full hog then and use that sys table...:
DECLARE @date DATETIME;DECLARE @sql NVARCHAR(MAX),@sqlparm NVARCHAR(MAX) = N'@pdate datetime';SET @date = GETDATE() - 5;SELECT YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];SELECT @sql = N'INSERT INTO [Check].[dbo].[Reconciliation] ( DocDate, Batch, Material)SELECT Date, --Don''t know what table this comes from, so couldn't alias itM.Batch,IV.DocumentIDFROM [Check].[dbo].[Master] MLEFT JOIN [DB2017].[dbo].' + QUOTENAME(t.[name]) + N' IV ON M.Batch = IV.TextValue COLLATE Latin1_General_CI_ASLEFT JOIN [DB2017].[dbo].[Documents] D ON IV.DocumentID = D.IDWHERE date >= @pdate;'FROM sys.tables tWHERE t.[name] = 'IndexValues-' + CONVERT(VARCHAR(2), @date, 1);EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;Then, if for what ever reason, the table doesn't exist, the query will return the value NULL to @sql, and then sp_executesql will fail.
I've also changed the alias' the something more representative. it bugs the hell out of me when when alias their tables things like T1, T2, T3 in the order that the declare them. They don't help you.
Absolutely this.
EDIT: Speaking aboyt OCD, you should always use unicode strings when using NVARCHAR(MAX) to create dynamic sql statements. Otherwise, you'll eventually get your string truncated.
December 5, 2017 at 9:03 am
On a topic related note, has anyone noticed that the OP hasn't responded to any of our questions? :rolleyes: I feel we'll never really know what the OP was after, or if the proposed solution(s) worked.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 5, 2017 at 9:14 am
Thom A - Tuesday, December 5, 2017 9:03 AMOn a topic related note, has anyone noticed that the OP hasn't responded to any of our questions? :rolleyes: I feel we'll never really know what the OP was after, or if the proposed solution(s) worked.
Happens more often than not it seems.
December 5, 2017 at 9:17 am
Luis Cazares - Tuesday, December 5, 2017 8:48 AMThom A - Tuesday, December 5, 2017 1:54 AMLynn Pettis - Monday, December 4, 2017 4:33 PMI have written a lot of dynamic SQL here and because of id ten ts I have gotten to the point that column names, table names, view names, schema names, function and procedure names, index names, constraint names, data type names, etc. are always quoted using [ and ]. And all of these are derived directly from the system tables but when code that has worked for years suddenly breaks because someone created an index name that includes special characters like <, spaces, punctuation, etc. I have to go to the extreme to ensure the code doesn't break the next time someone does something stupid because they don't bother to consult with me before doing something.
We might as well go the full hog then and use that sys table...:
DECLARE @date DATETIME;DECLARE @sql NVARCHAR(MAX),@sqlparm NVARCHAR(MAX) = N'@pdate datetime';SET @date = GETDATE() - 5;SELECT YEAR(@date) AS [Year], MONTH(@date) AS [Month], DAY(@date) AS [Day];SELECT @sql = N'INSERT INTO [Check].[dbo].[Reconciliation] ( DocDate, Batch, Material)SELECT Date, --Don''t know what table this comes from, so couldn't alias itM.Batch,IV.DocumentIDFROM [Check].[dbo].[Master] MLEFT JOIN [DB2017].[dbo].' + QUOTENAME(t.[name]) + N' IV ON M.Batch = IV.TextValue COLLATE Latin1_General_CI_ASLEFT JOIN [DB2017].[dbo].[Documents] D ON IV.DocumentID = D.IDWHERE date >= @pdate;'FROM sys.tables tWHERE t.[name] = 'IndexValues-' + CONVERT(VARCHAR(2), @date, 1);EXEC [sys].[sp_executesql] @sql, @sqlparm, @pdate = @date;Then, if for what ever reason, the table doesn't exist, the query will return the value NULL to @sql, and then sp_executesql will fail.
I've also changed the alias' the something more representative. it bugs the hell out of me when when alias their tables things like T1, T2, T3 in the order that the declare them. They don't help you.
Absolutely this.
EDIT: Speaking aboyt OCD, you should always use unicode strings when using NVARCHAR(MAX) to create dynamic sql statements. Otherwise, you'll eventually get your string truncated.
That would have to be one hell of a long piece of dynamic SQL. I do agree, however, especially if you actually need to include UNICODE characters in the dynamic SQL. Of course there is someone (not me) out there that would disagree with you.
December 5, 2017 at 9:25 am
Lynn Pettis - Tuesday, December 5, 2017 9:17 AMThat would have to be one hell of a long piece of dynamic SQL. I do agree, however, especially if you actually need to include UNICODE characters in the dynamic SQL. Of course there is someone (not me) out there that would disagree with you.
That would indeed need to be a hefty script. Largest one I've got is about 520MB, which contains 274,762,643 characters!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply