July 7, 2011 at 6:22 am
[font="Times New Roman"]
Hello Marry,
Pease understand if i wrote SP to avoid SQL Injection then obviously i would not any ad-hoc query within SP.
Regards,
Anil
[/font]
July 7, 2011 at 8:09 am
Marry Krissmess (7/7/2011)
anil_kumar32 (7/7/2011)
[font="Times New Roman"]Good things that comes with SP are security, less network traffic and SQL injection aviodance.
[/font]
Attention with security. Sometimes SPs execute with admin permissions even when the user does not have this level. Network traffic is indeed reduced. BUT you can easily write an SP where injection is possible for example when you create the ad-hoc SQL inside the SP.
Only when you do that wrong. for an example of how to generate dynamic SQL in a stored proc and still be relatively safe from injection read Gail's blog
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
July 7, 2011 at 9:08 am
Hi Suresh,
Good article!
Is it really necessary to include the database name in all object references? I've always included schema.object but not database name as it's impossible to deploy the same code base on many databases if you hardcode the db name.
Surely the search for object statistics will start in the current database so adding the db name won't help. Also shouldn't we be using synonyms when making cross database queries.
July 7, 2011 at 10:17 am
If option recompile is used the plan doesn't remain in cache so it won't lead to cache bloat. Disclaimer, don't run this in production.
dbcc freeproccache
go
select * from msdb.dbo.sysjobs option (recompile)
go
select * from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text like '%sysjobs%' option (recompile)
go
select * from msdb.dbo.sysjobs
go
select * from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text like '%sysjobs%' option (recompile)
go
While looking into this I found that the recompiled statement didn't exist in sys.dm_exec_query_stats either which has performance troubleshooting implications.
July 7, 2011 at 10:26 am
OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.
I'm not sure I agree with this. I've seen cases where there's a statement that doesn't take a lot of resources to compile and depending on the parameters passed in different execution plans can be generated. Statements like that are good candidates for recompiles. Granted, it may be more efficient to compile it with a specific parameter but that depends on the situation. Using option recompile certainly isn't the first thing I would look at but there are times it may end up being the best tool.
July 7, 2011 at 10:52 am
We have two e-books here that can help you learn more about this topic:
Inside the SQL Server Query Optimizer - http://www.sqlservercentral.com/articles/books/71564
SQL Server Execution Plans - http://www.sqlservercentral.com/articles/books/65831
July 7, 2011 at 10:54 am
Thank you Suresh, this was a useful article, quite simple to follow with neat examples.
M&M
July 7, 2011 at 11:08 am
Dear reader,
What I meant by thae statement is the following:
Usually developers tend to use TSQL from their application instead of SP's. Then their plan cache fills up faster that usual. Then they use OPTION (recompile) as an answer to reduce plan cache contention. And continue to use adhoc TSQL code from their application. This kills the many benefits of SP's completely.
July 7, 2011 at 11:23 am
I too am curious about having to fully qualify the object w/ db name. Can someone please elaborate? I haven't heard that before.
Thanks.
July 7, 2011 at 11:42 am
"Is it really necessary to include the database name in all object references? I've always included schema.object but not database name as it's impossible to deploy the same code base on many databases if you hardcode the db name..."
Hi,
Although SQLBOL says use DBname.SchemaName.ObjectName, I have found in my practical experience that SchemaName.ObjectName itself suffices. I have not found any unnecessary recompilations due to usage of SchemaName.ObjectName. But I have when using just ObjectName.
July 7, 2011 at 11:43 am
Thanks for the links Steve!
July 7, 2011 at 2:21 pm
July 7, 2011 at 5:27 pm
WITH RECOMPILE has been a new friend of mine for few months only now,
we have a few SSRS reports running against our data warehouse and some of them have many parameters, including a date range
and basically without WITH RECOMPILE SQL Server will simply cache the plan used for the first execution after the CREATE statement runs, then use it for all subsequent executions... (which so far is exactly what we would want most of the time)...
but the plan the optimizer will use and cache for a request that had a very wide date range and say 'null' in all other input params will not be optimal for let's say the next request which may have a much narrower date range and a few other input parameters set to different values
WITH RECOMPILE shouldn't be used blindly but in some cases (vast minority...) it turns out that the overhead of recalculating the plan each time is less then the overhead of having a less than optimal plan running.
On an OLTP system in the past I personally never found WITH RECOMPILE useful.
Claude
July 8, 2011 at 1:41 am
cfradenburg (7/7/2011)
OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.
I'm not sure I agree with this. I've seen cases where there's a statement that doesn't take a lot of resources to compile and depending on the parameters passed in different execution plans can be generated. Statements like that are good candidates for recompiles. Granted, it may be more efficient to compile it with a specific parameter but that depends on the situation. Using option recompile certainly isn't the first thing I would look at but there are times it may end up being the best tool.
Yes, e.g. in SAP environments all selects to the DB from the business application are going to the DB interface via parameterized SPs. But e.g. for the iDOCs the status will be changed after each processing of it and the status flag has a very high inequality in the distribution of values per status. And also the autoupdate of statistics don't runs often enough by the 20% thrashould value. We so run daily an update statistics manually for the status field and recompile all executions with an hint in the ABAP-code which going to the IDOC status table with very good performance results per execution. In this case, the distribution of date is unequally, the auto update statistics runs not often enough; the parameters are very different and the selects runs seldom in relation to other selects. Here the recompile is a very good option.
Suresh Kumar Maganti (7/7/2011)
Dear reader,What I meant by thae statement is the following:
Usually developers tend to use TSQL from their application instead of SP's. Then their plan cache fills up faster that usual. Then they use OPTION (recompile) as an answer to reduce plan cache contention. And continue to use adhoc TSQL code from their application. This kills the many benefits of SP's completely.
Thats absolutly understandable.
Harveysburger (7/7/2011)
...On an OLTP system in the past I personally never found WITH RECOMPILE useful.
Claude
Never say never 😉
July 8, 2011 at 6:53 pm
hehe yes my mistake,
I didn't mean to say that it should never happen, just that from my own past experiences I haven't seen a good use for it, but I only worked on a handful of OLTP systems so who knows 🙂
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply