.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

Update statistics and cache plan invalidation

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server
 

Hi,

I assumed that whenever we run update statistics on the database tables it would invalidate all the plans in cache involving those tables and SQL Server would generate a new execution plan for queries involving those tables but this is not a behavior we are getting on test system.

Let me know if this assumption is not true and we need to clear cache after running update statistics to make sure SQL Server generates optimal plan with new statistics.

Following is the sample code I tried on SQL Server 2005sp3:

drop table test_stats
create table test_stats(id int not null primary key, id1 int, id2 int, id3 char(1000))
create index idx_dd1 on test_stats(id1)
--The following statement would generate execution plan with full scan on test_stats
select * from test_stats where id1 = 100
set nocount on
declare @i int
set @i = 1
while (@i < 10000)
begin
insert into test_stats values(@i,@i,@i,@i)
set @i = @i+1
end
update statistics test_stats with fullscan
--The following statement still generates execution plan with full scan on test_stats even after updating statistics
select * from test_stats where id1 = 100
dbcc freeproccache
--The following statement generates execution plan with index seek after clearing cache
select * from test_st


View Complete Post


More Related Resource Links

Maintenance Update Statistics plan

  
Hi, I've created a maintenance plan for many databases with these tasks (SQL 2005) 1- shrink Database 2- Update Statistics 3- Cleanup History 4- Backup Database (Full) 5- Maintenance Cleanup task The maintenance plan failed at task Update Statics.  Here the error message : Error number : -1073548784 Error message : Executing the query "UPDATE STATISTICS [dbo].[LOG_DETAILS] WITH FULLSCAN " failed with the following error: "Could not continue scan with NOLOCK due to data movement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. My question :How do I know which database is causing the problem and how can I fix the problem ? I don't know a dbo database? thanks,   Jasmin  

SQL Server Maintenance Plan - Update Statistics fails if schema contains special chars

  

We have a database schema with a period in it's name.  This is valid as per http://msdn.microsoft.com/en-us/library/aa224033(SQL.80).aspx. When we create a maintenance plan to update statistics it will succeed if the object property is set to 'Tables and Views'. If this is set to 'Table' it will fail.

Steps to Reproduce

  • Create a new schema in a user database with a period in it's name, ie
    CREATE SCHEMA [Windows.EventLog] AUTHORIZATION [dbo]
  • Create a table with owner using schema created in #1, ie
    CREATE TABLE [Windows.EventLog].[Computer](
    
    	[ComputerId] [smallint] IDENTITY(1,1) NOT NULL,
    
    	[ComputerName] [varchar](255) NOT NULL,
    
     CONSTRAINT [PK_Computer] PRIMARY KEY CLUSTERED ([ComputerId] ASC)
    
    ) ON [PRIMARY]
    
    
  • Create a new maintenance plan, drag 'Update Statistics Task' to designer surface, edit properites, choose the user database, View T-SQL and test execution: it will work
  • Edit maintenance plan and modify 'Update Statistics Task', change Object to Table, change selection to all, execute task and it will fail. Also, if you now try to click 'View T-SQL' or change it back to 'Tables and Views', a error dialog with title 'Microsoft.SqlServer.MaintenancePlanTasksUI' shows will message 'Object

Improving performance of UPDATE CUBE by executing preceding CREATE CACHE for the same cell / subcube

  
Hi All, Recently I came across one of suggestions by Microsoft Advisory consultant regarding optimization data write-back pattern for the SSAS cubes. It was recommended issuing CREATE CACHE command following by UPDATE CUBE for the same cell/subcube definition: CREATE CACHE FOR [MYCUBE] AS ("Cross join of all the dimension members defining subcube to be updated") UPDATE CUBE [MYCUBE] SET ("Cell") = 100, ("Another Cell") = 200,... etc. for every cell. This seems quite weird, but based on our benchmark the entire process (CREATE CACHE and UPDATE CUBE) takes less time than just UPDATE CACHE. Folks, can someone advice on the insights of the process? Shell this approach to be suggested for an enterprise-level planning application? Regards, -h

Re-indexing or update statistics after purging data

  
All,   I’m purging 250GB from a 1TB Multi SQL server databases in one weekend.   After running a re-indexing job do I have to run update statistics?   It takes about 5 hours to run 5% stats, so it will take a loooong time to do 100%.   I have been told that I don’t need to run update stats if I ran Re-Indexing, What do you think?

