Posts Tagged ‘SCA’

I recently had a requirement to start SCA automatically on remote machines. This was needed for our stress test where we normally have a bunch of servers taking part in the test with different roles: MSSQL Server, IIS Server, File Server, etc.

Now SCA, version 5.7.2 onwards, comes with a console app, SCARunner, that allows you to run SCA from command line. That is fine if you are starting ScA from a batch file interactively, but still doesn’t solve the issue of running it remotely. Luckily PowerShell with it’s remoting and WMI capabilities is geared for just these kind of task, and helped me solve this problem.

In this blog, I’ll show you one way of accomplishing this task.

First Step

The first step is to install SCA on a remote server and then register the server within SCA. I choose the use the default hostname as the registration name since it makes it easier to use it within the PowerShell script. In the image below, my machine name is SJOSHI


Second Step

The next step is to test the SCARunner on the remote machine. To do that, you need to open a DOS/Command window in SCA installation folder which is C:\Program Files (x86)\Intergraph\SCA

To start SCARunner you need to specify these options:

-s  – The registration name. So in my case it would be SJOSHI

-f  – The folder where you want to save the files. The scan xml and perfmon .blg file

So for example, if I needed to start SCA on my machine, I would do:

SCRunner –s SJOSHI –f D:\Temp\Low


When invoked, SCARunner first runs a scan of the registered server and saves the scan xml. Then it creates a perfmon collector with the name of the machine and starts it.

To stop this collector , I need to do:

SCARunner –stop

And this will stop and remove the collector from perfmon.

Third Step

To be able to invoke this from PowerShell remotely, I need to first enable remoting on the server. This is done by running the command, Enable-PSRemoting –Force on the server from a PowerShell admin prompt.



Fourth Step

So at this stage we have the server configured (SCA & PowerShell) and we know the options to run SCARunner. The remaining part is to work out the PowerShell script, so let’s tackle that now.

PowerShell with its WMI Cmdlets, allows you to invoke methods against WMI classes. In our case, we need to start a process, SCARunner, so the Win32_Process with it’s Create method looks like an ideal candidate.

To invoke a WMI method, we can use the Invoke-CimMethod Cmdlet which takes the class name, method name and an arguments dictionary. So let’s see how you can use it.

Say I want to open a file, test.txt in my D:\Temp\Low folder using this class. I can do this in command line by typing notepad D:\Temp\Low\Test.txt


And the file does open up. To do the same in PowerShell, you need to type:

Invoke-CimMethod –ClassName Win32_Process –MethodName Create –Arguments @{CommandLine=“notepad D:\Temp\Low\Test.txt”}

And notepad does open up:


What’s with the –Arguments field ? Well the Get-Help on Invoke-CimMethod shows it to be a dictionary (key-value type).


So it takes, a key and a value. The key name is CommandLine and value is the path to exe with arguments if reqd. The key name is obtained from the Create method description as shown here


So we can get notepad to open via WMI. Now lets try it against a remote-machine. If you do a Get-Help Invoke-CimMethod in PowerShell, you will see that it takes a –ComputerName or –CimSession parameter.


So type the same command with an additional –ComputerName option followed by a machine name that you have for this test:

Invoke-CimMethod –ComputerName XYZ ..rest of the command as before.

As you can see from the image below, the remote process is created and shown under Task Manager. Do note, the remote processes don’t show a UI because of security reasons.



So let’s now try with the SCARunner application against a remote machine on which SCA is installed (and server registered). Everything will be same as before except the CommandLine, which should now include the path to SCARunner along with the start-up options:



And you can see that it does start successfully which is pretty cool!

To stop it, you can just use the –stop option, in the $cmdLine variable:

$cmdLine = "C:\Program Files (x86)\Intergraph\SCA\SCARunner.exe -stop"

In my setup, I use a CSV file to import a list of servers using the Import-Csv cmdlet, and startup SCA on those machines.


Hope this motivates you to use PowerShell to automate remote-tasks which would otherwise be hard to accomplish in a simple and efficient way.

Read Full Post »

As you may know, SQL Server will create a query plan for each batch it executes. The plan is then stored in plan cache for future re-use. However if your application uses lots of ad-hoc queries (like S3D filters for e.g.), you will end up with a lot of cached plans that are used only once and are just lying idle, taking space and wasting memory.

So how do you check the usage of such plans? You can do it two ways:

  • Run this query against your database
SELECT Count(*) Total, SUM(size_in_bytes)/1048576 MBs
FROM sys.dm_exec_cached_plans 
WHERE cacheobjtype LIKE 'Compiled Plan%' 
And objtype = 'Adhoc' And usecounts=1

  • Run a scan of your database server using SCA (System & Configuration Analyzer) 5.6.5 or later. The AdHoc counters are shown under MSSQL Info node as shown below:


So now you know the adhoc plan usage but how do you limit it’s growth? Luckily SQL Server 2008 has added a configuration option, “optimize for ad hoc workloads”, that can be used to improve the efficiency of the plan cache. With this options set to 1, the SQL engine will store a small compiled plan stub in the plan cache when a batch is compiled the first time, instead of a full compiled plan.

If you use SCA, you can see the current settings for this option under MSSQL Config Options node:


Alternatively you can run sp_configure to show advanced options:


Since it’s an advanced option, you need to reconfigure it with override. So to enable it, you would need to set the run_value to 1.


In some cases, like mentioned here , you may need to periodically monitor the cache and clear it manually using the command, DBCC FREESYSTEMCACHE(‘SQL Plans’), or create an automated SQL server job to do that.

To help with an automated solution, SCA now includes a job to clear the Adhoc cache:


The job is then invoked as per the schedule and shows up in the Job Manager view:


The job is only invoked if the cache size is greater than 500 MBs



Hopefully this gives you an idea on how to manage your “cache plans”…till next time.

Read Full Post »