January 12, 2014 at 4:46 pm
hello folks,
we are using dynamic sql in most of the stored procs for application needs, this is causing performance issues.
How can improve overall performance of database i know this is a generic question
enabling parameterization
or MAXDOP
or some other new features in sql2008 or 2012 or 14?
January 13, 2014 at 12:23 am
Dynamic SQL doesn't automatically cause performance issues by itself (security issues however...)
What symptoms are you seeing, why do you attribute the problem to the fact that the query is dynamic, rather than the query itself?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2014 at 4:57 am
Like Gail says, T-SQL is T-SQL. Dynamic T-SQL will prevent some levels of code reuse which can lead to increased CPU use as more and more queries have to go through full optimization, but in general, it doesn't, by itself, lead to poor performance. So, the methods for troubleshooting and making it run faster are the same as you would do with stored procedures. Gather performance metrics such as wait states to understand where your bottlenecks lie. Gather query metrics through extended events so you can see which queries are the longest running or the most frequently called. Then, determine from those queries if it's code or indexing to make things run faster.
Always work from a position of knowledge. Simply saying "slow" and then throwing switches on the server such as disabling parallel processing by mucking with MAXDOP is absolutely not the way to go about tuning. In fact,it's a good way to make performance worse rather than better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2014 at 5:51 am
thanks guys. few issues i noticed is queries are running slow, not always but few times
stored proc need to create a execution plan whenever there is change in parameters
what can be done in this scenario?
January 13, 2014 at 6:05 am
mxy (1/13/2014)
thanks guys. few issues i noticed is queries are running slow, not always but few timesstored proc need to create a execution plan whenever there is change in parameters
what can be done in this scenario?
Not enough information to guess. Identify the slow queries, get their details and execution plans
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
You troubleshoot poor performance from dynamic SQL exactly the same way as poor performance from anything in SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2014 at 6:28 am
Again, I'm with Gail. I couldn't even guess. It could be almost anything from bad parameter sniffing to problematic code to missing/incorrect indexes to one query moving a lot more data than another. If it's just the last thing, moving large amounts of data, sometimes the only way to tune that is either restrict what the application can ask for, or throw hardware at the problem. But even saying that much is still making guesses way beyond any information you've supplied so far actually warrants.
Again, don't guess. Gather the metrics. Know. If you know what is running slow and when, you then start figuring out what to do to make it run faster.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2014 at 5:36 pm
thanks again. I dont have any performance issues right now to share execution plan or ddl code. when we discuss last we in team meet we have discussed all the topics i specified earlier.
our application uses lot of dynamic sql, since sql cannot reuse the execution plan we thought of enabling parameteriztion would be a option and others based on further analysis.
let me know your opinion
January 13, 2014 at 11:40 pm
mxy (1/13/2014)
our application uses lot of dynamic sql, since sql cannot reuse the execution plan
Why can't SQL reuse the execution plans?
If you're properly parameterising your Dynamic SQL (necessary for avoiding SQL injection), then the plans can be reused just as one for a procedure.
If you don't have a performance problem, don't go poking at options and settings randomly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2014 at 3:46 am
mxy (1/13/2014)
thanks again. I dont have any performance issues right now to share execution plan or ddl code. when we discuss last we in team meet we have discussed all the topics i specified earlier.our application uses lot of dynamic sql, since sql cannot reuse the execution plan we thought of enabling parameteriztion would be a option and others based on further analysis.
let me know your opinion
I don't believe in changing settings and configurations without an understanding of why I would be changing them. There is not a single switch or setting that you can change that would take badly written queries and make them run faster. The problem is not some switch or setting in the case. It's the queries. The solution is to fix the queries. Just because they're dynamic doesn't mean you can throw your hands up. If the code that generates the queries is badly written, fix it.
After you fix the code and after you address the use of parameters as Gail says (please, look up and understand the serious consequences of SQL Injection), there is something you can do to help situations where you have lots of dynamic SQL, but it only helps a little bit. You can turn on the 'Optimize For Ad Hoc' setting. That will slightly improve memory management by storing plan stubs the first time a query is called instead of storing the entire plan. But it's only going to be a marginal improvement. You will absolutely be best served by getting the queries right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply