About SQL Server, Schema z, and Others
Dbcc Config is an undocumented DBCC command which returns information of server configuration, which presented in DMV sys.configurations, on file 1, page 10 in master database. This command will return nothing if you don’t have trace flag 3604 on.
dbcc traceon(3604) dbcc config --with tableresults dbcc page(master,1,10, 3) -- with tableresults
Do you know you can suspend the write IO on one of your database and resume them later? 2 undocumented DBCC command allow you to do so, DBCC Freeze_IO and DBCC Thaw_IO. When DBCC Freeze_IO is called all the IO performing writing operation will be suspended immediately (not terminated). Once the IO is frozen, you can treat the database as a read-only database. Any writing activity will not return like you write data to a readonly database, they hang until you unfreeze the io by calling DBCC Thaw_IO.
Counting semaphores restricts number of concurrent sessions from accessing the same resource. It’s useful when code blocks perform well when number of concurrent users is under a certain number. For instance, SQL Server merge replication implements semaphores to limit number of subscribers to access publication at the same time.
DBCC WritePage is a dangerous undocumented DBCC command. It’s useful in your sandbox to change data, crash objects, and do some fun things you may think of. But never ever use it in your productions. Using DBCC WritePage command is at your own risk. . I will give you demos on how to use dbcc writepage to perform simple insert, delete, and update on a table.
dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])
Building lock based process control is to avoid conflicting processes from running at same time. A very common question DBAs undergo is that Task A can be launched when Task B(s) are not running. Many times I heard that “I will have to disable jobs, wait until they are finished, start another job, wait until it finishes, and then enable the jobs were disabled previously.”. The cycle of this process some time is just very few minutes but mostly it’s half to few hours which is occasionally scheduled in the midnight. The work like this is no longer to be fun to DBAs. It would be nice if something can be implemented in the process which it can detect, wait, and/or safely stop or interrupt running business operations, start its own process after all business processes stopped, and then resume the business processes automatically…This is possible in SQL Server. SQL Server application locks can even bring more value for such matters.
Sequence is a new feature introduced in SQL Server 2012. Unlike identity values, sequence is an independent object. It can be used in table definition as a default constraint on a column or referenced from a query including OVER clause. There are few DDLs and DMLs supporting sequences.
Semaphore is an abstract for controlling access by multiple threads (sessions) to a common resource. Binary semaphore, it can be called Mutex, or Lock/Unlock, allows only one thread access a resource at a time. Back old days, in C++ programming, critical section object is used for this purpose. In C#, keyword lock(obj){code block} and Monitor class implements binary semaphores. Once a object is locked by one thread, other threads who need to acquire the lock on the object will have to wait until the lock on the object is released by thread previously held. SQL Server is an multi-threading application. It uses semaphores internally to control the access to the record, for instance, while you are modify a record, an exclusive lock is placed on the record and released when modification finishes. This is a binary semaphore. In T-SQL, you can implement binary semaphore by locking row(s) and application locks.
SQL Mail is removed from SQL Server 2012 as Microsoft promised in SQL 2008’s BOL. Configuration “SQL Mail XPs” is removed from SQL 2012, check sp_configure in SQL Server 2012. Supporting procedures are removed as well. Those procedures are removed from master database xp_startmail, xp_stopmail, xp_findnextmsg, xp_readmail, xp_deletemail, xp_sendmail, and sp_processmail. Ensure your SQL Server … Read more
Deadlock can happen at latch level, memory, MARS, parallel query, etc. In this post, I am only going to talk about deadlocks at lock level. Resource A and B, when Session 1 locks Resource A and tries to get a lock on Resource B which has a incompatible lock held by Session2, Session 1 will wait until Session 2 releases locks on Resource B. This is called live lock. A live lock will be eventually resolved when the blocking session releases the lock or requesting lock gets timeout. If Session 2 requests a incompatible lock on Resource A at this moment, deadlock happens, because Session 1 will be waiting for the released lock on Resource B and Session 2 will be waiting for the released lock on resource A. Both sessions are deadly locked and blocked. I am going to give you few deadlock scenarios and solutions below.
Functions are the most restrictive code blocks you can define in SQL Server. They perform just like stored procedures in which it can return set and output parameters. But you cannot create or modify objects and the data within the object in a function except the objects are table variables and table value type variables. Actually any transaction depended objects are not allowed to run in a function, such as SEND and RECEIVE. You can run exec proc within a function but only some extended procedures are allowed. One is not allowed but most developers(including me) desire is to run sp_executesql in a function. You can get around it by creating CLR functions.