Category Archives: Scripts

T-SQL Tuesday: What’s Currently Running?

My good friend Allen White is hosting this months installment of #TSQL2sDay so I am motivated to jump in. #TSQL2sDay is the creation of Adam Machanic. The concept is simple, about a week before the second Tuesday of the month a theme will be posted.  Any blogger that wishes to participate is invited to write a post on the chosen topic and any post that is related to both SQL Server and the theme is fair game.The challenge for this month’s T-SQL Tuesday is: What T-SQL tricks do you use today to make your job easier?

What’s Currently Running?

One of my favorite tricks is actually just a little script I have in my toolbox to find out what queries are currently running right now. In fact I have had quite a few people ask me the for this script so I am glad to share it in this blog post.  With SQL Server 2005 and above  SQL Server provides Database Management Views that give you direct access to executing requests and running process. The following query uses sys.dm_exec_request, sys.sysprocesses. We will also use cross apply to get the query text from sys.dm_exec_sql_text and the execution plan from sys.dm_exec_query_plan.

The Good Stuff…

{UPDATE: 1/1/2012 – Replaced sysprocesses with sys.dm_exec_sessions as recommended by Phil in the comments

UPDATE: 6/5/2013 – Changed CROSS APPLY’s to OUTER APPLY’s so we capture statements without execution plan or SQL Text.}

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
[Spid] = sp.session_Id
,er.request_id
,er.command
,[Database] = DB_NAME(er.database_id)
,[User] = login_name
,er.blocking_session_id
,[Status] = er.status
,[Wait] = wait_type
,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,
(er.statement_start_offset / 2)+1,     ((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)    ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS xml) as sql_statement
,[Parent Query] = qt.text
,p.query_plan
,er.cpu_time
, er.reads
, er.writes
, er.Logical_reads
, er.row_count
, Program = program_name
,Host_name
,start_time
FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions sp ON er.session_id = sp.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) p
WHERE sp.is_user_process = 1
/* sp.session_Id > 50
-- Ignore system spids. -- */
AND sp.session_Id NOT IN (@@SPID)
ORDER BY 1, 2

Using Profiler to trace database calls from third-party applications

Today using profiler to trace database calls from third-party applications was published on www.mssqltips.com.  Hopefully, this tip will help some people understand why profiler is bacon.  In this example I trace two queries with Management Studio.  Speaking of Management Studio, have you ever wondered what queries are executed by your favorite features of Management Studio?  You can follow the steps in this tip to do that too.

This is my fist tip published at www.mssqltips.com.  I look forward to publishing tips on a monthly basis.

When was that object modified?

Yes, every once in a while when I am deploying an application I ask myself the following question.  What database objects (tables, stored procedures, functions etc..) did I modify with this release?  

Ideally this is documented in the release plan but I will admit I have been known to slip every once in a while.  Therefore, I am showcasing a query that can provide help.  This query was written by Gordon Bell and it can be found here.   It uses the sys.objects DMV that are included in SQL 2005 & 2008. 

I will defiantly throw this script into my bag of tricks. 

select name, modify_date,
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 
'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc

Get index fragmentation statistics

I recently attended a Pittsburgh SQL Server user group meeting where Brent Ozar gave a presentation on the silent performance killer.  This motivated me to create a stored procedure that could leverage the DMVs in SQL 2005/2008 to gather index fragmentation statistics for all databases on a given server.

Goal

The goal is very simple.  Build a query that could be scheduled to grab statistics that are helpful towards determining if an index needs to be defragged or reorganized.  I would like to throw these results into a table so I could analyze them at a later date.  I would also like to monitor the fill factor and padding to determine if I need to make changes and to analyze if the changes are really helpful.

Download Scripts

The following script uses the following DMV’s sys.dm_db_index_physical_stats, sys.objects and sys.indexes and this script is provided as is.

To download the script click here

To download the create table script for the table used click here


Table Definition

The following is an explanation of the columns.  The following descriptions come from MSDN.

Column Name

Description

databaseName

Name of database, unique within an instance of SQL Server.

objectName Object name.
indexName Name of the index. name is unique only within the object.

NULL = Heap

partitionNumber 1-based partition number within the owning object; a table, view, or index.

1 = Nonpartitioned index or heap.

fragmentation Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

fill_factor > 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

is_padded 1 = PADINDEX is ON.

0 = PADINDEX is OFF.

type_desc Description of index type:

HEAP
CLUSTERED
NONCLUSTERED
XML
SPATIAL

page_count Total number of index or data pages.

For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.

date this is the current date GETDATE()

