vwWho2

Most are familiar with using the procedures sp_who or sp_who2 to get a quick glimpse of the processes running on the SQL Server instance.  However these procs are missing some valuable and easily extractable information.  Also you cannot intrinsically JOIN or filter on these results.

Examining the code from sp_who2 reveals that the essence of the proc is

SELECT *

FROM sys.sysprocesses

The below code for vwWho2 is exactly that, with some slight enhancements.  The advantage of having this code in a view rather than a proc is so that you can filter the results easily with a WHERE clause, something that would be helpful on a busy server.

In sys.sysprocesses, rows for spid < 51 can be ignored because they are being used by the system.  However, vwWho2 does include one of those rows, just to have a handy way of getting the date/time when this instance of SQL Server was last started.

Note this view is dependent upon scalar function udfPadLeft() whose code was given in the last post.  This is just for formatting some columns and is not essential.  

In the next post I will show the code for vwWho3 which will helps to filter on the most pertinent spids.

USE Admin

go

IF Object_ID('dbo.vwWho2') Is Not Null

      DROP VIEW dbo.vwWho2

go

CREATE  VIEW [dbo].[vwWho2]

AS

/*      DATE            AUTHOR          REMARKS

            7/5/11                  PPaiva          Initial creation.

            DESCRIPTION

                  Pertinent info from sysprocesses.  This is an alternative

                        to using sp_who2 because results from sp_who2 cannot be

                        intrinsically joined nor queried.

                  Note that the first row in the output is given just to get the

                        start time of the SQL Server service (login_time).

                  NOTE:   Executing this Trace Flag will force SQL Server to record the

                              sql_handle for even trivial queries.

                                    DBCC TRACEON (2861)

                              However, beware as this has been known to sometimes cause

                                    performance issues.

            USAGE

                  SELECT *

                  FROM vwWho2

                  WHERE WaitTimeMin > 0

                  -- By User

                  SELECT *

                  FROM vwWho2

                  ORDER BY LogiName

                  -- By User, summary

                  SELECT LogiName, Count(*) Qty

                  FROM vwWho2

                  GROUP BY LogiName

                  ORDER BY LogiName

                  NOTE:  For versions of SQL Server that do not support the

                              view sys.dm_exec_sql_text, you can use this system

                              function to get the SQL text from the SQL Handle.

                                    SELECT *

                                    FROM :: fn_get_sql(0x010006007756E52D60372D320000000000000000)

            DEBUG

                  SELECT *

                  FROM sys.sysprocesses

*/

SELECT  TOP 1

                  spid,

                  RTrim(LogiName) LogiName,

                  RTrim(db_name(dbid)) DB,

                  Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,

                  Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,

                  LastWaitType,

                  blocked,

                  Status,

                  RTrim(HostName) HostName,

                  dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),

                                                2, '        ') MinsFromLastBatch,

                  last_batch,

                  login_time,

                  cpu,

                  physical_io PhysicalIO,

                  MemUsage,

                  RTrim(Program_Name) Program,

                  Null SqlText,

                  WaitTime WaitTimeMs,

                  open_tran,

                  cmd,

                  RTrim(nt_UserName) NTUser,

                  net_address,

                  net_library,

                  sql_handle

FROM master.dbo.sysprocesses P

WHERE spid <= 50

UNION ALL

SELECT  TOP 100 PERCENT

                  spid,

                  RTrim(LogiName) LogiName,

                  RTrim(db_name(p.dbid)) DB,

                  Convert(decimal(7,1), WaitTime/60000.0) WaitTimeMin,

                  Convert(decimal(7,1), WaitTime/60000.0/60) WaitTimeHr,

                  LastWaitType,

                  blocked,

                  Status,

                  RTrim(HostName) HostName,

                  dbo.udfPadLeft(Convert(varchar, DateDiff(MINUTE, last_batch, GetDate())),

                                                2, '        ') MinsFromLastBatch,

                  last_batch,

                  login_time,

                  cpu,

                  physical_io PhysicalIO,

                  MemUsage,

                  RTrim(Program_Name) Program,

                  sh.Text,

                  WaitTime WaitTimeMs,

                  open_tran,

                  cmd,

                  RTrim(nt_UserName) NTUser,

                  net_address,

                  net_library,

                  sql_handle

FROM master.dbo.sysprocesses p

OUTER APPLY sys.dm_exec_sql_text(sql_handle) sh

WHERE spid > 50

   OR Blocked <> 0

  

udfPadLeft()

This is a simple user-defined scalar function which will be referenced by subsequent posts.  It is just a tool that comes in very handy for formatting.

Use Admin

go

IF object_id('dbo.udfPadLeft') Is Not Null

      DROP FUNCTION dbo.udfPadLeft

go

CREATE FUNCTION dbo.udfPadLeft

                              (@In varchar(100),

                               @Width int,

                               @PadChar varchar(1))

RETURNS varchar(100)

AS

/*    DATE        AUTHOR            REMARKS

      7/6/11            PPaiva            Initial creation.

     

      DESCRIPTION

            Pads @In with @PadChar so that the length of the returned

            value is @Width.  If the length of @In is greater than @Width

            then @In is returned. 

      USAGE

            SELECT dbo.udfPadLeft('7', '3', '0')

*/

BEGIN

      DECLARE @Out varchar(100),

                  @LenIn int

      SET @LenIn = Len(@In)

     

      IF @Width - @LenIn < 0

            SET @Out = @In

      ELSE

            SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In

      RETURN @Out

END

SQL 2008 or 2008 R2 Installation Guide

Here is a guide to installing SQL Server 2008 or SQL 2008 R2.  It merely contains 30 screen shots that you would encounter during the installation.  This is geared toward a full installation, and helpful for non-SQL-savvy professionals who are doing this installation for the first time.

SQL 2008 R2 Installation Guide
(this is a pdf document you can save)

Some of the options can be intimidating, but know that all of the paths and user account options can be changed after installation, so you don't need to fret about having a perfect configuration from the start.

Hope this is helpful to you!

- Paul

PopSpaceUsed - Populates table SpaceUsed for a given DB or all DBs

If you have been reading the past few blog entries, you know we are building the foundation for having a quick method for viewing the size of all tables in all databases on a given SQL Server instance.  By size I mean both file size in bytes and number of rows.  This global view will be called vwTable and will be presented very soon in a subsequent post.

Here is the last piece of the foundational layer that vwTable will use.  The below proc PopSpaceUsed creates a table called SpaceUsed.  When the proc is run, it will populate the table with the size in bytes for all tables in the SQL Server instance.  The table is truncated and repopulated each time the proc is executed.  The guts of this proc is that it calls Exec sp_spaceused for every table in a given database, or optionally all the databases.  I wasn’t able to find a dynamic management view (DMV) that contains size in bytes for a table.  Please let me know if you run across this.  Besides size in bytes, sp_spaceused  also returns the number of rows.  Nevertheless our future views will not need to use this because the number of rows is available in a DMV. (See vwPartitionRaw which draws from sys.partitions).  Surprisingly, sp_spaceused executes rather quickly. 

Use MyDatabaseName

Exec sp_spaceused 'dbo.Employees'

Since sp_spaceused must be run in the database context of the table, I use a loop to get all the databases on the SQL instance.  It is my custom to do this with a simple WHILE loop, which incidentally is the only LOOP construct that T-SQL offers other than cursors.  I avoid using cursors as a general principle, although it would probably be just fine to do that if you prefer since the cursor would be very small.  

Once the proc is run, you can examine the table.

SELECT *

FROM SpaceUsed

The header of the proc contains a description and proc usage information. 

USE Admin

IF OBJECT_ID('dbo.PopSpaceUsed') Is Not Null

      DROP PROC dbo.PopSpaceUsed

