November 5, 2015 at 10:17 pm
Hello all!
Currently, in the new environment I'm working in, there are no stored procedures everything comes in as Ad-Hoc T-SQL with local variables.
I brought up that we could save system resources by introducing stored procedures (found multiple queries which run hundreds of times an hour that only have one parameter and always return one (but different) result each time), rather than using a local variable each time.
I encountered some push back (because they don't want to deal with getting approval from the DBA to modify stored procedures once they are in place), and they said they would rather just use sp_executesql.
After some research sp_executesql looks better than local variables and Ad-hoc T-SQL but I still feel like stored procedures would be better. Any thoughts/help on this would be welcome.
Thanks!
November 6, 2015 at 12:04 am
jigging.tech (11/5/2015)
Hello all!Currently, in the new environment I'm working in, there are no stored procedures everything comes in as Ad-Hoc T-SQL with local variables.
I brought up that we could save system resources by introducing stored procedures (found multiple queries which run hundreds of times an hour that only have one parameter and always return one (but different) result each time), rather than using a local variable each time.
I encountered some push back (because they don't want to deal with getting approval from the DBA to modify stored procedures once they are in place), and they said they would rather just use sp_executesql.
After some research sp_executesql looks better than local variables and Ad-hoc T-SQL but I still feel like stored procedures would be better. Any thoughts/help on this would be welcome.
Thanks!
There are many reasons why stored procedures are a better option to ad hoc queries and I'm quite surprised that the DBA isn't forcing the other way around, allow only stored procedures and blocking the ad hoc queries.
😎
Look at the plan cache, plan reuse count in particular, compilation counts etc.
November 6, 2015 at 10:39 am
Hi,
I'm actually the DBA, only been with the company for a few months and they've never had a DBA before so it's a lot of politics.
As I said, I'm trying to enforce stored procedures by A) they don't want to give up control of code and have to go through me (Stored procs would require me to create/alter VS right now they just add new T-sql in the application layer)
So far it looks like the sp_executesql created a prepared statement and the plan is reused even when I change the input for the variable.
I'm mostly trying to see if there are known performance differences or anything I can use to backup my desire to use Stored Procs (other than it adds a layer of control on what goes into Production)
November 6, 2015 at 10:46 am
one thing that Eirikur left out was the fact that you can grant rights to stored procedures to individual groups/users and save yourself a lot of hassle that way too. users without rights to an object can't use it. That could save you a lot of headache too... not to mention people doing questionable things in ad hoc SQL...
November 6, 2015 at 11:18 am
I'm assuming they use sysadmin credentials as well to connect to the database through the app, because of course it won't work otherwise. (I hope the sarcasm sign was visible)
They could have the possibilities to change anything in development, but why would they have direct access to production? Is there any kind of security in place to prevent a change that would affect the production environment? Permissions is the way to go and security will put management on your side as long as you can show some disasters that can occur if db code has no real control.
Why would they have problems when making changes to a procedure? Is their code that ugly?
November 6, 2015 at 12:44 pm
Thank you all for the replies.
The applications connect to a middle man home grown application that then sends the T-sql queries to production... so 85% of the queries all come from the same login.... SQL Server sends the results to the middle man who then sends it back to the applications.
From my understanding this was put in place to "load balance" / "give order" to queries.
One of the things I'm working on is adjusting the security access to read only rather then db_owner.
Yes, a lot of the queries could stand optimization, but the biggest hurdle is they don't want a "slower" process (dev>QA>production). They want to keep the current system of, modify in the application layer. Example: Change a query from selecting from 1 table to running with 5 joins.
So yes, there is a lot that needs to be done in the environment and politics aside (which considering there has never been a DBA here before, it's a huge culture shock to have someone saying all these things need to change), I have to have something solid behind me other than "Stored procedures are more secure and help add a layer of protection since new code has to be put in by the DBA".
So far from testing I ran
declare @p1 int
exec sp_prepexec @p1 output, N'@p1 char(21)' ,N'select * from DATABASE..TABLE
where type =2
and number = @P1',1'
exec sp_execute @p1,'number here'
exec sp_execute @p1,'another number here'
The plan was reused but is there a performance gain (plans stay in cache longer, easier to optimize, ect) by turning that into a stored proc and calling execute stored_proc_name numbers-here
November 6, 2015 at 4:17 pm
jigging.tech (11/6/2015)
Thank you all for the replies.The applications connect to a middle man home grown application that then sends the T-sql queries to production... so 85% of the queries all come from the same login.... SQL Server sends the results to the middle man who then sends it back to the applications.
From my understanding this was put in place to "load balance" / "give order" to queries.
One of the things I'm working on is adjusting the security access to read only rather then db_owner.
Yes, a lot of the queries could stand optimization, but the biggest hurdle is they don't want a "slower" process (dev>QA>production). They want to keep the current system of, modify in the application layer. Example: Change a query from selecting from 1 table to running with 5 joins.
So yes, there is a lot that needs to be done in the environment and politics aside (which considering there has never been a DBA here before, it's a huge culture shock to have someone saying all these things need to change), I have to have something solid behind me other than "Stored procedures are more secure and help add a layer of protection since new code has to be put in by the DBA".
So far from testing I ran
declare @p1 int
exec sp_prepexec @p1 output, N'@p1 char(21)' ,N'select * from DATABASE..TABLE
where type =2
and number = @P1',1'
exec sp_execute @p1,'number here'
exec sp_execute @p1,'another number here'
The plan was reused but is there a performance gain (plans stay in cache longer, easier to optimize, ect) by turning that into a stored proc and calling execute stored_proc_name numbers-here
So what you are saying is that the application doesn't go through a "slower" process (dev>QA>production).
Sounds like if it works it's production else it was a test.
November 6, 2015 at 4:55 pm
Your intentions are good one's but you've walking into the lion's den wearing a pork chop necklace and gravy for hair tonic. 🙂
The only way that you're going to be able to effect a change is through proof and that proof must be substantial performance gains realized by demonstrable, repeatable code. If you can't prove it with code, then it's not proof.
Like I said, the performance gains are going to need to be "substantial". Many will balk at something like changing a 100ms bit of ad hoc code to a 5 or 10 ms gem until you can prove that the system is being taxed because such code runs 50,000 times in an 8 hour day and that there are dozens of such pieces of code.
That'll come but I don't recommend starting with that. Being the new guy on the block and entering a shop where there's a heavy stigma about DBAs, you need to earn your stripes before anyone will listen to you in areas of performance. Find the worst problem there is and fix that. It will probably be one of those things that run 50,000 times in 8 hours and that would be good luck because that also means it's likely going to be a piece of their code.
If you're successful, don't gloat over your "kill". Enable them to do the same. Sit in their midst and listen before you try to help. As David Poole once so very accurately and thoughtfully stated, "If you're the first person that people turn to for database help rather than the last, then you might be an exceptional DBA".
Earn your stripes... treat the Developers with respect... help them help themselves and it will help you. Give them the opportunity to see it your way because "A man forced against his will is of the same opinion still". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply