Call a Powershell PS1 File from a Stored Procedure

Last week I was working on a project to help us better identify over 600 databases we have in production.  The final solution wound up including the following components:

  • Store various contact people and internal information on the databases themselves as Extended Properties.  We are tagging all of our production databases with a business owner, system owner, and development lead, as well as information describing the application(s) that the database supports.  While extended properties are a rarely used functionality within SQL Server, they are a good fit for our purposes:
    1. Extended properties travel with the database, so when we restore production databases to our development and testing SQL instances (either weekly or nightly in most cases) the non-production database copies will automatically have the same extended properties.  This is beneficial for us because we only have to maintain the content on the production databases and the information will automatically “trickle down” to the non-production environments with our existing restore processes.
    2. Extended properties are simple, expandable and native to SQL Server.  Extended Properties are implemented as simple name/value pairs so they are extremely flexible.  We will easily be able to alter the types and number of tags we want to assign to the databases without extensive development efforts.  You can also view the DB extended properties by right clicking on any database in SSMS which is a nice to have for DBAs and SysAdmin troubleshooting issues either on the machine itself or with remote access via SSMS.
    3. Extended properties are backwards compatible to SQL 2005.  We still have a handful of SQL 2005 instances and a few dozen SQL 2008 instances in our environment, so we wanted a mechanism that would work the same for everything we are running today.
  • Establish a centralized dataset of the database properties to support a Reporting Services report that is current and usable by application support and other less technical or privileged teams (no SSMS access is most cases).  We often make database server performance information available to non-DBA team members with custom SSRS reports that render various monitoring data (session activity, wait stats, etc).  A report that allows users to review the internal contact people and applications associated with specific databases is very useful for our application support team to determine who to contact if there are issues or questions.

In the initial phase of this effort, we were provided a spreadsheet that was exported from our disaster recovery planning system with the application name, system owner, business owner, and lead developer.  This information didn’t include a direct link to individual databases, so we went through and updated the spreadsheet with servernames (instances) and DB names.  We then developed a powershell script to go through the spreadsheet, connect to the various remote servers and assign DB level extended properties to the database in question.  This was great for our initial effort, but as you might imagine, there were several applications that we couldn’t map directly to a database and we needed to work with various business users to identify the appropriate app/db mappings.  We got the initial dataset done and propagated out to the various databases, but we needed a simple mechanism to allow users to assign an app/app owners set to a database easily from a web based front end.

In the end, we decided to provide a stored procedure on our central monitoring database instance that would take application/owner information as parameters and add the database level extended properties on the remote servers automatically.  Since we already had a decent set of working code to do this in powershell (from the initial import), we decided to implement the procedure by having it call a powershell ps1 script with parameters to shell out from the SQL stored procedure by using sp_executesql.

The powershell file is attached at the end of this post for reference, but here is the syntax for calling the PS1 script file with arguments(parameters) from within a SQL stored procedure.

create procedure [dbo].[set_DatabaseProperties]
(@ServerName varchar(100) = NULL,
 @DatabaseName varchar(100) = NULL,
 @App varchar(500) = 'UNKNOWN',
 @AppDesc varchar(500) = 'UNKNOWN',
 @ApprovalUser varchar(500) = 'UNKNOWN',
 @BusinessFunction varchar(500) = 'UNKNOWN',
 @BusinessOwner varchar(500) = 'UNKNOWN',
 @Dept varchar(500) = 'UNKNOWN',
 @LeadDeveloper varchar(500) = 'UNKNOWN',
 @ManagedBy varchar(500) = 'UNKNOWN',
 @SystemOwner varchar(500) = 'UNKNOWN',
 @Type varchar(500) = 'UNKNOWN'
 )
/************************************************************************************
**
** 07/25/13 MRaftree Initial Creation
**
*/
AS
BEGIN

 DECLARE @CmdExecStr varchar(5000)
if (@ServerName is not null and @DatabaseName is not null and @App <> 'UNKNOWN')
 BEGIN

-- build a command line string to execute the PS1 file with each argument in double quotes and separated by a space  Also, check the location of the powershell.exe file
--on your database server itself and make sure the path below is right
 Select @CmdExecStr = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -File "C:\Powershell\SetDBProperties.ps1" "' + isnull(@ServerName,'Unknown') +'" "' + isnull(@DatabaseName,'Unknown') +'" "' + @App +'" "' + @AppDesc +'" "' + @ApprovalUser +'" "' + @BusinessFunction+'" "' + @BusinessOwner +'" "' + ISNULL(@Dept,'Unknown') +'" "' + @LeadDeveloper +'" "' + @ManagedBy +'" "' + @SystemOwner +'" "' + ISNULL(@Type,'Unknown') +'" '

 print @CmdExecStr
create table #returncode (value varchar(1000))
insert into #returncode exec master..xp_cmdshell @CMDExecStr
select * from #ReturnCode
 END
END

Here is the ps1 file itself for reference:  SetDBProperties.ps1

Linked Servers and Table Valued UDFs

I recently had an issue where a user wanted to call a user defined function on a remote machine via a linked server call.  Sounds logical right?  The problem is that there is an issue with SQL Server linked server that prevents User Defined Functions from being called via linked servers with the standard 4 part naming convention.

For example,  on SQLServerA I have a linked server to SQLServerB and there is a database on SQLServerB called Inventory that has a table valued user defined function called dbo.GetCurrentInventoryLevel(@productcategory int)

On SQLServerB I can just call the UDF normallyn from the Inventory database with something like:

Select * from dbo.GetCurrentInventoryLevel(3) 

The code above returns a resultset of current inventory levels for a series of products within a certain product category.  The problem is when you try to call that same function from SQLServerA via a linked server call.  You would assume this would work fine from SQLServerA (with a linked server in place to SQLServerB):

Select * from SQLServerB.Inventory.dbo.GetCurrentInventoryLevel(3) 

The problem is that this throws the error:

Msg 4122, Level 16, State 1, Line 1
Remote table-valued function calls are not allowed.

As it turns out, this is a known issue with SQL Server and there are no plans to resolve it in the next (2014) version.  There is an MS Connect page open for this issue that indicates that this won’t be fixed and presents some workarounds for the current SQL Server versions.

http://connect.microsoft.com/SQLServer/feedback/details/276758/remote-table-valued-function-calls-are-not-allowed

Basically, you can still call the Table Valued UDF over the linked server, but you can’t do it with the 4 part naming syntax.  You have to use OPENQUERY like this:

select * from OPENQUERY([SQLServerB], ‘Select * from Inventory.dbo.GetCurrentInventoryLevel(3) ‘) as CurrentInventoryTable

Hope this saves folks some time.

Wait Stats Report – Visualize Raw WaitStats Data

This post should close the loop on my last few Wait Stats monitoring posts.  In order to more easily sift through and make sense of raw wait stat information, I put together an SSRS report that connects to the DBAMonitoring database we’ve been populating and provides a clickable, visual display of wait stats per server.

Installing and configuring an SSRS setup is beyond the scope of this post, so I am going to assume that:

  1. You have access to a basic SQL Server 2008 SSRS instance that is running and you can get to via http://localhost/reports (or whatever your SSRS base URL is).
  2. You have configured the tables, procedures, powershell scripts and SQL Agent jobs from my previous posts on wait stats collection
  3. Download these two files
    • Wait_Stats.rdl (rename to .rdl)
    • rpt_FetchWaitStatsDiff.sql (rename to .sql) – this is the code for a stored procedure in the DBAMonitoring database that is used by the report when rendering wait stats across collection intervals.

In this post, I plan to cover the following:

  1. Deploying the report file (wait_stats.rdl) to your SSRS instance and stored procedure code to your
  2. Configuring a data source for the report
  3. Reviewing the queries that feed into the various visual components of the report

Assuming your localhost database has a DBAMonitoring database with all the tables and procedures from this and previous posts, you can simply upload the .rdl report file to your running SSRS instance from the web interface. Go to the SSRS url (e.g. http://localhost/Reports/Pages/Folder.aspx) and then click the “Upload File” button in the toolbar.  Select the rdl file from your local hard drive and click OK:upload2

Once the report is uploaded you should be able to run it from the SSRS web portal by simply clicking it.  if everything works properly, you should be able to open up the report right away and see a list of servers you have been collecting wait stat data from:

waitstatsserverlist

You can click on one of the sql instances (in my example, localhost and mraftree-bos-v are the same machine, but I can collect them separately to give the illusion of multi-server centralized management).  Once you click a sql server instance in the list, the report will refresh with the wait stat content:

fullwiatstats

Sections of the report:

1) Top Section – Wait Stats High Level Stacked Bar Chart – shows the interval specific wait stats on the 30 minute collection interval.  By default, this view shows the last 3 hours of collections.  You can easily see in this chart if there is a specific wait category that has spiked in a specific interval (indicating some issue with a poor query plan, paralellized workload, etc.)

2) Wait Stats Detail – shows the numeric representation of the wait stats for the last 30 minute interval.  This is a good place to look for the highest wait types and potentially drill in to determine a lower level reason for waits

3) Wait Group History Matrix – a numeric representation of the top level stacked bar chart.  It shows the specific wait category numbers by interval

4) Wait Type History – similar to the Wait Group Category Matrix, but shows the lower level wait type level stats.

Let me know if you have any issues deploying this report and the supporting code and I’ll be glad to help.

In my next post, I’ll be building on this central monitoring paradigm to capture and report on active session activity (running and inactive SPIDs and their associated SQL calls).  Happy Monitoring!

Wait Stats – Making Sense of Raw Data Collection

A lot of the work that went into this wait stats collection and reporting solution was inspired by the functionality of the MS SQL Server PerfDashboard reports. You can download the latest version here.  The PerfDashboard reports are great.  They provide good diagnostic information, they are accessible from within management studio and they look good.  The major limitation of these reports is that they do no offer a view into wait stats history at regular intervals.  At any point in time when you look at the perfdashboard reports, for wait stats you are just seeing the cumulative wait stats since the SQL Server instance started.  In most cases a SQL instance stays up for days/weeks/years and it becomes impossible to discern recent changes in wait stat activity due to the long time frame that the DMV’s wait stats have been accumulating.  For example, you may be having an issue in the last 6 hours with queries running inefficiently due to parallelism, but you may not be able to see a lot of parallelism waits (CX_PACKET wait type) relative to other types of waits that have been accumulating for months.

This is what the “Historical Waits” perfdashboard report shows:

Historical Waits (PerfDashboard)

It looks nice, but if the above wait stat information is based on a server running for the last 180 days, it’s not much use in terms of debugging a recent issue with SQL Server performance.  You may have had a serious CPU issue eariler in the day, but even a significant event for 45 minutes wouldn’t skew the “CPU” wait type’s overall value or percentage position in the list above.  It just wouldn’t have enough impact to “show through” against the larger time frame that all the stats are collected within cumulatively.

The collection approach I covered in my last post is the first step toward discerning recent wait statistics without restarting the instance.  The collection approach is admittedly simple (just collect the accumulated wait stats per server every 30 minutes).  But once you have a reasonable set of snapshots (a few hours’ worth), you can run calculations across collections to see the “real” wait stats by wait type on a 30 minute interval basis.  Before I get into the logic required to calculate the diffs across collections, take a look at the SSRS report I put together based on DB_WaitStats table data.

waitstatssrs

Notice that information is displayed in 30 minute increments (since the job is collecting a snapshot of wait stat data every 30 minutes from SQL Agent).  The top section shows a stacked bar chart with each 30 minute collection’s wait category data (so you can easily see if any one wait category – color in the graph – is growing or shrinking significantly).

The tabular matrix below it is showing the same data as the graph, but in numerical format with each column representing a different collection interval.  The same query (within a stored procedure) is used for both the stacked bar chart and the numerical matrix.  The high level control of flow in the procedure is depicted in the image below:

waitstatsproc

  1. Before the loop, get the latest increment_id for the server in question (so we can use it as a starting point to go back in time across collection intervals)
  2. Within the loop – compare the cumulative collected wait stats between the current interval_id and previous interval_id (to determine the “real” wait time between collections).  For each iteration, insert rows into a temp table with the “real” wait times for the server/interval combination.
  3. Return the results from the temp table.

The procedure could probably be altered to do a recursive CTE to accomplish the same thing, but looping through increment_id’s for a particular server seemed more logically consumable and understandable.  You can download the code for the procedure here: rpt_FetchWaitStatsDiff.sql  (rename to .sql from the .key file extension).  I also leverage a great table create (and populate) script from Jason Strate (http://www.jasonstrate.com/2010/01/wait-stat-categories/) to generate a table called WaitTypeCategory that I use to join wait_types together in order to provide a higher level aggregate of wait types.  That script can be downloaded here: WaitTypeCategory.sql

So now we have the collection powershell script and the sql agent job to force the collection of SQL Instance wait stats every 30 minutes to the central DBAMonitoring database.  In addition to that, we have the rpt_FetchWaitStatsDiff procedure that will calculate the intra-collection “real” wait stats and return them for the last 6 intervals.

In my next post, I’ll go through the SSRS report I showed above in detail to show you how to visualize this data and enable yourself to quickly diagnose high level wait-oriented issues with your SQL instances.

Baby Steps 2 – Central Collection of WaitStats

The next step in the process will be to build upon my earlier post (“Baby Steps”) by expanding the components in the following ways:

  1. Add a new table called DB_WaitStats to the DBAMonitoring database (structure detailed below)
  2. Build on the existing powershell script to loop through our SQL instances, run a WaitStats oriented DMV query and save the results to the DBAMonitoring..DB_WaitStats table.
  3. Schedule the powershell script to run every 30 minutes in SQL Server Agent.

This post will discuss the collection and storage mechanisms of the solution and my next post will cover an SSRS report that allows you to easily visualize this data (per server) to assist in troubleshooting and debugging.

DB_WaitStats Table to Persist Wait Stat Collections

The following is a table definition to store a fairly standard set of Wait Stat information from DMV calls:

CREATE TABLE [dbo].[DB_WaitStats](
[wait_type] [nvarchar](60) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL,
[ServerName] [varchar](128) NOT NULL,
[capture_time] [datetime] NOT NULL,
[increment_id] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [CI_WaitStats_ServerName] ON [dbo].[DB_WaitStats]
(
[capture_time] ASC,
[ServerName] ASC,
[increment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
So it is just a basic table with a clustered index on capturetime/servername/increment_id (more on this later).

Also create a simple table to hold the wait types that we want to ignore (there may be cases where certain wait stats can be disregarded when reviewing the data later):
CREATE TABLE [dbo].[DB_ignorewaits](
[wait_type] [varchar](250) NULL
) ON [PRIMARY]

With that in place, it is time to revisit the powershell script from my previous post.  Two key areas of the script are enhanced in this iteration including a smarter loop of the servers to collect from as well as the logic to execute the DMV query on the remote server and save the results to the local DBAMonitoring..DB_WaitStats table.

Note On The Cumulative nature of WaitStats DMVs

The queries we will be using to collect wait stats use the sys.dm_os_wait_stats DMV.  This DMV maintains cumulative wait stats by type since the last time the  sql instance was started.  In order to more effective (and easily) determine the actual wait stats between collection intervals (every 30 minutes in our case), the DB_WaitStats table has a increment_id int column that stores an integer value that is one greater than the last collection’s interval_id for the same server.  In a later post I will show you how to use this field to perform some comparisons across collections to get the real “wait stats for the last 30 minutes”

PowerShellCollectWaitStats.ps1 Script

The code below is for setting up the foreach loop to got through each SQL instance we want to collect from, connecting to it and running the DMV query to gather information.  It has a few additional elements than my original script;

  1. The $query variable that selects from DBAMonitoring..DB_ServerMain has been updated with a left join to DB_WaitStats so that we can pull back each SQL instance name AND the highest increment_id value we have for previous wait stat collections on a given SQL instance.
  2. I also declare a new SQL Server connection variable ($SourceConnection) that will be used within the loop to connect directly to each monitored sql instance and execute the wait stats DMV query
#—- BEGIN CODE —
try
{# create a connection string to connect to the DBAMonitoring Instance and Database
$ServerAConnectionString = “Data Source=localhost;Initial Catalog=DBAMonitoring;Integrated Security=SSPI;”
$ServerAConnection = new-object system.data.SqlClient.SqlConnection($ServerAConnectionString);#Create a Dataset to hold the DataTable from the DB_ServerMain table
# we will loop through this recordset next
$dataSet = new-object “System.Data.DataSet” “DBServers”$query = “SET NOCOUNT ON;”
$query = $query + “Select a.DBServerID, a.ServerName, max(isnull(b.increment_id,0))+1 as increment_id ”
$query = $query + “from DB_ServerMain a ”
$query = $query + “left outer join DB_WaitStats b ”
$query = $query + “on a.ServerName = b.ServerName ”
$query = $query + “group by a.DBServerID,a.ServerName “#Create a DataAdapter which we will use to populate a data set (like a recordset)
$dataAdapter = new-object “System.Data.SqlClient.SqlDataAdapter” ($query, $ServerAConnection)
$dataAdapter.Fill($dataSet) | Out-Null#create a new connection object we will use to connect dynamically to each sqlinstance
$sourceConnection = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString)#the “$a” is one row from the resultset, and you can reference specific fields with $a.fieldname from within the loop
foreach($a in $dataSet.Tables[0].Rows)
{

Within the foreach loop (below), the updated script is performing a few new operations.  Each time through the loop (once for each SQL Instance) the code will:

  1. Set Variables
    • Set variables for source/target connection strings (to connect to the remote sql instance AND the central monitoring instance with the DBAMonitoring database)
    • Set the $sql variable which contains the select statement that will be executed against the remote instance (in our case against the sys.dm_os_wait_stats DMV)
  2. Connect to the remote instance and execute the DMV query
  3. Use the BulkCopy object to copy the results of the query from the remote sql instance to the central DBAMonitoring database

#the “$a” is one row from the resultset, and you can reference specific fields with $a.fieldname from within the loop
foreach($a in $dataSet.Tables[0].Rows)
{

# This is the area of code where “per server” queries can be executed and then saved back to the central database
write-host “DBServer: ” $a.ServerName ;
# open a connection for the server/database
$SourceConnectionString = “Data Source=”+$a.Servername+”;Integrated Security=SSPI;”
$DestinationConnectionString = “Data Source=localhost;Initial Catalog=DBAMonitoring;Integrated Security=True”

#the name of the target table we are going to insert into ni the DBAMonitoring database
$tableName = “DB_WaitStats”

#the wait stats query we will run aganist each sql instance in DBAMONITORING..DB_ServerMain
$sql = “DECLARE @DT DATETIME ; ”
$sql = $sql + “SET @DT = GETDATE() ; ”
$sql = $sql + “SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], ”
$sql = $sql + ” [signal_wait_time_ms], ‘” + $a.ServerName+ “‘ AS ServerName, @DT as capture_time, ” + $a.increment_id + ” as increment_id ”
$sql = $sql + “FROM sys.dm_os_wait_stats; ”
#write-host $sql
$sourceConnection.ConnectionString = $SourceConnectionString
$sourceConnection.open()
$commandSourceData = New-Object system.Data.SqlClient.SqlCommand($sql,$sourceConnection)
$commandSourceData .CommandTimeout = ‘300’

$reader = $commandSourceData.ExecuteReader()

$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $DestinationConnectionString
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.BatchSize = 5000
$bulkCopy.BulkCopyTimeout = 0
$bulkCopy.WriteToServer($reader)

$sourceConnection.close()

}

You can download the whole script powershellcollectwaitstats .ps1  (rename to .ps1 as the file extension)

Once you have the script in the PowerShell ISE, you can run it and should see a simple output in the console pane of each sql instance that was collected from:

waitstsrun

Confirm it is working properly by performing a “Select * from DB_WaitStats” in your DBAMonitoring database.  You should have a few hundred rows per SQL instance, per collection.

Once the powershell script is working from the ISE, create a SQL Agent Job to have it run every 30 minutes.  You can use this code to create the job on your SQL Server instance (rename to .sql WaitStatsSqlAgentJob.txt)

The job is a simple 2 step job:

waitstatsjobsteops

The first step deletes aging records out of the DBAMonitoring..DB_WaitStats table so it doesn’t grow uncontrollably.  The script is configured to delete collections older that 3 days, but you can easily alter this to fit your needs.

The second step calls the ps1 powershell script file using the Operating System (CmdExec) job step type.  You basically call the powershell environment and give it the path to your ps1 file as shown below:

waitstatsjobsteps2

The job is scheduled to run every 30 minutes to collect new snapshot of remote server wait stats and it will delete aging collections.  Run it manually a couple of times from sql agent to make sure it is executing properly

Meanwhile…. back in DBAMonitoring..DB_WaitStats

Now go back to your central instance and query the DB_WaitStats table.  You should see a few hundred rows per instance/per collection.  Don’t worry that in most cases, the majority of the wait types have 0 values.  For our purposes we collect everything so we can compare across collections to determine what is happening with all wait stats.  The data that is contained in the table is very raw (just wait type, the cumulative wait time and a server and time stamp).  In my next post I’ll cover a stored procedure that can refactor this raw data into the actual wait times by wait types across collection intervals (so you can easily see the biggest wait type(s) for the last 30 minutes or major changes in the wait profile over the course of a few hours).  I’ll also cover a basic SSRS report that can sit on top of the DB_waitStats table to provide a more easily interpreted view of the information.