GO

CREATE PROC [dbo].PopSpaceUsed

      @DB varchar(300),

      @ShowSql bit = 0,

      @SuppressOutput bit = 1

AS

/*    DATE        AUTHOR            REMARKS

      5/24/11           PPaiva            Initial creation.

      DESCRIPTION

            Populates SpaceUsed for a given dB.  Based on sp_spaceused.

            To populate tables of all databases on this server,

                  use @DB = ALL.

            Column SizeMB strips the KB out of column Data,

                  and converts to MB.

      DEPENDENCY

            sp_spaceused

      USAGE

            Exec Admin..PopSpaceUsed 'All'

            Exec Admin..PopSpaceUsed 'All', 1

            Exec Admin..PopSpaceUsed 'Blah'

            Exec Admin..PopSpaceUsed 'Admin', 1

      DEBUG

            SELECT *

            FROM SpaceUsed

            Use MyDatabaseName

            Exec sp_spaceused 'dbo.Employees'

            SELECT *

            FROM Admin.sys.tables

            ORDER BY name

            SELECT *

            FROM Admin.sys.schemas

            -- Compare the same table in different DBs

            SELECT *

            FROM SpaceUsed

            ORDER BY Server, TableName, SchemaName, DB

            -- Largest tables

            SELECT *

            FROM SpaceUsed

            ORDER BY Rows desc, TableName, SchemaName, DB

            -- Tables per Database

            SELECT Server, DB, Min(PopDate) PopDate, Count(*) Qty

            FROM SpaceUsed

            GROUP BY Server, DB

            ORDER BY Server, DB

*/

SET NOCOUNT ON

DECLARE @ID int,

            @MaxID int,

            @Name nvarchar(128),

            @MaxDB nvarchar(300),

            @SchemaName nvarchar(50),

            @s nvarchar(1000),

            @ServerName varchar(50) = Convert(varchar(50), ServerProperty('ServerName')),

            @sNow varchar(30) = Convert(varchar, GetDate(), 120)  -- Allows the population date to be consistent

--IF Object_ID('dbo.SpaceUsed') Is NOT Null

--    DROP TABLE dbo.SpaceUsed

-- Create table only once

