Tips
Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server
I've read many articles about defragmenting indexes and how it's a good practice. However, I've noticed that my physical database files are terribly fragmented too. What can cause this? Can I benefit from defragmenting them?
Custom SQL Server Index Defrag and Rebuild Procedures
Efficiently Rebuild SQL Server Clustered Indexes with DROP_EXISTING
When re-building a table’s clustered index using a DROP INDEX command followed by a CREATE INDEX command, if a table has non-clustered indexes then all of non-clustered indexes on the table may be automatically rebuilt twice by the SQL Server engine. Once when the clustered index is dropped and a second time when the index is created again, depending on the new clustered index definition. Is there some way we can eliminate the system resource overhead of rebuilding all of the table’s non-clustered indexes twice?
Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008
In my last tip, Index Fragmentation Report in SQL Server 2005 and 2008, I discussed what fragmentation is, its different types, its performance impact and what are different methods available in SQL Server 2005 and 2008 to identify fragmentation levels. So now once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this tip, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels.
Identify Database Fragmentation in SQL 2000 vs SQL Server 2005
Database fragmentation is a hindrance to SQL Server performance, because with excessive fragmentation more data pages need to be traversed to fulfill a query request. Luckily, fragmentation is a manageable problem that is resolved by re-building the indexes to reduce the fragmentation at the index or table level.
Index Fragmentation Report in SQL Server 2005 and 2008
While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.
Index Metadata and Statistics Update Date for SQL Server
Recently I needed to provide information about when index statistics were updated. I was planning using a query against one of the many Dynamic Management Views (DMVs) that ship with SQL Server 2005. Statistics update date is stored as metadata on the SQL instance, but after a great deal of research against the system tables and DMVs I realized that this information is not readily exposed. In this tip I provide a script on how to gather detailed information about statistics for all indexes on a table.
Index Rebuilds in SQL Server 2000 vs SQL Server 2005
Index rebuilds are a core component for database maintenance which ultimately improve performance and the user experience. DBAs and Developers alike have become accustom to much of the syntax in SQL Server 2000 and know the purpose for each set of code. In SQL Server 2005, the original syntax to support index maintenance is still supported, but will be removed in a future version of SQL Server.
Managing SQL Server Database Fragmentation
There are several things that should be done on a regular basis and one of these things is to manage database fragmentation. Depending on the tables, queries and indexes that are being used fragmentation can cause performance issues as well as using unnecessary space in the database. Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file instead of sequentially or next to like data within the database. This often occurs where you have non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented as well as the use of additional data pages to store the data. So what steps should be taken?
Rebuilding SQL Server indexes using the ONLINE option
As each year goes by the uptime requirement for our databases gets larger and larger, meaning the downtime that we have to do maintenance on our databases is getting smaller and smaller. This tip will look at a feature that was introduced in SQL Server 2005 that allows us to leave our indexes online and accessible while they are being rebuilt.
Selectively rebuild indexes with SQL Server maintenance plans
With SQL Server 2000 you had the ability to rebuild indexes using maintenance plans. This was great if you wanted or needed to rebuild every index in your database, but for very large databases or tables that were not changed that often you were forced to create scheduled jobs with the appropriate T-SQL to only rebuild indexes on the tables that you specified. With SQL Server 2005 this has all changed and now there is an option to select which tables or views you want to rebuild or reorganize indexes on vs having to rebuild every single index in the database.
SQL Server Fragmentation Custom Detection Procedure, Page Linkage Part 7
SQL Server Fragmentation Hands On Experience (Part 9 of 9)
SQL Server Fragmentation How to address it (Part 8 of 9)
SQL Server Fragmentation How to avoid it (Part 4 of 9)
SQL Server Fragmentation How to detect it (Part 6 of 9)
SQL Server Fragmentation Storage basics and Access Methods Part 1 of 9
SQL Server Fragmentation What causes it (Part 3 of 9)
SQL Server Fragmentation What it impacts and what it does not Part 5 of 9
SQL Server Fragmentation What it is, what types there are Part 2 of 9
SQL Server Index Maintenance Checklist
In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The third item on the list was to maintain indexes. There have been several tips on the MSSQLTips.com website about index maintenance, but we will take a look at a checklist of things to perform to ensure your indexes are not degrading performance on your system.
SQL Server script to rebuild all indexes for all tables and all databases
One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Top 10
SQL Server script to rebuild all indexes for all tables and all databases
One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Rebuilding SQL Server indexes using the ONLINE option
As each year goes by the uptime requirement for our databases gets larger and larger, meaning the downtime that we have to do maintenance on our databases is getting smaller and smaller. This tip will look at a feature that was introduced in SQL Server 2005 that allows us to leave our indexes online and accessible while they are being rebuilt.
Index Fragmentation Report in SQL Server 2005 and 2008
While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.
Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008
In my last tip, Index Fragmentation Report in SQL Server 2005 and 2008, I discussed what fragmentation is, its different types, its performance impact and what are different methods available in SQL Server 2005 and 2008 to identify fragmentation levels. So now once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this tip, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels.
Managing SQL Server Database Fragmentation
There are several things that should be done on a regular basis and one of these things is to manage database fragmentation. Depending on the tables, queries and indexes that are being used fragmentation can cause performance issues as well as using unnecessary space in the database. Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file instead of sequentially or next to like data within the database. This often occurs where you have non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented as well as the use of additional data pages to store the data. So what steps should be taken?
Identify Database Fragmentation in SQL 2000 vs SQL Server 2005
Database fragmentation is a hindrance to SQL Server performance, because with excessive fragmentation more data pages need to be traversed to fulfill a query request. Luckily, fragmentation is a manageable problem that is resolved by re-building the indexes to reduce the fragmentation at the index or table level.
Index Rebuilds in SQL Server 2000 vs SQL Server 2005
Index rebuilds are a core component for database maintenance which ultimately improve performance and the user experience. DBAs and Developers alike have become accustom to much of the syntax in SQL Server 2000 and know the purpose for each set of code. In SQL Server 2005, the original syntax to support index maintenance is still supported, but will be removed in a future version of SQL Server.
Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server
I've read many articles about defragmenting indexes and how it's a good practice. However, I've noticed that my physical database files are terribly fragmented too. What can cause this? Can I benefit from defragmenting them?
Selectively rebuild indexes with SQL Server maintenance plans
With SQL Server 2000 you had the ability to rebuild indexes using maintenance plans. This was great if you wanted or needed to rebuild every index in your database, but for very large databases or tables that were not changed that often you were forced to create scheduled jobs with the appropriate T-SQL to only rebuild indexes on the tables that you specified. With SQL Server 2005 this has all changed and now there is an option to select which tables or views you want to rebuild or reorganize indexes on vs having to rebuild every single index in the database.
Efficiently Rebuild SQL Server Clustered Indexes with DROP_EXISTING
When re-building a table’s clustered index using a DROP INDEX command followed by a CREATE INDEX command, if a table has non-clustered indexes then all of non-clustered indexes on the table may be automatically rebuilt twice by the SQL Server engine. Once when the clustered index is dropped and a second time when the index is created again, depending on the new clustered index definition. Is there some way we can eliminate the system resource overhead of rebuilding all of the table’s non-clustered indexes twice?
Last 10
Rebuilding SQL Server indexes using the ONLINE option
As each year goes by the uptime requirement for our databases gets larger and larger, meaning the downtime that we have to do maintenance on our databases is getting smaller and smaller. This tip will look at a feature that was introduced in SQL Server 2005 that allows us to leave our indexes online and accessible while they are being rebuilt.
SQL Server script to rebuild all indexes for all tables and all databases
One of the main functions of a DBA is to maintain database indexes. There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server 2000 and SQL Server 2005. In addition, other tips have been written about using maintenance plans to maintain indexes on all databases. One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not. What other options are there to rebuild indexes on all databases besides using a maintenance plan?
Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008
In my last tip, Index Fragmentation Report in SQL Server 2005 and 2008, I discussed what fragmentation is, its different types, its performance impact and what are different methods available in SQL Server 2005 and 2008 to identify fragmentation levels. So now once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation. In this tip, I am going to discuss the different methods and its feasibility to fix the identified high fragmentation levels.
Index Fragmentation Report in SQL Server 2005 and 2008
While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor.
Index Metadata and Statistics Update Date for SQL Server
Recently I needed to provide information about when index statistics were updated. I was planning using a query against one of the many Dynamic Management Views (DMVs) that ship with SQL Server 2005. Statistics update date is stored as metadata on the SQL instance, but after a great deal of research against the system tables and DMVs I realized that this information is not readily exposed. In this tip I provide a script on how to gather detailed information about statistics for all indexes on a table.
Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server
I've read many articles about defragmenting indexes and how it's a good practice. However, I've noticed that my physical database files are terribly fragmented too. What can cause this? Can I benefit from defragmenting them?
Custom SQL Server Index Defrag and Rebuild Procedures
SQL Server Fragmentation Hands On Experience (Part 9 of 9)
SQL Server Fragmentation How to address it (Part 8 of 9)
SQL Server Fragmentation Custom Detection Procedure, Page Linkage Part 7
