I’ve found two scripts on how to rebuild or reorganize all indexes in a specific 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:
EXEC [dbo].[usp_Maintenance_RebuildAndReorganizeIndexes] @PreferredSchemaName = 'dbo' --optional input parameter
Click here to download the script (*.docx):
Caution: Columnstore Indexes feature is only available in the Enterprise Edition!
I mean….WTF :$
First feature a BI Developer will mention about SQL Server 2012…and it’s not even included in the BI Edition.
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.