Phil Parkin wrote:
If the procs don't contain DML, what do they contain?
ok, let me elaborate a bit more.
The idea that I am working on, is to add a debug flag as parameter to my procs. when the value of debug flag is 1, then most of the important queries timing will be printed and with the help of IF ELSE, DML commands will be run as SELECT COUNT(*). If Debug flag is 0, then all DML operations will be done and no timing will be printed.
Now the idea is to run the copy of proc with debug flag=1 in prod before deployment, so that we are sure of the timing it is going to take when actually deployed. We are doing this as we have burned our hands many times when a proc was working fine in Performance env. but ran extremely slow in Prod because of index mismatch, variation in data volume, server load etc.
But we have a risk here what if the copy of proc is ran with debug 0 by the person who runs it in prod for performance check? it will modify the actual data and will be a disaster. So i wanted to prevent the copy proc to do any DML even though it has DML commands in it.
I hope I made it clear. Pls ask questions if not.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.