@SQLTrooper's SQL Server Journal

Identify and Retry Failed Backups using Ola's Solution

2019-04-22T10:48:00.000-07:00

I ran into a situation where a database backup would fail due to a network error - specifically "Operating system error 59(An unexpected network error occurred.)" This post pertains to those using the Ola Hallengren backup solution, but the concept and/or approach could likely be applied to other backup solutions as well.

When the solution iterates through the databases to be backed up, it may succeed on all but one or two but the job as a whole would report a failure. In this scenario, you wouldn't want to re-run the entire job or configure the job step to use the Retry attempt/Retry Interval options, as that would result in all databases getting backed up again. That would consume unnecessary resources. The ideal solution would identify only those database backups that failed and retry those. With Ola's solution, we can identify and retry failed backups by querying the CommandLog table and running a subsequent backup script:

DECLARE @SQL VARCHAR(MAX), @FailedBackups NVARCHAR(MAX), @BackupType VARCHAR(4)

SET @BackupType = 'FULL'; --LOG, DIFF or FULL

WITH cteRowNumber
AS (SELECT [DatabaseName],
[ErrorNumber],
ROW_NUMBER() OVER(PARTITION BY [DatabaseName]
ORDER BY ID DESC) AS RowNum
FROM [CommandLog]
WHERE [Command] LIKE '%_' + @BackupType + '_%'
AND [CommandType] IN ('BACKUP_DATABASE','BACKUP_LOG'))
SELECT @FailedBackups = COALESCE(@FailedBackups + ',', '') + [DatabaseName]
FROM cteRowNumber
WHERE RowNum = 1
AND [ErrorNumber] <> 0
ORDER BY [DatabaseName];

SELECT @SQL = 'EXECUTE [dbo].[DatabaseBackup]
@Databases = ''' + @FailedBackups + ''',
@Directory = NULL,
@BackupType = ''' + @BackupType + ''',
@LogToTable = ''Y''';
PRINT @SQL;
--EXEC (@SQL);

After testing the script and verifying it worked as intended, I created a second step in the backup job that would be triggered if the main backup step failed. If it succeeds, all backups will then be current.

I'm sure I could expand the script to cover a broader set of use cases, but this covered my immediate needs.

Using Synonyms with Availability Groups

2017-09-14T12:30:00.000-07:00

I'm a big fan of using synonyms to point to objects in other databases, but if you've tried to use them in conjunction with Availability Groups you may have encountered a slight issue: upon failover, the synonyms are no longer valid because the instance name is part of the synonym definition. In order for the synonyms to work again, they have to point to the instance they now reside in - assuming the databases were in the same instance, respectively.

The next challenge was to automate the of detection of when the primary has changed, therefore triggering an update of the synonyms. I put the following tSQL into a SQL Agent job and set it to run every five seconds. This will be Step 1 in the job, as it will have two steps total.

IF  (   SELECT role_desc
        FROM   sys.dm_hadr_availability_replica_states
        INNER JOIN sys.dm_hadr_name_id_map

               ON  sys.dm_hadr_availability_replica_states.group_id =                                                 sys.dm_hadr_name_id_map.ag_id
        WHERE  sys.dm_hadr_name_id_map.ag_name = 'AGName'
               AND is_local = 1
    ) = 'secondary'
    BEGIN
        RAISERROR('This is secondary replica. Do not countinue this job.', 11, 1);
    END;

ELSE
    BEGIN
        PRINT ('This is primary replica and you can go to next step.');
    END;

Now, if no failover has occurred, the Step 1 above will fail - as will the job as a whole obviously. If in fact a failover has occurred it will go on to Step 2, which has the below syntax. You'll note the syntax of the query in the cursor: it will query sys.synonyms to see if there are any pointing to the incorrect (not local) instance. If they are all correct, no records will be returned and it will stop. If there are synonyms that need updating, it will fall into the cursor and drop/recreate them using REPLACE syntax to point locally (@@SERVERNAME).

DECLARE @ObjectName SYSNAME
      , @Definition VARCHAR(MAX)
      , @Schema VARCHAR(50);

DECLARE @SQL VARCHAR(MAX);

DECLARE loccur CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
    SELECT name
         , SCHEMA_NAME(schema_id)
         , base_object_name
    FROM   sys.synonyms
    WHERE  base_object_name LIKE '%OtherInstanceName%';

OPEN loccur;

FETCH NEXT FROM loccur
INTO @ObjectName

   , @Schema
   , @Definition;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Converting: Synonym, ' + @ObjectName;

        SET @SQL = 'DROP SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName);
        EXEC (@SQL);
        --PRINT @SQL

        SET @SQL = 'CREATE SYNONYM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@ObjectName) + ' FOR ' + REPLACE(@Definition, 'OtherInstanceName', @@SERVERNAME);
        EXEC (@SQL);
        --PRINT @SQL

        FETCH NEXT FROM loccur
        INTO @ObjectName
           , @Schema
           , @Definition;
    END;

CLOSE loccur;
DEALLOCATE loccur;

Monitor and stop long-running SQL Server Agent Jobs

2017-08-29T10:32:00.000-07:00

In recent days I've been having a periodic issue with the distribution cleanup job for replication. The vast majority of the time it runs in a few seconds. There are times at night when it may take a few minutes which is not an issue. But recently I've had a couple cases where the job ran for 13 or 14 hours and that subsequently led to prolonged blocking of other replication activities such as the distribution agent SPIDs, etc. That in turn led to all sorts of other issues that I won't detail here.

To address the situation, I would essentially have to stop the SQL Server Agent job called 'Distribution clean up: distribution'. Oddly enough, subsequent runs would be right back on track.

To mitigate the issue as quickly as possible (perhaps there are other solutions out there) I decided to put together a simple monitoring job to check how long the cleanup job has been running. If it has been running for more than 10 minutes, stop the job.

Here is the code I used. It has to run in msdb and I scheduled it to run every 10 seconds. You could use this syntax to monitor any jobs - perhaps an index maintenance job is running abnormally long on the weekend and bloating TempDB or something - but you wouldn't know right away. This will at least stop the activity until there is time to further investigate.

The syntax:


DECLARE @Duration_secs INT;

SELECT @Duration_secs = DATEDIFF(ss, ja.start_execution_date, GETDATE())
FROM msdb.dbo.sysjobs jobs
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL

WHERE  jobs.name = 'Distribution clean up: distribution'
       AND stop_execution_date IS NULL;

IF @Duration_secs > 600 -- 10 minutes
    BEGIN
        EXEC dbo.sp_stop_job N'Distribution clean up: distribution';
    END;

VMware Host Performance and Configuration Reports

2016-08-18T07:46:00.002-07:00

So you are a DBA and you are in a virtual environment - VMware in particular. You are curious to know the health of the VMware hosts in terms of CPU and RAM, but you really don't know how to get the data you need and you're not certain if the information you are asking for is entirely accurate. Well, chances are you have access to the VMware databases themselves - if that is the case, you can create these reports based on a blog post from Jonathan Kehayias: "Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information".

I have created five reports that are based on Jonathan's queries and you can download the RDL for the SSRS reports below - enjoy!

Host Configuration
CPU Ready Values (Daily)
CPU Ready Values (Weekly)
Memory Ballooned (Daily)
Memory Ballooned (Weekly)

Hopefully you find these useful,
Allen (@SQLTrooper)

SSMSBoost - A Productivity Tool

2016-01-21T10:02:00.002-08:00

SSMSBoost Tips and Trips (http://www.ssmsboost.com)

Today I typed up a little "tips and tricks" document for the various teams I work with and figured heck, why not share it with everyone? As you can see, I had to obfuscate some of the database/connection information with white boxes. Hope the tips come in handy!

Tip 1: Locate Object – when you have hundreds or sometimes thousands of tables to comb through, this feature makes it very simple to locate an object with a couple clicks. Simply hover over the object (normally a table), right-click and click “Locate object in Object Explorer”. All the menus will expand and send you right to the object.

Tip 2: Easily switch connections – I often switch between connections, which natively is a little cumbersome (Right-click > Connection > Change Connection, etc.). With SSMSBoost, you can define your Preferred Connections. For each connection, as you can see below you can also choose a default database that it will connect to rather than master by default.

The color coding is nice as well. I choose to set all production servers to have a red Status Bar (see Tip 1 image). That way I know when I’m connected to production and when I’m not. Once you have configured your Preferred Connections, you’ll note the Connections drop-down in the SSMSBoost ribbon will contain them all for quick switching between instances:

Tip 3: Fatal Actions Guard – Let’s face it – we’ve all likely ran a query and forgot to put a where clause, or perhaps you clicked F5 to truncate a table in dev… BUT you were in production. At least this feature gives you one last chance to change your mind if you really didn’t mean it.

Preview of the popup that will appear upon execution:

Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever. You end up saving the query results to Excel and about five minutes later you have the totals, etc. With SSMSBoost, you can have your totals in seconds. Below I’m selecting 10 records from a sales related table. Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE. All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:

Tip 5: Format SQL – Not every developer has a tSQL formatting tool. SSMSBoost does come with one if you want to use it. Your teams could all configure the tool for the same sort of formatting for consistency sake.

Tip 6: History – How many times have you wanted to retrieve a query from a few days ago? Maybe SSMS crashed, or you wanted to revert back to another version. You can customize the History feature to suit your needs. In the example below I searched for UPDATE – I know I ran an update a few days ago, but I don’t recall the exact syntax. Not a problem:

Custom Backup Exec SSRS Report

2015-10-16T08:35:00.000-07:00

I'm not a Backup Exec administrator, but recently one of my co-workers wanted a custom SSRS report to track our backups, which also allows him to receive this report on a regular basis via a subscription. This is what I came up with - perhaps you too will find it useful.

Query:
SELECT CASE TaskTypeID
WHEN 200 THEN 'Backup'
WHEN 300 THEN 'Duplicate'
END AS TaskType
,JobName
,ActualStartTime
,EndTime
,(ElapsedTimeSeconds / 3600.0) AS ElapsedTimeHours
,CASE FinalJobStatus
WHEN 1 THEN 'Success w/ Exception'
  WHEN 3 THEN 'Success w/ Exception'
  WHEN 6 THEN 'Canceled'
  WHEN 19 THEN 'Success'
ELSE 'Unknown'
END AS FinalJobStatus
,TargetName
,MediaSetName
,(TotalDataSizeBytes / 1024.0 / 1024.0 / 1024.0) AS TotalDataSizeGB
,TotalNumberOfDirectories
,FinalErrorDescription
FROM BEDB.dbo.vwJobHistorySummary
WHERE TaskTypeID IN (200, 300)
AND ActualStartTime > DATEADD(MONTH, -1, GETDATE())
ORDER BY ActualStartTime DESC

RDL: click here.

Custom System Center Reports for Windows Updates

2015-08-26T10:22:00.000-07:00

Chances are if you've used System Center (SCCM) you'll find that the reports are less than adequate for keeping tabs on software updates in your environment. Because of that, I decided to author a couple of Reporting Services (SSRS) reports to inform me which servers/computers (I'll use "server(s)" the rest of the way) require how many updates, along with the ability to click in and get more details as to which updates those are. On the summary report you can search for servers containing certain characters - if you have a lot of servers this comes in handy. You can then click on the server name to get more details. On the detailed report, I decided to color code the severity and provide a direct link to the Microsoft KB.

New! I have added a Severity Level drop-down to allow you to just review servers/workstations that only require critical updates. I've also added a Type drop-down so you can choose between Servers and Workstations (you will have to get some data from the database for this - review/modify the Dataset as needed). I've also added a total count of devices and count of those requiring critical updates, along with a percentage - management often inquires on those high-level sorts of statistics.

Click here to get the RDL for the summary report. Here is a preview:

Click here to get the RDL for the summary report. Here is a preview:

Happy patching!

Work Smarter, Not Harder - The UTILITY Instance

2014-11-19T10:22:00.001-08:00

If your a SQL Server DBA, chances are you've walked into environments that were pretty disorganized to say the least. Nobody seems to have a complete list of SQL Server instances, the instances are not configured consistently, database recovery models and growth rates are all over the board, the master database is muddied up with old versions of scripts and other objects - the list is endless. And it's a nightmare to administer.

I've come up with an organizational approach that has worked very well for me over the years. The heart of the organizational effort starts by having a centralized administrative instance. I personally call this instance my "\UTILITY" instance. It acts as the Central Management Server, is used for Policy Based Management, is the master server (MSX) for common SQL Agent jobs, houses the core DBADB (which every instance I administer has) which in turn is where I keep my favorite administrative scripts, and finally it's where I keep my monitoring and utility databases. It's important to note that I push out my administrative scripts to all instances using replication. If a new version comes out, I simply update the central version and reinitialize all subscriber instances so they too receive a fresh, updated copy.

If you have questions and/or feedback on this approach, I would love to hear it! I'm always looking to work smarter, not harder!

Here is a graphical representation of my \UTILITY instance's responsibilities:

Dynamically Adjust SQL Server Failover Cluster Instance Memory

2014-11-18T14:23:00.000-08:00

I recently installed a third FCI (Failover Cluster Instance) on a three-node WSFC (Windows Server Failover Cluster), meaning each FCI will have it's own node - assuming all nodes are online. It's worth noting this is a non-production cluster - I would normally have an N+1 configuration in production, meaning I have a spare node at all times and typically would not have to worry about running more than one FCI on a single node. But in this case it is quite possible to be running two instances on a single node, or the rare case where all three instances are on a single node. When that happens, you'll want to adjust the memory in each instance so they can co-exist on the same node while you address which ever node is offline (patching, other failure, etc.).

All that said, I found a blog post authored by Aaron Bertrand (b|t) where he dynamically adjusted memory for two instances that have different hardware, whereas in my case I have three identical virtual machines. Therefore I wanted to take some of his existing logic and tailor it to my situation. While I'm pretty certain I could have made it more efficient, the goal was to keep it very easy to read and maintain moving forward.

Some keys to remember: you will have to create linked servers to each instance and you will make this procedure a startup procedure. Also worth noting is that each of my nodes have 32GB of RAM with 28GB assigned to SQL Server.

EXEC [master].dbo.sp_procoption 
   'dbo.BalanceMemory', 
   'startup', 
   'true'

USE [master]
GO

CREATE PROCEDURE [dbo].[BalanceMemory]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ThisNode NVARCHAR(255)
,@ThisInstance NVARCHAR(255)
,@OtherNode2 NVARCHAR(255)
,@OtherNode3 NVARCHAR(255)
,@command NVARCHAR(MAX)
,@run NVARCHAR(MAX)
,@LoneMemory VARCHAR(5) = 28672
,@SharedMemory2 VARCHAR(5) = 14336
,@SharedMemory3 VARCHAR(5) = 9216

SELECT @ThisNode = CONVERT(sysname, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
,@ThisInstance = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
,@command = 'USE [master];
EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory (MB)'', $;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''show advanced options'', 0;
RECONFIGURE WITH OVERRIDE;';

IF @ThisInstance = 'SQL01'
BEGIN
EXEC [SERVER02\SQL02].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))', N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
  EXEC [SERVER\SQL03].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))', N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
BEGIN -- ALL THREE ON SAME NODE!
  SET @run = REPLACE(@command, '$', @SharedMemory3);
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode2
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;

  SET @run = REPLACE(@command, '$', @LoneMemory);
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode3
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;

SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
  END
ELSE
BEGIN -- EACH ON OWN NODE
  SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
END

IF @ThisInstance = 'SQL02'
BEGIN
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
BEGIN -- ALL THREE ON SAME NODE!
  SET @run = REPLACE(@command, '$', @SharedMemory3);
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode2
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;

  SET @run = REPLACE(@command, '$', @LoneMemory);
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode3
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;

  SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  END
ELSE
BEGIN -- EACH ON OWN NODE
  SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  EXEC [SERVER03\SQL03].[master].dbo.sp_executesql @run;
  END
END

IF @ThisInstance = 'SQL03'
BEGIN
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
N'@node SYSNAME OUTPUT', @OtherNode2 OUTPUT;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql N'SELECT @node = CONVERT(SYSNAME, SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))',
N'@node SYSNAME OUTPUT', @OtherNode3 OUTPUT;

IF @ThisNode = @OtherNode2 AND @ThisNode = @OtherNode3
BEGIN -- ALL THREE ON SAME NODE!
  SET @run = REPLACE(@command, '$', @SharedMemory3);
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode2
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;

  SET @run = REPLACE(@command, '$', @LoneMemory);
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
  END
ELSE
IF @ThisNode = @OtherNode3
BEGIN -- TWO ON SAME NODE
  SET @run = REPLACE(@command, '$', @SharedMemory2);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;

  SET @run = REPLACE(@command, '$', @LoneMemory);
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  END
ELSE
BEGIN -- EACH ON OWN NODE
SET @run = REPLACE(@command, '$', @LoneMemory);
EXEC [master].dbo.sp_executesql @run;
  EXEC [SERVER01\SQL01].[master].dbo.sp_executesql @run;
  EXEC [SERVER02\SQL02].[master].dbo.sp_executesql @run;
END
END
END

Consolidate your Reports with Dynamic Data Sources

2014-10-21T14:17:00.002-07:00

I love writing SSRS reports that assist me in consuming data across my instances. In some cases I set up subscriptions to my reports so I don't forget to review the data periodically. But how many times have you created a really nice report and made a copy of it so you could have it query data from another instance? I was modifying a report today and since I had three copies (one for three different instances), I set out to find a better solution.

If you write reports, you know that when you create a data source, it is tied to an instance of SQL Server. If you want your report to be able to query more than one data source, you will want to create what is called a "dynamic data source". Here is one way you can go about doing that. Note that this solution is tailored around the fact that I have databases in each of my instances with the same schema to harvest performance related data, for example, but you might have a report that queries a DMV - that would be another use case.

Create Shared Dataset

The first thing I'm going to start with is a blank report. I'm then going to create a Shared Dataset in my Visual Studio Solution called CMSInstances. Creating a shared dataset allows you to use it in all the reports in your solution. I prefixed it with CMS because I'm going to get a list of instances from my Central Management Server (CMS). If you are not using a CMS, you should be! But if that were the case you would simply specify the parameter values individually, keeping in mind that if you add/remove instances - you will have to edit every report!

Configure the shared dataset to connect to your CMS instance and paste the following query in the query window:

SELECT g.[name] + ' - ' + s.[name] AS [SQLAlias]

,[server_name] AS [InstanceName]

FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] g

INNER JOIN [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] s ON g.[server_group_id] = s.[server_group_id]

WHERE g.[server_type] = 0

AND g.[is_system_object] = 0

ORDER BY [SQLAlias]

Create your Data Source

Now that we have our shared dataset created, we can go to our report and add it as a data source. In the Report Data window, right-click Data Sources and add a new one. Call it "DynamicDataSource". Next, in the embedded connections section click the expression button.

Enter the following expression:

="Data Source=" & Parameters!InstanceName.Value

Create your Report's Dataset

Because we created the shared dataset prior, we can use that dataset over and over in any report we author. To add that shared dataset to this report, simply right-click Datasets, give it a Name and select the shared dataset.

Create your Parameter

Now that you've created the reports dataset to populate your instance pull-down, we can create the Parameter. Right-click on Parameters and Add Parameter. For Name type in InstanceName and Prompt just type in Instance. Next, click on Available Values and configure it as follows:

You're all done!

You should now be able to Preview the report and your Instance pull-down will be populated with all the instances configured in your CMS. If you group your servers by environment, you can further refine the query as needed. Any queries using this data source would obviously have to be drawing their data from similar databases and table names.

Using Replication to keep your Admin Scripts Current

2014-07-17T11:08:00.003-07:00

We all have handy scripts that we want present in our SQL Server database instances. Examples would include sp_WhoIsActive, sp_Blitz, sp_BlitzCache, Ola's maintenance scripts or custom scripts like Estimated Time to Complete. But how do you keep all your instances updated when new versions are released? You certainly don't want to run the updated scripts on each instance one-by-one, especially if you manage quite a few instances. One way to keep them in sync is by using a Central Management Server (CMS) and running the updated script on multiple instances at once. However, what if there are some instances that you don't really want or need to deploy the scripts to? That was my situation, so I opted to use SQL Server Replication to keep my scripts in sync.

I personally have an instance that I call my "UTILITY" instance. I use this instance as my CMS. It's also where I store all my monitoring databases like Spotlight, utility databases like ClearTrace and most importantly - my central DBADB. I have a DBADB in every instance I administer. It's a place to keep all your DBA related scripts and tables (maintenance logging tables, sp_BlitzCache output, harvested wait stat data, etc.) and you can have peace of mind that nobody else is going to go in there and mess with them. I then created a publication on my DBADB, selected only the stored procedures that I want to deploy and set up subscriptions to all the target instances. With just a few clicks of the mouse, I was able to deploy out the latest and greatest version of all my administrative scripts.

These are the ones I personally replicate out - if you have other useful scripts, feel free to share!

Reporting on sp_BlitzCache Results

2014-06-18T08:21:00.004-07:00

Regularly examining the plan cache in your database instances is a must, as there is a wealth of useful information that helps you determine which statements and/or stored procedures are your biggest performance problems. Doing such analysis has become much easier with the release of sp_BlitzCache by Brent Ozar Unlimited. One of the great features of sp_BlitzCache is the fact that you can log the results to a table, allowing for more effective analysis of the results. I've decided to take it one step further and put together an SSRS report to help dive into the data. Get the Report RDL.

To harvest the data, I created a scheduled job in SQL Server which is set to run nightly at 7pm. I keep all of my utility scripts and data in a database called DBADB. Each instance has its own such database.

EXEC DBADB.dbo.sp_BlitzCache

@top='200',

@results = 'expert',

@output_database_name = 'DBADB',

@output_schema_name = 'dbo',

@output_table_name = 'BlitzCache'

The report I wrote is based off expert mode results. There is a plethora of data in the report so it does get fairly wide. I don't display the values in QueryText or QueryPlan on the report, but in Figure 3 you'll note I provided the syntax to obtain the query plan and/or SQL text in the header. Copy in the plan_handle or sql_handle, respectively. Another option is to directly query your BlitzCache table for it.

There are four parameters on the report: Database Name, Sample Time, Query Type and Warnings. Most column headers allow you to sort so you can zero in on a particular performance metric of concern.

Figure 1 - Parameters

Figure 2 - Report Data - these are the first set of columns in the report:

Figure 3 - Report Data, cont. - scrolling to the right is more data:

SQL Server Spinlock Reference

2014-05-13T10:20:00.001-07:00

I've been troubleshooting some performance issues of late, and it was brought to my attention that there is yet another DMV that may shed some light on the subject: sys.dm_os_spinlock_stats.

First, what is a spinlock?
Per Microsoft's whitepaper Diagnosing and Resolving Spinlock Contention on SQL Server: "Spinlocks are lightweight synchronization primitives which are used to protect access to data structures. They are generally used when it is expected that access to a given data structure will need to be held for a very short period of time."

How does a spinlock differ than a latch?
Per the whitepaper: "The main difference between a latch and a spinlock is the fact that spinlocks will spin (execute a loop) for a period of time checking for availability of a data structure while a thread attempting to acquire access to a structure protected by a latch will immediately yield if the resource is not available. Yielding requires context switching of a thread off the CPU so that another thread can execute. This is a relatively expensive operation and for resources that are held for a very short duration it is more efficient overall to allow a thread to execute in a loop periodically checking for availability of the resource."

What condition might indicate spinlock contention?
Per the whitepaper:

1. A high number of spins and backoffs are observed for a particular spinlock type.

AND

2. The system is experiencing heavy CPU utilization or spikes in CPU consumption. In heavy CPU scenarios one may also observe high signal waits on SOS_SCHEDULER_YEILD (reported by the DMV sys.dm_os_wait_stats).

AND

3. The system is experiencing very high concurrency.

AND

4. The CPU usage and spins are increased disproportionate to throughput.

Options and workarounds for handling spinlock contention:
Per the whitepaper:

Fully Qualified Names: Fully qualifying names of all objects will result in removing the need for SQL Server to execute code paths that are required to resolve names. We have observed contention points also on the SOS_CACHESTORE spinlock type encountered when not utilizing fully qualified names in calls to stored procedures. Failure to fully qualify these the names results in the need for SQL Server to lookup the default schema for the user which results in a longer code path required to execute the SQL.

Parameterized Queries: Another example is utilizing parameterized queries and stored procedure calls to reduce the work needed to generate execution plans. This again results in a shorter code path for execution.

LOCK_HASH Contention: Contention on certain lock structure or hash bucket collisions is unavoidable in some cases. Even though the SQL Server engine partitions the majority of lock structures, there are still times when acquiring a lock results in access the same hash bucket. For example, an application the accesses the same row by many threads concurrently (i.e. reference data). This type of problems can be approached by techniques which either scale out this reference data within the database schema or leverage NOLOCK hints when possible.

The diagram below provides a conceptual view of the spinlock algorithm:

Here are some additional resources I found on the topic:

Paul Randal
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock, April 2011
Advanced SQL Server performance tuning, April 2011

Thomas Kejser
Thread Synchronization In SQL Server, November 2011
Diagnosing And Fixing SOS_OBJECT_STORE Spins for Singleton INSERTS, May 2011
Secret Scale Sauce: Heavy Singleton INSERT Spinlocks, May 2011

Arvind Shyamsundar
Exotic spinlocks: XTS_MGR, January 2014

Fabricio Catae
Spinlock (Part I), July 2010
Spinlock (Part II), July 2010
Spinlock (Part III), July 2010
Spinlock Contention, December 2009

SQL Server Wait Stats Harvesting and Reporting

2014-05-07T12:24:00.000-07:00

One the the keys - if not THE key - to identifying SQL Server performance issues is by investigating wait statistics. As is the case with various SQL Server performance metrics, however, is the fact that these stats are cumulative. That means you can't just query sys.dm_os_wait_stats and determine the root cause of an issue at a particular point in time unless your issue is happening right now. If that were the case, you run one of the various scripts to calculate the differential of multiple sys.dm_os_wait_stats executions (example from Brent Ozar). But what if your issue occurred off-hours, during the night, while you were traveling or in a meeting?

While I realize there are various ways to go about analyzing wait stats, I decided to put together a solution that was very easy to manage, deploy and write custom reports from. You will need to configure a Central Management Server (CMS) and register your target servers - the servers you want to deploy this solution to.

Once you have your CMS configured, you will create a SQL Server Agent job ("Collect and Cycle Wait Stats", for example). The job is going to have four steps:

Check for DBADB database, which is the database I keep all DBA related scripts and data in

If not present, create it
Get script here

Check for table that will house the wait stats

If not present, create it
Get script here

Collect wait stats

I personally harvest the wait stats hourly but you may adjust as you see fit
This is a pretty typical query you will find in most blogs
Get script here

Clear wait stats

By clearing the wait stats, I know each hourly harvest are the wait stats just for that hour and I don't have to do any differential calculations
If I want to know wait stats to a point in time, I can add them up accordingly
Get script here

Report on it!

Preview of report below
Get the RDL here

By checking for and creating the database and table if they aren't present, you can deploy this by going to your CMS checking a box to target another instance.

Below is a preview of a report I just put together. I'll likely update it in the coming days/weeks to better highlight anomalies, but this works for now. You choose a date and the top 95% of waits are summarized by hour, which you can further drill into by clicking the plus sign by the hour on the left. I added a few helpful reminders/notes from Paul Randal and Joe Sack's blogs. As you can see, I had a CPU issue on this particular day at or around 10am as indicated by the elevated/irregular SOS_SCHEDULER_YIELD signal waits.

Useful SQL Server Articles

2014-05-07T08:41:00.001-07:00

I used to have these on My DBA Toolbox post, but it was getting a little full so I'm pulling out my list of favorite articles and sharing them in their own post. Enjoy!

Paul Randal (b | t)
Misconceptions around Trace Flag 1118
Turn off backup success messages, Trace Flag 3226
Wait statistics, or please tell me where it hurts
Script - open transactions with text and plans

Kimberly Tripp (b | t)
Transaction Log VLFs - too many or too few?
Ever-increasing clustering key - the Clustered Index Debate.... again!

Brent Ozar (b | t)
Triage Wait Stats in SQL Server
What is the CXPACKET Wait Type, and How Do You Reduce It?
SQLIO Tutorial: How to Test Disk Performance
SQL Server 2005/2008/2012/2014 Setup Checklist

Joe Sack (b | t)
Performance Tuning with Wait Stats

Thomas LaRock (b | t)
Are you Using the Right SQL Server Performance Metrics

Kalen Delaney (b | t)
Geek City: Reducing Cache Bloat and a Metadata Bug

Aaron Bertrand (b | t)
SQL Server DDL Triggers to Track All Database Changes
Useful, free resources for SQL Server
Bad habits : Putting NOLOCK everywhere

Robert Davis (b | t)
Schema-only Backups and Restores

Paul White (b | t)
Parameter Sniffing, Embedding, and the RECOMPILE Options
Temporary Tables in Stored Procedures
Temporary Table Caching Explained

Allan Hirt (b | t)
How to Properly Configure DTC for Clustered Instances of SQL Server

Jonathan Kehayias (b | t)
Great SQL Server Debates: Lock Pages in Memory (LPiM)
Extended Events - Targets Week - ring_buffer

Other

SQL Server Builds and Versions
Trace Flags in SQL Server
Monitoring Best Practices by Using Policy Based Management (PBM)
SQLCAT.com consolidated index of Whitepapers
Max Worker Threads and when you should change it
Troubleshooting Databae Mail New add!

Harvest and Report on SQL Server IO

2014-03-20T09:09:00.001-07:00

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.

First, you need to create the tables (all links go to my public GDrive folder)
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

Then, create the stored procedure that will compare the snapshots
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

My SQL Server Installation Checklist

2014-03-07T10:01:00.000-08:00

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.

Install SQL Server!

Create AD account to run services under, if necessary
Create folder structure for data files, transaction logs, TempDB, backups, etc.
Install any SP's and/or CU's
Enable the DAC

Register the instance with the Central Management Server
Instance Configuration

Configure Min/Max Server Memory appropriately (Properties > Memory)
Double-check the Data and Log locations (Properties > Database Settings)
Configure baseline values for Cost Threshold for Parallelism (50) and MAXDOP (1/2 cores in single processor)
Create Login for SQL Server service AD Group/Account(s)
Change the number of SQL Server Logs to retain from 6 to 25
Create job to cycle errors logs nightly at midnight (goes with #5, normally TSX)
Create Operator(s) for DBA
Enable Optimize for Ad-Hoc Workloads (in most cases)
Configure Database Mail for sending alerts (if new,

Scripts

Install Ola Hallengren's maintenance scripts

Schedule user backups accordingly
If using centralized administration for jobs, add to master jobs

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.

Changing the Default Installation Paths for SQL Server

2014-03-05T10:05:00.000-08:00

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.

Open SQL Server Configuration Manager on the server/node
Click SQL Server Services
Right click on SQL Server service and click Properties
Click the Advanced tab (in SQL 2012/2014 click on the Startup Parameters tab)
Change the path following the -e parameter

```
-e'<common folder path>\Log\ERRORLOG'
```

In SQL 2012/2014, be sure to click the Update button or the changes will not stick!

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.

Open SQL Server Configuration Manager on the server/node
Click SQL Server Services
Right click on SQL Server service and click Properties
Click the Advanced tab
Type in the new path

```
'<common folder path>\Log'
```

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.

Go to the server and open the registry (Start > Run, and type in "regedit")
Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
Change the value of SQLDataRoot to the new location

```
'<common folder path>\DATA'
```

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.

Go to the server and open the registry (Start > Run, and type in "regedit")
Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
Change the value of WorkingDirectory to the new location

```
'<common folder path>\JOBS'
```

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.

Go to the server and open the registry (Start > Run, and type in "regedit")
Browse to HKLM\SOSFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
Change the value of FullTextDefaultPath to the new location

```
'<common folder path>\FTData'
```

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

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);

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

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

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!

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!

Stop the instance of SQL Server
Move the physical files to their new location
Restart the instance of SQL Server
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

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 = ( DB_ID(N'master') )

ORDER BY DB_NAME(database_id);

Custom SQL Server IO Report for Quest/Dell Spotlight

2014-02-28T10:11:00.000-08:00

As was the case with my CPU report, I wanted a better way to tap into my Spotlight data to investigate IO data. I figured I would share the RDL.

Here is my initial version - I'll probably update it to highlight anomalies, but that may take a bit of time to determine and develop. You may want to adjust the retention of such data within Spotlight itself - this report is based off the default retention. It happens to be data from a data warehouse server that loads data at night - pretty interesting!

RDL for Ola's Index Maintenance Logging

2014-02-27T10:36:00.001-08:00

Here is the RDL for this report - I moved it to Google Drive.

Custom SQL Server CPU Report for Quest/Dell Spotlight

2014-02-19T09:31:00.003-08:00

I often share some of the custom SSRS reports I've created and this one seemed like a pretty good candidate. It's a custom CPU report by hour that is driven from data collected by Quest/Dell Spotlight for SQL Server. While Spotlight does have an SSRS Reports Pack, I don't find them particularly useful.

The first thing you need to do to make this report useful is to change the frequency in which the data is harvested and extend the retention. Since the report has hourly intervals, I decided to store the data every 15 minutes so I can get a descent sample to then average for each hour, respectively.

Now you just have to create the SSRS report and update the data sources in the report. You can get the RDL here. This is what the report will look like once you get the plumbing all hooked up - note I'm only displaying Monday through Friday because I've found that my servers are pretty quiet on the weekends.

Using SQL Server to Report on your IIS Log Data

2014-01-14T14:24:00.003-08:00

I shared this post on a SQL Tuesday but am going to modify it a bit so folks better understand what it's all about. In this post I'm going to share how you can set up a very cost effective process to report on your IIS log data.

Where are your IIS logs?

The first thing you need to do is locate the correct log directory for the website you are interested in. If you open IIS and click on the Sites, you will see associated ID for that site. Remember that, then in Windows Explorer browse to C:\inetpub\logs\LogFiles\W3SVC<ID>. So if the ID of your website is 2, you would find your logs in the W3SVC2 folder (I will use this as the example going forward).

Copy the logs

The next thing I did was to create a batch file and place it in the same folder as the logs. You don't have to do it this way, but I chose to. There will be a single line in this batch file:

robocopy C:\inetpub\logs\LogFiles\W3SVC2\ \\DB_SERVER\SHARE\IISLogs\ /maxage:1

I used Windows Scheduler to kick off this batch file every 10 minutes, and since we roll our logs over nightly this will get the current and previous days (maxage:1 means one day). The reason for the frequency may vary depending on how accurate you want your data. If our websites have an issue, I prefer to have pretty current data to dive into versus having to open the log itself.

Create the database

Now you'll want to set up your IISLogs database - the schema for the table is as follows:

CREATE TABLE [dbo].[LogData](

[LogFilename] [varchar](255) NOT NULL,

[RowNumber] [int] NOT NULL,

[date] [datetime] NULL,

[time] [datetime] NULL,

[sIp] [varchar](255) NULL,

[csMethod] [varchar](255) NULL,

[csUriStem] [varchar](255) NULL,

[csUriQuery] [varchar](255) NULL,

[sPort] [int] NULL,

[csUsername] [varchar](255) NULL,

[cIp] [varchar](255) NULL,

[csUserAgent] [varchar](255) NULL,

[scStatus] [int] NULL,

[scSubstatus] [int] NULL,

[scWin32Status] [int] NULL,

[timeTaken] [int] NULL,

CONSTRAINT [PK_LogData] PRIMARY KEY CLUSTERED

(

[LogFilename] ASC,

[RowNumber] ASC

)

CREATE TABLE [dbo].[IISDashboard](

[LogFilename] [varchar](255) NOT NULL,

[LogDate] [date] NOT NULL,

[Server] [varchar](10) NOT NULL,

[Records] [int] NOT NULL,

[CallsAbandoned] [int] NOT NULL,

[CallsAnswered] [int] NOT NULL,

[ID30Plus] [int] NOT NULL,

[ID10Plus] [int] NOT NULL,

[ID2Minus] [int] NOT NULL,

[ID200Errors] [int] NOT NULL,

[ID500Errors] [int] NOT NULL,

[GoogleBotHits] [int] NOT NULL,

[GoogleBotLoadTime] [int] NOT NULL,

[BingBotHits] [int] NOT NULL,

[BingBotLoadTime] [int] NOT NULL,

[MSNBotHits] [int] NOT NULL,

[MSNBotLoadTime] [int] NOT NULL,

[BotHits] [int] NOT NULL,

[BotLoadTime] [int] NOT NULL,

CONSTRAINT [PK_IISDashboard] PRIMARY KEY CLUSTERED

(

[LogFilename] ASC,

[LogDate] ASC,

[Server] ASC

)

Import the logs

Now that the logs are over on the database server, it's time to import them. I use logparser from Microsoft to do this. You'll want to create a SQL Server Agent job and run it every 10 minutes or so, making sure you don't bump heads with the copy job from the IIS server. Stagger them accordingly. You'll note the script dynamically creates the file name for me - the rest is pretty straight forward. Yes, I have xp_cmdshell enabled - if you don't allow the use of xp_cmdshell, you may have to cook up an alternative way to import the data. You'll also note I clear out the current day and reload it all - that's much easier than trying to figure out what's already been imported from a text file and what has not. Shortly after midnight I reload the entire previous day (the reason I copy two logs each time) - that ensures I have the entire day of data loaded for that log file.

DECLARE @TodaysDate VARCHAR(6)

,@LogFileName VARCHAR(100)

,@LogParserCommand VARCHAR(1000)

,@FileCheckCommand VARCHAR(1000)

,@FileCheck TINYINT

SELECT @TodaysDate = CONVERT(VARCHAR(6),GETDATE(),12)

SET @LogFileName = 'I:\PathTo\IISLogs\u_ex' + @TodaysDate + '.log'

DELETE FROM IISLogs.dbo.LogData

WHERE LogFilename = @LogFileName

SET @FileCheckCommand = 'DIR "' + @LogFileName + '" /B'

EXEC @FileCheck = master.dbo.xp_cmdshell @FileCheckCommand,NO_OUTPUT

IF @FileCheck = 1

PRINT 'File does not exist.'

ELSE

BEGIN

PRINT 'Importing ' + @LogFileName

SET @LogParserCommand = 'C:\"Program Files (x86)"\"Log Parser 2.2"\LogParser "SELECT * INTO LogData FROM '

+ @LogFileName

+ '" -i:W3C -o:SQL -server:DB_SERVER\INSTANCE -database:IISLogs -driver:"SQL Server" -username:<username> -password:<password>'

EXEC master..xp_cmdshell @LogParserCommand

END

-- CLEANUP

EXEC master.dbo.xp_cmdshell 'del I:\PathTo\IISLogs\*.* /F /Q'

Report on the data

Well, the hard part is over. The data is in SQL Server, and now the fun begins - doing something with it. Personally I set up an SSRS report that I receive and review each morning. It takes me a few seconds to identify if there are any performance issues that require investigation. The primary report is below, as is the query. I actually roll up the data each iteration of the scheduled job so the dashboard is quick (versus plowing through tons of data each refresh). You'll note I have two web servers (whited out under Page Hits) load balanced, but this process is very repeatable regardless of how many servers you have. I also make some pieces of data appear to be hyperlinks, but it's really just blue font and underlined so people know they can further dive into the data. The call data in the report comes from our I3 phone system - I have removed those from the query to avoid confusion. If you wish to have a copy of the RDL, feel free to contact me.

DECLARE @Today DATE = GETDATE()

DELETE FROM IISLogs.dbo.IISDashboard

WHERE LogDate = @Today

INSERT INTO IISLogs.dbo.IISDashboard

(

LogFilename

,LogDate

,Server

,Records

,CallsAbandoned

,CallsAnswered

,ID30Plus

,ID10Plus

,ID2Minus

,ID200Errors

,ID500Errors

,GoogleBotHits

,GoogleBotLoadTime

,BingBotHits

,BingBotLoadTime

,MSNBotHits

,MSNBotLoadTime

,BotHits

,BotLoadTime

)

SELECT LEFT(LogFilename,16) AS LogFileName

,CONVERT(DATE,DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),

[Date] + CAST(CONVERT(TIME,[Time]) AS DATETIME))) AS 'LogDate'

,SUBSTRING(LogFilename,CHARINDEX('SERVER_PREFIX',LogFilename),9) AS 'Server'

,COUNT(*) AS 'Records'

,SUM(CASE WHEN timeTaken > 30000 THEN 1

ELSE 0

END) AS '30Plus'

,SUM(CASE WHEN timeTaken > 10000 THEN 1

ELSE 0

END) AS '10Plus'

,SUM(CASE WHEN timeTaken < 2000 THEN 1

ELSE 0

END) AS '2Minus'

,SUM(CASE scStatus

WHEN 200 THEN 1

ELSE 0

END) AS '200Errors'

,SUM(CASE scStatus

WHEN 500 THEN 1

ELSE 0

END) AS '500Errors'

,SUM(CASE WHEN csUserAgent LIKE '%googlebot%' THEN 1

ELSE 0

END) AS 'GoogleBotHits'

,AVG(CASE WHEN csUserAgent LIKE '%googlebot%' THEN timeTaken

ELSE 0

END) AS 'GoogleBotLoadTime'

,SUM(CASE WHEN csUserAgent LIKE '%bingbot%' THEN 1

ELSE 0

END) AS 'BingBotHits'

,AVG(CASE WHEN csUserAgent LIKE '%bingbot%' THEN timeTaken

ELSE 0

END) AS 'BingBotLoadTime'

,SUM(CASE WHEN csUserAgent LIKE '%msnbot%' THEN 1

ELSE 0

END) AS 'MSNBotHits'

,AVG(CASE WHEN csUserAgent LIKE '%msnbot%' THEN timeTaken

ELSE 0

END) AS 'MSNBotLoadTime'

,SUM(CASE WHEN csUserAgent LIKE '%bot%' THEN 1

ELSE 0

END) AS 'BotHits'

,AVG(CASE WHEN csUserAgent LIKE '%bot%' THEN timeTaken

ELSE 0

END) AS 'BotLoadTime'

FROM IISLogs.dbo.LogData

WHERE CONVERT(DATE,DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),

[Date] + CAST(CONVERT(TIME,[Time]) AS DATETIME))) = @Today

AND csUriStem LIKE '%.aspx%'

AND DATEPART(dw, CONVERT(DATE,DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),

[Date] + CAST(CONVERT(TIME,[Time]) AS DATETIME)))) BETWEEN 2 AND 6

GROUP BY LEFT(LogFilename,16)

,CONVERT(DATE,DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),

[Date] + CAST(CONVERT(TIME,[Time]) AS DATETIME)))

,SUBSTRING(LogFilename,CHARINDEX('SERVER_PREFIX',LogFilename),9)

Detailed Statistics for SSRS Reports

2014-01-14T11:57:00.000-08:00

I've had this report for years but until today never really had a conversation about the statistical value you can find in the Reporting Services database. When a user contacts you and claims that a report is "slow", where do you start? Do you fire up a profiler trace and ask them to run it again? Do you open the report and examine the query's execution plan for optimization opportunities? Perhaps you weren't aware, but there is some really good statistical information in the Reporting Services database. I put together the following report to assist me with my SSRS tuning investigation and efforts. You can get the RDL for the report here.

(Note #1: I pulled the margins in to hide some account and report path information - better to be safe than sorry!)

The query/dataset to populate the report:

SELECT TOP 1000
UserName
,Name
,Path
,[Status]
,Format
,TimeStart
,TimeEnd
,ByteCount
,ByteCount / 1024 AS KB
,ByteCount / 1024 / 1024 AS MB
,[RowCount]
,TimeDataRetrieval
,TimeProcessing
,TimeRendering
,[Parameters]
FROM [ReportServer$SQL01].dbo.ExecutionLog AS ex
,[ReportServer$SQL01].dbo.Catalog AS cat
WHERE ex.ReportID = cat.ItemID
AND Name NOT LIKE '_My Reports%'
AND ( UserName = @UserName
OR @UserName = '--- ALL USERS ---' )
AND [Status] IN (@ReportStatus)
AND ( [Name] = @ReportName
OR @ReportName = '--- ALL REPORTS ---' )
ORDER BY TimeStart DESC

SSRS Subscriptions and their SQL Server Agent job names

2014-01-14T10:45:00.001-08:00

For those that run SQL Server Reporting Services and have a high volume of report subscriptions, you probably know the pain of trying to figure out which jobs in SQL Server Agent kick off which subscriptions in Reporting Services. This is a query that you can put into a report of its own so you can pull it up and reference in the event you want to kick off a job (and subscription) manually:

SELECT Schedule.ScheduleID AS JobName
,[Catalog].Name AS ReportName
,Subscriptions.Description AS Recipients
,[Catalog].Path AS ReportPath
,StartDate
,Schedule.LastRunTime
FROM [ReportServer$SQL01].dbo.ReportSchedule
INNER JOIN [ReportServer$SQL01].dbo.Schedule ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN [ReportServer$SQL01].dbo.Subscriptions ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN [ReportServer$SQL01].dbo.[Catalog] ON ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID

Importing SQLIO Data

2014-01-10T14:27:00.000-08:00

In a previous post I shared with you how you might use an SSRS report to analyze your SQLIO results. What I didn't share was how you might get that data into SQL Server to begin with, so I will share how I go about that.

[Update: You can get the RDL here]

The first thing you'll want to do is set up your SQLIO database, along with the related objects that you will need to import the results from the text file(s). I have provided the necessary scripts below. Upon creating those objects, there are a series of simple scripts you have to run to import the data. Note: I did not write these scripts myself, but I did alter them a bit to include some information I felt necessary to do proper analysis:

USE SQLIO
GO

--STEP 1: import the data by changing to the appropriate txt file below.
TRUNCATE TABLE dbo.SQLIO_Import_Step1 ;

BULK INSERT dbo.SQLIO_Import_Step1
FROM 'C:\Program Files (x86)\SQLIO\SQLIO_Results\results-timestamp.txt' ;

TRUNCATE TABLE dbo.SQLIO_Import ;

INSERT INTO dbo.SQLIO_Import
( ResultText )
SELECT ISNULL(ResultText, '')
FROM dbo.SQLIO_Import_Step1 ;

--STEP 2: ETL the data into the live table for reporting purposes.
EXECUTE [dbo].[USP_Import_SQLIO_TestPass] '[DATABASE INSTANCE]', 'D:', 2, 15000, 'RAID 10', '2014/1/10', 'EMC CX3 Model 20', '', 64, 'NTFS', '65536'

Tables needed:
CREATE TABLE [dbo].[SQLIO_Import]
(
[RowID] [int] IDENTITY(1,1) NOT NULL
,[ParameterRowID] [int] NULL
,[ResultText] [varchar](MAX) NULL
,CONSTRAINT [PK_SQLIO_Import] PRIMARY KEY CLUSTERED ( [RowID] ASC )
)

CREATE TABLE [dbo].[SQLIO_Import_Step1](
[ResultText] [varchar](MAX) NULL
)

CREATE TABLE [dbo].[SQLIO_TestPass]
(
[TestPassID] [int] IDENTITY(1,1) NOT NULL
,[ServerName] [nvarchar](50) NOT NULL
,[LogicalDrive] [varchar](10) NOT NULL
,[DriveQty] [int] NOT NULL
,[DriveRPM] [int] NOT NULL
,[DriveRaidLevel] [nvarchar](10) NOT NULL
,[TestDate] [datetime] NOT NULL
,[SANmodel] [nvarchar](50) NOT NULL
,[SANfirmware] [nvarchar](50) NULL
,[PartitionOffset] [int] NULL
,[Filesystem] [nvarchar](50) NULL
,[FSClusterSizeBytes] [int] NULL
,[SQLIO_Version] [nvarchar](20) NULL
,[Threads] [int] NULL
,[ReadOrWrite] [nchar](1) NULL
,[DurationSeconds] [int] NULL
,[SectorSizeKB] [int] NULL
,[IOpattern] [nvarchar](50) NULL
,[IOsOutstanding] [int] NULL
,[Buffering] [nvarchar](50) NULL
,[FileSizeMB] [int] NULL
,[IOs_Sec] [decimal](18,0) NULL
,[MBs_Sec] [decimal](18,0) NULL
,[LatencyMS_Min] [int] NULL
,[LatencyMS_Avg] [int] NULL
,[LatencyMS_Max] [int] NULL
,CONSTRAINT [PK_SQLIO_TestPass] PRIMARY KEY CLUSTERED ( [TestPassID] ASC )
)

Stored Procedure:
CREATE PROCEDURE [dbo].[USP_Import_SQLIO_TestPass]
@ServerName NVARCHAR(50)
,@LogicalDrive VARCHAR(10)
,@DriveQty INT
,@DriveRPM INT
,@DriveRaidLevel NVARCHAR(10)
,@TestDate DATETIME
,@SANmodel NVARCHAR(50)
,@SANfirmware NVARCHAR(50)
,@PartitionOffset INT
,@Filesystem NVARCHAR(50)
,@FSClusterSizeBytes INT
AS
SET nocount OFF

IF @TestDate IS NULL
SET @TestDate = GETDATE()

/* Add a blank record to the end so the last test result is captured */
INSERT INTO dbo.SQLIO_Import
( ParameterRowID,ResultText )
VALUES ( 0,'' );

/* Update the ParameterRowID field for easier querying */
UPDATE dbo.sqlio_import
SET parameterrowid = ( SELECT TOP 1
rowid
FROM dbo.sqlio_import parm
WHERE parm.resulttext LIKE '%\%'
AND parm.rowid <= upd.rowid
ORDER BY rowid DESC
)
FROM dbo.sqlio_import upd

/* Add new SQLIO_TestPass records from SQLIO_Import */
INSERT INTO dbo.sqlio_testpass
(servername
,logicaldrive
,driveqty
,driverpm
,driveraidlevel
,testdate
,sanmodel
,sanfirmware
,partitionoffset
,filesystem
,fsclustersizebytes
,sqlio_version
,threads
,readorwrite
,durationseconds
,sectorsizekb
,iopattern
,iosoutstanding
,buffering
,filesizemb
,ios_sec
,mbs_sec
,latencyms_min
,latencyms_avg
,latencyms_max
)
SELECT @ServerName
,@LogicalDrive
,@DriveQty
,@DriveRPM
,@DriveRaidLevel
,@TestDate
,@SANmodel
,@SANfirmware
,@PartitionOffset
,@Filesystem
,@FSClusterSizeBytes
,( SELECT REPLACE(resulttext,'sqlio ','')
FROM dbo.sqlio_import impsqlio_version
  WHERE   imp.rowid + 1 = impsqlio_version.rowid
) AS sqlio_version
,( SELECT LEFT(resulttext,( CHARINDEX(' threads',resulttext) ))
  FROM dbo.sqlio_import impthreads
  WHERE   imp.rowid + 3 = impthreads.rowid
) AS threads
,( SELECT UPPER(SUBSTRING(resulttext,( CHARINDEX('threads ',resulttext) ) + 8,1))
  FROM dbo.sqlio_import impreadorwrite
  WHERE   imp.rowid + 3 = impreadorwrite.rowid
) AS readorwrite
,( SELECT SUBSTRING(resulttext,( CHARINDEX(' for',resulttext) ) + 4,
( CHARINDEX(' secs ',resulttext) ) - ( CHARINDEX(' for',resulttext) ) - 4)
  FROM dbo.sqlio_import impdurationseconds
  WHERE   imp.rowid + 3 = impdurationseconds.rowid
) AS durationseconds
,( SELECT SUBSTRING(resulttext,7,( CHARINDEX('KB',resulttext) ) - 7)
  FROM dbo.sqlio_import impsectorsizekb
  WHERE   imp.rowid + 4 = impsectorsizekb.rowid
) AS sectorsizekb
,( SELECT SUBSTRING(resulttext,( CHARINDEX('KB ',resulttext) ) + 3,
( CHARINDEX(' IOs',resulttext) ) - ( CHARINDEX('KB ',resulttext) ) - 3)
  FROM dbo.sqlio_import impiopattern
  WHERE   imp.rowid + 4 = impiopattern.rowid
) AS iopattern
,( SELECT    SUBSTRING(resulttext,( CHARINDEX('with ',resulttext) ) + 5,
( CHARINDEX(' outstanding',resulttext) ) - ( CHARINDEX('with ',resulttext) )
- 5)
  FROM dbo.sqlio_import impiosoutstanding
  WHERE   imp.rowid + 5 = impiosoutstanding.rowid
) AS iosoutstanding
,( SELECT REPLACE(CAST(resulttext AS NVARCHAR(50)),'buffering set to ','')
  FROM dbo.sqlio_import impbuffering
  WHERE   imp.rowid + 6 = impbuffering.rowid
) AS buffering
,( SELECT SUBSTRING(resulttext,( CHARINDEX('size: ',resulttext) ) + 6,
( CHARINDEX(' for ',resulttext) ) - ( CHARINDEX('size: ',resulttext) ) - 9)
  FROM dbo.sqlio_import impfilesizemb
WHERE imp.rowid + 7 = impfilesizemb.rowid
) AS filesizemb
,( SELECT RIGHT(resulttext,( LEN(resulttext) - 10 ))
  FROM dbo.sqlio_import impios_sec
  WHERE   imp.rowid + 11 = impios_sec.rowid
) AS ios_sec
,( SELECT RIGHT(resulttext,( LEN(resulttext) - 10 ))
  FROM dbo.sqlio_import impmbs_sec
  WHERE   imp.rowid + 12 = impmbs_sec.rowid
) AS mbs_sec
,( SELECT RIGHT(resulttext,( LEN(resulttext) - 17 ))
  FROM dbo.sqlio_import implatencyms_min
  WHERE   imp.rowid + 14 = implatencyms_min.rowid
) AS latencyms_min
,( SELECT RIGHT(resulttext,( LEN(resulttext) - 17 ))
  FROM    dbo.sqlio_import implatencyms_avg
  WHERE   imp.rowid + 15 = implatencyms_avg.rowid
) AS latencyms_avg
,( SELECT RIGHT(resulttext,( LEN(resulttext) - 17 ))
  FROM dbo.sqlio_import implatencyms_max
  WHERE   imp.rowid + 16 = implatencyms_max.rowid
) AS latencyms_max
FROM dbo.sqlio_import imp
INNER JOIN dbo.sqlio_import impfulltest ON imp.rowid + 20 = impfulltest.rowid
AND impfulltest.resulttext = ''
WHERE imp.rowid = imp.parameterrowid
ORDER BY imp.parameterrowid

/* Empty out the ETL table */
DELETE dbo.sqlio_import