How stored procedure could create performance issue, can anyone show production DB stored procedure which creates performance issue. Please it will help me to improve
There is no 1 solution for everyones problem, so even if someone gives you sp and points the problems doesnt mean it will fix all your problem.
You need to follow basic principle of writing correct sql and then make changes according to you target database needs.
Like for someone Users table may need index on UserName and Address whereas depending on data in question and their use you may need it somewhere else.
One may be dealing with financial applications where dirty read is not allowed whereas for you, you maybe able to change isolation level to give a greater performance gain.
The list goes on, so follow principle of creating index, using proper joins, optimising functions and their use, setting isolation level where need be and then change things as per your production needs.