Maria's journey in the data fields

It appears that Registered servers are not being stored in the registry now. There is a file here C:\Documents and Settings\{you}\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml that can be moved from server to server.

Today I have played with partition merging. The target of this was to see how long it can take, which locks are being held etc. I see that even if partions are empty, the minimum time that it takes is 30 sec ( 2x2.66 CPU 7Gb RAM ). Most of the merge time the folowing system tables are locked exclusive which means that no new query compilations, no schema changes are not allowed: sysrowsetrefs,sysrowsets,sysallocunits,syssingleobjrefs, sysobjvalues,syshobtcolumns,sysrowsetcolumns,sysserefs,syshobts,sysschobjs,sysclsobjs. If there are any schema modification processes running in the database or any data modification processes merge of course waits till those are finished and it can aquire the required locks. If there are other tables that reside on the same partition function, Sch-M locks are placed on them. Regardless of partition sizes, the merged partition will reside on the filegroup that did not hold the merged boundary. This means that is you need to merge many partitions, start ...

Currently table statistics and histograms are saved per table and not per table partition. Many environments that i see experience real performance degradation when migrating from view partitions to native partitions because, thanks to partition elimination, we never work on table level and always on partition level so statistics that exist only on table level give wrong estimations when data spread not equally between partitions, for instance, when some value appears a lot in one partition and only once in another, estimations for partition where the value appears once are bad. I see this behavior in both SQL 2005 and I was sure that this will be changed in SQL 2008 but the behavior stayed the same.

Yes. Even if constraint checks partitioning column and "bad" data can exist in one partition only. See for yourself: I create table natively partitioned on integer field with another char field to fill more pages (full script at the end of message), I fill the table with 1000000 rows. I also have a nonclustered index in partitioned column. For a design of our application i need to add check constraint (i >2) to the table. create partition function f (int) as range for values (5,50,100,500); create partition scheme s as partition f all to ([primary]) create table t ( i int not null,c char(2000)) on s(i) declare @i int set @i = 1 while @i begin insert into t select @i,replicate('a',2000) set @i = @i + 1 end create index ind1 on t(i) Checking the view dm_db_index_operational_stats I see that sql server scanned index on ALL partitions to check if data ok for the constraint : field range_scan_count changed from 0 to 1 in all partitions, page_lock_count also changed for...