InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:26:02 GMT20http://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Bhuvnesh (1/30/2013)[/b] Does it mean that you added an intermediate step where u pulled out the larger table data into temp table then used it in further steps ?[/quote]Yes, exactly this is what I did.Wed, 30 Jan 2013 01:39:17 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Radu Costescu (1/29/2013)[/b][hr]Finally, I got to a solution. Is not a nice solution (old fashion style) but it works. I have clear criteria for selecting the data from the three major tables, so I decided to isolate the raw data from these tables prior to start processing (summs, groups, data conversion etc). [/quote] Does it mean that you added an intermediate step where u pulled out the larger table data into temp table then used it in further steps ?Wed, 30 Jan 2013 00:25:53 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxPut in a request for an exception to that policy (you have a damn good reason). What I suggest is a procedure with EXECUTE AS a higher privileged user (perhaps even a login-less user) and that proc just updates the necessary stats. That way the app doesn't get any alter permissions and you can still call something in the middle of your execution that updates the necessary stats.If they say no, then you put into an document that the proc is slow due to the stats and those cannot be updated due to policy, therefore the procedure will have to remain slow. (after you test that that is indeed the case)Tue, 29 Jan 2013 23:49:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxGail/Grant/Ed, I agree with you that it can be done. However at times, these stupid policies made by self acclaimed DBA legends works against us. update statistics need alter table permissions on the target tables and which is DDL permission. Unfortunately in our organization, there is strict policy not to provide DDL permission to anyone other than DBAs (not even to application account through which processing is done).Tue, 29 Jan 2013 22:04:36 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxGrant is right. You can fire UPDATE STATISTICS commands from within a stored procedure. They'll execute and then subsequent statements will get the benefits of having fresh and update indexes.Tue, 29 Jan 2013 05:55:55 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]GilaMonster (1/29/2013)[/b][hr][quote][b]sqlnaive (1/29/2013)[/b][hr]there is no way we can updates the statistics in middle of the whole process. [/quote]Why not?[/quote] +1Tue, 29 Jan 2013 05:40:42 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]sqlnaive (1/29/2013)[/b][hr]there is no way we can updates the statistics in middle of the whole process. [/quote]Why not?Tue, 29 Jan 2013 05:39:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxGrant, the link was really great. Shows the issues practically. There was the fix provided:"The fix isn’t hard, a scheduled statistics update, maybe daily depending on when data is loaded and what the queries filter for, fixes this completely. The trick is often realising that it is necessary."Interestingly this situation is similar to our processes but with little difference. Our process starts with data processing on Sybase and then that processed data is bcp'd to SQL tables. Once it is done, the whole new set of processing starts in SQL. However the challenge with us is that the stats of the tables hit in mid of process and there is no way we can updates the statistics in middle of the whole process. I hope I have not confused you guys. Is there anything I cna do in my scenario ?Tue, 29 Jan 2013 05:36:40 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxFinally, I got to a solution. Is not a nice solution (old fashion style) but it works. I have clear criteria for selecting the data from the three major tables, so I decided to isolate the raw data from these tables prior to start processing (summs, groups, data conversion etc). Now it started to work perfectly. Thank you all for the interest and the hints given.Best regardsTue, 29 Jan 2013 05:08:35 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[url=http://msdn.microsoft.com/en-US/library/ms187348(v=SQL.90).aspx]Here's how to manually update statistics[/url]. You'll need to use the WITH FULLSCAN option. This documentation is for 2005, but the 2000 syntax is the same (if I remember correctly).Tue, 29 Jan 2013 04:42:20 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxsp_updatestats does sampled updates. That may not be good enough.Tue, 29 Jan 2013 04:34:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxHello again,I tried updating the statistics using sp_updatestats. Well, now is it worst because executing the stored procedure code is also slow in execution now. Is good that it behave now consistent, but is bad that the problem is not solved yet... :(Tue, 29 Jan 2013 04:23:00 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]sqlnaive (1/29/2013)[/b][hr]Don't you think once the statistics are upgraded, even if the new query plan is created, it will be optimal ? [/quote]Maybe, maybe not. If all the stats are up to date and the optimiser has enough time then it should be good enough. [quote]And secondly, what does it mean by the issues like "not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan" ?[/quote][url]http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/[/url]Tue, 29 Jan 2013 01:28:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]GilaMonster (1/28/2013)[/b][hr][quote][b]Grant Fritchey (1/28/2013)[/b][hr]You can set a traceflag, 2371, to change this behavior. [url=http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx]Here are the details[/url].[/quote]For SQL 2008 R2 and above only unfortunately.[/quote]Grant/Gail, It's very very good article and rather very great thing provided by MS. However as it mentions:"The downside is that updating the statistics results in recompilation of the queries accessing the table. This again can increase the risk of getting a different query plan for the next executions of queries against those tables. On the other side, the dynamic threshold to trigger update statistics should address issues encountered like not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan."Don't you think once the statistics are upgraded, even if the new query plan is created, it will be optimal ? And secondly, what does it mean by the issues like "not finding a new month or new fiscal year for month end reporting in the statistics and hence choosing a sub-optimal plan" ?Tue, 29 Jan 2013 00:47:59 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Grant Fritchey (1/28/2013)[/b][hr]You can set a traceflag, 2371, to change this behavior. [url=http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx]Here are the details[/url].[/quote]For SQL 2008 R2 and above only unfortunately.Mon, 28 Jan 2013 05:26:04 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxYes, the default behavior is that 20% of the data must be modified before statistics are updated automatically (for those desperate to correct me, yes, I know it's more complex a formula than that, but when it comes to very large tables, this is effectively the formula, right?). You can set a traceflag, 2371, to change this behavior. [url=http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx]Here are the details[/url]. In general, you have to figure out if either of these automated updates are adequate or if you need to manually update statistics. If you do manually update the statistics, you further have to determine if you can use the sampled scans or a full scan to get your statistics. There are no hard and fast rules for me to provide here. You have to figure out your own situation. Just know that there is no one size fits all solution for statistics updates.Mon, 28 Jan 2013 05:22:16 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Grant Fritchey (1/25/2013)[/b][hr][quote][b]sqlnaive (1/25/2013)[/b][hr]Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?[/quote]"internal parameters" are called variables. And SQL Server can't sniff variables. The difference is that with a parameter on a stored procedure you get a precise seek against the statistics based on the value provided. With a variable you get a sampled average of the statistics. The issue is, are your queries better off with precise values or with averages? I can't tell you, you have to investigate it. But, the other issue comes into play when the statistics are bad (out of date or sampled instead of a full scan). Then, the precise seek returns a value, but it's an imprecise value leading the optimizer to make bad choices again. So, if you have lots of data being added to these tables and your statistics are going out of date (and this becomes especially true when dealing with date based data that is usually filtering for the latest information) then your query plans are going to go wonky. Your solutions are limited, update the stats. Other than that... you can get into trying weird stuff. If the date range being searched against regularly is the latest data, then create a filtered index that includes today's date. Then the data set will be very small and you could update the statistics several times during the day (assuming the automatic updates don't do it for you).But the issue is, if you want fast queries, you need good statistics and there's very few options around that.[/quote]For the big tables which are constantly getting affected by DMLs and hence impacting the statistics, what should be the solution in production environment ? Though we have enabled automatic updates of statistics on those tables, but I believe these work only on the basis of the amount of records getting impacted by the DMLs and/or on percentage basis only.Mon, 28 Jan 2013 03:44:04 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Ed Wagner (1/25/2013)[/b][hr]As a word of caution, I believe you can do online index rebuild only with the Enterprise Edition of SQL Server. [/quote]and only in SQL 2005 above, so on SQL 2000, it's ofline index rebuilds.Fri, 25 Jan 2013 09:10:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]sqlnaive (1/25/2013)[/b][hr]Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?[/quote]"internal parameters" are called variables. And SQL Server can't sniff variables. The difference is that with a parameter on a stored procedure you get a precise seek against the statistics based on the value provided. With a variable you get a sampled average of the statistics. The issue is, are your queries better off with precise values or with averages? I can't tell you, you have to investigate it. But, the other issue comes into play when the statistics are bad (out of date or sampled instead of a full scan). Then, the precise seek returns a value, but it's an imprecise value leading the optimizer to make bad choices again. So, if you have lots of data being added to these tables and your statistics are going out of date (and this becomes especially true when dealing with date based data that is usually filtering for the latest information) then your query plans are going to go wonky. Your solutions are limited, update the stats. Other than that... you can get into trying weird stuff. If the date range being searched against regularly is the latest data, then create a filtered index that includes today's date. Then the data set will be very small and you could update the statistics several times during the day (assuming the automatic updates don't do it for you).But the issue is, if you want fast queries, you need good statistics and there's very few options around that.Fri, 25 Jan 2013 08:59:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxAs a word of caution, I believe you can do online index rebuild only with the Enterprise Edition of SQL Server. If you have EE, great. If not, taking down the index on a very large table will most likely result in the table being effectively offline. You aren't going to efficiently query 3M rows without lots of disk I/O.If the table has to remain online, consider reorganizing the indexes instead of rebuilding. Doing a rebuild generally yields the best results, but reorganizing is sometimes necessary.Another word of caution, if you alter your clustered index, all nonclustered indexes will be dropped, then the clustered index is changed and built, then nonclustered indexes get recreated. This is necessary because the clustered index gets inherited in by all the nonclustered indexes. This can take considerable time and should always be tested on a test system first.I completely understand being under the gun to get something fixed, but please don't rush too much. Think it through, make sure you understand how it works and test it out.Fri, 25 Jan 2013 07:37:29 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxYou can do a targeted Update Statistics on single tables, the BoL should help lookup the UPDATE STATISTICS TSQL page.You can I beleive run an EXEC sp_updateStats in line as well. however I would also suggest that a Rebuild of indexes be run on a weekly basis during quiet times, 12-5am local is generally a good window. There are also targeted index rebuild scripts available on line that will hit the worst offenders on a nightly basis (say over 60% fragmentation).You can rebuild Nonclustered Indexes on the fly but it will impact perfomance a little using something like ALTER INDEX <index> ON <Table> REBUILD (WITH ONLINE=ON)(check the syntax in BOL).I wouldnt recommend doing this with a clustered index though.Fri, 25 Jan 2013 07:05:16 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]sqlnaive (1/25/2013)[/b][hr]Grant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?[/quote]OK, now I will have to admit my lack of knowledge in SQL Server administration, but is better to look stupid than to be stupid. So, admitting that the customer never updated the statistics for that database (which is a large one, and the tables involved are the three largest within it), can I do the update statistics for these tables in live (production) environment without jeopardizing the user activity? Or I need special conditions (like DB in Single User mode or stuff)? Thanks again.Fri, 25 Jan 2013 06:31:54 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxThat last post makes me think of indexes. Since you changed the code to make things simpler and the database isn't maintained, check to see if the procedure is using any of your indexes. If, say, a change resulted in a full table scan, that would explain the performance degradation. It could be that a single covering index isn't being used any more. If it is using the indexes properly, then definitely go with updating the statistics. Just food for thought.Fri, 25 Jan 2013 06:31:18 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Grant Fritchey (1/25/2013)[/b][hr]I agree. It sounds like parameter sniffing is leading to issues. I would suggest updating the statistics on the tables being referenced. In addition to the solutions for parameter sniffing, just getting better statistics frequently solves the issue.[/quote]I ruled out the parameter sniffing possibility, while within the code I have no such condition (@P1='yyyy-mm-dd' or @P1 is null). I will take the second suggested path, the update of the statistics (involved tables are really big and the customer does not care very much to maintain the database) and inform you.Thank you all for the contributions and interest.Fri, 25 Jan 2013 06:03:42 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxGrant, in addition to this, does it means just declaring internal parameters won't resolve parameter snififng ? Is updating stats mandatory (I know there's always "as per situation" criteria but in generalized way) ? I am asking this because at times it becomes hard to update stats on big tables on daily basis in prod environment. In that case what would you suggest ?Fri, 25 Jan 2013 05:57:55 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxI agree. It sounds like parameter sniffing is leading to issues. I would suggest updating the statistics on the tables being referenced. In addition to the solutions for parameter sniffing, just getting better statistics frequently solves the issue.Fri, 25 Jan 2013 05:36:02 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxSounds like a badplan query plan or a case of Paramater sniffing not selecting the optimal plan for the paramaters. Theres a simple way to test if its parameter sniffing, create internal paramaters in the SP and use those in the query.Eg [code="sql"]CREATE PROCEDURE SP_NAME @P1 Datetime,@P2Varchar(100)ASDECLARE @Date DateTIme=@DateDECLARE @Text Varchar(100)=@textSelect *from TableWhere Col1=@Date and Col2=@Text[/code][strike]Hope that give you the jist, as a quick fix in 2008 you could also issue a hint with the OPTION(RECOMPILE) at the end of the query, HOWEVER this short term fix in order to give you breathing space to put something more robust in place.[/strike]Sorry the above strike through is 2008 specific just noticed you're on SQL 2000.I would suggest reading Gail's blogs on Paramater sniffing [url]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]and Catch all queries [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]hope these help.Fri, 25 Jan 2013 05:14:26 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]sqlnaive (1/25/2013)[/b]What exactly are you checking from query analyzer ? Running the procedure with parameters or the code inside the procedure ?[/quote]I tried both. Running the procedure with parameters (exec sp_name 'yyyy-mm-dd','xxxxxx') result in slow execution, executing the code itself (declare @P1 datetime, @P2 varchar(10) set @P1='yyyy-mm-dd' set @P2='xxxxxx' select ... etc) give immediate results.[quote][b]sqlnaive (1/25/2013)[/b]If the code inside the procedure is running perfectly and procedure not, then try recompiling the procedure and check the result.[/quote]I tried to run exec sp_name 'yyyy-mm-dd','xxxxxx' with recompile (after I dropped the s/p and re-create it, to be sure) but unfortunately still no improvements :(Fri, 25 Jan 2013 04:57:32 GMTRadu Costescuhttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspx[quote][b]Radu Costescu (1/25/2013)[/b][hr]If I execute this s/p, either from calling application code, or from Query Analyzer (exec ...), it take very much time to execute (1.5 - 2 hours). [/quote][quote]But if I directly execute the s/p code from Query Analyzer, the result come almost instantly (5-10 seconds). [/quote]What exactly are you checking from query analyzer ? Running the procedure with parameters or the code inside the procedure ?If the code inside the procedure is running perfectly and procedure not, then try recompiling the procedure and check the result.Fri, 25 Jan 2013 04:34:46 GMTsqlnaivehttp://www.sqlservercentral.com/Forums/Topic1411536-1292-1.aspxHello,Please help me, I'm desperate. I have an old SQL2000 (SP4) - and no, I can't upgrade it, is in my customer server - on which I have a very, very strange issue which started on 01/01/2013. Until then, this problem never appeared.I have an data export stored procedure, which fill a temporary table from a quite complicated select (multiple subqueries, with data cast, sums and so on) and export the final content in another table in a specific format. If I execute this s/p, either from calling application code, or from Query Analyzer (exec ...), it take very much time to execute (1.5 - 2 hours). If I check the status with sp_who2 I see that the process appears to be blocked by itself in the "INSERT INTO" temporary table. But if I directly execute the s/p code from Query Analyzer, the result come almost instantly (5-10 seconds). I tried to simplify the main query, to remove some subqueries and sums, no result 'till now.Does anybody faced this problem? Is there any solution, except re-writing the whole s/p from scratch?Interested fact: I have a similar (not exactly the same, but same kind) of stored procedure, which retrieve from exact same tables similar data in a similar way for a report, and this is working fine...Thanks in advance for any hint that may get me out of this problem.Fri, 25 Jan 2013 02:11:02 GMTRadu Costescu