November 2009 Blog Posts
A few weeks ago, we were preparing to move our SQL Server 2005 databases from a 4-node Windows 2003 cluster to three 2-node Windows 2008 clusters. On the new clusters, we noticed that Database Mail and the monitoring application for Database Mirroring weren’t working on four of the SQL Server 2005 instances that we had installed. For the monitoring issue, we found that we were missing all of the sys.dbmmonitor* system stored procedures in the msdb database. We had installed six SQL Server 2005 instances across the six servers using the same media on each, so we were very perplexed...
You can only have one clustered index on each SQL Server table, however there are two ways to create pseudo clustered indexes on a table: Create clustered indexed on a view (indexed view) that covers the table Create covering index on the entire table Let's look at the following table: CREATE TABLE t1
(
c1 int,
c2 varchar(5),
c3 bigint,
c4 datetime,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1, c2, c3,...
I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008. The code change has just one bug fix. The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters.
In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type. In SQL Server 2008, the DMF was modified so that you could have either...