Thursday, March 20, 2014

Harvest and Report on SQL Server IO

I'm a big advocate of automation, and as a result I often create processes and reports to help with such automation.  My most recent task was to create a process to properly report on IO stall/wait stats.  A recent post of mine called IO Stats - Putting Lipstick on the Pig was a report I created to lay on top of statistics that derive from querying sys.fn_virtualfilestats.  At the end of the day, it's still a pig.  While various DBA's have shared queries that do computations on results from that function, it's very misleading because the data is cumulative.  That means the IO wait stats include all your maintenance activity: backups, index rebuild/reorgs, DBCC activity, etc.  Including all that activity can and will dramatically skew the data.  In fact the results I get for IO waits on one of my TempDB files is >200ms currently when in reality I rarely see values exceed 5ms on a per-hour basis.

I would like to share a better way of harvesting and reporting on your IO data.  Now, I personally have no problem putting these objects in the master database - adjust to suit your needs accordingly.  I will admit that a portion of this process was found online - if/when I find that author, I will credit them accordingly.  The summarizing and reporting portion is what I put together.

  1. First, you need to create the tables (all links go to my public GDrive folder)
  2. Next, create the stored procedure that will harvest the the snapshots
    • You will note that this procedure uses the compare process to store summarized data for reporting purposes
  3. Then, create the stored procedure that will compare the snapshots
  4. Lastly, create a scheduled job.  My reports are based on hourly snapshots, so if you change the frequency you may have to adjust some things.
    • exec master.dbo.sp_IOStatsSnapshot
That's it! Your data will start to collect as soon as your job schedule kicks in.  The data won't really mean much until a couple days go by so you'll have plenty of time to get the reports in place.

The first report is an IO summary report (Figure 1) that computes differentials based on the times you select.  So with this report you can get the IO wait stat summary for core hours, for example.  As is the case with all RDL I share, you need to change the data source information.

The second report is an IO analysis report (Figure 2) that shows the hourly data, highlighting metrics that are outside certain ranges.  I go with the standard recommendation that a read IO should take <20ms while a write should take <4ms.  The other values I chose for warning and critical were personal preferences and can be adjusted accordingly.

*Recent updates to the second report (Figure 2):
  • You can now type in a list of instances and the file listing will automatically pull from that instance via a dynamic data source - this keeps from having one report per instance
  • I changed the file listing to come from sys.master_files so it's always got just the current files and not those that may have moved drives and/or instances

Figure 1

Figure 2


Friday, March 7, 2014

My SQL Server Installation Checklist

I figured since I was doing a fresh install of SQL Server 2012 Enterprise, I might as well document the installation process for future reference.  The items may not be in any particular order.

  1. Install SQL Server!
    1. Create AD account to run services under, if necessary
    2. Create folder structure for data files, transaction logs, TempDB, backups, etc.
    3. Install any SP's and/or CU's
    4. Enable the DAC
  2. Register the instance with the Central Management Server
  3. Instance Configuration
    1. Configure Min/Max Server Memory appropriately (Properties > Memory)
    2. Double-check the Data and Log locations (Properties > Database Settings)
    3. Configure baseline values for Cost Threshold for Parallelism (50) and MAXDOP (1/2 cores in single processor)
    4. Create Login for SQL Server service AD Group/Account(s)
    5. Change the number of SQL Server Logs to retain from 6 to 25
    6. Create job to cycle errors logs nightly at midnight (goes with #5, normally TSX)
    7. Create Operator(s) for DBA
    8. Enable Optimize for Ad-Hoc Workloads (in most cases)
    9. Configure Database Mail for sending alerts (if new, 
  4. Scripts
    1. Install Ola Hallengren's maintenance scripts
      • Schedule user backups accordingly
      • If using centralized administration for jobs, add to master jobs
    2. Install additional SQL Server Agent alerts from Brent Ozar
      • Be sure the operator is set up correctly prior - find/replace appropriately
This is not complete, but I wanted to publish it for feedback and further input.

Wednesday, March 5, 2014

Changing the Default Installation Paths for SQL Server

There are often times where you need to move all the files and SQL Server objects from the default installation paths - the path where system databases were installed, default traces are stored, error logs reside, etc.  I found no single resource that addressed each and every item I needed to move, which led to this blogging opportunity.  I have to do this twice in the coming days (new SAN, new LUN's/drives) and this is part of the migration process to retire the old SAN.

I prefer to keep the default folder structure in place, which means the following exist in a common folder: DATA, FTData, JOBS, Log and repldata.

Note: Some require editing of the registry. Proceed with great caution!

Move the SQL Server Agent error log

USE msdb
GO

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<common folder path>\Log\SQLAGENT.OUT'
GO


Move the SQL Server error log

Note: moving the SQL Server error log also changes the location of the default traces.
  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab (in SQL 2012/2014 click on the Startup Parameters tab)
  5. Change the path following the -e parameter
    • -d'<common folder path>\Log\ERRORLOG'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  6. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Dump Directory Path

SQL Server creates dump files (files with a .mdmp extention) by default under the log folder.  These dump files are generally created when SQL Server encounters an exception or access violation.
  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab
  5. Type in the new path
    • '<common folder path>\Log'
  6. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Data Path

Changing the data path requires editing a registry entry.  Note this is not the same path as the "Database default locations > Data" location found when reviewing the Properties of your instance.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
  3. Change the value of SQLDataRoot to the new location
    • '<common folder path>\DATA'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the SQL Agent Jobs Path

Changing the SQL Agent jobs path requires editing a registry entry.  This path is used by various features under the hood.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
  3. Change the value of WorkingDirectory to the new location
    • '<common folder path>\JOBS'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Full-text Default Path

Changing the full-text default path requires editing a registry entry.
  1. Go to the server and open the registry (Start > Run, and type in "regedit")
  2. Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
  3. Change the value of FullTextDefaultPath to the new location
    • '<common folder path>\FTData'
  4. If you are working on a cluster, be sure to make this adjustment on all nodes

Change the Default Snapshot Path for Replication

Whether or not you are currently using replication, it's a good idea to make sure all resources are reconfigured accordingly.  I found the easy way to do this was by running the following script:

USE distribution
EXEC sp_changedistpublisher
    @publisher = 'Database Instance'
    @property = 'working_directory'
    @value = '<common folder path>\repldata'

Note that you may have to edit existing publications:

Move msdb and model databases

Run the following query to get the logical file names.
USE master
GO

SELECT  DB_NAME(database_id) AS "Database"
       ,name AS "Logical Name"
       ,physical_name AS "Physical Location"
       ,state_desc AS "State"
FROM    sys.master_files
WHERE   database_id IN ( DB_ID(N'msdb'),DB_ID(N'model') )
ORDER BY DB_NAME(database_id);
GO

Plug in the logical file names and new paths do the data and log files, respectively.  It's likely you will only need to adjust the common folder paths - the logical file names are probably as shown below.
USE master
GO

ALTER DATABASE model
MODIFY FILE (NAME = 'modeldev', FILENAME = '<common folder path>\DATA\model.mdf');
GO

ALTER DATABASE model
MODIFY FILE (NAME = 'modellog', FILENAME = '<common folder path>\DATA\modellog.ldf');
GO

ALTER DATABASE msdb
MODIFY FILE (NAME = 'MSDBData', FILENAME = '<common folder path>\DATA\MSDBData.mdf');
GO

ALTER DATABASE msdb
MODIFY FILE (NAME = 'MSDBLog', FILENAME = '<common folder path>\DATA\MSDBLog.ldf');
GO

After running the above script, stop the instance of SQL Server and move the physical files to their new location.  Once moved, restart the instance.  You can run the first query again to verify they have moved as intended.

Move master database

  1. Open SQL Server Configuration Manager on the server/node
  2. Click SQL Server Services
  3. Right click on SQL Server service and click Properties
  4. Click the Advanced tab (in SQL 2012/2014 click on the Startup Parameters tab)

  1. Change the path following the -d parameter for the new data file location
    • -d'<common folder path>\DATA\master.mdf'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  2. Change the path following the -l parameter for the new log file location
    • -l'<common folder path>\DATA\master.ldf'
    • In SQL 2012/2014, be sure to click the Update button or the changes will not stick!
  3. Stop the instance of SQL Server
  4. Move the physical files to their new location
  5. Restart the instance of SQL Server
  6. If you are working on a cluster, be sure to make this adjustment on all nodes
Run the following query to verify they have moved as intended.
USE master
GO

SELECT  DB_NAME(database_idAS "Database"
       ,name AS "Logical Name"
       ,physical_name AS "Physical Location"
       ,state_desc AS "State"
FROM    sys.master_files
WHERE   database_id DB_ID(N'master') )
ORDER BY DB_NAME(database_id);
GO