Sql Server Perfmon Counters Quick Setup

The following steps allows you to quickly setup perfmon counters for Sql Server..

Create a text file called "sql2x51.txt" and copy\paste the following list of counters.
( Replace the Sql instance name with your Sql instance name.)


"\PhysicalDisk(*)\Avg. Disk sec/Read"
"\Memory\Available MBytes"
"\PhysicalDisk(*)\Avg. Disk sec/Write"
"\PhysicalDisk(*)\Disk Reads/sec"
"\PhysicalDisk(*)\Disk Writes/sec"
"\PhysicalDisk(*)\Disk Reads Bytes/sec"
"\PhysicalDisk(*)\Disk Write Bytes/sec"
"\PhysicalDisk(*)\Avg. Disk Bytes/Reads"
"\PhysicalDisk(*)\Avg. Disk Bytes/Write"
"\Process(sqlserver)\IO Data Operations/sec"
"\Process(sqlserver)\Thread Count"
"\Process(*)\% Processor Time"
"\Processor(*)\% Privileged Time"
"\Processor(*)\% Processor Time"
"\Processor(_Total)\% Privileged Time"
"\Processor(_Total)\% Processor Time"
"\System\Context Switches/sec"
"\mssql$MySqlInstance:Access Methods\Page Splits/sec"
"\mssql$MySqlInstance:Buffer Manager\Buffer cache hit ratio"
"\mssql$MySqlInstance:Buffer Manager\Checkpoint pages/sec"
"\mssql$MySqlInstance:Buffer Manager\Lazy writes/sec"
"\mssql$MySqlInstance:Buffer Manager\Page life expectancy"
"\mssql$MySqlInstance:Buffer Manager\Page reads/sec"
"\mssql$MySqlInstance:Buffer Manager\Page writes/sec"
"\mssql$MySqlInstance:General Statistics\User Connections"
"\mssql$MySqlInstance:Locks(_Total)\Lock Requests/sec"
"\mssql$MySqlInstance:Locks(_Total)\Lock Timeouts (timeout > 0)/sec"
"\mssql$MySqlInstance:Locks(_Total)\Lock Wait Time (ms)"
"\mssql$MySqlInstance:Locks(_Total)\Lock Waits/sec"
"\mssql$MySqlInstance:Locks(_Total)\Number of Deadlocks/sec"
"\mssql$MySqlInstance:Memory Manager\Target Server Memory (KB)"
"\mssql$MySqlInstance:Memory Manager\Total Server Memory (KB)"
"\mssql$MySqlInstance:Memory Manager\Memory Grants Pending"
"\mssql$MySqlInstance:SQL Statistics\Batch Requests/sec"
"\mssql$MySqlInstance:SQL Statistics\SQL Compilations/sec"
"\mssql$MySqlInstance:SQL Statistics\SQL Re-Compilations/sec"

Place the text file in the directory c:\temp or a directory of your preference.

Now run the following command from the command console.

logman create counter SQLBASELINEGG -f bincirc -max 500 --v -o "c:\temp\SQLout" -cf "c:\temp\sql2x51.txt"

You now have setup perfmon counters for your Sql Instance.
Open up perfmon and you will see a new User Defined set called SQLBASELINEGG.

DB2 script to Remotely execute SSIS Packages

In preparation to allow DB2 job to remotely execute SSIS packages several configurations need to be put in place mostly on Windows side.

1.
    Create a .BAT file on the Windows servers and apply the following code. (Include the disk letter)
    Driver Letter:Call osql.exe -Q"execute msdb.dbo.sp_start_job [My SQL Agent Job]" 
     -S MySQLServer -U My SQL account -P My Password

2.
    Create a Windows account that has execute permissions to the .BAT file.

3.
    DB2 command to remotely execute the SSIS Package.

              RUNRMTCMD  CMD('I:\SSISJobs\ExecuteMySQLJob.BAT') +                                
              RMTLOCNAME('111.111.111.111' *IP) +                             
              RMTUSER('MyWindowsUser') RMTPWD('password') +                   
              CCSID(1252) 

    You will need to include the full path of the Batch file, IP address of the windows server, Windows      user account and password.

There are several other ISeries DB2 commands that can be used as an alternative to RUNRMTCMD

but this is sufficient to handle the task. 

4.
    Install the following IBM ISeries Client service. This needs to be enabled on the Windows Server.



5.
    Allow port 512 to be opened on the Windows server firewall.

Alter multiple Columns data types

This script was used in a staging environment to change all decimal and char columns to float and varchars.


Instructions are:

1. Run the script in SSMS.
2. Copy and paste the results in a new query pane and execute..