SQL Server tracing system is used very often by DBAs and programmers for pinpointing issues in the databases. It’s supported by few system objects that describes itself including list of events, columns of each event might return, start and stop event, retrieve event data, status of each event sessions, etc. SQL Profiler utilizes those objects to retrieve and show the trace information on the screen. From debugging perspective, it’s enough for a SQL developer. DBAs, however, prefers to create trace programmatically and save the trace in the database or file system for alerting and later performance analysis. You can use SQL Profiler to trace the SQLs issued by anbother SQL Profiler to generate the trace procedure sequences. Alternatively, you can also browse trace related DMVs to find out all trace related metadata.
Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012
Rows|Range enhances partition functionality of Over clause in SQL Server 2012. In the Over clause before SQL 2012, you can group rows with the same attributes together, which is called partition. You can also order the rows within each of partitions, which is implemented by Order By. The aggregation functions in front of Over clause will be are partition and order based. In SQL Server 2012, a lower level “partition”, which is called windows fram, range of rows, can be defined under the partition. A window frame can be static like partition clause but can also be dynamic – moving window. This provides great power on data analytics, for instance, you no longer need joins for calculating moving average, instead, one SQL with moving window can give you the result.
Auto Adjust Memory Configuration When Failover/Failback Happens
SQL Server failover clustering is widely implemented in the world. One of the DBA’s tasks is to adjust memory when failover happens. When that happens, 2 or more SQL instances are running on one physical node, then max memory for both instances should be adjusted. When the failed nodes are up, failback will perform, then DBAs need to do memory adjustments again on all affected instances. It would be more productive if this process can be automated. In this post, I will give you my ideas and the implementation of it.
sp_rename Causes Lock Leaking
One of my friends finds this issue in the systems he is managing. In my locking and blocking series, I mentioned that when a record is being modified it always acquires IX lock on the object, IX lock on the page, and X lock on the record. In rare case, this may also hold IX lock on another object. This is cased by sp_rename.
DBCC Config
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
Freeze and Unfreeze Write IO on a Database
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.
Locking and Blocking (12) – Counting Semaphore
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
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])
- {‘dbname’ | dbid}
- Database name or database id
- fileid
- File id in sys.database_files
- pageid
- Page number in the file
- offset
- Starting position of the data you want to change
- length
- number of bytes to be written to the page
- data
- data to be written. It’s binary, for example 0x13432
- directORbufferpool
- 2 possible values, 0 and 1. When it’s 0, which is also the default value, the data modification is done at buffer level, related data will be calculated automatically such as page hash. When it’s 1, the modification is written to disk directly, related values, such as page hash, will not be recalculated automatically. Unless you want to test your math level…
Locking and Blocking (11) – Process Control and Customized Locking Hierarchies
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.
SQL Server 2012 Sequence Internal
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.
- Create Sequence: create a sequence optionally with properties, returning type, starting value, interval, minimum value, maximum value, cycle, cache. If you don’t specify any properties, bigint will be used as returning data type.
- Alter Sequence: Modify properties of sequence, returning type, starting value, interval, minimum value, maximum value, cycle, cache
- Drop Sequence: Remove a sequence.
- Next Value For: Get next value of a sequence.It cannot be called from functions.
- sp_sequence_get_range: Get range of sequence.
- sys.sequences: DMV for sequence.