Identify long running queries by using T-SQL

Two ways to find out the current running query:

First Query

SELECT s.session_id,
       Db_name(r.database_id)                    AS dbname,
       s.login_name,
       r.blocking_session_id                     AS blkby,
       r.open_transaction_count,
       r.status,
       s.host_name,
       s.program_name,
       r.command,
       (SELECT Substring(TEXT, statement_start_offset / 2, (
                       CASE
                       WHEN statement_end_offset = -1 THEN Len(
                       CONVERT(NVARCHAR(MAX), TEXT)) * 2
                       ELSE statement_end_offset
                       END  statement_start_offset ) / 2 )
         FROM   sys.dm_exec_sql_text(sql_handle)) AS querytext
FROM   sys.dm_exec_sessions s
       JOIN sys.dm_exec_requests r
         ON s.session_id = r.session_id
WHERE  r.session_id = 57 -- SPID

Configuring Tempdb From The Basic

What use TempDB in SQL Server?

• Temporary user objects, such as global (##temp) or local (#temp) temporary tables, temporary indexes and stored procedures, table variables, tables returned in table-valued functions or cursors.
• Internal objects are created by database engine to process a SQL server statement

o        Work tables for cursor or spool operations and temporary large object (LOB) storage, such as varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

o        Work files for hash join or hash aggregate operations.

o        intermediate results for particular GROUP BY, ORDER BY, or UNION queries.

• Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
• Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.

All About RAID

I have been asked about RAID a lot of times at the interviews previously. My mindset was quite blurred at a couple of times. But now, I am asking myself, ‘What is RAID?’, ‘What are RAID levels?’ and ‘How do you configure RAID level for SQL Server?’ In this post, I am starting with the basic concept of RAID.

What is RAID?

RAID shorts for Redundant Array of Independent (or Inexpensive) Disks. It’s the combination of multiple disk drives into large, high performance logical disks, which allows the same data to be redundantly stored in the different places crossed the physical disks. As the result, it will increase fault tolerance, data availability, system reliability and I/O performance.  

What are RAID levels?

The common RAID levels used for SQL Server are RAID 0, 1, 5 0+1 or 1+0.

Scheduling Server Side Trace (Profiler) For Deprecated Features

I was trying to use profiler to trace deprecated features in a SQL Server 2005, before it’s migrated from 2005 to 2008. And also capturing the use of deprecated features in different time period during a day was required, especially for those SQL queries that run over night. However SQL Server Profiler doesn’t provide a build-in scheduling option, so I decided to use SQL command to schedule an agent job, so that the profiler trace can automatically start.

Firstly, Use SQL Profiler to create the trace definition

1. Start SQL Profiler and select File > New Trace. Specify the events, columns, filters you want in your trace.
2. Start the trace and then stop it.
3. Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. Save the scripted trace as ‘*.sql’.
   Note: For SQL Sever 2000 and 2008 choose the appropriate output type.

Secondly, Create a store procedure to set up profiler trace.

Here, I am using tracing deprecated features as a sample.