Saturday, October 20, 2012

Test data generator using T-SQL

Some times DBAs want to generate test data to test some applications designed and developed by themselves. I have faced many situations like this in past. Recently also I had to generate some test data to test one of my research projects. Below is the ERD of the application. It is just four tables.

  1. Customer ( master data)
  2. Item (master data)
  3. SalesOrder (Sales order header data)
  4. SalesOrderDetail (Sales order detail data)

image

Friday, October 5, 2012

Currently executing queries

It is important to know what is currently executing in SQL Server at any given time. You may need this for troubleshooting purposes. The below query is useful in such occasions;

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
s.login_name,
s.host_name,
req.wait_type,
req.wait_resource,
SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1,
        ((
CASE req.statement_end_offset
         
WHEN -1 THEN DATALENGTH(sqltext.text)
        
ELSE req.statement_end_offset
        
END - req.statement_start_offset)/2) + 1) AS statement_text,
req.plan_handle,
sqltext.text
FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions s
   
ON req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.cpu_time DESC

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...