Maria's journey in the data fields

If you ever tried to set up replication on the large environment with numerous servers, hundreds of databases then you, for sure, spend some time searching/writing scripts to set flag NOT FOR REPLICATION on thousands of identities, constraints and triggers. I want to share here my scripts and I hope it will save some readers hours of headache. Feel free to use them, just add comments below - there is nothing better than feedback. Here we go. ******************************************************************************** 1. Identities. The resutset is a list of identity columns on all tables in all databases where NOT FOR REPLICATION option is not set and fixing script: DROP TABLE #IdentityNFR CREATE TABLE #IdentityNFR (ServerName varchar(250), Dbname varchar(250),TableName varchar(250), IdentityColName varchar(250), IsNFR varchar(10),FixingScript varchar(4000)) exec sp_MSforeachdb ' IF ''?'' NOT IN (''tempdb'',''master'',''mo...

Did you know, that when you use READ UNCOMMITTED isolation level or WITH (NOLOCK) hint inside the query, it is still places Sch-S (schema stability) locks during compilation and execution and can be blocked by any concurrent trancastion that that holds a Sch-M (schema modification) lock on the table! So, when using READ UNCOMMITTTED isolation level, keep in mind that READUNCOMMITTED and NOLOCK hints apply only to the data locks and there is nothing you can do with Sch-S lock.

Are you familiar with Trace Replay feature SQL Profiler? It's not really popular but actually can do a lot for you. Replay is the ability to save a trace and replay it later. This functionality lets you reproduce activity captured in a trace. When you create or edit a trace, you can save the trace to replay it later. Replaying trace is useful when - you need to troubleshoot production issues then you can run recorded trace against fixed application to make sure all bugs were fixed. - you need to simulate production load on QA/dev environment for stress testing - test new code that is written on development server by simulating real DML load. The process of recording and replaying trace is really easy. - Use preconfigured replay template (TSQL_Replay) because, if you do not capture all required data, SQL Server Profiler will not replay the trace (!) - Save the trace using any format: table or trace file. If you want to use rollover files with limited size take into consideration the...

If you ever wondedered what king of locks your selct query ever issues and for how long, run it in QA environment with isolation level Repeatable Read, which holds all locks you select issues till the end of transaction. Default isolation level Read Committed keeps query locks only till select query has finished running so in most cases you are not able to catch them in sp_lock if your select works fast enough. So, add"SET TRANSACTION ISOLATION LEVEL REPEATABLE READ" inside transaction, then open another window and check out EXEC sp_lock and in some cases you will be surprized how many locks sql server places and removes during simple queries.

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 ...

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...