3

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state for a long duration. I've ran this sp to track down what query is being ran by the processes and to my surprise, the following is what we discovered:

  --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''  
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', 
  ... Several more executes that are commented out

Here's what our SP looks like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''  
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', 
ALTER              PROCEDURE [SomeDB].[SP_POS_SU012_XML] @CRDATE        VARCHAR(08) ,
                    @CUS_CD     VARCHAR(10) ,
                    @PRODUCT_CD     VARCHAR(13) ,
                    @ORDER_GB   VARCHAR(03) ,
                    @CANCEL_YN      VARCHAR(01)  
                            ...

We weren't sure if the commented execute codes we defined for testing purposes in SP were actually being ran with the SP calls. From our internal testing, we couldn't reproduce the same behavior, but removing those commented lines of execute seemed to do the trick as sp_who2 no longed showed hundreds of processes stuck on SELECT.

But since I have absolutely no way to tell if this was the real cause or not, I have to ask: could such comments actually produce side-effects described here? Or worse, could this have happened from a deliberate attempt to attack our server/database?

3
  • 1
    I have seen situations where such comments caused trouble (it's been a while, can't recall the exact circumstances). After using block comments /* ... */ instead of line comment things were OK.
    – user1822
    Apr 16, 2014 at 5:46
  • 2
    It may not be related to this problem, but I'm going to throw in this link to one of my blog posts because IMO block comments should be used as a standard practice anyway. voluntarydba.com/post/2012/09/18/…
    – Jon Seigel
    Apr 16, 2014 at 13:25
  • It could be as simple as a bad execution plan due to parameter sniffing that sorted itself out on the recompile that happened after you modified the stored procedure. Apr 20, 2014 at 17:37

1 Answer 1

2

I've never heard of comments causing a problem.

The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code.

For testing you could add another INSERT to the SP, writing as single value to a dummy table. Then if the commented-out lines run there will be more rows in the dummy table than expected.

1
  • Oh woops. That was a mistake on my part. The code is supposed to be the same but I miscopied them.
    – TtT23
    Apr 17, 2014 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.