Tag Archives: Indexes

Rebuild or Reorganize ALL Indexes in a Database dynamically

Posted on by

I’ve found two scripts on how to rebuild or reorganize all indexes in a specific database:
http://geekswithblogs.net/robz/archive/2009/06/09/sql-server-2005-sql-server-2008—rebuild-or-reorganize.aspx
and
http://stackoverflow.com/questions/8326595/how-to-alter-index-for-every-table-in-my-database

But, the thing is that I also wanted a check whether the fragmentation index is higher or lower than 30% (and decide to reorganize or rebuild an index).
So I’ve hustled the script a bit and added a few lines of code and here’s how to execute/use it:


USE []

EXEC [dbo].[usp_Maintenance_RebuildAndReorganizeIndexes] @PreferredSchemaName = 'dbo' --optional input parameter
GO


usp_Maintenance_RebuildAndReorganizeIndexes-results

Click here to download the script (*.docx):
usp_Maintenance_RebuildAndReorganizeIndexes

 

Columnstore Indexes for SQL Server 11.0 release (code named “Denali”)

Posted on by

Microsoft released a new whitepaper on Columnstore Indexes:

Summary: The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

SQL Server Denali columnstore indexes are “pure” column stores, not a hybrid, because they store all data for separate columns on separate pages. This improves I/O scan performance and buffer hit rates. SQL Server is the first major database product to support a pure columnstore index.

Download the whitepaper here.