July 30, 2016 at 12:56 pm
Hi All,
Sorry to ask this question in this forum. Its more kind of general and not a technical question.
Can anyone tell me what are the drawbacks of using Production Server as a Testing Server.
Recently, I got into an uncomfortable conversation with my manager who is a not so technical but he wanted me to
do performance testing of slow running SQL Agent jobs which client was asking for a long time.
I just joined this team a week before and this is my 1st task assigned to me!!
They are blunt and wanted me to work on the same.
I explained to some extent that doesn't work and if any changes made to the code and re-running/creating indexes/updating stats/turning on trace flags/running traces,
will lead to adverse performance of ongoing/running workload. Still they insist me to do that. I just kept quite and came out of that discussion room.
I need some inputs/specific usecase's of what can go wrong if we use a PROD server for testing purposes. How can I convince these management guys that what I am supposed to do is wrong and in a polite way.
Not sure how to convey the BIG NO NO message here..
Can anyone help me with some specific use case so that I can convince my management and get a testing environment.
Thanks.
July 30, 2016 at 6:51 pm
I tune production systems for a living and have been doing this for about 20 years now. There are exceptionally few companies out there that have a test platform that even remotely resembles their production hardware, and many don't even restore their main (often VERY large) databases down to a test server very often either for many reasons. Yes, you have to be careful and take steps to measure impact and minimize the probability that something is adversely affected. Yes, you have to set managements' expectations and understandings that something bad could happen. Yes, you do have to be much more competent so you know how to better judge if something you want to do for tuning will make other stuff worse.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 30, 2016 at 11:05 pm
Thanks Kevin for sharing your thoughts.
July 31, 2016 at 2:32 am
Piling on Kevin's fine answer; production system tuning should not be an trial & error process but rather information gathering with careful interactions and modifications to the system. Knee jerks like blindly adding indices based on the sql server missing index recommendations etc. have no place on production systems.
😎
Be careful and understand the impact when testing long running jobs/tasks on a production system, I've seen such tests fill logs, data files, drives and saturate tempdbs, something you do not want to run into.
July 31, 2016 at 6:16 am
As with all else in the world of databases, "It Depends".
While I agree that production shouldn't be used as a testing system, it's rare that any other system will be under the same load as the production system. Sometimes, the only place to do some performance testing is in production. I agree that it should NOT be a willy nilly creation of indexes. The code that has a problem should be clearly identified from production because, a lot of times, an index won't help at all. If it's code that needs to be reworked for performance, then that becomes a development process and should be done on some dev box and follow the promotion and testing process from Dev to Test to Prod. Sometimes, though, all that is needed is an index and unless someone is stupid enough to make an index with a very low cardinality leading column, there's usually no reason why the index couldn't be added directly in production. On reason would be if the index is huge and can't be built in an online fashion... you might want to wait for or schedule and outage for that so it would be worthwhile to test the index as if it were code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply