About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, December 29, 2010

udfFormatInteger()

Here’s another scalar user-defined function (UDF) that I frequently reference.  This may be referenced on occasion in subsequent posts on this blog.

USE Admin
GO

IF OBJECT_ID('dbo.udfFormatInteger') Is Not Null
      DROP FUNCTION dbo.udfFormatInteger
GO

CREATE FUNCTION dbo.udfFormatInteger(
      @In bigint
      )
RETURNS varchar(20)
AS
/*    DATE              AUTHOR            REMARKS    
      12/29/10          PPaiva            Initial creation.
     
      DESCRIPTION
            Formats a given integer with commas.
                  Examples:
                              IN                OUT
                              123               123
                          12345           12,345
                         -54321           -54,321

      USAGE
            SELECT dbo.udfFormatInteger(1234567890)
            SELECT dbo.udfFormatInteger(12345)
            SELECT dbo.udfFormatInteger(-54321)
            SELECT dbo.udfFormatInteger(0)
*/

BEGIN
      DECLARE @Out varchar(20),
                  @sIn varchar(20),
                  @Balance varchar(20),
                  @CurrTextLen smallint,
                  @IsNegative bit

      IF @In < 0
            BEGIN
                  SET @IsNegative = 1
                  SET @In = Abs(@In)
            END

      SET @sIn = Convert(varchar, @In)
      SET @CurrTextLen = Len(@sIn)
      SET @Out = ''
      SET @Balance = @sIn

      IF @CurrTextLen > 3
            BEGIN
                  WHILE 1 = 1
                        BEGIN
                              SET @Out = ',' + Right(@Balance, 3) + @Out
                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)
                              IF @CurrTextLen > 3
                                    CONTINUE
                              ELSE
                                    BEGIN
                                          SET @Out = @Balance + @Out
                                          BREAK
                                    END  
                        END
            END

      ELSE
            SET @Out = @sIn

      IF @IsNegative = 1
            SET @Out = '-' + @Out

      RETURN @Out

END


GO



udfGetPathOnly()

This is a companion to the last post.  This scalar user-defined function helps with splitting up those long path names by extracting the path only.  This may be referenced on occasion in subsequent posts on this blog.

USE Admin
GO
IF Object_ID('dbo.udfGetPathOnly') Is Not Null
      DROP FUNCTION dbo.udfGetPathOnly
go

CREATE FUNCTION dbo.udfGetPathOnly(
@In varchar(1000)
)
RETURNS varchar(1000)
AS
/*    DATE        AUTHOR            REMARKS
      12/29/10    PPaiva            Initial creation.


      DESCRIPTION
            Returns the path only, with a trailing backslash. 

      USAGE
            SELECT dbo.udfGetPathOnly('C:\Windows\Notepad.exe')
            SELECT dbo.udfGetPathOnly('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
            SELECT dbo.udfGetPathOnly('tempdb.ldf')
            SELECT dbo.udfGetPathOnly('C:\asdf')
            SELECT dbo.udfGetPathOnly('C:')
            SELECT dbo.udfGetPathOnly('Z:')

*/

BEGIN
      DECLARE @Out varchar(300),
                  @PosSlash smallint,
                  @PosDot smallint


      -- Get position of last backslash
      SET @PosSlash = CharIndex('\', Reverse(@In))
      -- Get position of last dot
      SET @PosDot = CharIndex('.', Reverse(@In))

      IF @PosDot > 0
            SET @PosDot = Len(@In) - @PosDot + 1

      IF @PosSlash > 0
            BEGIN
                  SET @PosSlash = Len(@In) - @PosSlash + 1
                  IF @PosDot = 0
                        SET @Out = @In
                  ELSE
                        SET @Out = Left(@In, @PosSlash)
            END

      ELSE
            IF Len(@In) = 2 AND Right(@In, 1) = ':'
                  SET @Out = @In + '\'


      RETURN @Out

END

udfGetFilenameOnly()

These posts are a couple of very helpful scalar user-defined functions to help split up those long path names.  They may be referenced on occasion in subsequent posts on this blog.

The names are self-explanatory.  

USE Admin
GO
IF Object_ID('dbo.udfGetFilenameOnly') Is Not Null
      DROP FUNCTION dbo.udfGetFilenameOnly
go

CREATE FUNCTION dbo.udfGetFilenameOnly(
      @In varchar(500)
      )
RETURNS varchar(300)
AS
/*    DATE        AUTHOR            REMARKS
      12/29/10    PPaiva            Initial creation.


      USAGE
            SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')
            SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')

*/

BEGIN
      DECLARE @Out varchar(300),
                  @Pos smallint
           
      -- Get position of last backslash
      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1
            BEGIN
                  SET @Pos = Len(@In) - @Pos               
                  SET @Out = Substring(@In, @Pos + 2, 500)
            END
     
      ELSE
            SET @Out = @In


      RETURN @Out

END


Monday, December 13, 2010

CreateVwPartitionRaw - Partition-Aware Count for All Tables

The last post showed how to get the rows for all tables in a given database without doing a table scan.  No table scan means the row count can be retrieved very quickly, even with millions of rows in the table.  

This post improves on that by making it partition-aware.  That is, if a table happens to be partitioned, this code breaks down the rows per partition.  That is something that is very useful when managing large tables and storage requirements.  

Another improvement demonstrated is instead of showing this information for all tables in one database, it will be shown how to create a “dynamic view” which will show the tables for all databases in this instance of SQL Server.  This is done be making a view that UNIONs the query for each database. 

But, such a view creates a maintenance problem.  When a database is added or removed from the server, the view will be inaccurate (database has been added) or will result in an error (database has been removed).  Re-creating the view manually when a database is added or removed would be tedious.

The strategy presented here will be to write a proc that cycles through a list of databases on the instance, and then creates the view.  The view should then be accurate until a database is removed from the instance (in which case querying the view would generate an error), or a database is added to the instance (in which case the view would not include the new database).

Therefore, anytime a database is added or removed, you would run the proc which generates the view to keep it up-to-date.

I strongly suggest that you create a special database to contain this and all subsequent administrative code.  Some DBAs prefer to put these code snippets into master or msdb, and there are good reasons to support that, but my preference is to put it into a separate user database.  I call mine Admin, but you can call it any other name that you prefer.

First, let’s create the proc which creates the view.


CREATE PROC dbo.CreateVwPartitionRaw
      @ShowSql bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      12/12/10    PPaiva            Initial creation.

      DESCRIPTION
            Creates a view in the Admin database for all table/partition
                  info for all non-system databases on this server.
            Name of view is vwPartitionRaw.
            System tables are excluded.
            Coded to use Convert(varchar, ServerProperty('ServerName'))
                  rather than @@ServerName since the latter can be erroneous.

      USAGE
            Exec Admin..CreateVwPartitionRaw 1

            SELECT *
            FROM Admin..vwPartitionRaw

*/
SET NOCOUNT ON

DECLARE @s varchar(max),
            @DB varchar(100),
            @MaxDB varchar(100),
            @i int,
            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwPartitionRaw'') Is Not Null
      DROP VIEW dbo.vwPartitionRaw
      '

