Logging the performance counter

The following script creates a table PerfMondata in a SQLMonitor database and logs the counter in the interval of 30 seconds



use [SQLMonitor]



GO
 SET NOCOUNT ON ;

DECLARE @PerfCounters TABLE (

[Counter] nvarchar(770),

[CounterType] int,

[FirstValue] decimal (38,2),

[FirstDateTime] datetime,

[SecondValue] decimal(38,2),

[SecondDateTime] datetime ,

[ValueDiff] AS ([SecondValue] - [FirstValue]) ,

[timeDiff] AS (DateDiff(ss,[FirstDateTime],[SecondDateTime])),

[CounterValue] decimal(38,2)

) ;




 INSERT INTO @PerfCounters (

[Counter],

[CounterType],

[FirstValue],


[FirstDateTime]
)
 SELECT RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name]),[cntr_type],[cntr_value],GETDATE()

from sys.dm_os_performance_counters

WHERE [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec' ,'Buffer cache hit ratio'



)
 order by [OBJECT_NAME] + N' : ' + [counter_name]+ N':' +RTRIM([instance_name])



  Waitfor delay '00:00:30';


Update @PerfCounters

SET [SecondValue]= [cntr_value],

[SecondDateTime] = GETDATE()

from sys.dm_os_performance_counters

where [Counter] = RTRIM([OBJECT_NAME]) + N' : ' + RTRIM([counter_name]) + N':' +RTRIM([instance_name])

AND [counter_name] IN ('Page life expectancy','Lazy writes/sec', 'Page reads/sec', 'Page writes/sec ','Page lookups/sec',

'Free list stalls/sec','User Connections','Lock Waits/sec','Number of Deadlocks/sec',

'Transactions/sec ', 'Forwarded Records/sec','Index Searches/sec','Batch Requests/sec','SQL Compilations/sec','SQL Re-Compilations/sec',

'Target Server Memory (KB)', 'Latch Waits/sec','Buffer cache hit ratio' )


 
--for per sec counters

Update @PerfCounters

SET CounterValue = ([SecondValue]-[FirstValue])/(DateDiff(ss,[FirstDateTime],[SecondDateTime]))

where [counterType] = 272696576;


--for non per sec counters

 Update @PerfCounters

SET CounterValue = [SecondValue]

where [counterType] <> 272696576;

select * from @PerfCounters



INSERT INTO PerfMondata (perfCounter,Value,CAptureDate)

SELECT [counter],[CounterValue],[SecondDateTime] from @PerfCounters



--select * from PerfMondata




--following stored procedure takes in a counter as the parameter and displays the report on a specific counter

use [SQLMonitor]



GO
 

if OBJECTPROPERTY(OBJECT_ID('proc_sqlAdmin_PerfMonReport'),'IsProcedure')= 1

DROP PROCEDURE proc_sqlAdmin_PerfMonReport

GO





CREATE PROCEDURE dbo.proc_sqlAdmin_PerfMonReport

(



@Counter nvarchar(128)= '%'

)



AS



BEGIN ;

SELECT [Perfcounter],value,captureDate

from perfMondata

WHERE [Perfcounter] like @Counter

ORDER BY [Perfcounter],[captureDate]



END ;



/*



exec dbo.proc_sqlAdmin_PerfMonReport '%Page life expectancy%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Batch Requests/sec%'

exec dbo.proc_sqlAdmin_PerfMonReport '%Page Writes/sec%'

*/ 



 

Dynamic sql : Getting result of dynamic SQL into a variable

DECLARE @sqlCommand nvarchar(1000)
DECLARE @city varchar(75)
declare @counts int
SET @city = 'New York'
SET @sqlCommand = 'SELECT @cnt=COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75),@cnt int OUTPUT', @city = @city, @cnt=@counts OUTPUT select @counts as Counts

I had a situation where I had to loop through a list of database connections and if one of the database connection was down the flow had to continue to the next database.

This is how I achieved it -

1. The list of database connections ( the result returned by the Execute SQL task : Retrieve hospital list ) was assigned to the user variable @HospitalList ( type Object)  and the list was assigned as  the enumerator  for the Foreach loop  show in the following diagram.

The Script editor had the following code.

public void Main()


{
// TODO: Add your code here

bool failure = false;

bool fireAgain = true;

foreach (var ConnMgr in Dts.Connections)


{
Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",

ConnMgr.Name, ConnMgr.ConnectionString), "", 0, ref fireAgain);

try


{
ConnMgr.AcquireConnection(null);

Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",

ConnMgr.Name), "", 0, ref fireAgain);


}
catch (Exception ex)


{
Dts.Events.FireError(-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",


ConnMgr.Name, ex.Message),
"", 0);

failure = true;


}

}
if (failure)

Dts.TaskResult = (int)ScriptResults.Failure;

else

Dts.TaskResult = (int)ScriptResults.Success;


}

}

}


The Precedence Constraint Editor for the success flow was set as follows

 

 

 

The Precedence Constraint Editor for the failure was set as follows.

 

 

Note: The connection string of the database was dynamically created . The connection string was stored in a database table which was read by the Execute SQL task : Retrieve hospital list

 

Script:Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

SELECT
  name AS FileName,
  size*1.0/128 AS FileSizeinMB,
  CASE max_size
    WHEN 0 THEN 'Autogrowth is off.'
    WHEN -1 THEN 'Autogrowth is on.'
    ELSE 'Log file will grow to a maximum size of 2 TB.'
  END,
  growth AS 'GrowthValue',
  'GrowthIncrement' =
  CASE
    WHEN growth = 0 THEN 'Size is fixed and will not grow.'
    WHEN growth > 0 AND is_percent_growth = 0
      THEN 'Growth value is in 8-KB pages.'
    ELSE 'Growth value is a percentage.'
  END
FROM tempdb.sys.database_files;
GO

Srcript: Analysing Performance monitor data

After capturing the data in  the tables  ( link here - http://sql-dba-online.blogspot.com/2015/02/performance-monitor-how-to-run-relog-to.html) The following script will help to analyze the data.

/*show data collected for specific collector set, ordered by counter and time */

SELECT * from counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

ORDER BY cdt.[ObjectName],

cdt.[CounterName],cd.[RecordIndex]



/* List max,min,avg,stdev for each counter for a specific collector*/

SELECT CONVERT(VARCHAR(10),cd.counterDateTime, 101) AS "collection" ,

RTRIM(cdt.objectName) + '\' + rtrim(cdt.CounterName) +

Case

WHEN cdt.InstanceName IS NULL then ''

WHEN cdt.instanceName is not null then '_' + rtrim(cdt.InstanceName)

END as "Counter",

CAST (MIN(cd.CounterValue) AS Decimal(6,0)) AS "Minimum",

CAST (MAX(cd.CounterValue) AS Decimal(6,0)) AS "Maximum",

CAST (AVG(cd.CounterValue) AS Decimal(6,0)) AS "Average",

CAST (STDEV(cd.CounterValue) AS Decimal(6,0)) AS "StDev"

FROM counterData cd

JOIN DisplayTOId di

ON cd.[GUID] = di.[GUID]

JOIN counterdetails cdt

ON cdt.[counterID] = cd.[counterID]

WHERE di.[DisplayString] = 'ProdSample.blg'

GROUP BY CONVERT(VARCHAR(10),cd.counterDateTime, 101),cdt.objectName,cdt.CounterName,cdt.instanceName

Performance Monitor : How to run relog to process performance monitor data

Run relog from the directory where the datacollector file is

Type the following in the command prompt
c:\cd PerfLogs\Admin\FromTemplate\000001

( the above is the path where my file is)

running relog
c:\cd PerfLogs\Admin\FromTemplate\000001 > relog ProdSample.blg -f  SQL -o SQL:BaselineData!ProdSample.blg


ProdSample is my perflog file
-f is the format in which you want
-o is the output
SQL:BaselineData  ( it is the ODBC  connection that  I had created pointing to the database where I wanted  the data to be dumped)

Once I ran the above command  relog transferred the data and it created the following tables in the database connected trough ODBC . BaseLinedata in this case.

DisplayToID
CounterData
CounterDetails

How do I use parameters in an SSIS ADO NET source?

How do I use parameters in an SSIS ADO NET source -

Question

 

When I use SQL Command Text, there is a Parameters button in OLE DB Source. But now I want to use ADO NET Source in Data Flow and I cannot find such button in ADO NET source. How do I pass parameters to an ADO NET Source?

 

Answer

 

You can use variables together with Expression to pass the parameters:

1)   Switch to Control Flow, click the Data Flow Task.

