Configuring a Perfmon Collector for SQL Server

Something I always stress to people when I write and present is the importance of tracking data *before* you need it.  Microsoft does a fair job collecting some data via DMVs, the default trace, and Extended Events, but the data is still very limited in many ways, and in many cases the data doesn't persist past a SQL service restart.

One of the important tools for a SQL Server DBA (or Dev, or Analyst, or...) is Windows Perfmon.  Perfmon can be configured to track a ton of data, but it doesn't collect anything unless you are actively watching via the Perfmon GUI or you have configured a Perfmon Collector.

One of the downsides to Perfmon, like most other monitoring tools, is performance overhead.  The more frequently you measure data, the more impactful it can be.

https://33.media.tumblr.com/871fe906d55f4f01872fd65482f94a8a/tumblr_inline_njxqxpHf471s778iq.jpg

Because of this, I set up my collector to gather data every five minutes to lessen that impact.  This number is a happy medium from past discussions with other colleagues for a number that is frequent enough to notice trends over time while still being infrequent enough to have minimal impact.  Every five minutes may strike you as liable to miss problems, and it can - if something spikes (or valleys) for a moment - or even a minute or two - you may not see it.  For many Perfmon counters however, you will see an extreme followed by a gradual change like this image from my recent "Server Using All of my RAM" blog post:

As you can see, Page Life Expectancy (PLE) on this graph dips, gradually climbs, and then dips again.  With a collection every five minutes you may not catch the exact peak - all you know is that the PLE was 50,000 at 12:55am and then only 100 at 1:00am on 03/13.  It may have climbed higher than that before it dipped, but by 1:00am it had dipped down to around 100 (coincidentally at 1am the CheckDB job had kicked off on a large database).

If you really need to know (in this example) exactly how high PLE gets before it dips, or exactly how low it dips, or at what specific time it valleys or dips, you need to actively watch or set up a collector with a more frequent collection.  You will find that in most cases this absolute value isn't important - it is sufficient to know that a certain item peaks/valleys in a certain five minute interval, or that during a certain five minute interval ("The server was slow last night at 3am") a value was in an acceptable/unacceptable range.

If you do set up a collector with a more frequent interval, make sure to delete it (or at least turn it off) after you have collected your data.  I am a fan of deleting it outright so that it doesn't accidentally get turned back on and cause impact, but sometimes it does make more sense to leave it in place. #ItDepends

--

My mechanism (not originally created by me but significantly modified from the original source material - I do not know who was the original author) uses a folder structure that I can ZIP and then unzip into C:\Perflogs, with batch files to create the collector and manage it via Windows Scheduled tasks.

Here are the counters I collect by default:

• "\Memory\Available MBytes"
• "\Memory\Pages/sec"
• "\MSSQLSERVER:Access Methods\Forwarded Records/sec"
• "\MSSQLSERVER:Access Methods\Full Scans/sec"
• "\MSSQLSERVER:Access Methods\Index Searches/sec"
• "\MSSQLSERVER:Buffer Manager\Buffer cache hit ratio"
• "\MSSQLSERVER:Buffer Manager\Free List Stalls/sec"
• "\MSSQLSERVER:Buffer Manager\Free pages"
• "\MSSQLSERVER:Buffer Manager\Lazy writes/sec"
• "\MSSQLSERVER:Buffer Manager\Page life expectancy"
• "\MSSQLSERVER:Buffer Manager\Page reads/sec"
• "\MSSQLSERVER:Buffer Manager\Page writes/sec"
• "\MSSQLSERVER:General Statistics\User Connections"
• "\MSSQLSERVER:Latches(*)\Latch Waits/sec"
• "\MSSQLSERVER:Locks(*)\Lock Waits/sec"
• "\MSSQLSERVER:Locks(*)\Number of Deadlocks/sec"
• "\MSSQLSERVER:Memory Manager\Target Server Memory (KB)"
• "\MSSQLSERVER:Memory Manager\Total Server Memory (KB)"
• "\MSSQLSERVER:SQL Statistics\Batch Requests/sec"
• "\MSSQLSERVER:SQL Statistics\SQL Compilations/sec"
• "\MSSQLSERVER:SQL Statistics\SQL Re-Compilations/sec"
• "\Paging File(*)\% Usage"
• "\PhysicalDisk(*)\Avg. Disk sec/Read"
• "\PhysicalDisk(*)\Avg. Disk sec/Write"
• "\PhysicalDisk(*)\Disk Reads/sec"
• "\PhysicalDisk(*)\Disk Writes/sec"
• "\Process(sqlservr)\% Privileged Time"
• "\Process(sqlservr)\% Processor Time"
• "\Processor(*)\% Privileged Time"
• "\Processor(*)\% Processor Time"
• "\SQLSERVER:Access Methods\Forwarded Records/sec"
• "\SQLSERVER:Access Methods\Full Scans/sec"
• "\SQLSERVER:Access Methods\Index Searches/sec"
• "\SQLSERVER:Buffer Manager\Buffer cache hit ratio"
• "\SQLSERVER:Buffer Manager\Free List Stalls/sec"
• "\SQLSERVER:Buffer Manager\Free pages"
• "\SQLSERVER:Buffer Manager\Lazy writes/sec"
• "\SQLSERVER:Buffer Manager\Page life expectancy"
• "\SQLSERVER:Buffer Manager\Page reads/sec"
• "\SQLSERVER:Buffer Manager\Page writes/sec"
• "\SQLSERVER:General Statistics\User Connections"
• "\SQLSERVER:Latches(*)\Latch Waits/sec"
• "\SQLSERVER:Locks(*)\Lock Waits/sec"
• "\SQLSERVER:Locks(*)\Number of Deadlocks/sec"
• "\SQLSERVER:Memory Manager\Target Server Memory (KB)"
• "\SQLSERVER:Memory Manager\Total Server Memory (KB)"
• "\SQLSERVER:SQL Statistics\Batch Requests/sec"
• "\SQLSERVER:SQL Statistics\SQL Compilations/sec"
• "\SQLSERVER:SQL Statistics\SQL Re-Compilations/sec"
• "\System\Processor Queue Length"

As you can see there is duplication between MSSQLSERVER counters and SQLSERVER counters - this is because at some version of SQL the hive name changed from MSSQLSERVER to SQLSERVER, and by including both of them in the list it covers all of the bases regardless of the Windows/SQL Server version being monitored.  If the listed hive doesn't exist, the collector creates without error, so it doesn't hurt to have both of them in the list.

This is my default list, curated over time from experiences and input from others - if you have other counters you want to collect just edit the list accordingly.

One note - if you have a named instance, the counter hive will be named differently - something like MSSQL$instancename.  The easiest way to handle this is to edit the counter list, copy-paste the SQLSERVER counter list, and then find-replace SQLSERVER to MSSQL$instancename for the new items.

--

The folder structure starts at the top level with a folder named SQLServerPerf (in my case Ntirety-SQLServerPerf).  Below that are two folders, SQLServerPerf\BatchFiles and SQLServerPerf\Logs.

By default, the Logs folder is empty,  In BatchFiles are five files - a .CFG file that includes the counter list above, and four .BAT files to create/start/stop/cycle the collector itself.

To start, unzip the package (or copy the SQLServerPerf folder) into C:\PerfLogs, resulting in C:\PerfLogs\SQLServerPerf\

Why do I use C:?  By default Windows creates a PerfLogs folder there, plus using that path guarantees (almost guarantees) that the batch files will run since Windows servers in general have C: drives - using a different drive would require edits to the files to reference that different drive, and it you absolutely can't write to C: that is the fix - edit the files to change references from C: to the drive of your choice.

--

SQLServerPerf\BatchFiles\SQLServer.CFG is a text file whose contents are just the counter list:

"\Memory\Available MBytes"
"\Memory\Pages/sec"
"\MSSQLSERVER:Access Methods\Forwarded Records/sec"
"\MSSQLSERVER:Access Methods\Full Scans/sec"
"\MSSQLSERVER:Access Methods\Index Searches/sec"
"\MSSQLSERVER:Buffer Manager\Buffer cache hit ratio"
"\MSSQLSERVER:Buffer Manager\Free List Stalls/sec"
"\MSSQLSERVER:Buffer Manager\Free pages"
"\MSSQLSERVER:Buffer Manager\Lazy writes/sec"
"\MSSQLSERVER:Buffer Manager\Page life expectancy"
"\MSSQLSERVER:Buffer Manager\Page reads/sec"
"\MSSQLSERVER:Buffer Manager\Page writes/sec"
"\MSSQLSERVER:General Statistics\User Connections"
"\MSSQLSERVER:Latches(*)\Latch Waits/sec"
"\MSSQLSERVER:Locks(*)\Lock Waits/sec"
"\MSSQLSERVER:Locks(*)\Number of Deadlocks/sec"
"\MSSQLSERVER:Memory Manager\Target Server Memory (KB)"
"\MSSQLSERVER:Memory Manager\Total Server Memory (KB)"
"\MSSQLSERVER:SQL Statistics\Batch Requests/sec"
"\MSSQLSERVER:SQL Statistics\SQL Compilations/sec"
"\MSSQLSERVER:SQL Statistics\SQL Re-Compilations/sec"
"\Paging File(*)\% Usage"
"\PhysicalDisk(*)\Avg. Disk sec/Read"
"\PhysicalDisk(*)\Avg. Disk sec/Write"
"\PhysicalDisk(*)\Disk Reads/sec"
"\PhysicalDisk(*)\Disk Writes/sec"
"\Process(sqlservr)\% Privileged Time"
"\Process(sqlservr)\% Processor Time"
"\Processor(*)\% Privileged Time"
"\Processor(*)\% Processor Time"
"\SQLSERVER:Access Methods\Forwarded Records/sec"
"\SQLSERVER:Access Methods\Full Scans/sec"
"\SQLSERVER:Access Methods\Index Searches/sec"
"\SQLSERVER:Buffer Manager\Buffer cache hit ratio"
"\SQLSERVER:Buffer Manager\Free List Stalls/sec"
"\SQLSERVER:Buffer Manager\Free pages"
"\SQLSERVER:Buffer Manager\Lazy writes/sec"
"\SQLSERVER:Buffer Manager\Page life expectancy"
"\SQLSERVER:Buffer Manager\Page reads/sec"
"\SQLSERVER:Buffer Manager\Page writes/sec"
"\SQLSERVER:General Statistics\User Connections"
"\SQLSERVER:Latches(*)\Latch Waits/sec"
"\SQLSERVER:Locks(*)\Lock Waits/sec"
"\SQLSERVER:Locks(*)\Number of Deadlocks/sec"
"\SQLSERVER:Memory Manager\Target Server Memory (KB)"
"\SQLSERVER:Memory Manager\Total Server Memory (KB)"
"\SQLSERVER:SQL Statistics\Batch Requests/sec"
"\SQLSERVER:SQL Statistics\SQL Compilations/sec"
"\SQLSERVER:SQL Statistics\SQL Re-Compilations/sec"
"\System\Processor Queue Length"

--

SQLServerPerf\BatchFiles\SQLPerfmonCollector-Create.bat is a batch (text) file whose contents create the collector and also several Windows Scheduled Tasks that manage the collector:

logman create counter SQLServerPerf -f bin -si 300 -v nnnnnn -o "c:\perflogs\SQLServerPerf\Logs\SQLServerPerf" -cf "c:\perflogs\SQLServerPerf\BatchFiles\SQLServer.cfg"
timeout /T 2
logman start SQLServerPerf
timeout /T 2
schtasks /create /tn "Cycle SQLServerPerf Perfmon Counter Log" /tr C:\PerfLogs\SQLServerPerf\BatchFiles\SQLPerfmonCollector-Cycle.bat /sc daily /st 23:59:58 /ed 01/01/2099 /ru system
timeout /T 2
schtasks /create /tn "Start SQLServerPerf Perfmon Counter Log" /tr C:\PerfLogs\SQLServerPerf\BatchFiles\SQLPerfmonCollector-Start.bat /sc onstart /ru system
timeout /T 2
schtasks /create /tn "Purge SQLServerPerf Perfmon Counter Log" /tr "PowerShell -command {Get-ChildItem -path C:\PerfLogs\SQLServerPerf\Logs -Filter *.blg | where {$_.Lastwritetime -lt (date).addmonths(-13)} | remove-item}" /sc daily /st 23:59:58 /ed 01/01/2099 /ru system
pause

As you can see there are references to the direct path to our C:\PerfLogs\SQLServerPerf - if you move the files to another drive/path, these references need to be changed.

This batch file does the following:

1. Uses "logman create" to create the actual collector, writing files to to the Logs folder and gathering the counters listed in the SQLServer.cfg file
2. Uses "timeout" to pause for two seconds to allow the user to see any return messages
3. Uses "logman start" to start the collector
4. Pauses two more seconds
5. Uses "schtasks /create" to create a scheduled task to run SQLPerfmonCollector-Cycle.bat nightly at 11:59:58pm (Batch file contents to follow)
6. Pauses two more seconds
7. Uses "schtasks /create" to create a scheduled task to run SQLPerfmonCollector-Start.bat on system startup - this makes sure that after a reboot the collector is automatically started for minimal interruption
8. Pauses two more seconds
9. Uses "schtasks /create" to create a scheduled task to run nightly at 11:59:58pm to run a Powershell command to delete log (.BLG) files older than 13 months
10. Uses "pause" to stop progress until the user hits a key - this again prevents the window from closing until the user acknowledges the progress

This is specifically written to be run interactively (right-click on the BAT file and "Run" or if available, "Run As Administrator") - if you want to truly automate the deploy of the collector, the  pause command should be removed.

--

**Important Note** - current versions of Windows have settings for some of what I am using Scheduled Tasks to do, such as restarting the collector.  This wasn't always the case, and I wanted a solution I could deploy without worrying about the Windows version.

--

SQLServerPerf\BatchFiles\SQLPerfmonCollector-Cycle.bat is a batch (text) file whose contents stop and then start the collector:

logman stop SQLServerPerf
timeout /T 2
logman start SQLServerPerf

I included the timeout to pause for two seconds as I sometimes had issues with the stop/start process where I would get an error from the start command complaining that the collector was already running since the stop hadn't completed yet - adding the two second pause give the collector time to be completely stopped before the start attempt.

--

SQLServerPerf\BatchFiles\SQLPerfmonCollector-Start.bat is a batch (text) file whose contents start the collector:

logman start SQLServerPerf

--

SQLServerPerf\BatchFiles\SQLPerfmonCollector-Stop.bat is a batch (text) file whose contents stop the collector:

logman stop SQLServerPerf

I included this for completeness when I was creating the batch files although I have yet to need it - if I want to stop the collector for some reason I simply do so interactively in the Perfmon GUI.

--

As noted above, I keep the SQLServerPerf folder as a ZIP, and then to deploy it I unzip it into C:\PerfLogs, and then run the Create batch file - it's a simple as that.

The output file from a Perfmon collector by default is a .BLG file.  As noted above, there is a scheduled tasks to stop and start the collector every night, resulting in a new .BLG file each day:

To look at an individual day's info, you simply open that day's file in Perfmon (by opening Perfmon and then opening the file, or by double-clicking the BLG file itself).  If you wish to string multiple days' data together, use the Relog command as I described in this post from a couple years ago: "Handling Perfmon Logs with Relog."

As you can see in the above screenshot, on my laptop the collector stores about 1MB per day.  Even on a big OLTP production cluster I don't see more than 1.5MB-2.0MB/day.  Stretch that out over 13 months (remember we purge files older than 13 months) and it comes out to a max size of around 800MB.  Hopefully 1GB of Perfmon data isn't going to fill your C: drive (if it is you have other problems) but if it is going be a problem, relocate the folders as described above.

--

There are few things more important for *reacting* to problems than to be *proactively* gathering data - and a Perfmon collector helps you to do just that.

Hope this helps!

How Bad Are Your Indexes?

In my last post "Copying SSIS packages With DTUTIL" I described a "gotcha" where the dynamic management views (DMVs) had different columns in SQL Server 2005 compared to 2008+, and I showed a version check I had built into the script to handle it.

It made me think about other places this check would be useful, and the first thing that came to mind was the Bad Indexes DMV query.

If you read my blog or have seen me speak at a SQL Saturday, you know I am a *big* fan of Glenn Berry (@GlennAlanBerry/blog) and his DMV Diagnostic Queries.

http://img.memecdn.com/im-your-biggest-fan_o_668248.jpg

Glenn has put many hours of work into deciphering and joining the dynamic management views/functions that have been in SQL Server since 2005 into useful query frameworks, and I leverage his work (with credit) whenever I can.

The concept of a "bad" index in this context is an index with many more writes than reads - that is, the index requires more effort to maintain than there is benefit from its existence.

http://www.lexisnexis.com/legalnewsroom/resized-image.ashx/__size/500x400/__key/telligent-evolution-components-attachments/13-12-00-00-00-00-00-89/ContentImage_2D00_MugShot.jpg

** IMPORTANT ** - any time you consider removing an index, always verify via use patterns, user interviews, etc. that the index is truly "removeable" - you do *not* want to be the one to remove a "bad" index only to find that it is needed for the monthly payroll run, or the quarterly bonus reports, or some other critical business process.  Quite often "bad" indexes are only used periodically but are very crucial.  An alternative to consider rather than dropping the index outright is to check if the index can be dropped and then recreated when it is needed, but often the index creation process incurs too much overhead for this to be viable.

Out of the box, the bad index query from Glenn's script is database-specific (rather than instance-wide):

-- Possible Bad NC Indexes (writes > reads)  (Query 47) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!

The query relies on database-specific tables/views (such as sys.indexes) and therefore returns results for the current database context.

The first thing I wanted to do was to wrap the query in my undocumented little friend sp_msforeachdb.

http://mergeralpha.com/blog/wp-content/uploads/2015/10/resized_one-does-not-simply-meme-generator-one-does-not-simply-say-hello-to-my-little-friend-fdd94d.jpg

The query turned out like this:

EXEC sp_msforeachdb '
/* MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes) */
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
'

The query has all of the standard artifacts of sp_msforeachdb, such as the question mark placeholder for the database name sprinkled throughout to set the proper context for all of the database-specific tables and views ( such as [?].sys.indexes).

This was where the version-specific problem came up - SQL Server 2008 introduced the concept of filtered indexes, and therefore a new column (has_filter) was added to sys.indexes.  The result is that running the above query (which came from Glenn's SQL 2008 query script) errors out with a non-existent column error.

A fix to this could have been to have a modified version of the query without the offending column, and it would line up with how Glenn publishes his queries, with different scripts for each SQL Server version.

For *my* purpose I wanted a single script that I could run against any SQL Server 2005+, and the version check logic allows for that.

Here is the version checked version of the Bad Indexes For All Databases script:

/*
Bad Indexes DMV For All Databases
Modified by Andy Galbraith to run across all databases on the instance
Modified version of the Bad Indexes query in the Glenn Berry DMV scripts
http://www.sqlskills.com/blogs/glenn/category/dmv-queries/
Tested on MSSQL 2005/2008/2008R2/2012/2014
*/
SET NOCOUNT ON
DECLARE @SQLVersion char(4)
SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)
/* PRINT @SQLVersion */
IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
 PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
/* SQL 2005 Version - removes i.has_filter column */
EXEC sp_msforeachdb '
/*
MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes)
*/
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;'
END
ELSE
BEGIN
EXEC sp_msforeachdb '
/*
MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes)
*/
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;'
END
END

This did exactly what I wanted, returning all non-clustered indexes with at least 75,000 more writes than reads (my chosen threshold) across all databases on the SQL Server 2005+ instance.

Hope this helps!

Copying SSIS packages With DTUTIL

A frequent need when performing a server migration is to copy the SSIS packages from one server to a new server.  There are a couple of different ways to do this, including a wizard in SSMS. (See https://www.mssqltips.com/sqlservertip/2061/how-to-manage-ssis-packages-stored-in-multiple-sql-server-database-instances/).  The catch to this is that these are manual and they only move one package at a time.

I recently had to migrate a server with over twenty packages, and I knew I didn't want to click-click-click over and over again.  :)

I looked around and was reminded of dtutil, the utility designed to manage DTS and then SSIS packages from the command line.  I found a comment at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx that included a SELECT statement to generate dtutil commands based on the contents of msdb.dbo.sysssispackages:

select 'DTUTIL /SQL "'+f.foldername+'"/"'+ name +'" /DestServer [YOURSQLSERVERDEST] /COPY SQL;"'+f.foldername+'"/"'+name+'" /QUIET' 
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid

I played with it a little and it did serve my purpose - I was able to generate twenty dtutil commands, drop them in a Notepad batch file, and successfully run that batch from Windows to move the packages.

I fiddled with the script and started testing it on different SQL Server versions.  The biggest gotcha I found was that on SQL Server 2005 there is no ssispackages table - the comparable table is sysdtspackages90 (and sysdtspackages90folders).  A quick modification to the script to add a version check dealt with this:

-------

/*

SSIS Package Copy with DTUTIL in xp_cmdshell

Run on source server where packages are stored
Set parameter @TargetServer to server name where packages are moving

Modified be Andy Galbraith @DBA_Andy from an idea at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx

Tested on MSSQL 2005/2008/2008R2/2012/2014

*/

SET NOCOUNT ON

DECLARE @TargetServer sysname,  @SQLVersion char(4)

SET @TargetServer = 'ServerB' 

SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)

/* PRINT @SQLVersion */

IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
select 'EXEC xp_cmdshell ''DTUTIL /SQL "'+f.foldername+'\'+ name 
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET''' 
from msdb.dbo.sysdtspackages90 p
inner join msdb.dbo.sysdtspackagefolders90 f
on p.folderid = f.folderid
END
ELSE /* 2008+ */
BEGIN
select 'EXEC xp_cmdshell ''DTUTIL /SQL "'+f.foldername+'\'+ name 
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET''' 
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
END
END

-------

In the above script I wrapped the dtutil statements in xp_cmdshell calls so that I could run it from SQL Server rather than the Windows command line (or batch files).

If your environment doesn't support xp_cmdshell (which is a completely different best practices discussion - see a great post by K Brian Kelley (blog/@kbriankelley) here about the risks of enabling xp_cmdshell in your environment) then it is easy to remove the xp_cmdshell piece to return the results back to simple dtutil calls:

-------

/*

SSIS Package Copy with DTUTIL

Run on source server where packages are stored
Set parameter @TargetServer to server name where packages are moving

Modified be Andy Galbraith @DBA_Andy from an idea at http://www.sqlservercentral.com/Forums/Topic1068518-1550-1.aspx

Tested on MSSQL 2005/2008/2008R2/2012/2014

*/

SET NOCOUNT ON

DECLARE @TargetServer sysname,  @SQLVersion char(4)

SET @TargetServer = 'ServerB' 

SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)

/* PRINT @SQLVersion */

IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
select 'DTUTIL /SQL "'+f.foldername+'\'+ name 
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET' 
from msdb.dbo.sysdtspackages90 p
inner join msdb.dbo.sysdtspackagefolders90 f
on p.folderid = f.folderid
END
ELSE /* 2008+ */
BEGIN
select 'DTUTIL /SQL "'+f.foldername+'\'+ name 
+'" /DestServer "'+@TargetServer+'" /COPY SQL;"'+f.foldername+'\'+name+'" /QUIET' 
from msdb.dbo.sysssispackages p
inner join msdb.dbo.sysssispackagefolders f
on p.folderid = f.folderid
END
END

-------

Hope this helps!

Speaking at SQL Saturday #500 in Boston!

I have been selected to speak at SQL Saturday #500 in Boston on March 19th - I will be speaking (at 9am in the morning!) on "Getting Started with Extended Events":

--

Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events.  Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years.  Why is this?

Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them.  Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace. 

Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more.  We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way.  You may never go back to Profiler again!

--

SQL Saturdays are put on by PASS and are amazing full-day events of free training and networking ($10 for lunch), with sessions presented by a wide array of speakers.  Many of the speakers are SQL Server MVPs and the topics cover a wide array of fields from DBA to Developer to BI and beyond.

--

If you are interested there are also full day sessions (albeit not free!) Thursday and Friday from SQL Server MVP Jes Borland and MVP Denny Cherry respectively.  Jes will be presenting "How to Get Started Using Microsoft Azure" and Denny is covering "SQL Server Performance Tuning and Optimization" - if you are coming out Saturday and have the availability Thursday and/or Friday, consider them as well.

--

Hope to see you there!

Counting Your VLFs, or, Temp Tables Inside IF...ELSE Blocks

There are many many blog posts out there about Virtual Log Files (VLFs) - one of the absolute best is "8 Steps to Better Transaction Log Throughput" from Kimberly Tripp (blog/@KimberlyLTripp) of SQLskills as well as the several other posts she links to from that post - if you haven't read them, click the link right now and do so - my post will be right here when you get back.

--

VLFs are the logical units that make up your transaction logs, and at the end of the day the TL;DR boils down to "Usually, too many VLFs Are bad" - they can decrease performance by functionally "fragmenting" your transaction log and slowing down everything transaction log-related (so basically, everything).

http://memegenerator.net/Phil-Hartman-Frankenstein/caption

VLF count is something that most vendors check during their health checks, and many SQL Server pros recommend it as well.  Of course "too many VLFs" is a relative term, with people throwing around numbers of 50 or 100 or 200 as their threshold of concern.

--

The resource I have always fallen back on to run this check is the script from Michelle Ufford (blog/@sqlfool).  She created it back in 2010 and it is the basis for the VLF script included in Glenn Berry's (blog/@GlennAlanBerry) Diagnostic Information (DMV) Queries.

Michelle's query relies on the undocumented DBCC LogInfo command to gather its VLF data - DBCC LogInfo returns a row for each VLF, so the count(*) of that query gives the number of VLFs for the database.  The catch is that in SQL Server 2012, Microsoft added a column to the front of the resultset (RecoveryUnitID).  As the DBCC command is undocumented, this new column is undocumented as well.

Michelle's code uses INSERT...EXEC to populate a temporary table with the VLF info, and the addition of this extra column breaks the original script.  Glenn's versions of the scripts handle this issue easily since they are version-specific - in the SQL 2012/2014/2016 versions of the script, the temp table declaration is modified to include the extra RecoveryUnitID column, which allows the rest of the script to function as designed.

--

My problem is I wanted a version of the script that could be used across versions 2005+, and this presented a problem.  At first I tried to add an IF...ELSE block to the start of the script to handle the differing CREATE TABLE statements:

--

If (select LEFT(cast(serverproperty('ProductVersion') as varchar),2)) in ('8.','9.','10')
BEGIN
Create Table #stage
(
FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
END
ELSE
BEGIN
Create Table #stage
(
RecoveryUnitID int /* This is the new column as of SQL 2012 */
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
END

--

http://memegenerator.net/Grumpy-Cat

Regardless of the SQL version I tested, I received this:

Msg 2714, Level 16, State 1, Line 16
There is already an object named '#stage' in the database.

I played with it a little, including adding an IF EXISTS check to the beginning of the second block (and yes, I directed it to tempdb..#stage to reference the temp table) and none of it worked.  I poked around a little online and couldn't find a way to make it work - many people saying that it couldn't be done with temp tables, and that you should use a "regular" table or maybe a regular view instead.

My problem is that I am creating a script I want to run on lots of different servers across lots of environments, and I don't want to assume that the table name I am using doesn't already exist.  Is it likely that a client server will have a table named dbo.AndyGVLFCountReallyUniqueTableNameGUIDPurpleMonkeyDishwasher?  Well no, but you never know...  Also, many environments have rules about creating "real" objects without change control - even an object that will be created, exist for <30 seconds, and be dropped.

Besides at this point it had become a challenge of how to make it work - there had to be a different way of looking at the problem.  I fiddled with a table variable solution and had no better luck, resulting in a similar "already exists" error.

I realized part of the problem was how my script was laid out - I was checking for the lower version as my decision gate (in 8/9/10 ELSE) and while that was what needs to happen (I didn't want to hard code 11/12/13 and have it break with future versions) I didn't need to have the CREATE be part of the check - I just needed to handle the fact that the down-level object couldn't have the offending column:

--

Create Table #stage
(
RecoveryUnitID int /* This is the new column as of SQL 2012 */
, FileID      int
, FileSize    bigint
, StartOffset bigint
, FSeqNo      bigint
, [Status]    bigint
, Parity      bigint
, CreateLSN   numeric(38)
);

If (select LEFT(cast(serverproperty('ProductVersion') as varchar),2)) in ('8.','9.','10')
ALTER TABLE #stage DROP COLUMN RecoveryUnitID

--

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpToMii3ijkw0vgfUfzJTiBAvTU-Pm7hAFUU0MUXCT58ocU5CDRzdXK-cP6YYvG6-1fw7iOONuYbVwk6H6yFLACZ9XP8XSS-MLiZs6wPSEselOmuH-IFbZYQHlFjLhim7sAaZmAWe7Uk-6/s1600/33617404.jpg

In this case, I was able to create the table before the version check *with* the extra column, and then run a version check to drop the column if the instance is down-level.

With this in hand, I was able to modify Michelle's script to run for all current versions of SQL:

--

/*

VLF Count Script

Modifed From Michelle Ufford @sqlfool 
http://sqlfool.com/2010/06/check-vlf-counts/

Added version check code due to changes in DBCC LOGINFO

Tested on MSSQL 2005/2008/2008R2/2012/2014

*/

/*
NOTE - the output of DBCC LogInfo adds an extra 
column as of SQL 2012 so there is a version check 
to drop that column for older versions
*/

Create Table #stage
(
RecoveryUnitID int /* This is the new column as of SQL 2012 */
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);

If (select LEFT(cast(serverproperty('ProductVersion') as varchar),2)) in ('8.','9.','10')
ALTER TABLE #stage DROP COLUMN RecoveryUnitID

Create Table #results(
Database_Name sysname
, VLF_count int 
);


Exec sp_msforeachdb N'Use [?]; 

Insert Into #stage 
Exec sp_executeSQL N''DBCC LogInfo([?])''; 

Insert Into #results 
Select DB_Name(), Count(*) 
From #stage; 

Truncate Table #stage;'

Select * 
From #results
Order By VLF_count Desc;

Drop Table #stage;
Drop Table #results;

--

I am happy with the final product (the modified VLF count script) but also with my brief path of discovery on handling Temp Tables in IF...ELSE blocks - I know I have had similar problems before as outputs vary from version to version and now I have another idea to try the next time it comes up!

Hope this helps!

T-SQL Tuesday #74 - Who Grew The Database?

This month T-SQL Tuesday is hosted by Robert Davis (blog/@SQLSoldier) and his topic of choice is “Be The Change”

(If you don’t know about T-SQL Tuesday check out the information here – each month there is a new topic and it is a great excuse to write each month (and to start writing!) because someone offers a topic, so you already have taken the first step!).

--

Robert's choice of topics threw me:

The topic for T-SQL Tuesday #74 is Be the Change. More specifically, data changes. How do you track changing data? How do you do your ETL? How do you clean or scrub your data? Anything related to changing data. Give us your most interesting post involving changing data next Tuesday, January 12, 2016.

As a mostly operational DBA, I have very limited contact with data changes and tracking them.  I have some very limited experience with Change Data Capture, but others have already written about it and done a very good job (also - read Mickey Stuewe's (blog/@SQLMickey) post "Knowing When Data Changes Occur in Standard Edition" about how to work around CDC being Enterprise Only!)

I had a breakthrough earlier today reading Jason Brimhall's (blog/@sqlrnnr) post "All about the Change" in which he writes about changes as related to SQL Audit.  I realized that a fair amount of the administrative triage that I do is directly caused by data changes, especially large scale ones. Here is a solution that I have modified over the years to help me track the changes in my systems *caused* by data changes.  (see how I spun that?)

--

The changes in question here are file size changes - as operational DBAs one of the problems we constantly deal with is files that grow out of control, often from maintenance work such as index rebuilds or from unusual ad-hoc operations such as the analyst working at 1am trying to create a personal copy of the giant Sales table.

We all know that the "best" thing to do (remember, #ItDepends) is to appropriately size your DATA and LOG files ahead of time, and if possible to manually grow those files after hours so that there isn't any production impact.  Even in this absolute best case (almost theoretical) scenario, it is still usually right to leave auto-growth enabled "just in case."

Very few of us live in that world, and we size our files as best as we can with almost no real business requirements using our DBA "Spidey Sense."  Our files are then "managed by auto-grow" as we try to find the best steady state for the DATA and LOG files and minimize impact while getting the best performance.

Does that sound familiar?

http://cdn.meme.am/instances/53655925.jpg

As a service provider, we monitor the drive free space and SQL Server Error Logs on our client servers (along with dozens of other things) - auto-growth problems can often be seen through messages like this:

Could not allocate space for object 'dbo.Table1'.'PK_Table1' in database 'myDatabase' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

...or...

The transaction log for database 'SomeOtherDatabase' is full due to 'ACTIVE_TRANSACTION'.

These messages usually mean that either the file has hit its MAXSIZE cap or that the drive hosting the file is full (or at least sufficiently full that it can't hold another FILEGROWTH increment worth of space). The first question that comes up in the root cause analysis is often "Who grew the database?"

Sometimes there are obvious culprits - is an index maintenance job running?  Is there a nightly ETL running that had an exceptionally large file last night?  Often there is no such smoking gun...and then you need something more.

--

I feel a little dirty writing about the Default Trace in the world of Extended Events, but I also know that many people simply don't know how to use XEvents, and this can be faster if you already have it in your toolbox.  Also it will work back to SQL 2005 where XEvents were new in SQL 2008.

I have modified this several times to improve it - I started with a query from Tibor Karaszi (blog/@TiborKaraszi), modified it with some code from Jason Strate (blog/@StrateSQL), and then modified that myself for what is included and what is filtered.  There are links to both Tibor's and Jason's source material in the code below.

--

/*

Default Trace Query

Especially useful for Auto Grow and Shrink Events 
but works for all default trace info

Modified from Tibor Karaszi at 
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/06/19/did-we-have-recent-autogrow.aspx 

Default Trace Path query modified from
http://www.jasonstrate.com/2013/01/determining-default-trace-location/

*/

DECLARE @fn VARCHAR(1000), @df bit
SELECT @fn =REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc',
/* reading log.trc instead of a specific file will query all current log files */
@df = is_default 
FROM sys.traces 
WHERE id = 1

IF @df = 0 OR @df IS NULL
BEGIN
  RAISERROR('No default trace running!', 16, 1)
  RETURN
END

SELECT te.name as EventName
, t.DatabaseName
, t.FileName
, t.TextData
, t.StartTime
, t.EndTime
, t.ApplicationName
, HostName
, LoginName
, Duration
, cast(Duration/1000000.0 as decimal(10,2)) as DurationSeconds
FROM fn_trace_gettable(@fn, DEFAULT) AS t  
INNER JOIN sys.trace_events AS te 
ON t.EventClass = te.trace_event_id  
WHERE 1=1 /* necessary to cleanly build WHERE clause */
/* find autogrowth events */ 
--and te.name LIKE '%grow%'
/* find autoshrink events */ 
--and te.name LIKE '%shrink%'
/* find manual shrink events */ 
--and (te.name = 'Audit DBCC Event' and (TextData like '%shrinkfile%' or TextData like '%shrinkdatabase%'))
--and DatabaseName='tempdb'
--and StartTime>'01/10/2016 00:00'
--and EndTime<='01/11/2016 13:00'
ORDER BY StartTime desc  

--

The base query pulls all events from the Default Trace.  As noted in the Variable assignment query from @fn, reading data from log.trc (rather than log_05.trc or log_44.trc for example) will combine the rows in the five current default trace TRC files.

The WHERE clause is built so that you can uncomment whichever lines you need.  The initial 1=1 is present so that all of the commented out lines can start with an 'AND' to allow them to flow together regardless of which lines you uncomment.

Want to find autogrowth events for the Bob database?  Uncomment the "and te.name like '%grow%' and the "and DatabaseName='Bob'" lines and you are set!  Need to add time filters?  Uncomment out those lines and modify the times.  And so on....

If you run the query, you can see that the autogrowth of Bob was caused by application "Sales" running on AppServer01, or by a .NET application on WebServer99 running as Domain\WebUser, or even by SQLCMD running on the server locally as the Agent service account (in my world this often means the growth is being caused by a Hallengren maintenance job, since his SQL Agent jobs run under SQLCMD).

--

Remember, this can be done via XEvents as well (a topic for another blog post) and since Trace is deprecated that is the "better" way to do this - but this still works and is quick to use.

Hope this helps!

Why is my SQL Server using all of the RAM on the server?

The TL;DR on this is simple:

"BECAUSE IT'S SUPPOSED TO!"

A frequent complaint we receive comes from a client that has an in-house sysadmin monitoring tool like Microsoft SCOM/SCCM.  They turn the tool on and it starts red-alerting because the Windows server hosting SQL Server is at 90%+ used RAM.  The sysadmin (or local DBA) logs on to the server and finds that there is 5GB free (~4%), and the sqlservr.exe process is using 120GB of the 128GB on the server!

http://cdn.meme.am/instances/55759451.jpg

Like almost all things SQL Server, this is a simplification of a situation that requires some serious #ItDepends.

• What is the baseline - how much RAM is usually free on this server?  This can be seen by tracking the Perfmon counter Memory\Available MBytes.
• What is the Buffer Manager\Page Life Expectancy number - not right this second, but over time?
• Is there any actual performance issue on the server?

I have written before about the need for baselines, especially regarding memory and CPU use on the Windows server itself.  As a DBA, I don't care that my Windows server is at 95% RAM *if* both Windows and SQL Server aren't having performance problems *over time*.  

I started my IT career on a support/help desk supporting servers and desktops, and 16 years ago being at 5% free RAM probably meant you had 500MB-1GB free, which was definitely an OMG! (although then we probably didn't actually say OMG.)

Today's servers often have 96GB or more of RAM, and the larger the server the less relevant a percentage-based measurement is - just because your server has 256B of RAM doesn't mean Windows is suddenly going to need 25GB+ of RAM to run cleanly.

This brings us back to our three questions above.  Rather than worrying about some artificial guideline like 10%, how much memory is usually free on this server?  Track Memory\Available MBytes over time and see where it tops out - if the regular operation of your server always leaves 2-4GB free, you are probably OK from an Windows point of view.

What is the Page Life Expectancy of the SQL Server instance over time?  As many other have written about, PLE is one of the most misused statistics in SQL Server.  A DBA checks Perfmon or runs a DMV query (like this one from the awesome set curated by Glenn Berry (blog/@GlennAlanBerry)):

-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 36) (PLE by NUMA Node)
SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

They find that the PLE is some relatively small number and freak out, or worse, they find it is a large number and think "my SQL Server doesn't need this much RAM, I can cut back."

https://lost100pounds.files.wordpress.com/2011/05/danger-cat.jpg

Like Available MBytes, PLE is a number that needs to be measured over time.  On the servers I touch I always recommend setting up a "permanent" Perfmon collector of SQL Server-related counters so that you can look at the numbers over time. For example, a usual PLE chart over a week can look like this:

...or it can look like this:

Even over time, some people look at this second chart and say one of two things:

• My PLE goes down to 14, what do I do?
• My PLE maxes out at 50,000, what do I do?

Again, #ItDepends - what is going on in SQL Server during each of these times?  If you look at the above chart you can see that PLE generally climbs during the day (with some minor fluctuations), but then tanks over night pretty regularly. 

Look at when SQL Server dips to 14 - is that when reindexing or CheckDB runs?  If so, is performance good enough?  If CheckDB runs in two hours, and your window is three to four hour, isn't that acceptable?  Are other things running at the same time that are negatively impacted, or is that window clear?

If you do need to improve the processes running at that moment (when PLE dips), what is the Available MBytes at this same time and over time?  If it is high over time, it shows that there is head room on your server to consider increasing your Max Server Memory and to help your PLE stay higher.  If your Available MBytes is not consistently high, you can't consider increasing Max Server Memory unless you increase actual server RAM.

This is the 60-second version of quick memory troubleshooting, but think about it - isn't this (even just this small piece of it) a lot more complicated than "I have less than 10% free RAM."

Is anybody or any process complaining (besides SCCM)?  Are any numbers out of line - for example is a virus scan that normally takes 2 hours suddenly taking 4-5?  If nothing else is abnormal, is there a problem?

--

As I mentioned at the start of this post, quite often your SQL Server process(es) are simply *supposed* to be using the lion's share by far of the resources on the Windows server - especially RAM.  As long as you reserve sufficient head room for the operating system (usually 4GB-16GB as described here by Jonathan Kehayias (blog/@SQLPoolBoy) but modified by monitoring Avail MBytes) then the percentage of RAM used probably doesn't matter.

...and if you have good sysadmins they will listen to you and realize that SQL Servers (like Oracle, Exchange, and other database servers) are simply different from web servers or file servers.

Hope this helps!