May 2, 2017 at 12:39 am
I have a complex procedure having multiple case statements which only select data from multiple table. But when i ran profiler trace I can see Writes against the same procedure which is supposed to do only read. Can anyone help me understand why the procedure is doing a write?
Is it because it uses variables?
May 2, 2017 at 3:08 am
Worktables, hashes (sorts, aggregation), spools, etc. There's plenty of reasons why SQL would write data (and it'll be to TempDB) during execution of a query
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
May 2, 2017 at 4:59 am
GilaMonster - Tuesday, May 2, 2017 3:08 AMWorktables, hashes (sorts, aggregation), spools, etc. There's plenty of reasons why SQL would write data (and it'll be to TempDB) during execution of a query
Thanks a lot Gail,
The procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.
May 2, 2017 at 5:11 am
Rechana Rajan - Tuesday, May 2, 2017 4:59 AMThe procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.
I don't understand what you're trying to say here.
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
May 2, 2017 at 5:13 am
Rechana Rajan - Tuesday, May 2, 2017 4:59 AMGilaMonster - Tuesday, May 2, 2017 3:08 AMWorktables, hashes (sorts, aggregation), spools, etc. There's plenty of reasons why SQL would write data (and it'll be to TempDB) during execution of a queryThanks a lot Gail,
The procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.
At a guess, is your parameter filtering on a column that does not have an index? That'll likely end up with SQL server having to use tempdb.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 2, 2017 at 6:33 am
Thom A - Tuesday, May 2, 2017 5:13 AMRechana Rajan - Tuesday, May 2, 2017 4:59 AMThanks a lot Gail,The procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.
At a guess, is your parameter filtering on a column that does not have an index? That'll likely end up with SQL server having to use tempdb.
No, an index scan alone (with or without predicate) will not require a spill to tempDB.
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
May 2, 2017 at 7:10 am
GilaMonster - Tuesday, May 2, 2017 6:33 AMThom A - Tuesday, May 2, 2017 5:13 AMAt a guess, is your parameter filtering on a column that does not have an index? That'll likely end up with SQL server having to use tempdb.
No, an index scan alone (with or without predicate) will not require a spill to tempDB.
Thanks Gail. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 3, 2017 at 4:58 am
GilaMonster - Tuesday, May 2, 2017 5:11 AMRechana Rajan - Tuesday, May 2, 2017 4:59 AMThe procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.I don't understand what you're trying to say here.
Thanks Gail,
The procedure takes two values as input , the writes happen only when the second parameter have a value ,if its NULL no writes only read. Inside the SP if the second parameter is NULL then its assign value ''
@User varchar(50),
@Where nvarchar(max)
IF (@User is null) SET @User = ''
IF (@Where is null) SET @Where = ''
Finally called like this
SET @FullStatement = @FullStatement + @Where
exec sp_executesql @FullStatement
May 3, 2017 at 5:02 am
Rechana Rajan - Wednesday, May 3, 2017 4:58 AMThanks Gail,The procedure takes two values as input , the writes happen only when the second parameter have a value ,if its NULL no writes only read. Inside the SP if the second parameter is NULL then its assign value ''
@User varchar(50),
@Where nvarchar(max)IF (@User is null) SET @User = ''
IF (@Where is null) SET @Where = ''Finally called like this
SET @FullStatement = @FullStatement + @Whereexec sp_executesql @FullStatement
OH wow... Have you considered SQL injection for this type of statement..?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 3, 2017 at 5:42 am
Thom A - Wednesday, May 3, 2017 5:02 AMRechana Rajan - Wednesday, May 3, 2017 4:58 AMThanks Gail,The procedure takes two values as input , the writes happen only when the second parameter have a value ,if its NULL no writes only read. Inside the SP if the second parameter is NULL then its assign value ''
@User varchar(50),
@Where nvarchar(max)IF (@User is null) SET @User = ''
IF (@Where is null) SET @Where = ''Finally called like this
SET @FullStatement = @FullStatement + @Whereexec sp_executesql @FullStatement
OH wow... Have you considered SQL injection for this type of statement..?
Thanks Thom,
No :w00t:
May 3, 2017 at 6:48 am
Rechana Rajan - Wednesday, May 3, 2017 4:58 AMGilaMonster - Tuesday, May 2, 2017 5:11 AMRechana Rajan - Tuesday, May 2, 2017 4:59 AMThe procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.I don't understand what you're trying to say here.
Thanks Gail,
The procedure takes two values as input , the writes happen only when the second parameter have a value ,if its NULL no writes only read. Inside the SP if the second parameter is NULL then its assign value ''
Well, major security flaw here, but other than that (major, critical security flaw), it's impossible to say anything useful about a completely dynamic statement.
Are the writes a problem?
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
May 3, 2017 at 10:08 pm
GilaMonster - Wednesday, May 3, 2017 6:48 AMRechana Rajan - Wednesday, May 3, 2017 4:58 AMGilaMonster - Tuesday, May 2, 2017 5:11 AMRechana Rajan - Tuesday, May 2, 2017 4:59 AMThe procedure have a parameter which is the where clause and the write occurs only when there is value for Where clause.I don't understand what you're trying to say here.
Thanks Gail,
The procedure takes two values as input , the writes happen only when the second parameter have a value ,if its NULL no writes only read. Inside the SP if the second parameter is NULL then its assign value ''
Well, major security flaw here, but other than that (major, critical security flaw), it's impossible to say anything useful about a completely dynamic statement.
Are the writes a problem?
Thanks Gail,
I am a noob in TSQL, can you help me understand the flaw and give advise on how to eliminate this?
As per Dev the whole select was called in a procedure to rectify the error they received when the statement is more than 8000 character in a procedure.
Was wondering why writes on Select only statement and it was giving very high value.
May 3, 2017 at 10:09 pm
Thom A - Wednesday, May 3, 2017 5:49 AMRechana Rajan - Wednesday, May 3, 2017 5:42 AMThanks Thom,
No :w00t:
Ok, before ANYTHING you need to look at that. As it stands, this could be a HUGE PROBLEM.
Thanks Thom,
Can you help me understand more?
May 3, 2017 at 10:24 pm
This is the issue found with the query I hope. Please give more advise.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply