- About us
- Services
- Skills
- Web Development
- ColdFusion Development
- SharePoint Development
- Classic ASP Development
- ASP.NET Development
- Modern ASP .Net Development
- DotNetNuke Development
- Java/JSP/servlets Development
- Python Development
- PHP Development
- Perl Development
- Ruby on Rails (RoR) Development
- Lasso Development
- Real Basic Development
- Google API Integration Services
- Mobile Development
- Database & Client server Development
- Plug-in Development
- Reports/BI Development
- Automated Testing
- QA/Testing Skills
- Domain-based Testing
- E-Commerce Development
- CMS Development
- Desktop Development
- RIA Development
- App-based development
- Salesforce Development
- SugarCRM Development
- Dynamics GP GreatPlains development
- Dynamics SL Solomon development
- Microsoft Dynamics CRM Consulting Development
- Dynamics RMS development
- LiveCycle development
- QuickBooks development
- Drupal development
- Joomla development
- Typo3 development
- Magento development
- Dolphin social development
- Others
- Web Development
- Industries
- Knowledge
- Process
- Models
- Clients
- Career
| |
Software Technology Tips
Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a sql job scheduled to run at a certain time.
|
SQL Server also has a database option called "Auto Update Statistics" which by default is True. This feature follows a basic algorithm as to when the statistics of a table is to be updated . One drawback of this setting is if certain set of conditions are satisfied for a table then it starts updating the stats at that time. This could be in the middle of the day at peak hours and hence could create perforrmance issues in production , so it is sometimes safe to disable this and do the update statistics through a script or a sql job for a very large database.
One of the easiest and fundamental steps involved in sql query optimization is updating the statistics of all tables involved in the query.
Updated stats help the SQL query optimizer to generate optimized query plans for queries. The SQL Server maintains information like number of data pages used by each table, the total row count of each table and the number of DML operations which affected the keys since the last statistics upgrade. Besides this SQL Server also maintains index statistics
To determine whether an index has it's stats updated we can run the following command:
DBCC SHOW_STATISTICS (table_name , index_name) To Update the Stats on a table the following command can be run :
UPDATE STATISTICS <table name> WITH FULLSCAN
The above command will scan all the rows of the table to get the updated statistics.
UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT
The above script can be run for very large tables in case we want to specify a percentage of the total no of rows.
We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version.
Note: The statistics update script/job should only be run during the maintenance window and not during peak hours of usage as it could slow down the performance of the application. |
Related Tags:
Database
Author: Arijit Rath
Database
- SQL Server Update Statistics
- How to use the Transact-SQL functions CONTAINSTABLE and CONTAINS to do a search on a full-text enabled table
- How to use double quotes in dynamic SQL statements
- How to insert more than one record using a single insert statement
- Using Inner Cursor in MySQL
- Advantage of sp_executesql over Exec
- Capture identity values while inserting multiple rows in Sql Server 2005
- Bulk Copy with Oracle using Array Binding
- Some Useful Date Time conversion Tips in SQL
- Which one to use in a SQL query:-"Not Exists"/ Left Join / Not In"?
- Consuming No space for storing NULL value in table
- How to use a comma seperated list as paramater to a stored procedure
- Set the Recovery model of the database to Simple/Full
- How to create temporary table in MySQL
- How to relocate database from C#.NET (SQL Server 2000)
- Importance of Max Degree of Parallelism in SQL Server
- How to display tabular data in CLR proc
- MySQL: Ordering values according to a predefined pattern
- Use of cross apply to show ',' separated values(SQL Server)
- MYSQL:Ordering A TextField Numerically
- More Related Tips
