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?
/* ... */
instead of line comment things were OK.