April 20, 2007 at 6:34 pm
Hi All,
I want to know if choice of parallelism is anyway dependent on whether the database has Auto Statistics update on or not.
Also, I have peculiar problem, there is this search procedure which does search on 2 tables having more than 10 million rows each. The search procedure is designed in Dynamic SQL to give it flexibility. The execution plan for this procedure almost always uses parallelism. (Server has 32 processors).
According to microsoft compiled queries (non - dynamic) are lot better than dynamic queries, following this dictum we changed the procedure to use normal compiled queries. But the procedure performance went really bad, it screwed up big time with load tests. The reason is with compiled queries SQL Server does not choose to have parallelism and the queries run a lot slower.
Can you explain this? Also, our database is configured not to have auto update on statistics, is this anyway causing the problem.
April 24, 2007 at 3:07 am
First, about statistics....
The optimiser chooses how to run the query depending on the values held in the statistics. If your statistics are so old they no longer accurately describe the characteristics of your data, the optimiser is likely to choose a poor access plan.
The statistics need to be statistically accurate. This does not mean they need to be accurate to the latest second, or even the latest day, but they need to show what your data is like.
In SQL 2000, autostats ON will cause statistics updates at unpredictable times, and these updates will be synchronous with the triggering query. This can cause SLA issues, so many installations prefer to do manual statistics updates at known times to avoid SLA problems.
For a volatile table, the statistics need to be up-to-date because the row count changes often, but these tables are often small and may always be accessed by a table scan. For the 10m row tables you have, it is often safe to run a manual statistics update once per week, but you need to check this in your installation.
In SQL 2005, you can set automatic stats updates to run asynchronously. This means you can get the benefit of the latest stats without keeping a query waiting, and avoiding possible SLA problems. I would recommend this as the standard approach for SQL 2005.
Regarding your SP...
SQL has to decide on a query plan when the SP is compiled. If you code in a SP SELECT a FROM b WHERE c = @value, SQL does not know the value of c for when the query runs, so it has to make standard assumptions. These assumptions could cause a poor access plan to be generated, compared to dynamic SQL where the run-time values are known. This does not happen often, and normally a SP will give as good access plan as dynamic SQL with the performance benefit it does not need recompiling every time it is run.
You can try a plan hint of ROBUST PLAN, which may give a better access path, or mark the SP as needing recompiling every time it is used (simply giving your SP a name starting 'sp_' will do this...) The main advantage of having a sp that is recompiled at run time over dynamic SQL is that the SQL text is often more visible for audit purposes. If this is not an issue, you may end up finding that dynamic SQL gives the best performance for this particular query.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 25, 2007 at 6:50 am
1) For a dynamic search on large tables you are probably best using dynamic sql, since the cost of the compile is often MUCH, MUCH less than the cost of a bad cached query plan.
2) Use the WITH RECOMPILE option if you do use a sproc, although this could be even worse than the dynamic code.
3) Even if you make a sproc for each type of search option, you will still run the risk of having a sub-optimal plan cached.
4) >> "mark the SP as needing recompiling every time it is used (simply giving your SP a name starting 'sp_' will do this...)" I have never heard of that. Do you have a link that shows that sp_... named sprocs are compiled automatically on execution?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 25, 2007 at 7:59 am
I think the trick with sp_ is in BOL somewhere, or Google will know where to find it...
If a sp starts 'SP_', SQL assumes it lives in master, regardless of how you qualify the call. It will search in master, and if it is found it will use the compiled version. If it is not found in master, it will mark any compiled version as invalid, and search in the explicit (exec mydb..sp_fred) or implicit (use mydb;exec sp_fred) database. When it is found, it is compiled and run. The next call to sp_fred causes SQL to search again in master.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 25, 2007 at 8:08 am
It's usually recommended that you avoid the 'sp_' prefix. Like you stated, it will search master first, every time.
Assuming it finds a version of the SP in master, the version in your local database will never be run.
Assuming it does not find the SP in master, you're going to make SQL Server perform an unecessary search through master each and every time you want to run the SP.
Also, though not extremely likely for SQL 2005 (and even less so for SQL 2000), MS could theoretically implement a system stored proc with the same name as your 'sp_'-prefixed SP, in which case you can count on all your apps that rely on that SP breaking.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply