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