Thursday, July 31, 2014

Should you enable Instant File Initialization for your SQL Server?

Yes.  You should.  Now let me explain.

What is 'File Initialization' ?  When the database data files talk to the disk, they have to be 'initialized'.  This means that any newly allocated space is overwritten with zero values (0×0). Windows does this to avoid problems by accessing old data, previously stored on the same disk. This zero File Initialization causes operations like this to take much longer:
  • Create new database
  • Auto-growth of any data file
  • Backup or restore
Ok. So what is 'Instant File Initialization' ?  This is a Windows setting that will allow SQL Server to skip the zero initialization.  Instant File Initialization will claim the space to be used WITHOUT pre-filling it with zeros.  The impact?  This setting will make data file growth faster.  Much faster.  

Instant File Initialization can be enabled by granting the SQL Server service account the Perform Volume Maintenance Task privilege, as I have outlined below.  This only applies to data files.  The SQL Server database log files cannot be initialized like this.

Start / Run – type in ‘secpol.msc’
Left  side under Security Settings, go to Local Policies, then User Rights Assignment.


Right side under Policy, double-click ‘Perform volume maintenance tasks’.  
In ‘Perform volume maintenance task Properties’, choose ‘Add User or Group’.


Add in your SQL Server Service account.


Restart your SQL Server service.


This is actually one of my defaults in any SQL Server build.  I encourage you to enable it on your existing servers now, as well as any new builds in the future.  Take a look at each of these references before making any changes.  Let me know if I can provide anything further.



Wednesday, July 30, 2014

Relocate and Re-size the TempDB

Often times it becomes necessary to move the tempdb.  Maybe your data files are becoming too large, or you want to relocate the files to achieve more optimal performance.  It is fairly easy to move the tempdb, but it will require a restart of the SQL Server service.  In this example I have both changed the location of the files, and added a couple of new ones.  The end result is a completely isolated TempDB -- on it's own drive, and separate from the user and other system databases.

Let's run this first to confirm the logical file names for the tempdb data files: 
 USE tempdb;
 EXEC sp_helpfile

Your results will look something like this:

name      fileid   filename                       filegroup        size        maxsize    growth    usage
tempdev       1            C:\MSSQL\Data\tempdb.mdf       PRIMARY        1092 KB   Unlimited   10%         data only
templog        2            C:\MSSQL\Log\templog.ldf         NULL                512 KB   Unlimited   10%         log only


The first step is to move TempDB to its own drive. I only set a 1mb file size because SQL Server does something a little screwy here. It does not matter if we tell it to use a different drive letter. SQL will still look for the given amount of free space on the drive that TempDB currently sits on. So, if SQL is installed at C:\Program Files\, and we try to create a 10GB TempDB file on a different drive, the server will look for 10GB on the C drive. Its a bug I learned about here:
      http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/

   -- Step 1.
   USE master;
   GO
   ALTER DATABASE TempDB MODIFY FILE (
      name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf'
   size = 1MB
   )
   GO

   ALTER DATABASE TempDB MODIFY FILE (
   name = 'templog'
   filename = 'F:\MSSQL\TempDB\templog.ldf'
   size = 1MB
   )
   GO


At this point, you will need to restart the SQL Server service, in order for the changes to take effect. Take a look at the F:\ drive (or whatever you used), and note your new files. 

The second step is to expand the TempDB data file to the correct full size, and create three additional TempDB files. I always use Paul Randal's advice, which is to make one data file for every processor core. Sometimes I need more, but I pretty much always start with 4 and go from there.

You can see that I expanded tempdev to 250MB, and created the 3 new files at 250MB each, with 0 FILEGROWTH.  This is VERY important.  We don't want them to auto-grow frequently in production, because this causes disc fragmentation, and impedes activity during the file growth.  Ideally, we preallocate TempDB to take whatever disc we have reserved for it.  Basically, build it proactively, not re-actively.  

  -- Step 2.
  -- Expand tempdev
    USE master;
    ALTER DATABASE TempDB MODIFY FILE (
   name = 'tempdev'
   filename = 'F:\MSSQL\TempDB\tempDB.mdf'
   size = 250MB,
   filegrowth = 0
     )
     GO

   -- Add three new data files
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev2'
   filename = 'F:\MSSQL\TempDB\tempdev2.ndf'
   size 250MB
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
      name = 'tempdev3'
   filename = 'F:\MSSQL\TempDB\tempdev3.ndf'
   size = 250MB
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev4'
   filename = 'F:\MSSQL\TempDB\tempdev4.ndf', 
   size = 250MB, 
   filegrowth 0
     )     
     GO

Now I will rename the original 'tempdev' data file to 'tempdev1'.  This is totally NOT a requirement. I just like to keep all of my files consistently named.  (tempdev1, tempdev2, tempdev3, etc.):

   /* Rename files */
   ALTER DATABASE tempdb
   MODIFY FILE (
       NAME ='tempdev'
       NEWNAME = 'tempdev1'
   )

Lastly, I will increase the size of the tempdb log to 2GB.  Remember, this is just an example.  The size of your tempdb log will be dictated by your SQL Server utilization.

   /* Increase TempDB Log file.  */
   ALTER DATABASE tempdb
   MODIFY FILE (
      NAME = 'templog'
      SIZE = 2048MB
   );


There are many other different references out there on tempdb performance, such as the ones I've linked to below.   I encourage you to do some research.  TempDB is not something where we have a lot of room for errors.  Take a look at these references, and let me know if I can help any further.


Script to backup all SQL Server databases

This post covers two topics --  First, it is a very simple method to backup all of the user databases. Secondly, it is an example of a cursor.  I typically stay away from cursors within any real data manipulations, but I don't mind using them for simple functions such as this. The tSQL is a very simple BACKUP command, and the cursor just allows us to walk through all of the databases with that command, very quickly and very efficiently.  

/*
Use cursor to backup all user databases.  */

DECLARE 
@DBName VARCHAR(25),   -- database name  
@BkupPath VARCHAR(255),   -- the path for backup files  
@BkupFileName VARCHAR(255),   -- the backup filename
@FileDate VARCHAR(10)   -- to put date into filename
 
-- Where is the backup directory?
SET @BkupPath = 'C:\MSSQL\Backup\'  
 
-- Construct the BkupFileName
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112
 
-- Declare your cursor
DECLARE DBCursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude the system databases
 
-- Open your cursor
OPEN DBCursor   
FETCH NEXT FROM DBCursor INTO @DBName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
  SET @BkupFileName = @BkupPath + @DBName + '_' + @FileDate + '.BAK'  
  BACKUP DATABASE @DBName TO DISK = @BkupFileName  
 
  FETCH NEXT FROM DBCursor INTO @DBName   
END   
 
-- Close your cursor
CLOSE DBCursor   
DEALLOCATE DBCursor

Note, you can revise the resulting .bak filename, if you'd like something different than 'DatabaseName_YYYYMMDD.bak'.  Also note the WHERE clause on the cursor declaration. You could adjust this to IN or NOT IN, and customize the database list to whatever databases you'd like to target.  This is the clause that I used in my test:     WHERE name IN ('master','model','msdb')

And this is my output:


Take a look at that run time.  All three sys dbs backed up to in 5 seconds.  Not bad.

That's pretty much it.  Like I said, you should modify that WHERE clause to suit your needs.  This is a very flexible, easily customized routine for backing up multiple databases via cursor. 

See here for more information on cursors and the BACKUP command:



Tuesday, July 29, 2014

Can you tell when a stored procedure was last executed?

Yep.  You certainly can.  The sys.dm_exec_query DMV returns aggregate performance statistics for cached query plans.  Important note:  It is only cached query plans.  When a plan is removed from the cache (or you restart the SQL Server service), the corresponding rows are eliminated from this reference.

     SELECT 
          qt.[text] [ProcedureName], 
          qs.last_execution_time [LastRan], 
          qs.execution_count [ExecutionCount]   
     FROM 
          sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     WHERE 
          qt.text LIKE '%your procedure name%'

To perform my test, I fired sp_who2 several times, and sp_who a couple times.  The above captured these results:  

     ProcedureText                             LastRan                                ExecutionCount
     create procedure sys.sp_who       2014-07-29 18:10:19.850          2
     create procedure sys.sp_who2     2014-07-29 18:11:27.520           5  
     create procedure sys.sp_who2     2014-07-29 18:11:27.523          5

Please take a look at these for more information:

    sys.dm_exec_query_stats -
         http://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx
    sys.dm_exec_sql_text -
         http://msdn.microsoft.com/en-us/library/ms181929(v=sql.110).aspx


You should also take a look at this one:

Query for Stored Procedure parameter details.

This is just a real quick tip for collecting parameter details for all user defined functions and stored procedures.  I've find this very helpful when I take on a new customer, and 'inherit' a database that someone else has created.  This just helps me to analyze all of the objects with their parameter details.

Take a look, let me know what you think.

     /*
     Return procedures and functions with parameter details.  */

     SELECT 
         SCHEMA_NAME(SCHEMA_ID) [Schema], 
          so.name [ObjectName],
         CASE WHEN so.Type_Desc = 'SQL_STORED_PROCEDURE' THEN 'Procedure'
                  WHEN so.Type_Desc = 'SQL_SCALAR_FUNCTION' THEN 'Function' END [ObjectType],
        p.name [Parameter],
        TYPE_NAME(p.user_type_id) [ParameterDataType]
     FROM 
        sys.objects so INNER JOIN sys.parameters p
           ON so.OBJECT_ID = p.OBJECT_ID
     WHERE 
        so.OBJECT_ID IN (
                        SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN') )
     ORDER BY 
       [Schema], 
       so.name


Take a look at each of these topics for more details:

  sys.objects - http://msdn.microsoft.com/en-us/library/ms190324(v=sql.110).aspx
  sys.parameters - http://msdn.microsoft.com/en-us/library/ms176074(v=sql.110).aspx







Friday, July 18, 2014

How do we collect SQL Server backup details from the system tables?

A customer told me yesterday that no SQL Server database backups were running.  Yet when I queried msdb..backupset, I could see backups were being recorded every day. There weren't any Agent jobs, so I wasn't sure exactly how the backups were being performed, or even where they were being written.

I threw this logic together to return all of the backup details to me, to include name of the software that is creating the backup. When we ran it for the customer, we found the backups were being run with Dell's AppAsure backup software. 

/* 
Returns backup details for every database on the targeted instance.  To include - 
   BkupSoftware
   Server
   Database
   CompatibilityLevel
   RecoveryModel
   BackupType
   BackupStart/Finish Dates
   PhysicalDevice
   DeviceType
   BackupSize(Bytes)
   CompressedBackupSize(Bytes) - (Same as BackupSize(Bytes) if you are not compressing.)    */

USE msdb; 
SELECT 
    bms.software_name [BkupSoftware],
    bs.server_name [Server],
    bs.database_name [Database],
    CASE bs.compatibility_level 
         WHEN 80 THEN 'SQL v2000'
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008' 
         WHEN 110 THEN 'SQL v2012'
    END AS CompatibilityLevel, 
    recovery_model [RecoveryModel],
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END [BackupType],
    bs.backup_start_date  [BackupstartDate],
    bs.backup_finish_date    [BackupFinishDate],
    bmf.physical_device_name [PhysicalDevice],
    CASE device_type 
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END [DeviceType],
    bs.backup_size [BackupSize(Bytes)], 
    compressed_backup_size [ConmpressedBackupSize(Bytes)] 
FROM
msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON ( bs.media_set_id = bmf.media_set_id ) INNER JOIN msdb.dbo.backupmediaset bms
      ON bs.media_set_id = bms.media_family_count
GROUP BY
    bms.software_name,
    bs.server_name,
    bs.database_name,
    CASE bs.compatibility_level
         WHEN 80 THEN 'SQL v2000' 
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008'
         WHEN 110 THEN 'SQL v2012' 
    END,
    recovery_model,
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    CASE device_type
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END,
    bs.backup_size,
    compressed_backup_size
ORDER BY 
bs.backup_start_date DESC 

Take a look at each of these references to see more detail for each of these backup objects located within the msdb:

    backupset - http://technet.microsoft.com/en-us/library/aa260602(v=sql.80).aspx
    backupmediafamily - http://msdn.microsoft.com/en-us/library/ms190284.aspx
    backupmediaset - http://msdn.microsoft.com/en-us/library/ms189513.aspx







Friday, July 11, 2014

What is your Database Mirror status?

If your databases are Mirrored, this one is for you.  As you know, SQL Server generates events if the status changes in your mirroring session.  But what if you want to get in and just check on things yourself?  How do we check our database mirroring state?  We have the Database Mirroring Monitor, which can be launched to return mirroring status details.  Personally, I am not a big fan of GUI tools.  They really are memory pigs!  

To avoid the GUI, I like to check my mirror status with the script below.  You can see I am using sys.database_mirroring, which contains details regarding the state of the mirrored databases.  You can run this by hand to check mirror status, or you could even wrap it into a job that would notify you if the mirrored state changes, or becomes suspect.  I will try to put that together, and post a sample job soon.

     /*
What is the state of your mirrored databases?    */

-- declarations
DECLARE  
@DatabaseID INT, 
@State VARCHAR(55), 
@IsMirrored INT,
@String VARCHAR(155) 

DECLARE @databases TABLE (
DatabaseID INT, MirroringState VARCHAR(55)
)

-- query status for mirrored databases 
INSERT @Databases (DatabaseID,MirroringState)
SELECT database_id, mirroring_state_desc 
FROM sys.database_mirroring 
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR'
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING'

-- walk through all mirrored databases 
WHILE EXISTS (
SELECT TOP 1 DatabaseID 
FROM @Databases 
WHERE MirroringState IS NOT NULL


BEGIN
SELECT TOP 1 @DatabaseID = DatabaseID, @State = MirroringState
FROM @Databases 

SET @String = 'Host: '+@@SERVERNAME+'.'+CAST(DB_NAME(@DatabaseID) AS VARCHAR)+ ' - DB Mirroring is '+@State +'.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'yourEmailAddress', @body = @string, @subject = @string 
DELETE FROM @Databases 
         WHERE DatabaseID = @DatabaseID
END

-- You can also check to see if there is no mirroring, when there should be
SELECT @IsMirrored = COUNT(*) 
FROM sys.database_mirroring 
WHERE mirroring_state IS NOT NULL 

IF @IsMirrored =
BEGIN
SET @string = 'Host: '+@@SERVERNAME+' - No databases are mirrored.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'YourEmailAddress', @body = 
     @string,  @subject = @string 
END



Take a look, let me know what you think.  You should also take a look at both of these for additional information regarding the catalog view and the Database Mirroring Monitor tool:

    Database Mirroring Monitor -
              http://msdn.microsoft.com/en-us/library/ms365786.aspx
    sys.database_mirroring catalog -
             http://technet.microsoft.com/en-us/library/ms178655(v=sql.110).aspx