Re-index or update statistics after purging data

  
All,   I’m purging 250GB from a 1TB Multi SQL server databases in one weekend.   After running a re-indexing job do I have to run update statistics?   It takes about 5 hours to run 5% stats, so it will take a loooong time to do 100%.   I have been told that I don’t need to run update stats if I ran Re-Indexing, What do you think?

UPDATE STATISTICS - Could not allocate space for object - Tempdb

  

Hi Everyone

I have scheduled a maintenance plan for index & update stats one after another.  Now-a-days this job is failing with below error.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Error Number: -1073548784

Executing the query "UPDATE STATISTICS [dbo].[***********]
WITH FULLSCAN
" failed with the following error: "Could not allocate space for object 'dbo.SORT temporary run
storage:  142101814116352' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Tables & Index Details

name rows reserved data index_size unused

*** 46613190 44534360 KB 37240624 KB 7293248 KB 488 KB

update statistics with fullscan and number of steps in histogram;

  

Hi I have a sql 2008 EE x64 sp1 on win 2008 x64. For a pk statistics, when I run update statistics with fullscan the number of steps in histogram drops from say 150 to less than 5? Is there any eason behind this? why would the number of steps drop instead of increase when i do an update with fullscan?

Thanks

I have currently

Name         Updated                             Rows                     RowsSampled      Steps.......

PK_Index Oct 22 2010 10:52PM            2634160                   158634             178               1 16 NO

But after I run update statistics with full scan I have only 3 steps?

Name          Updated &n

in update statistics the sampling taken is 100% even if less is specified

  

SQL Server 2005 SP3 / SQL Server 2008 SP1 : EVALUATION EDITION in both cases

Auto update stats was set to false >>I created the table >> the column was a primary key so the stats was created on the primary key column >> I inserted 10000 rows >> i updated the statistics WITH SAMPLE 5 PERCENT

I ran dbcc show_statistics and it shows me 100% sampling .

I created another stats on a new column >> and updated it with SAMPLE 30 PERCENT ...

I ran dbcc show_statistics and it shows me 100% sampling .

Is it something I am missing here ....


Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Plan for massive update of publishing pages

  

Hi,

We have a subsite which is currently in production, but we are planning to make changes to a lot of pages that will only go live on a certain date. The thing is we have to start making the changes as it is impossilbe to make all the changes within one single day. I wonder what the best approach is while the current pages might change daily and we are going to replace a lot of the pages with the new content on a certain date.

Please advice. Thanks in advance


Rebuild Index and Update Column Statistics

  

As Index rebuild process will create and update stats, we should not update stats as the row sampling would be less than ideal. However, here is my question. Would a column Stats need to be updated after a Index Rebuild.

If yes, why, if no why. Please provide some documentation to get a better understanding of this with the help of an example. 

Thank you.


"Performance statistics" event: execution plan in "binary xml"

  
The SQL Trace "Performance statistics" event returns for EventSubClass 2 (Batch:Plan) the execution plan in "binary xml" format in the BinaryData column. How do I convert that data into readable XML?

Creating your SharePoint Governance Plan

  
One of the most common questions that I get from prospects as I'm talking to them about the creation of a governance plan and process is what does it look like. In other words, there are materials available which describe what should be in a governance plan but there isn't a ton of guidance on what the process of creating a governance plan and process are.

ASP.Net Gridview Edit Update Cancel Commands

  
In ASP.Net 2.0, GridView Control also provides the functionality to edit and update the data retrieved from the database using CommandField template. You can cancel the action using Cancel Command of the CommandField. GridView consists of events that can be used to perform the actions like edit, update and cancel upon the Data items displayed in the ASP.Net GridView Data Control.

How to format and update GridView and DataGrid rows using JQuery

  
The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

Update Vs SystemUpdate

  
Many of you might noticed that share point ListItem has Update() method as well as SystemUpdate().

What is the difference between these two methods and why MOSS has two different APIs for updating an ListItem

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

  
Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?



Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.



For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

>>>How to update multiple rows of gidview by one click

  

Hi,

I want to add multiple rows of a gridview if the data for multiple fields are same.

Idea for this is that a user may search a record with some parameter e.g. Sector, plot.

For example 15 records being displayed in gridview.

There should be checkbox before every row and if user wants to update Plot and Flat No that is same for 10 rows out of 15 so he will check the rows and update.

For this a dialog box should appear asking for the parameter i.e. Plot and Flat or something else next time may be it will be City or country. Moreover, there is already an Edit button to update records, when user click it another page open for the record to be updated and whatever he wants to update, it updates.

Hope everone understand the scenario.

I don't want to disturb the already provided Edit functionality and want to provide same record to be updated.

Any idea in this regard would be appreciated.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend