sql ftp handling and perfmon command

 

http://walkthrough-dba.blogspot.kr/2011_05_01_archive.html

 

 

Collecting performance counters and using SQL Server to analyze the data

http://www.mssqltips.com/tip.asp?tip=1722

Problem
Quite frequently I find myself in situation where I need to get detailed information on performance monitor counters. For example I need to determine which processes are consuming all CPU at certain times. I find it handy to push the performance monitor counters into SQL Server where I can query it or perhaps display it in Reporting Services.

Solution
The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.

Select Counters

I first need to collect my data.

I use TypePerf to display a list of performance monitor counters on the machine I wish to collect from. TypePerf is installed by default on all machines after Windows 2000.

I use the following query to get a list of all the counters that can be used on the machine I am monitoring. Although the counters are generally the same they may be different from machine to machine.. This is run in a DOS command window.

typeperf -q

You can return the entire list and store this list in a text file by using the following command.

typeperf -q > counterstxt

Once you have the counters from the machine you want to monitor you need to edit the list to keep only the counters you wish to collect.

I normally collect all counters for the following objects. The * after the counter group specifies that all counters should be collected. After you have the list of counters you want to collect save this as counterstxt. This is a list of counters that were available on the machine that I wanted to analyze again not all of these counters may exist on your machine that you are monitoring.

\.NET CLR Data(*)\SqlClient: (for connection pooling)
\SQLServer:SSIS Pipeline\*
\SQLServer:SSIS Service \*
\MSFTESQLServer\*
\MSOLAPServer\*
\LogicalDisk(*)\*
\Memory\*
\Network Interface(*)\*
\Paging File(*)\*
\PhysicalDisk(*)\*
\Process(*)\*
\Processor(*)\*
\Server\*
\SQLServer:Access Methods\*
\SQLServer:Buffer Manager\*
\SQLServer:General Statistics\*
\SQLServer:Latches\*
\SQLServer:Locks(*)\*
\SQLServer:Memory Manager\*
\SQLServer:SQL Statistics\*
\System\*

--------------------------------------------------------------------------------

Collect Data

I then use logman to create my collection

logman create counter MyCollection -s %computername% -cf counterstxt

I then start the collection like this:

logman MyCollection start

Once I have collected a representative sample I stop the collection as follows:

logman MyCollection stop

By default on Vista and Windows 2008 servers, your performance monitor counters will be stored in %systemdrive%\PerfLogs\Admin and will be named after your collection name (in our case they will be called MyCollection.blg (blg is the extension for perfmon counters). On Windows 2000, 2003 and XP machines they will be stored by default in %systemdrive%\PerfLogs.


--------------------------------------------------------------------------------

Load to SQL Server

Now that I have collected my perfmon counters I am ready to push them into SQL Server. To do this I use relog.

To use relog to input your performance monitor counters into SQL Server you must first select a database you wish to push them into and then create a system DSN to this SQL Server database (any version of SQL Server will work from SQL 2000 to SQL 2008). I use Windows Authentication as I don’t want to worry about saving the account and password in the DSN.

Open up the Data Sources (ODBC) (In the Control Panel applet in the Administrative Tools section)
Under "User DSN" click "Add" and select SQL Server for your driver and click "Finish"
Give your System DSN a name – I call it "relog", and then point to a SQL Server in the drop down list or type in the server name and click "Next"
Select Windows Authentication (ensure that your windows login has dbo rights in the database that you wish to write your performance monitor counters to). and click "Next"
Select your database from the dropdown and click "Next"
Click "Finish"
Click "Test Data Source..." to test your data source
If the test was successful click "OK" and click "OK" again and then close this applet
Now push your performance monitor counters to your SQL Server database by using the following command. ServerName is the name of the server which I collected the data on. This name will be written to the SQL Server table DisplayToID that is created and I can query on it when I want to look at my counters.

You will want to run this command in the folder that has the "blg" file that was created or you will need to specify the path too. Also, you need to make sure the filename that was created is what is used for the command.

relog MyCollection.blg -f SQL -o SQL:relog!ServerName

--------------------------------------------------------------------------------

Analyze the Data

Now that the data has been loaded it is time to query the data.

The collection tables that are created when the data is loaded are the following:

DisplayToID - table containing information about your collection
CounterDetails - contains details about your perfmon counters
CounterData - contains the actual counter data
Here is a sample query illustrating how to access your perfmon counter data. Here we are looking for context switches (Context Switches/sec). This will group the data in one minute intervals.

SELECT MachineName,
CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime)) as [Date],
AVG(CounterValue) as Average,
MIN(CounterValue) as Minimum,
MAX(CounterValue) as Maximum
FROM CounterDetails
JOIN CounterData ON CounterData.CounterID = CounterDetails.CounterID
JOIN DisplayToID ON DisplayToID.GUID = CounterData.GUID
WHERE CounterName = 'Context Switches/sec'
GROUP BY MachineName,
CONVERT(DATETIME, CONVERT(VARCHAR(16), CounterDateTime))

Posted by walkthroughat 3:06 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

Collect and store historical performance counter data using SQL Server DMV sys.dm_os_performance

http://www.mssqltips.com/tip.asp?tip=2188

This process involves three steps, each described separately below.

Step One

Create the Registered Server Group for the SQL Instances you intend to push this solution. I tend to do this step in three phases:

Two test instances (one default instance and one named instance – you’ll see why shortly when we look at the results that are returned for queries against sys.dm_os_performance_counters for named instances.)
A group of remaining non-production SQL instances since I intend to push out the solution and monitor its behavior for a couple days before I push to production servers.
The production instances of SQL Server that I intend to run this process for monitoring.
It should be noted that since we’re dealing with Dynamic Management Views that this solution will only work for SQL Server 2005 and newer. Only for the purposes of this article have I created the MSSQLTips registered servers group. It contains two instances: the default instance and the MAPS named instance.



Before proceeding I'd like to show you quickly what to expect when you query sys.dm_os_performance_counters. If you read the tip I provided above you'll have a good understanding of what to expect - for the default instance. However, the results are different for named instances when it comes to the [object_name] column values. Let's take a look at the results of a simple query against this DMV for both a default and named instance and you'll see what I mean, furthermore it will lead you to understand some of the dynamic T/SQL I employ in the next step when I populate the list of counters I intend to persist.

SELECT [object_name], [counter_name], [instance_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';
When run against the default instance the results appear as such:



When run against a named instance you'll see the results differ when it comes to the object_name column:



You'll see in the next step how I account for the fact that the instance name is integrated into the object_name value.


--------------------------------------------------------------------------------

Step Two

I utilize a dedicated database on all my SQL Servers to host tables, views, stored procedures and functions to run in a uniform fashion against all my SQL Server databases and instances.

Here at MSSQLTips I’ve always referred to it as iDBA (named changed to protect the innocent DBA.) So, in my iDBA database (or whatever name you prefer on your SQL Servers) I create the necessary objects to support the data collection process from sys.dm_os_performance_counters:

MetaBOT schema
MetaBOT.dm_os_performance_counters table – hosts the collected results from sys.dm_os_performance_counters
MetaBOT.watched_counters table – hosts the counters that I intend to collect
USE [iDBA];
GO

CREATE SCHEMA MetaBOT AUTHORIZATION dbo;
GO

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'dm_os_performance_counters')
CREATE TABLE [MetaBOT].[dm_os_performance_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[instance_name] NVARCHAR(128),
[cntr_value] bigint,
[date_stamp] DATETIME
);

IF NOT EXISTS (SELECT name FROM iDBA.sys.[tables] T WHERE name = 'watched_counters')
CREATE TABLE [iDBA].[MetaBOT].[watched_counters]
(
[object_name] NVARCHAR(128),
[counter_name] NVARCHAR(128),
[active] bit
);

GO
I then populate the MetaBOT.watched_counters table with values and finally create a stored procedure that I can call from inside a scheduled SQL Server Agent job to populate the MetaBOT.dm_os_performance_counters table on a consistent basis. This is where I account for the named instance's impact on the object_name column by building the value dynamically based upon the core object name and the instance name as derived from use of the @@SERVICENAME constant:

--+-- Now populate the watched counters table based upon instance properties
DECLARE @NamedInstance bit
DECLARE @ObjectNamePrefix VARCHAR(50)
SELECT @NamedInstance = 1

IF @@SERVICENAME = 'MSSQLSERVER'
BEGIN
SELECT @NamedInstance = 0 --This is the default instance

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Buffer cache hit ratio base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Database pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Free pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page life expectancy', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page lookups/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page reads/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Page writes/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Reserved pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Stolen pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Target pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Buffer Manager', 'Total pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Cursor Manager by Type', 'Active cursors', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Active Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Data File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Log File(s) Used Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Percent Log Used', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Databases', 'Transactions/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'DTC calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Exec Statistics', 'OLEDB calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_namme], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:General Statistics', 'User Connections', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Latch Waits/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Latches', 'Total Latch Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Average Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Locks', 'Lock Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Target Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Memory Manager', 'Total Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Hit Ratio Base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Plan Cache', 'Cache Pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:SQL Statistics', 'Batch Requests/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Free Space in tempdb (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Longest Transaction Running Time', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'NonSnapshot Version Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Update Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Transactions', 'Version Store Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Lock waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log buffer waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Log write waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Memory grant queue waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Network IO waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Non-Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page IO latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Thread-safe memory objects waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Transaction ownership waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Wait for the worker', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES ('SQLServer:Wait Statistics', 'Workspace synchronization waits', 1);
END

ELSE
BEGIN
--Account for named instance when adding object names to watch
SELECT @ObjectNamePrefix = 'MSSQL$' + @@SERVICENAME

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Buffer cache hit ratio base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Database pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Free pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page life expectancy', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page lookups/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page reads/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Page writes/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Reserved pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Stolen pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Target pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Buffer Manager', 'Total pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Cursor Manager by Type', 'Active cursors', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Active Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Data File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Log File(s) Used Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Percent Log Used', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Databases', 'Transactions/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Exec Statistics', 'DTC calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Exec Statistics', 'OLEDB calls', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':General Statistics', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':General Statistics', 'User Connections', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Latches', 'Latch Waits/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Latches', 'Total Latch Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Locks', 'Average Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Locks', 'Lock Wait Time (ms)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Memory Manager', 'Target Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Memory Manager', 'Total Server Memory (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Hit Ratio Base', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Plan Cache', 'Cache Pages', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':SQL Statistics', 'Batch Requests/sec', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Free Space in tempdb (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Longest Transaction Running Time', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'NonSnapshot Version Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Update Snapshot Transactions', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Transactions', 'Version Store Size (KB)', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Lock waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log buffer waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Log write waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Memory grant queue waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Network IO waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Non-Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page IO latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Page latch waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Thread-safe memory objects waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Transaction ownership waits', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Wait for the worker', 1);

INSERT INTO [MetaBOT].[watched_counters] ([object_name], [counter_name], [active])
VALUES (@ObjectNamePrefix + ':Wait Statistics', 'Workspace synchronization waits', 1);
END
You may note that I track quite a few counters here - I figure if I go through the effort of setting this up then I may as well capture too much than not enough. Depending upon your environment and the number of databases you're hosting you may wish to persist fewer counters. Some of these counters exist in a 1:1 relationship to your database count. I did calculations based upon my most-populous instance and determined that each row of data persisted averaged 232 bytes; from there I then looked at how frequently I ran the associated collection job impacted the amount of data I was storing. Running the collection process every 10 minutes was estimated to yeild 1.5 gb/month. Increasing the frequency to every two minutes meant increasing the storage required to 8 gb/month - and this was for a single instance.

Now it's time to create the stored procedure that you'll call from inside that SQL Server Agent job. It is a simple INSERT statement from sys.dm_os_performance_counters filtered through an INNER JOIN against the MetaBOT.watched_counters table we created and populated in the last step. I didn't bother with creating any indexes in that table as it is so small that the optimizer would choose to scan the table instead of using the index. The maintance overhead of an unused index is unwarranted.

CREATE PROCEDURE MetaBOT.usp_collect_perfmon_counters AS
DECLARE @datestamp DATETIME
SELECT @datestamp = GETDATE()
INSERT INTO MetaBOT.[dm_os_performance_counters]
(
[object_name],
[counter_name],
[instance_name],
[cntr_value],
[date_stamp]
)
SELECT
DOPC.[object_name],
DOPC.[counter_name],
DOPC.[instance_name],
DOPC.[cntr_value],
@datestamp
FROM sys.[dm_os_performance_counters] DOPC
INNER JOIN iDBA.[MetaBOT].[watched_counters] WC
ON [DOPC].[object_name] = [WC].[object_name]
AND [DOPC].[counter_name] = [WC].[counter_name]
ORDER BY [object_name], [counter_name];
--------------------------------------------------------------------------------

Step Three

The final step involves creating the actual job to run on each instance for collection of counter values.

What I find easiset to do when coding SQL Agent Job creation is to build the job in the SQL Server Mangement Studio GUI and then script it out. That is what I did here. There is one change you need to make when doing this however - you must either remove or comment-out the line of code that presents the job_id GUID for output. It's not needed and will cause the script to fail across all but the initial instance when run. There is a single variable in this code that allows you to set how frequently the job runs (@RunEveryXMinutes); set the value equal to the number of minutes you want to expire between job runs.

USE [msdb]
GO
/****** Object: Job [Metadata_Collect_SQL_Perfmon_Counters] Script Date: 12/09/2010 12:14:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @RunEveryXMinutes TINYINT
SELECT @ReturnCode = 0
SELECT @RunEveryXMinutes = 10
/****** Object: JobCategory [Tuning and Optimization] Script Date: 12/09/2010 12:14:39 ******/
IF NOT EXISTS (
SELECT name
FROM msdb.dbo.syscategories
WHERE name=N'Tuning and Optimization'
AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Tuning and Optimization'
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Metadata_Collect_SQL_Perfmon_Counters',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Poll and store output from sys.dm_os_performance_counters',
@category_name=N'Tuning and Optimization',
@owner_login_name=N'SPECTRUM-HEALTH\svcSQLNotify', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Collect Metadata] Script Date: 12/09/2010 12:14:39 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Metadata',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC MetaBOT.usp_collect_perfmon_counters;',
@database_name=N'iDBA',
@flags=0
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'dm_os_perfmon_counters Job',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=@RunEveryXMinutes,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20101209,
@active_end_date=99991231,
@active_start_time=30,
@active_end_time=235959
-- ,@schedule_uid=N'519325e6-3114-453c-bfeb-6597300a45d2' <---Comment this line out of auto-generated script
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0
OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
EndSave:
GO
Once activated it's just the matter of querying the persisted values in the MetaBOT.dm_os_performance_counters table. I also recommend that you set up a purge command, this can be done as part of the MetaBOT.usp_collect_perfmon_counters stored procedure as a DELETE from the MetaBOT.dm_os_performance_counters table where the date_stamp column is less than whatever threshold you want to set. Depending upon your needs I would also recommend creating an index on that column if your performance reviews warrant doing so.

In the next tip of this series I'll examine different ways to disect this data and use it for analysis for such things as consolidation planning or reviewing for good people doing bad things in your databases. (Think CURSORS! folks!)

Posted by walkthroughat 3:02 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

find sql 2000 / 2005 failed job

SQL Server 2000 Code Snippet

-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC

SQL Server 2005 Code Snippet

-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic
SELECT j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC

Posted by walkthroughat 2:34 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

typeperf

TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file.

To get a list of counters with instances enter the following command:

TYPEPERF -qx "SQLServer:Databases" | FIND "tempdb"

You will see output similar to the following:

\SQLServer:Databases(tempdb)\Data File(s) Size (KB)
\SQLServer:Databases(tempdb)\Log File(s) Size (KB)
...

C:\Documents and Settings\automatingdba>TYPEPERF -q "SQLServer:Databases"
\SQLServer:Databases(*)\Data File(s) Size (KB)
\SQLServer:Databases(*)\Log File(s) Size (KB)
...

TYPEPERF -f CSV -o MyCounters.csv -si 15 -cf MyCounters.txt -sc 60

The above example writes the counter values to MyCounters.csv every 15 seconds. It stops after writing out the counters 60 times (i.e. 15 minutes).

The list of counters in MyCounters.txt is:

\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec
\SQLServer:Databases(tempdb)\Percent Log Used
...

the following link talks about how to load the output into sqlserver to analyze.
http://www.mssqltips.com/tip.asp?tip=2395

Posted by walkthroughat 2:08 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

sc command to stop and start windows service

@echo off
Echo. > c:\dir\sstate.txt

Echo Stopping service on all servers
sc \\servername stop servicename> nul

Echo Waiting 10 seconds
CHOICE /N /C YN /T 10 /D Y

Echo Starting service on all servers
sc start service > nul
sc \\servername start servicename > nul

Echo Waiting 10 seconds
CHOICE /N /C YN /T 10 /D Y

Echo servername >> c:\dir\sstate.txt
sc \\servername interrogate servicename >> c:\dir\sstate.txt

if defined batch goto :EOF
notepad c:\dir\sstate.txt

Posted by walkthroughat 8:50 AMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

Thursday, May 19, 2011

sqlplus and ftp

###this script run a query to generate an output file and ftp to remote site.
export ORACLE_SID=PROD
ORAENV_ASK=NO
. oraenv
FTP="ip.ip.ip.ip"
LOGIN="ftpuser"
PASS="password"
sqlplus -s " / as sysdba" <set verify off
set pagesize 0
set linesize 500
set head on
--set markup html on
set feedback off
set timing off
--set pagesize 0
set colsep ,
col channel_name format a29
col programme_name format a49
col house_media format a9
spool /folder/filename.csv
SELECT statement;
spool off
exit
EOF
ftp -nv $FTP < /folder/log.log
echo "############# Starting FTP to $FTP ###################"
user $LOGIN $PASS
type ascii
lcd /folder
del filename.csv
put filename.csv
dir filename.csv
quit
EOFLOG
echo finished ftp

Posted by walkthroughat 12:25 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

shell script to check oracle alert log

#!/bin/ksh
######################################################
# alert_log_mail.ksh
# Purpose: email error msg in alert log to DBA
# to use it, run thisscript.ksh instancename or ALL
################################################################################
if [ $# -eq 0 ]
then
echo "Usage: `basename $0` | ALL "
exit
else
OPTION=$*
fi

if [ "$OPTION" = "ALL" -o "$OPTION" = "all" ]
then
DB_LIST=`cat /etc/oratab|grep -v ^#|grep -v agent|grep -v Apach|grep -v ^$|awk -F: '{print $1}'`
else
DB_LIST=$OPTION
fi


export DBLOG=/home/oracle/dba/logs

get_msg(){
FILENAME=$1
MSG=$2
if [ -s ${DBLOG}/$(basename ${FILENAME}).mail ]
then
CKPOINT=`tail -1 ${DBLOG}/$(basename ${FILENAME}).mail|awk -F: '{print $1}'`
else
CKPOINT=0
fi
FILESIZE=`cat $FILENAME|wc -l`
if [ $FILESIZE -lt $CKPOINT ]; then CKPOINT=0; fi
STARTPOINT=`expr ${CKPOINT} + 1 `
tail +${STARTPOINT} ${FILENAME}|head -$(expr ${FILESIZE} - ${STARTPOINT} + 1)|egrep -i "${MSG}"|tail -r > ${DBLOG}/$(basename ${FILENAME}).m
sg
}

###########################################################
# Main Program
###########################################################
for db in $DB_LIST
do

if [ -z "`cat /etc/oratab|grep -v ^#|grep -v agent|grep -v Apach|grep -v ^$|awk -F: '{print $1}'|grep $db`" ]
then
echo "there is not db named $db defined in the server"
continue
fi
echo $db

get_msg /oracle/admin/$db/bdump/alert_$db.log "ora-|^Mon |^Tue |^Wed |^Thu |^Fri |^Sat |^Sun "

> $DBLOG/$(basename ${FILENAME}).mail

PRE_FLAG=0
cat ${DBLOG}/$(basename ${FILENAME}).msg|while read L
do
CUR_LINE=$L
if echo $CUR_LINE|egrep -i "ora-|error"
then
FLAG=1
else
FLAG=0
fi

case $PRE_FLAG in
0) if [ $FLAG == 1 ];then echo $CUR_LINE >> $DBLOG/$(basename ${FILENAME}).mail;fi; PRE_FLAG=$FLAG ;;
1) echo $CUR_LINE >> $DBLOG/$(basename ${FILENAME}).mail; PRE_FLAG=$FLAG ;;
esac

done


if [ -s ${DBLOG}/$(basename ${FILENAME}).mail ]; then tail -r ${DBLOG}/$(basename ${FILENAME}).mail|mailx -s "${db}: Alert log error message
" DBA@MAILSERVER.COM; fi


echo "${FILESIZE}:" >> ${DBLOG}/$(basename ${FILENAME}).mail
rm ${DBLOG}/$(basename ${FILENAME}).msg

done

exit

Posted by walkthroughat 12:20 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

pathping to detect network timeout

Recently our BPC nightly job failed intermittently, a few database mirror servers report timeout as well, I need to prove to network team that it's not an application issue, it's a network issue, so I implement the a windows scheduled task to run pathping among those servers every 1 minute. it did prove that network timeout. here is the script and sample of logs.

pingnetwork.bat :
echo %date% %time%
pathping server1
echo %date% %time%
pathping server2
echo %date% %time%
pathping server3
echo %date% %time%
pathping server4

pingnetwork.bat >> e:\dropit\d.txt

the log d.txt looks like this

"
Thu 05/19/2011 3:20:15.19
E:\dba>pathping agenwi034
Tracing route to agenwi034.corp.ctv.ca [10.1.38.134]
over a maximum of 30 hops:
0 agends030.corp.ctv.ca [10.1.38.130]
1 * agenwi034.corp.ctv.ca [10.1.38.134]
Computing statistics for 25 seconds...
Source to Here This Node/Link
Hop RTT Lost/Sent = Pct Lost/Sent = Pct Address
0 agends030.corp.ctv.ca [10.1.38.130]
68/ 100 = 68% |
1 1ms 68/ 100 = 68% 0/ 100 = 0% agenwi034.corp.ctv.ca [10.1.38.134]
Trace complete.
"

ftp the log file to unix and format it in a readable way.
grep -E "\/ 100" d.txt > d1.txt

the new log d1.txt looks like this:
"
E:\dba>echo Thu 05/19/2011 3:20:15.19
Thu 05/19/2011 3:20:15.19
0/ 100 = 0% |
1 0ms 0/ 100 = 0% 0/ 100 = 0% agends030.corp.ctv.ca [10.1.38.130]
68/ 100 = 68% |
1 1ms 68/ 100 = 68% 0/ 100 = 0% agenwi034.corp.ctv.ca [10.1.38.134]
65/ 100 = 65% |
1 1ms 65/ 100 = 65% 0/ 100 = 0% agenwi031.corp.ctv.ca [10.1.38.131]
60/ 100 = 60% |
1 1ms 60/ 100 = 60% 0/ 100 = 0% agenwi032.corp.ctv.ca [10.1.38.132]
39/ 100 = 39% |
1 0ms 39/ 100 = 39% 0/ 100 = 0% agenwi033.corp.ctv.ca [10.1.38.133]
"

now network is convinced of the network issue and is working on it.

Posted by walkthroughat 10:41 AMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

Tuesday, May 17, 2011

sql mirror witness disconnected

The witness sqlserver sometime get disconnected, reboot it sometime fix the problem, if not, reset witness is required, here is how.

