What are online index operations? SQL Server 2005 Enterprise Edition can create, rebuild and drop indexes without locking tables, and I explain how.
SQLAuthority News – Author Visit – Database Architecture and Implementation Discussion – New York, New Jersey Details
Notes from my database architecture meeting in New York and New Jersey: backup routines, index optimization and the maintenance tasks we discussed.
SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005
What is Index Fragmentation and how do page splits cause it? How to measure it with DBCC SHOWCONTIG in SQL Server 2000 and sys.dm_db_index_physical_stats in 2005.
SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance
Why does de-fragmentation of database files at the operating system level skip MDF files? SQL Server has them open, so stop the services first.
SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key
A query on sys.indexes to find every table without clustered index in a database, why heaps can slow you down, and how to pick a clustered key.
SQL SERVER – Change Default Fill Factor For Index
The Default Fill Factor in SQL Server is 0. Change it with sp_configure to leave free space on index pages and reduce page splits. I like mine at 90.
SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN
The EXCEPT operator returns distinct rows from the left query with no match on the right, just like NOT IN. Both give the same plan, but EXCEPT reads simpler.