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.

Saturday, December 15, 2012

Virtual Log Files – a sometimes forgotten performance bottleneck

If the number of virtual log files (VLFs) for a given database gets too high, say more than 50 or so, then there will be a performance hit due to fragmentation.  Whenever the "auto-grow" for a log file kicks in, more VLFs are created.  The best prevention is to ensure that the auto-grow value is not set too low.  The default value is 10% in SQL 2005, 2008, 2008R2, and 2012.  Depending on how often you add data to your database, this value may be appropriate or way too low.  Also be careful of the initial size.  The default value is 1 Mbyte, which could be disastrous if it is not increased at database creation.


          What is a log file?
        guarantees data integrity of the database and for data recovery
        it is what makes ACID, in part, possible
          atomicity
          concurrency
          isolation level
          durability

          A database has at least one transaction log file
          Each transaction log file is comprised of VLFs
        no fixed size
        no fixed number of files

          Make sure initial size and auto-grow is set correctly
        Try to minimize the number of times a files grows
          Some growth is of course ok

          To determine number of VLFs, run

DBCC LogInfo(DatabaseName)

          50 or less is ok
          over 50:  use SHRINKFILE to reduce the number of virtual log files.


BACKUP LOG databasename TO devicename
DBCC SHRINKFILE(TransactionLogLogicalFilename, TRUNCATEONLY)
ALTER DATABASE databasename
MODIFY FILE
(
      NAME = TransactionLogLogicalFilename
    , SIZE = NewTotalSize
)

Now, for the real power of using code to help with database administration.  Create a procedure that will store the VLF info for all databases.  You can schedule this to run daily, and even set up alerts to be notified if the number of VLFs exceeds a threshold value, say 50 or 100.

See next post for a handy view which will show how many VLFs per database.











USE Admin
GO

CREATE PROC dbo.PopLogInfo
      @ShowSql bit= 0
AS
/*    DATE        AUTHOR            REMARKS
      10/16/12    PPaiva            Initial creation.
     
      DESCRIPTION
            Truncates and populates LogInfo using DBCC LogInfo.
           
      DEBUG
            SELECT *
            FROM LogInfo

            -- Number of VLFs per database
            SELECT Server, DB, COUNT(*) Qty
            FROM LogInfo
            GROUP BY Server, DB
            ORDER BY 3 desc, 1, 2

            SELECT status, COUNT(*)
            FROM LogInfo
            GROUP BY status;


            SELECT TOP 5 *
            FROM vwServerDBFile

            DBCC LogInfo ('AmRept')
     
      USAGE
            PopLogInfo 1
            PopLogInfo


*/   
SET NOCOUNT ON

-- DROP TABLE dbo.LogInfo
IF OBJECT_ID('dbo.LogInfo') Is Null
      CREATE TABLE LogInfo(
            ID int NOT NULL IDENTITY(1, 1) CONSTRAINT pk_LogInfo PRIMARY KEY CLUSTERED,
            Server varchar(30) NULL,
            DB sysname NULL,
            FileID int NOT NULL ,
            MBytes As Convert(decimal(8, 2), (SizeBytes/1000000.)),
            StartOffsetBytes bigint NOT NULL,
            FSeqNo bigint NOT NULL,
            Status int NOT NULL,    -- can be 0 or 2.  0 = reusable, 2 = not reusable
            StatusDesc  AS (case when Status=(0) then 'Reusable' when Status=(2) then 'Not Reusable' else '(undefined)' end),
            Parity int NOT NULL,    -- can be 64 or 128.  Value switches upon reuse.
            CreateLSN Numeric(25, 0) NOT NULL,  -- 0 indicates an original file, when database was created.
            SizeBytes bigint NOT NULL,
            dbID int NULL,
            InsertDate datetime CONSTRAINT def_LogInfoInsertDate DEFAULT GetDate()
            )




SELECT name, database_id
INTO #DBs
FROM sys.databases

DECLARE @MaxDB sysname,
            @DB sysname,
            @dbID int,
            @s varchar(500)
           
SELECT  @MaxDB = MAX(Name),
            @DB = ''
FROM #DBs        

           
IF @ShowSql = 0
      TRUNCATE TABLE LogInfo       
     
     
WHILE @DB < @MaxDB
      BEGIN
            -- Get next DB
            SELECT @DB = Min(Name)
            FROM #DBs
            WHERE Name > @DB       

            -- Get corresponding dbID
            SELECT @dbID = database_id
            FROM #DBS
            WHERE Name = @DB

            SET @s = 'INSERT INTO LogInfo (FileID, SizeBytes, StartOffsetBytes, FSeqNo, Status, Parity, CreateLSN)
      Exec (''DBCC LogInfo(''''' + @DB + ''''')'')