2)   In the Properties window, notice a property called Expression and a small button next to it. Click the button to open the Expression Editor.

3)   In the Property list, select [The name of ADO NET Source].[SqlComamnd] and click the button under expression column to open Expression Builder.

4)   Write the query with variable names and click the Evaluate Expression button to test the expression. For example:
"SELECT * FROM EMPLOYEES WHERE FNAME='" +  @[User::FName] + "'"

 

 

source : - http://blogs.msdn.com/b/sqlforum/archive/2011/04/11/forum-faq-how-do-i-use-parameters-in-an-ssis-ado-net-source.aspx

Performance monitor counters - Top 10 counters for monitoring SQL server

script:User Permission

The following script  creates a new user and lets the user create new objects and grant permission on the objects to other users


IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')

DROP USER [user1]

create user [user1] for login [domain\user1]

EXEC sp_addrolemember db_ddladmin, [user1]; -- allows to create objects

EXEC sp_addrolemember db_securityadmin, [user1]; --allows to assign permission on objects

suspect pages errors

USE msdb

GO

SELECT * FROM dbo.suspect_pages


Event_type column gives different values

Errors Recorded in suspect_pages Table
The suspect_pages table contains one row per page that failed with an 824 error, up to a limit of 1,000 rows. The following table shows errors logged in the event_type column of the suspect_pages table.

Error description	event_type value
823 error caused by an operating system CRC error or 824 error other than a bad checksum or a torn page (for example, a bad page ID)	1
Bad checksum	2
Torn page	3
Restored (The page was restored after it was marked bad)	4
Repaired (DBCC, AlwaysOn, or mirroring repaired the page)	5
Deallocated by DBCC	7

The suspect_pages table also records transient errors. Sources of transient errors include an I/O error (for example, a cable was disconnected) or a page that temporarily fails a repeated checksum test.

How the Database Engine Updates the suspect_pages Table
The Database Engine takes the following actions on the suspect_pages table:

• If the table is not full, it is updated for every 824 error, to indicate that an error has occurred, and the error counter is incremented. If a page has an error after it is fixed by being repaired, restored, or deallocated, its number_of_errors count is incremented and its last_update column is updated
• After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).
• If a DBCC check is run, the check marks any error-free pages as repaired (event_type = 5) or deallocated (event_type = 7).

---To clean up the suspect_pages table run the following

delete s

from msdb.dbo.suspect_pages s

where event_type in (4,5,7)

Script: List all foreign keys in a database

SELECT

o1.name AS FK_table,

c1.name AS FK_column,

fk.name AS FK_name,

o2.name AS PK_table,

c2.name AS PK_column,

pk.name AS PK_name,

fk.delete_referential_action_desc AS Delete_Action,

fk.update_referential_action_desc AS Update_Action

FROM sys.objects o1

INNER JOIN sys.foreign_keys fk

ON o1.object_id = fk.parent_object_id

INNER JOIN sys.foreign_key_columns fkc

ON fk.object_id = fkc.constraint_object_id

INNER JOIN sys.columns c1

ON fkc.parent_object_id = c1.object_id

AND fkc.parent_column_id = c1.column_id

INNER JOIN sys.columns c2

ON fkc.referenced_object_id = c2.object_id

AND fkc.referenced_column_id = c2.column_id

INNER JOIN sys.objects o2

ON fk.referenced_object_id = o2.object_id

INNER JOIN sys.key_constraints pk

ON fk.referenced_object_id = pk.parent_object_id

AND fk.key_index_id = pk.unique_index_id

ORDER BY o1.name, o2.name, fkc.constraint_column_id