Script

   1:  ALTER PROCEDURE [dbo].[GetStatsForIndexes]
   2:      @PageCount INT = 100
   3:  
   4:  AS
   5:  BEGIN
   6:      -- SET NOCOUNT ON added to prevent extra result sets from
   7:      -- interfering with SELECT statements.
   8:      SET NOCOUNT ON;
   9:  
  10:      -- Declare varables
  11:      DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)
  12:  
  13:      -- Create a temp table to store all active databases
  14:      CREATE TABLE #databaseList
  15:      (
  16:            databaseID        INT
  17:          , databaseName      VARCHAR(128)
  18:      );
  19:  
  20:      -- we only want non-system databases who are currenlty online
  21:      INSERT INTO #databaseList (databaseID, databaseName)
  22:      SELECT d.database_id, d.name FROM sys.databases d where d.[state] = 0 and d.database_id > 4
  23:  
  24:  
  25:      -- Loop through all databases 
  26:         WHILE (SELECT COUNT(*) FROM #databaseList) > 0  BEGIN
  27:  
  28:             -- get a database id
  29:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  30:          FROM #databaseList;
  31:  
  32:              SET @SQL = 'INSERT INTO DBA_Tools.dbo.IDX_FRAG (databaseName, ObjectName, indexName, partitionNumber, fragmentation, fill_factor, is_padded, type_desc, page_count, [date])
  33:                  SELECT
  34:                    db.name AS databaseName
  35:                  , obj.name AS ObjectName
  36:                  , idx.name AS indexName
  37:                  , ps.partition_number AS partitionNumber
  38:                  , ps.avg_fragmentation_in_percent AS fragmentation
  39:                  ,idx.fill_factor
  40:                  ,idx.is_padded
  41:                  ,idx.type_desc
  42:                  , ps.page_count
  43:                  , GETDATE() as [date]
  44:              FROM sys.databases db
  45:                INNER JOIN sys.dm_db_index_physical_stats ('+CAST(@dbID AS VARCHAR(10))+', NULL, NULL , NULL, N''Limited'') ps
  46:                    ON db.database_id = ps.database_id
  47:                INNER JOIN '+ @dbName+'.sys.objects obj ON obj.object_id = ps.object_id
  48:                INNER JOIN '+ @dbName+'.sys.indexes idx ON idx.index_id = ps.index_id AND idx.object_id = ps.object_id
  49:              WHERE ps.index_id > 0
  50:                 AND ps.page_count > 100
  51:              ORDER BY page_count desc
  52:              OPTION (MaxDop 1);'
  53:  
  54:          EXECUTE sp_executesql @SQL
  55:          -- remove the database from the databases table
  56:          DELETE FROM #databaseList WHERE databaseID = @dbID
  57:  
  58:          -- get the next database in the databases table
  59:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  60:          FROM #databaseList;
  61:  
  62:      END
  63:      -- temp table is no longer needed, so we will kill it.
  64:      DROP TABLE #databaseList;
  65:  END

Converting a vertical table to horizontal table

Last week I received a request to convert a vertical table from a vendor application into a horizontal table.  There was one catch, the vertical table included text columns that needed to be pivoted horizontally.  The following was my plan for tackling this request.

  1. You must figure out how many columns are required for the worst case scenario in you horizontal table.  You can do this by multiplying the columns being pivoted by the rows in the vertical table. In this example you know the worst case is eight (see figure two.)  In this example we will assume that you will not know how many columns are needed.
  2. Now we will use dynamic sql to create our new table that will support the columns needed in the horizontal table.
  3. Next we will create a cursor that will loop through the vertical table to create and execute insert statements to populate the horizontal table.

Example of vertical table (Input)

ManagerName ManagerEmail Review Employee
Jack Wilson jwilson@comp.com 2009 Review John Sterrett
Jack Wilson jwilson@comp.com 2009 Review Bo Smith
Hank Reed hreed@comp.com 2008 Review John Sterrett
Jack Wilson jwilson@comp.com 2008 Review Chris Cupp

Figure 1 – The vertical table

The following is the horizontal table (output)

ManagerName ManagerEmail Review1 Employee1 Review2 Employee2 Review3 Employee3
Jack Wilson jwilson@comp.com 2009 Review John Sterrett 2009 Review Bo Smith 2008 Review John Sterrett
Hank Reed hreed@comp.com 2008 Review Chris Cupp NULL NULL NULL NULL

Figure 2 – The horizontal table

For this example we used two scripts.  The first script will create a vertical table and insert the sample data.  The second script populates the horizontal table and also prints out all scripts created dynamically to the message window.

If you have any questions please feel free to leave a comment . I will try to point you in the right direction.

Find tables that contain column name

The following script is used when I need to perform a search to find tables that contain a column name.

-- Use Control+Shift+M to specify a value column name

SELECT    TABLE_SCHEMA + '.' + TABLE_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE    COLUMN_NAME = N'<column_name,varchar,(column_name)>'

or you can also use the following query.. 

-- Use Control+Shift+M to specify a value column name

SELECT sc.[name] AS column_name, so.[name] AS [TABLE]
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.[name] LIKE N'<column_name,varchar,(column_name)>'
AND so.xtype = 'U'

I