IF @ShowSql = 1
      Print @s
Exec(@s)
     
Print ''


SET @s = 'CREATE VIEW dbo.vwPartitionRaw
AS
/*    DATE        AUTHOR            REMARKS
      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Intial creation.

      DESCRIPTION
            Provides a list of non-system tables and partitions
                  for all non-system databases on this server.
            This view provides the number of rows per partition,
                  among other partition attributes.
            This view should be scheduled to auto-refresh daily. 
           
            To manually refresh this view:
                        Exec Admin.dbo.CreateVwPartitionRaw


      SELECT *
      FROM Admin..vwPartitionRaw
      ORDER BY Server, DB, SchemaName, TableName, PartNum

*/
'

SELECT  *
INTO #DBs
FROM sys.databases
WHERE state_desc = 'ONLINE'


SELECT  @MaxDB = Max(Name),
            @DB = '',
            @i = 0
FROM #DBs


WHILE @DB < @MaxDB
      BEGIN
            SET @i = @i + 1

            SELECT @DB = Min(Name)
            FROM #DBs        
            WHERE Name > @DB


            SET @s = @s + '  
SELECT  '

            SET @s = @s + 'Convert(varchar, ServerProperty(''ServerName'')) as Server,
            ''' + @DB + ''' as DB,
            sc.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,
            t.name COLLATE SQL_Latin1_General_CP1_CI_AS TableName,
            p.partition_number PartNum,
            p.Rows,
            p.object_id,
            p.index_id,
            p.partition_id PartID,
            au.total_pages,
            au.used_pages,
            au.data_pages,
            au.container_id,
            ''' + @sNow + ''' ViewCreateDate
FROM [' + @DB + '].sys.partitions p
LEFT JOIN [' + @DB + '].sys.allocation_units au
      ON p.hobt_id = au.container_id
JOIN [' + @DB + '].sys.tables t
      ON t.object_id = p.object_id
JOIN [' + @DB + '].sys.schemas sc
      ON sc.schema_id = t.schema_id
WHERE p.index_id In (0, 1)          -- 0 = heap, 1 = has clustered index
  AND au.type = 1                   -- in-row-data only, not LOB, not row-overflow-data, not dropped
  AND sc.name <> ''sys''
 
'

            IF @DB <> @MaxDB
                  SET @s = @s + 'UNION ALL
                  '

      END


IF @ShowSql = 1
      BEGIN
            SELECT *
            FROM #DBs
            ORDER BY Name

            Print @s
      END

           
Exec (@s)


Now, run the proc.  Notice you can optionally pass in a “1” to show the SQL within the view that is about to be created.  This is useful for debugging or just being curious.

Exec Admin.dbo.CreateVwPartitionRaw 1

Finally, we are now ready to use the new view.

-- Sort by the largest tables on this instance
SELECT *
FROM Admin.dbo.vwPartitionRaw
ORDER BY Rows desc

Note there is a column called PartNum.  If you have any partitioned tables, the row count will be broken-down by partitions.

Or if you are after a simple view to retrieve row counts (not partition-aware) then you can make a view like this:

 
CREATE VIEW dbo.vwTable
AS
/*    DATE        AUTHOR            REMARKS
      12/12/10    PPaiva            Initial creation.
     
      -- Alphabetical list of all tables
      SELECT *
      FROM vwTable
      ORDER BY Server, DB, SchemaName, TableName
     
      -- Largest tables
      SELECT *
      FROM vwTable
      ORDER BY Rows desc

*/

SELECT Server, DB, SchemaName, TableName, SUM(Rows) Rows
FROM vwPartitionRaw
GROUP BY Server, DB, SchemaName, TableName

Use Admin
go