IF Object_ID('dbo.SpaceUsed') Is Null

      BEGIN

            -- Left indendted columns are inserted and can be NOT NULL

            -- The rest must be NULL unless calculated or a default exists

            SET @s = '

            CREATE TABLE Admin.dbo.SpaceUsed(

                  ID int IDENTITY(1,1) NOT NULL CONSTRAINT pk_SpaceUsed PRIMARY KEY CLUSTERED,

                  Server varchar(100) NOT NULL CONSTRAINT def_SpaceUsed_Server DEFAULT ''' + @ServerName + ''',

                  DB varchar(100) NULL,

                  SchemaName varchar(50) NULL,

            TableName nvarchar(128) NOT NULL,

            Rows int NOT NULL,

                  SizeMB decimal(22, 3) NULL,

                  TotalMB  AS (CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)+CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),

                  IndexMB  AS (CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)),

                  DataMB  AS (CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),

                  ReservedMB  AS (CONVERT(int,replace(Reserved,''KB'',''''),0)/(1000.0)),

                  object_id int NULL,

            Reserved varchar(18) NOT NULL,

            data varchar(18) NOT NULL,

            index_size varchar(18) NOT NULL,

            unused varchar(18) NULL,

                  PopDate datetime NULL

                  )

            '

           

            --Print @s

            Exec(@s)

           

            CREATE UNIQUE INDEX ix_SpaceUsed_ServerDBSchemaTable

                  ON dbo.SpaceUsed (Server, DB, SchemaName, TableName)

            CREATE INDEX ix_SpaceUsed_Rows

                  ON dbo.SpaceUsed (Rows)

      END

IF @DB = 'All'

      TRUNCATE TABLE Admin.dbo.SpaceUsed

ELSE

      DELETE

      FROM Admin.dbo.SpaceUsed

      WHERE DB = @DB

         OR DB Is Null

IF Object_ID('tempdb.dbo.#SpaceUsedDBList') Is Not Null

      DROP TABLE #SpaceUsedDBList

IF Object_ID('tempdb.dbo.#PopSpaceUsedTableList') Is Not Null

      DROP TABLE #PopSpaceUsedTableList

IF @DB <> 'All'

      IF Not Exists(    SELECT *

                              FROM sys.databases                             

                              WHERE name = @DB

                                AND state_desc = 'online')

            BEGIN

                  SET @s = 'Database <' + @DB + '> was not found on this server.'

                  RaisError(@s, 16, 1)

                  RETURN

            END

-- Since sp_spaceused only works for the current database context,

-- we have to execute it as embedded SQL.  This table will contain

-- the names of all tables with database names, then we are ready

-- to call sp_spaceused.

CREATE TABLE #PopSpaceUsedTableList(

      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,

      DB nvarchar(300),

      object_id int,

      SchemaName nvarchar(50),

      TableName nvarchar(128)

      )

SELECT name

INTO #SpaceUsedDBList

FROM sys.databases

WHERE name Not In ('master', 'model', 'msdb', 'tempdb')

  AND state_desc = 'online'

  AND (name = @DB OR @DB = 'All')

 

SELECT  @MaxDB = Max(name),

            @DB = ''

FROM #SpaceUsedDBList

-- For all designated databases

WHILE @DB < @MaxDB

      BEGIN

            -- Get next DB

            SELECT @DB = Min(name)

            FROM #SpaceUsedDBList

            WHERE name > @DB

            -- Get table list for this DB

            SET @s = 'INSERT INTO #PopSpaceUsedTableList (DB, object_id, SchemaName, TableName)

SELECT ''' + @DB + ''' , t.object_id, s.name SchemaName, t.Name

FROM [' + @DB + '].sys.tables t

JOIN [' + @DB + '].sys.schemas s

ON t.schema_id = s.schema_id

WHERE t.name Not In (''dtproperties'')

ORDER BY t.name, s.name'

            Exec(@s)

      END

      IF @ShowSql = 1

            SELECT *

            FROM #PopSpaceUsedTableList

      SELECT  @MaxID = Max(ID),

                  @ID = 0

      FROM #PopSpaceUsedTableList

-- Ready to call out sp_spaceused for each table in designated DBs

WHILE @ID < @MaxID

      BEGIN

            -- Get next table

            SELECT @ID = Min(ID)

            FROM #PopSpaceUsedTableList

            WHERE ID > @ID

            SELECT  @DB = DB,

                        @Name = TableName,

                        @SchemaName = SchemaName

            FROM #PopSpaceUsedTableList

            WHERE ID = @ID

            SET @s = 'use [' + @DB + '] INSERT INTO Admin.dbo.SpaceUsed (TableName, Rows, Reserved, data, index_size, unused)

Exec sp_spaceused ''[' + @SchemaName + '].[' + @Name + ']'''

            IF @ShowSql = 1

                  Print @s

            Exec(@s)

            UPDATE SpaceUsed

            SET SchemaName = @SchemaName,

                  DB = @DB,

                  PopDate = @sNow

            WHERE ID = @ID

      END

UPDATE SpaceUsed

SET Server = Convert(varchar, ServerProperty('ServerName')),

      SizeMB = Convert(decimal(22, 3), Replace(data, ' KB', '')) / 1000.

UPDATE SpaceUsed

SET object_id = t.object_id

FROM SpaceUsed s

JOIN #PopSpaceUsedTableList t

      ON  s.DB = t.DB

      AND s.SchemaName = t.SchemaName

      AND s.TableName = t.TableName

IF @SuppressOutput = 0

      SELECT *

      FROM SpaceUsed

      ORDER BY Server, DB, TableName, SchemaName