to find out how many databases are using this witness, run query on witness:

SELECT principal_server_name, mirror_server_name, database_name, safety_level_desc
FROM sys.database_mirroring_witnesses

to find out which server is disconnected, run this sql on the principle server:

select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring

if the mirroring_witness_state_desc = disconnected, then remove the witness by running the following script. let's refer this list as $DBLIST.

on witness:

select 'alter database '+database_name+' set witness off' FROM sys.database_mirroring_witnesses

pickup the output that matching $DBLIST and save the output as o1.sql.

select 'alter database '+database_name+' set witness ='+'''TCP://REPLACETHISSERVERNAME:REPLACETHISPORTNUMBER''' FROM sys.database_mirroring_witnesses

pickup the output that matching $DBLIST and save the output as o2.sql.

restart witness sqlserver service, and reset the witness by running o1.sql and o2.sql on principle. if the o2.sql fail, we need to re-run it or even restart the witness db service.

then check the status again by running the following sql on principle server, now the mirroring_witness_state_desc should show "connected".

select mirroring_partner_name,mirroring_partner_name,mirroring_witness_name, mirroring_state_desc,mirroring_witness_state_desc, * from sys.database_mirroring

Posted by walkthroughat 3:12 PMNo comments:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

awk oracle control file

goal: create database
after creating control file using "alter database create controlfile to trace as 'filename';", I sometime need to replace the path of datafiles, the two awk command below helps me doing so.

awk -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print "\x27/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control.sql

awk –v q=“’” -F '/' '{ if (NR>10 && NR < 107 && ($NF ~ /dbf/ ||$NF ~ /log/) ) {print q "/bossdb/bnwrms/"$NF;} else if (NF==0) {} else {print $0;}}' control