Automatic Statistics Update Slows Down SQL Server 2005

Statistics are objects which contain information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses this statistical data to estimate the number of rows in the query result. In the case where the statistics are invalid or outdated then the query optimizer is likely to choose a wrong execution plan and query performance will decrease. Therefore keeping statistics up to date is an essential part of SQL Server maintenance.

If statistics updates are such a vital part of SQL Server then how can they cause performance issues? In contrast to the above mentioned benefits, updating the statistics also takes resources from the system. This can lead to performance issues when SQL Server is under heavy load especially when you are doing bulk data inserts or have continuous pressure from insert and update operations. In this case statistics are constantly updated keeping your server busy. You can use SQL Profiler to investigate whether the statistics updates are causing a slowdown or not. You can capture the "Auto Stats" event to see the overhead on your system.

If you experience problems due to statistics automatically being updated then the solution might be to disable the Auto Update Statistics option. However, we cannot live with outdated statistics on large databases, so if you turn this option off you should create a scheduled job which explicitly updates the statistics during off-peak periods. To do this, you can use the sp_updatestats procedure.

Following are the steps to disable the "Auto Update Statistics" and to put in place a scheduled job to maintain statistics.

---

Disable Auto Update Statistics

Open Microsoft SQL Server Management Studio and navigate to your database using Object Explorer. Right click on the database and choose Properties. Select "Options" in the new window and set "Auto Update Statistics" to False as shown on the following screen.

Alternatively you can use the following script to disable this option:

ALTER DATABASE YourDBName  SET AUTO_UPDATE_STATISTICS OFF 

---

Create New SQL Job

Open Microsoft SQL Server Management Studio and navigate to SQL Server Agent -> Jobs using Object Explorer. Right click Jobs and choose New Job.

Enter the job name, owner, category (Database maintenance) and description on the following screen:

Click on Steps in the left side pane. Fill in the job name and database. The command type is T-SQL script. You should enter the following command: EXEC sp_updatestats

On the advanced tab you can choose success/failure actions. I recommend you log the job output to a file. You should enter a run as user, but please note that only the DBO and sysadmins can execute this procedure.

The last task is to create a schedule to run the job in off-peak periods. It depends on your database usage on how often you should update the statistics: daily, twice a day, etc. It really depends on your database size, the number of changing rows, etc... I recommend you experiment with this to find the optimal solution for your database. First schedule the job to run once a day in a convenient time and also measure how long it runs. Check the performance gain and consider whether more frequent updates are required.

Additional Options

There is also an option to update the statistics for only one table or a specified index. For this purpose you can use the UPDATE STATISTICS command. Please note that this command sets the Auto Update Statistics to ON if you do not use the NORECOMPUTE option.

UPDATE STATISTICS YourTableName(YourIndex) WITH NORECOMPUTE 

In addition, you can turn off Auto Update Statistics for a specific table using the sp_autostats system stored procedure.

Ref: http://www.mssqltips.com

Cannot resolve the Collation Conflict.

Problem

We had created a new Database in SQL Server 2008 instance; we received the following error massage while using SQL Server Management Studio:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation. (Microsoft SQL Server, Error:468)

Additionally, when we tried to run a query joining two tables, one table that belongs to the database created and the other a temporary table, both tables joined by a common column of varchar(10) data type, then we received the following error from SSMS:

(1 row(s) affected)
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.



Cause.

We configured the SQL Server instance with the SQL_Latin1_General_CP1_CI_AS because it is the standard in our organization, and that means all system databases on the server have that collation (including TempDB), but we had created the new database with a different collation, collation Latin1_General_CI_AS. The conflict is originated by the difference in collation between the instance and the created database.

Solution.

If possible change the database collation. The following link gives instructions on how to change the database collation.

http://msdn.microsoft.com/en-us/library/ms175835.aspx

To change the server collation, you either have to reinstall SQL Server or rebuild system databases. For more information, please see the following article:

http://msdn.microsoft.com/en-us/library/ms179254.aspx

To allow specific queries to run despite the difference on collations, we need to modify those queries and include the COLLATE or COLLATE database_default clause when comparing string columns with different collations. Please see the following article, for more information:

http://msdn.microsoft.com/en-us/library/ms184391.aspx

A severe error occurred on the current command. The results, if any, should be discarded.

Error Messages:

• Msg 5242, Level 22, State 1, Line 2
• An inconsistency was detected during an internal operation in database 'Data'(ID:13) on page (1:26292). Please contact technical support. Reference number 4.

• Msg 3316, Level 21, State 2, Line 2
• During undo of a logged operation in database 'Data', an error occurred at log record ID (68865:616:348). The row was not found. Restore the database from a full backup, or repair the database.

• Msg 3314, Level 21, State 4, Line 2
• The log for database 'Data' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

• Msg 3314, Level 21, State 5, Line 2
• During undoing of a logged operation in database 'Data', an error occurred at log record ID (68847:438:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

• Msg 0, Level 20, State 0, Line 0
• A severe error occurred on the current command. The results, if any, should be discarded.
  
  
  Solution:

  
  

  All the above mentioned error occurs due to Data corruption in database.

  To recover from this error you should run the following queries. Please use the following steps…

  1. Take a current backup of the Database (Strictly recommended)
  2. Go to Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio Express
  3. Click on New Query button.
  4. run the following Queries one by one

  
  

  • ALTER DATABASE your_database_name SET SINGLE_USER
  • Go
  • DBCC CHECKDB ('your_database_name', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS
  • Go
  • ALTER DATABASE your_database_name SET MULTI_USER
  • Go

  
  

  Notes: Make Sure All Connection with the Database is stopped at that time.

  
  

HOW TO CALCULATE THE NUMBER OF WEEK DAYS BETWEEN TWO DATES

If the start date and end date are both week days, then the total number of week days in between is simply:

(total difference in days) - (total difference in weeks) * 2

or

DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2

... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.

If you have a table of holidays, then you can simply subtract them out as well:

DateDiff(dd, @start, @end) -

DateDiff(ww, @start, @end)*2 -

(select count(*) from holidays where holiday_date between @start and @end)

Now, what if the start day or the end day is on a weekend? In that case, you need to define what to do in those situations in your requirements.

For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many week days are between those dates? There's no universal correct answer; it could be 0, or 1, or perhaps even "undefined" (null) depending on your needs.

How many types of data models are there?

There are no standards in this area. Authors and theorists make it up as they go. The entity-relationship model (ER) has hundreds of derivitives (bachman, chen, ibm, IDEF1x etc.). the most popular of the OO models is Unified Modeling Language (UML). Actually UML and IDEF1x are closest to becoming a standard that can support software products. Rational already has products and IDEF1x is the language of ERwin.

Don't be fooled by these variations. They all represent the same things, you have to be very careful that you understand all of the non-standard symbols or you will surely make mistakes in interpreting what the pictures mean.