Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk.  The purpose of  asynchronous IO is to release resources and  for other tasks to progress.

i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can… Read more

Bulk loading with large amounts of data  requires strategy.  The Data Loading Performance Guide for SQL Server 2008 and SQL Server 2005 is a very useful document.

 Highlights

Understanding Minimally Logged Operations
Trace Flag 610 (sql server 2008)
Summarizing Minimal Logging Conditions

Bulk Load Methods

Integration Services Data Destinations Read more

A  sql reindex command was being blocked by two active transactions in MS DTC.   Use

sys.dm_tran_active_transactions to report more detail on transactions

select * from sys.dm_tran_active_transactions where transaction_uow  = '922D5B60-A5CA-4C96-8891-703021B40AD8'

/*Returns: 

Transaction_id = “1770489134”	
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”
Transaction_uow=”922D5B60-A5CA-4C96-8891-703021B40AD8”
Transaction_state=”2”
Transaction_status=”12”
Transaction_status2…

Read more

Developers like to have some control over the development process. An example is index building.  Asking a DBA repeatedly for changes  to a Development Database  might not be the best use of time – for either parties.

For example , a developer may want to work over the weekend to… Read more

The dynamic management view (DMV) sys.dm_exec_requests returns information about each request that is executing within SQL Server.

Instead of using Activity Monitor to view the status of a Rollback , use this T-SQL code. 

Activity monitor is based on window panes and is cumbersome to export any… Read more

Quotename() is a SQL Server String function.

It accepts an input string of of up to 128 characters. Which is a limitation , although you could easily create a workaround with a UDF

Returns a Unicode string  of a valid delimited identifier.

 Why would you use Quotename()?

  1)  … Read more

Torn Pages \ Corrupt pages – all in a days work for a  DBA. But can anyone tell me how to use DBCC CHECKDB and the steps to take if a corrupt page is discovered? 

This is a typical question I’ll ask at an interview for a DBA. In most… Read more

DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

       1)  Same… Read more