UPDATE LogInfo
SET DB = ''' + @DB + ''',
      dbID = ' + Convert(varchar, @dbID) + '
WHERE DB Is Null

'

            IF @ShowSql = 1
                  Print @s
            ELSE
                  Exec(@s)         
     
     
      END

UPDATE LogInfo
SET Server = Convert(varchar(30), SERVERPROPERTY('ServerName'))
WHERE Server Is Null




                                                        

Saturday, November 3, 2012

vwWhoActiveSummary - vwWho3 GROUPed BY spid


Sometimes we need to further refine vwWho2 and vwWho3, to get a quick glimpse of server activity. 

Here we will create vwWhoActiveSummary.  It shows a summary of the active spids, GROUPed BY spid.  A new column is introduced, Qty, which is how many rows (threads) SQL Server is dedicating to that spid. 

I use this to see if there are any spids that are using multiple threads.  That can happen for very large or complex queries.

USE Admin
GO

IF object_id('dbo.vwWhoActiveSummary') Is Not Null
      DROP VIEW dbo.vwWhoActiveSummary

go

CREATE VIEW dbo.vwWhoActiveSummary
AS
/*    DATE        AUTHOR            REMARKS
      11/3/12           PPaiva            Initial creation.

      DESCRIPTION
            A summary of the active processes.  Useful for identifying how many
                  threads are dedicated to a given spid.  A large quantity of
                  rows indicates a large query is being serviced.

            SELECT *
            FROM vwWhoActiveSummary

            SELECT *
            FROM vwWho3

*/

SELECT  DB,
            spid,
            blocked,
            (     SELECT LogiName
                  FROM Admin.dbo.vwWho3
                  WHERE spid = v.spid
                    AND LogiName <> '') LogiName,
            HostName,
            Count(*) Qty,
            Max(WaitTimeMin) WaitTimeMin,
            Max(WaitTimeHr) WaitTimeHr,
            Cmd,
            Program,
            (     SELECT SqlText
                  FROM Admin.dbo.vwWho3
                  WHERE spid = v.spid
                    AND LogiName <> '') SqlText
FROM Admin.dbo.vwWho3 v
WHERE spid <> 1
GROUP BY DB, spid, blocked, HostName, Program, Cmd





Monday, October 15, 2012

vwWho3 - a filtered vwWho2


In the last post we created a view that is the contents of sp_who2, plus some other valuable information.

Here we will create vwWho3, which is the same as vwWho2, with a filter to see only active or pertinent spids.


USE Admin
GO

IF object_id('dbo.vwWho3') Is Not Null
      DROP VIEW dbo.vwWho3

go

CREATE VIEW [dbo].[vwWho3]
AS
/*    DATE            AUTHOR          REMARKS
      10/15/12          PPaiva          Initial creation.
     
      DESCRIPTION
            Same as vwWho2 except this only gets active processes, plus
                  one row from spids between 1 and 50, so that it is easy
                  to show the date/time this instance of SQL Server was
                  last started.

      NOTE
            If spid = -2, it is an orphaned distributed transaction. 
                  This can be terminated with
                        KILL 'Unit Of Work ID (this is a guid)'
     
      USAGE
            SELECT *
            FROM vwWho3

*/

SELECT *
FROM vwWho2
WHERE Status Not In ('sleeping')
   OR Blocked <> 0
   OR Open_Tran <> 0
   OR spid < 1



Tuesday, October 2, 2012

vwWho2 - a better sp_who2

sp_who and sp_who2 are wonderful system procs that give you immediate information on all spids, (active or sleeping or any status), that are currently connected  to your SQL Server instance.

However, you cannot intrinsically JOIN these results to any other queries you may wish to write, perhaps for filtering purposes.   

SOLUTION:  Let’s make a view with the contents of sp_who2, then add some more useful information such as the SQL text for a given spid.

Find sp_who2 in the master database, and you will see it is mostly this query:

SELECT *
FROM sys.sysprocesses

In sys.sysprocesses, spids 1 thru 50 can be ignored because they are used by the system.  In our view, we will retain just one row from these 50, to make it easy to show the date/time that this instance of SQL Server was started.  Columns last_batch and login_time will contain that information.

USE Admin
GO
IF OBJECT_ID('dbo.vwWho2') Is Not Null
      DROP VIEW dbo.vwWho2
GO


CREATE  VIEW [dbo].[vwWho2]
AS
/*      DATE            AUTHOR          REMARKS
            10/1/12                 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,
                  RTrim(Program_Name) Program,
                  Null SqlText,
                  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,
                  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,
                  RTrim(Program_Name) Program,
                  sh.Text,
                  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,
                  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