SQL Server Performance Tips for Rebuilding Indexes. Periodically daily, weekly, or monthly perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Servers performance. It will also update column statistics. If you do a reorganization on a table with a clustered index, any non clustered indexes on that same table will automatically be rebuilt. Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent see below. The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease Script to automatically reindex all tables in a database USE Database. Name Enter the name of the database you want to reindex DECLARE Table. Name varchar2. 55 DECLARE Table. Cursor CURSOR FOR SELECT tablename FROM informationschema. WHERE tabletype base table OPEN Table. Cursor FETCH NEXT FROM Table. Cursor INTO Table. Name WHILE FETCHSTATUS 0 BEGIN DBCC DBREINDEXTable. Name, ,9. 0 FETCH NEXT FROM Table. Cursor INTO Table. Name END CLOSE Table. Cursor DEALLOCATE Table. Cursor. The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 9. You can substitute any number appropriate for the fill factor in the above script. X2lc5mP-5z8/WbAmkjhh6MI/AAAAAAAADeU/8nqJaqJvswUeJhuqNdvd8T0MJywELehQgCLcBGAs/s1600/Check_Stat_time.jpg' alt='Sql Server Update Statistics Full Scan All Tables Sql' title='Sql Server Update Statistics Full Scan All Tables Sql' />When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users. For example, when a non clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users dont need access to the tables being reorganized. Updated 7 2. 4 2. Duplicate of Dynamic SQL Comma Delimited Value Query Parameterized Queries with Like and In I have a SQL Server Stored Procedure where I would like to pass a. Check out this script to read all errors and warnings in the SQL Server Error Log for all versions. When you create or rebuild an index, you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 1. If you create a clustered index that has a fill factor of 1. Numerous page splits can slow down SQL Servers performance. Heres an example Assume that you have just created a new index on a table with the default fill factor. Note The the way primary and secondary datafiles are mapped in SQL Server does not relate to how data files are mapped in Oracle. Real Application Cluster. When SQL Server creates the index, it places the index on contiguous physical pages, which allows optimal IO access because the data can be read sequentially. But as the table grows and changes with INSERTS, UPDATES, and DELETES, page splitting occurs. When pages split, SQL Server must allocate new pages elsewhere on the disk, and these new pages are not contiguous with the original physical pages. Because of this, random IO, not sequential IO access must be used to gather the data, which is much slower, to access the index pages. So what is the ideal fill factor It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines Low Update Tables 1. High Update Tables where writes exceed reads 5. Everything In Between 8. You may have to experiment to find the optimum fill factor for your particular application. Dont assume that a low fill factor is always better than a high fill factor. While page splits will be reduced with a low fill factor, it also increases the number of pages that have to be read by SQL Server during queries, which reduces performance. And not only is IO overhead increased with a too low of fill factor, it also affects your buffer cache. As data pages are moved in from disk to the buffer, the entire page including empty space is moved to the buffer. So the lower the fill factor, the more pages that have to be moved into SQL Serves buffer, which means there is less room for other important data pages to reside at the same time, which can reduce performance. If you dont specify a fill factor, the default fill factor is 0, which means the same as a 1. In most cases, this default value is not a good choice, especially for clustered indexes. Updated 7 2. 4 2. If you find that your transaction log grows to an unacceptable size when you run DBCC REINDEX, you can minimize this growth by switching from the Full Recovery mode to the Bulk Logged mode before you reindex, and when done, switch back. This will significantly reduce the size of the transaction log growth. Updated 7 2. 4 2. If you have a table that has a clustered index on a monotonically increasing or decreasing primary key, and if the table is not subject in UPDATEs or if it has no VARCHAR columns, then the ideal fill factor for the table is 1. This is because such a table will normally not experience any page splits. Because of this, there is no point in leaving any room in the index for page splits. And because the fill factor is 1. SQL Server will require fewer IOs to read the data in the table, and performance will be boosted. Updated 7 2. 4 2. If you are not sure what to make the fill factor for your indexes, your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters Physical Disk Object Disk Read Time and Physical Disk Object Write Time. When you run both counters on an array, you should get a good feel for what percentage of your IO are reads and writes. You will want to run this over a period of time representative of your typical server load. If your percentage writes greatly exceeds the percentage of reads, then a lower fill factor is called for. If your percentage of reads greatly exceeds the percentage of writes, then a higher fill factor is called for. Another Performance Monitor counter you can use to help you select the ideal fill factor for your environment is the SQL Server Access Methods Pages SplitsSec. This counter measures the number of page splits that are occurring in SQL Server every second. For best performance, you will want this counter to be as low as possible, as page splits incur extra server overhead, hurting performance. If this number is relatively high, then you may need to lower the fill factor in order to prevent new page splits. If this counter is very low, then the fill factor you have is fine, or it could be a little too low. You wont know unless you increase the fill factor and watch the results. Ideally, you want a fill factor that prevents excessive page splits, but not so low as to increase the size of the database, which in turn can reduce read performance because of all the extra data pages that need to be read. Once you know the ratio of disk write to reads, you now have the information you need to help you determine an optimum fill factor for your indexes. Updated 7 2. 4 2. If you want to determine the level of fragmentation of your indexes due to page splitting, you can run the DBCC SHOWCONTIG command. Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script Script to identify table fragmentation Declare variables DECLARE ID int, Index. ID int, Index. Name varchar1. Set the table and index to be examined SELECT Index. SQL Server Statistics Questions We Were Too Shy to Ask. If you need to optimise SQL Server performance, it pays to understand SQL Server Statistics. Grant Fritchey answers the 1. SQL Server Statistics the ones we somehow feel silly asking in public, and think twice about doing so. Whats the difference between statistics on a table and statistics on an indexHow can the Query Optimizer work out how many rows will be returned from looking at the statistics When data changes, SQL Server will automatically maintain the statistics on indexes that I explicitly create, if that setting is enabled. Does it also maintain the statistics automatically created on columnsWhere are the statistics actually stored Do they take up much space Can I save space by only having the essential ones How do I know when stats were last updated How reliable is the automated update of statistics in SQL Server Are there any scripts or tools that will help me maintain statistics Do we update statistics before or after we rebuildreorganize indexesIs using UPDATE STATISTICS WITH FULL SCAN the same as the statistics update that happens during an index rebuild How do you determine when you need to manually update statistics How often should I run a manual update of my statistics Is there a way to change the sample rate for particular tables in SQL Server Can you create a set of statistics in SQL Server like you do in Oracle Can you have statistics on a View Are statistics created on temporary tablesHow does partitioning affect the statistics created by SQL Server What kind of statistics are provided for SQL Server through a linked server Can statistics be importedexported Try as I might, I find it hard to over emphasize the importance of statistics to SQL Server. Bad or missing statistics leads to poor choices by the optimizer The result is horrific performance. Recently, for example, I watched a query go from taking hours to taking seconds just because we got a good set of statistics on the data. The topic of statistics and their maintenance is not straightforward. Lots of questions occur to people when Im doing a presentation about statistics, and some get asked in front of the rest of the audience. Then there are other questions that get asked later on, in conversation. Here are some of those other questions. Whats the difference between statistics on a table and statistics on an index There is no essential difference between the statistics on an index and the statistics on a table. Theyre created at different points and, unless youre creating the statistics manually yourself, theyre created slightly differently. The statistics on an index are created with the index. So, for an index created on pre existing data, youll get a full scan against that data as part of the task of creating the index which is also then used to create the statistics for the index. The automatically created statistics on columns are also usually created against existing data when the column is referenced in one of the filtering statements such as WHERE or JOIN. The Light Of Other Days Summary. ON. But these are created using sampled data, not a full scan. Other than the source and type of creation, these two types of statistics are largely the same. How can the Query Optimizer work out how many rows will be returned from looking at the statisticsThis is the purpose of the histogram within the statistics. Heres an example histogram from the Person. Address table in Adventure. Works. 20. 12 If I were doing a search for the address 1. Mockingbird Lane then the query optimizer is going to look at the histogram to determine two things Is it possible that this set of statistics contains this value. If it does contain the value, how many likely rows will be returned. The RANGEHIKEY column shows the top of a set of data within the histogram, so a search for 1. RANGEHIKEY value of 1. Lancelot Dr. So the first question is answered. The optimizer will then look at the AVGRANGEROWS to determine the average number of rows that match any given value within the step. So in this case, the optimizer will assume that there are 1. But, these are all estimates. In reality, the database doesnt contain the value 1. Mockingbird Lane. When data changes, SQL Server will automatically maintain the statistics on indexes that I explicitly create, if that setting is enabled. Does it also maintain the statistics automatically created on columns As data changes in your tables, the statistics all the statistics will be updated based on the following formula When a table with no rows gets a row. When 5. 00 rows are changed to a table that is less than 5. When 2. 0 5. 00 are changed in a table greater than 5. By change we mean if a row is inserted, updated or deleted. So, yes, even the automatically created statistics get updated and maintained as the data changes. Where are the statistics actually stored Do they take up much space Can I save space by only having the essential onesThe statistics themselves are stored within your database in a series of internal tables that include sysindexes. You can view some of the information about them using the system views sys. DBCC SHOWSTATISTICS. The statistics themselves take very little space. The header is a single row of information. The density is a set of rows with only three columns, equal in the number of rows to the number of columns defining the key columns of the statistic. Then you have the histogram. The histogram is up to 2. This means statistics do not require much room at all. While you can save a little bit of space by removing unneeded statistics, the space savings are too small to ever be worthwhile. How do I know when stats were last updated You can look at the header using DBCC SHOWSTATISTICS. This contains a bunch of general information about the statistics in question. This example is from the Person. Address table As you can see, the last time the statistics were updated was January 4, 2. AM. 6. How reliable is the automated update of statistics in SQL Server Youd have to define what you mean by reliable. They are very reliable. Theyre also sampled and automated to update on the criteria that we outlined in the first question. If the data in your system is fairly well distributed, as is usually the case, then the sampled statistics will work well for you. By well distributed I mean that youll get a consistent view of all the available data by pulling just a sample of the data. Most systems, most of the time, will have reasonably well distributed data. But, almost every system Ive worked with has exceptions. There always seems to be that one rogue table or that one index thats got a very weird distribution of data, or gets updated very frequently, but not frequently enough to trigger an automatic update of the statistics. In this situation, the statistics can get stale or be inaccurate. But the problem isnt the automated process. Its that this data is skewed. These are the situations where youll need to manually take control of your statistics. This should be an exceptional event in most systems. Are there any scripts or tools that will help me maintain statisticsSQL Server provides two basic commands to help you to maintain your statistics, spupdatestats and UPDATE STATISTICS. Spupdatestats will look at all the statistics within a database to see if any rows have been modified in the table that the statistics support. If one or more rows have been modified, then youll get a sampled update of the statistics.