Large Comment Blocks Causing Issues

Question

Large Comment Blocks Causing Issues

David Paskiet

SSCrazy

Points: 2949
More actions
September 2, 2011 at 8:39 am

#96428

I have had some real performance issues as well as some 'phantom' problems. After a long and arduous search I found some articles that shed some light on the issue. It was in part or total due to these large comment blocks the developers put in the stored proc as a comment block. IN some instance I removed these novelettes and VIOLA! my problem went away.
(Still here? Good.)
Developers, myself included so no smackin' please, being who they are do not want to believe the DBA (who is also me) that these blocks can cause this issue even though the problems in thos procedures and processes have disappeared.
(Stay with me I am getting to the point.)
Apparently my making the problem go away is not good enough and proof is needed! I cannot find those article again! I don't even remember how I stumbled upon them. If anyone can help me find these link from the 'reputable' people I can provide them and get on with my life. 😎
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

comments are ignored by SQL server completely...it's much more likely that because you edited the procedure, it was recompiled,a nd the recompilation made a better plan, which executes faster than the previous plan.

i'd be expecting bad statstics or parameter sniffing as the main performance culprits for your procedure.

If as time goes on, the procedure seems to slow down again, and that would be because statistics need to be refreshed.

bad statsitics makes procedures plans not as efficient as they were at compilation

if the procedure works quick when you test it in SSMS, but slow on production, i'd suspect parameter sniffing.

Let us know some more details, so we can help furhter!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Paskiet SSCrazy Points: 2949 More actions · Answer 2

Parameter sniffing is a separate issue that I had them deal with. As for the coment block, there is somthing regarding the amount of lines and size of the comments that causes the procedure to act really weird and slow as molasses. I have proven they are the problem on many occasions but I cannot find the articles that led me to this as being an issue.

BTW - when I say large coment blocks I am talking well over 500 lines.

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you get an execution plans of the same proc with and without the huge comment blocks? that would be where i'd look first, i think...there's GOt to be a difference.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Paskiet SSCrazy Points: 2949 More actions · Answer 4

As I remember the article, that is the root of the problem. Due to the large block, the compiler/execution plan cant do it's thing properly. so pretty much it is a recompile each time the proc is executed.

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Lowell SSC Guru Points: 323518 More actions · Answer 5

David Paskiet (9/2/2011)
As I remember the article, that is the root of the problem. Due to the large block, the compiler/execution plan cant do it's thing properly. so pretty much it is a recompile each time the proc is executed.

weird; everything i ever heard was that comments are fine in procedures, recommended even, as they are ignored by the compiler;

i'm testing my Google-Fu to see if it's up to the task...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Paskiet SSCrazy Points: 2949 More actions · Answer 6

you and me both. I comment the hell out of my stuff. I dont think the couple lines here and there are an issue. I think it is only the 'War and Peace' blocks. Let me know if your Google-Fu works better than my Google VooDoo ritual did.

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **

Lowell SSC Guru Points: 323518 More actions · Answer 7

well i found a post stating the obvious: adding debug stuff like PRINT statements would slow down a proc, and forgetting to sue using SET NOCOUNT ON to avoid extra messages like rows affected.... but still looking..

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

A plan won't be cached if there's an >8000 character literal in the query, but that's a literal, not a comment. The SQL parser strips comments out, what gets to the query optimiser (a query tree) has no comments in it, in fact bears no direct resemblance to the query entered.

By literal, I mean this:

SELECt <stuff>

FROM SomeTable

WHERE LargeColumn = 'adsfjnklhfdsjf .... 8000+ characters later asdadjjj!!!!'

That query's plan will never be cached due to the length of the string in it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Paskiet SSCrazy Points: 2949 More actions · Answer 9

Good Stuff. I will find those articles! I wasnt drinking at the time. LOL

Kindest Regards,
David

** Obstacles are those frightening things that appear when we take our eyes off the goal. **