Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Useful MOM 2005 SQL queries

Useful MOM 2005 SQL queries

  • Comments 2
  • Likes

SCDW/DTS:

 

Primary 6 tables that SCDW data is retained in:

SC_AlertFact_Table
SC_AlertHistoryFact_Table
SC_AlertToEventFact_Table
SC_EventFact_Table
SC_EventParameterFact_Table
SC_SampledNumericDataFact_Table

Commands to execute to modify the data retention age:  Please run these commands changing the values from 385 to the number to the number of days of data to be retained:

Exec p_updategroomdays 'SC_AlertFact_Table', 385
Exec p_updategroomdays 'SC_AlertHistoryFact_Table', 385
Exec p_updategroomdays 'SC_AlertToEventFact_Table', 385
Exec p_updategroomdays 'SC_EventFact_Table', 385
Exec p_updategroomdays 'SC_EventParameterFact_Table', 385
Exec p_updategroomdays 'SC_SampledNumericDataFact_Table', 385

Queries to verify above setting:

For example, to verify the number of days that the SC_AlertFact_Table retains data, use the following command:

select cs.cs_tablename 'Table Name', wcs.wcs_groomdays 'Groom Days' from warehouseclassschema wcs
join classschemas cs
on cs.cs_classID = wcs.wcs_classID
where cs.cs_tablename = 'SC_AlertFact_Table'
and wcs.wcs_mustbegroomed = 1

Grooming the SCDW: 
If you set the grooming data to attempt to groom too much data at a time, the job will likely fail due to the transaction log filling up.  You should first ensure your transaction log has plenty of free space, or grow it manually, or set it to auto-grow (if ample disk space).  Ensure your tempdb has ample room (disk space) to grow temporarily, and then attempt to run the SCDWgroomjob.  Start with only grooming out 10 days at a time.

Queries to find out how old present data is:

SELECT DATEDIFF(d, MIN(DateTimeLastModified), GETDATE()) AS [Current] FROM SC_AlertFact_View
SELECT DATEDIFF(d, MIN(DateTimeLastModified), GETDATE()) AS [Current] FROM SC_AlertHistoryFact_View
SELECT DATEDIFF(d, MIN(DateTimeEventStored), GETDATE()) AS [Current] FROM SC_AlertToEventFact_View
SELECT DATEDIFF(d, MIN(DateTimeStored), GETDATE()) AS [Current] FROM SC_EventFact_View
SELECT DATEDIFF(d, MIN(DateTimeEventStored), GETDATE()) AS [Current] FROM SC_EventParameterFact_View
SELECT DATEDIFF(d, MIN(DateTimeAdded), GETDATE()) AS [Current] FROM SC_SampledNumericDataFact_View

Delete event data from SCDW (NOT SUPPORTED):

USE SystemCenterReporting
DELETE SC_EventFact_Table
FROM SC_EventFact_Table AS ft
INNER JOIN SC_EventDetailDimension_Table AS ed
ON ft.EventDetail_FK = ed.SMC_InstanceID
WHERE ed.EventID_PK = '1234'

Query to examine the last groom job and how long it took in the SCDW:

Use SystemCenterReporting
GO
Select MWCS.GroomDays, MCS.ClassName, MCS.TableName,MWGI.*, DATEDIFF(s,MWGI.StartTime, MWGI.EndTime) AS GroomTookSeconds FROM dbo.SMC_Meta_WarehouseGroomingInfo MWGI
INNER JOIN SMC_Meta_ClassSchemas MCS
ON MWGI.ClassID=MCS.ClassID
INNER JOIN SMC_Meta_WarehouseClassSchema MWCS
ON MWGI.ClassID=MWCS.ClassID
ORDER BY MCS.ClassName

Running the DTS job with latency:
Add the “/latency:90”  switch to the scheduled task command line for the DTS job (MOM.Datawarehousing.DTSPackageGenerator.exe)
(The latency switch specifies the number of days to skip)

Setting query timeout value:
If a *remote* query or DTS job times out, you can change the value in Ent. Manager, on the properties of the server, connections tab. 
Default is 600, set this to 0 for unlimited.

Query to look at oldest date of event records:

use SystemCenterReporting
select min(timestored) from sdkeventview

Given the event ID – a quick query to view the number of distinct events for each message, and view the text of the event message:

select distinct message, count(*) as number from sdkeventview where nteventid = '7036' group by message order by number desc

Faking the Onepoint database to think DTS is working in order to groom:
Changed the Date of "TimeDTSLastRan" to today's date in ReportingSettings table of Onepoint DB.
Then, run the MOMX Partitioning and grooming job.  This will delete all data in Onepoint based on the global setting of data retention, even if it has not been transferred to the SCDW.

The GroomingSettings table also contains a “TimeDTSLastRan” value – but this will be an old date, as it is typically the time and date of the reporting server install and is apparently not used by the grooming stored procedure.

DTS Logging:

1. Setup Error Logging for the DTS Package. You can use info from the following MS KB to setup DTS Package Logging:
316043 HOW TO: Log Data Transformation Services Package Execution to a Text File
http://support.microsoft.com/?id=316043

2. SQL Log location:  Zip up all the files in the SQL Server Log folder "..\program files\microsoft
sql server\MSSQL\LOG" Note: if you have a SQL Named Instance then the path will be as following for
the LOG Folder:  "..\program files\microsoft sql server\SQLNamedInstance\MSSQL\LOG"

3.     SQL profiler settings to capture for DTS logging:
- Errors and Warnings   ---> Add All events under this
- Stored Proc ---> RPC/SP Starting/Completed, SP:Stmt Starting/Completed, and  SP:
Recompile
- Transactions ---> All of these
- TSQL ---> SQL:Batch/SQL:Stmt Starting/Completed

 

 

Onepoint:

 

 

Alerts:

Most common alerts in a Onepoint DB:
This will tell us how many alerts we are generating per day, and

SELECT CONVERT(char(10), TimeRaised, 101) AS "Alert Date (by Day)", COUNT(*) AS "Number of Alerts"
FROM SDKAlertView
GROUP BY CONVERT(char(10), TimeRaised, 101)
ORDER BY "Alert Date (by Day)" DESC

SELECT Culprit, Name, SUM(1) AS AlertCount, SUM(RepeatCount+1) AS AlertCountWithRepeatCount
FROM Alert WITH (NOLOCK)
WHERE ResolutionState = (0|255)
GROUP BY Culprit, Name
ORDER BY AlertCount DESC
-- ORDER BY AlertCountWithRepeatCount DESC

Events:

Most common events in a Onepoint DB:

SELECT CONVERT(char(10), TimeGenerated, 101) AS "Events Date (by Day)", COUNT(*) AS "Number of Events"
FROM SDKEventView
GROUP BY CONVERT(char(10), TimeGenerated, 101)
ORDER BY "Events Date (by Day)" DESC

SELECT NTEventID, COUNT(*) AS "Numver of Events"
FROM SDKEventView
GROUP BY NTEventID
ORDER BY "Numver of Events" DESC

To view the oldest events in the database (this should mirror the “1” in Lastgroomed from above query):

select min(timestored) from sdkeventview

To count the number of events in Onepoint:

select count(*) from sdkeventview

To determine computers that are generating the most event data:

select distinct ComputerName, count(*) as NumberOfOccurences from SDKEventView
group by Computername order by numberofoccurences desc

To determine the noisiest computers and see the events they are generating:

select top 100 Computername, Message, NTEventID, Source, Count(*) AS TOTAL
from SDKEventView
Group by Computername, Message, NTEventID, Source
Order by TOTAL DESC

Selecting multiple events:

Select * from SDKeventview where NTeventID in (‘9980’,’9981’,’9982’,’9983’)

Given the event ID – a quick query to view the number of distinct events for each message, and view the text of the event message:

Select distinct message, count(*) as number from sdkeventview where nteventid = '7036' group by message order by number desc

Performance:

To display the most common perf insertions:

select performanceobjectname, performancecountername, count(performanceobjectname) as 'count' from sdkperformanceview
group by performanceobjectname, performancecountername
order by 'count' desc

To determine computers are generating the most perf insertions:

select distinct server, count(*) as NumberOfOccurences from SampledNumericDataPerformanceReportView
group by server order by numberofoccurences desc

Top 10% Computers with High Perf Volume, by Counter, by Object, by Day

SELECT top 10 percent CONVERT(char(10), TimeSampled, 101) AS "Perf Date (by Day)"
, Computername, PerformanceObjectName, PerformanceCounterName, COUNT(*) AS "Number of PerfObjects"
FROM SDKPerformanceView
GROUP BY CONVERT(char(10), TimeSampled, 101), computername, PerformanceObjectName, PerformanceCounterName
ORDER BY "Number of PerfObjects" DESC 

Top 10% Computers with High Perf Volume, by Day

SELECT  top 10 percent CONVERT(char(10), TimeSampled, 101) AS "Perf Date (by Day)", computername, COUNT(*) AS "Number of PerfObjects"
FROM SDKPerformanceView
GROUP BY CONVERT(char(10), TimeSampled, 101), computername
ORDER BY "Number of PerfObjects" DESC

Top 10% Computers with High Perf Volume, by Object, by Day

SELECT top 10 percent CONVERT(char(10), TimeSampled, 101) AS "Perf Date (by Day)", computername, performanceobjectname, COUNT(*) AS "Number of PerfObjects"
FROM SDKPerformanceView
GROUP BY CONVERT(char(10), TimeSampled, 101), computername, performanceobjectname
ORDER BY "Number of PerfObjects" DESC

To display the counters causing the most perf impact per computer:

select performanceobjectname, performancecountername, count(performanceobjectname) as 'count'
from sdkperformanceview
where computername = 'COMPUTERNAME'
group by performanceobjectname, performancecountername
order by 'count' desc

Misc Onepoint:

To view the number of Operations Console currently open:

SELECT program_name, count(*)
FROM Master..sysprocesses
WHERE ecid=0 and program_name='Microsoft Operations Manager - DAS Operations Console'
GROUP BY program_name
ORDER BY count(*) desc 

To view the grooming information on tables in one point, especially with respect to the “Current” and “LastGroomed” columns =1
SELECT * FROM dbo.PartitionTables

To view or modify the TimeDTSLastRan field to allow grooming to occur:
select * from ReportingSettings

To display the rule name given a GUID:

select Name FROM onepoint.dbo.ProcessRule WHERE idProcessRule=’GUIDstringHERE’

To find all rules associated with a provider:

select pr.name 'Rule Name', pi.name 'Provider Name' from processrule pr
join ProviderInstance pi
on pr.idproviderinstance = pi.idproviderinstance
where pi.name like 'ISA Server%'

To display the Total number of agents (all agent types)

SELECT * FROM ManagedCountsView WHERE ManagedType = ‘-1’

To display the total number of Unmanaged agents

SELECT * FROM ManagedCountsView WHERE ManagedType = ‘0’

To display the total number of Managed agents

SELECT * FROM ManagedCountsView WHERE ManagedType = ‘2’

To display the total number of Windows Server Cluster computers

SELECT * FROM ManagedCountsView WHERE ManagedType = ‘3’

To display the total number of Agents reporting to a specific Mgmt Server

SELECT COUNT(*) FROM MOMv2_ComputerAllPropertiesView WHERE ConfigManagerName = ‘mgmt server name’

or to list the total number of Agents reporting to all Mgmt Servers (NULL are unmanaged)

SELECT configmanagername, COUNT(*) FROM MOMv2_ComputerAllPropertiesView group by ConfigManagerName

To display the agent action account for all servers:

use OnePoint
Select Name, Value
from Attribute INNER JOIN Computer ON DISCOVERYCOMPUTERID = IDCOMPUTER
WHERE ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'Action Account Identity')
AND IDComputer IN (Select DiscoveryComputerID from Attribute WHERE
ClassAttributeID IN (Select ClassAttributeID from ClassAttribute where
ClassAttributeName = 'Action Account Identity'))
order by value, name

MISC:

Simple query to display large tables:

SELECT
so.name,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE 'U' = so.type GROUP BY so.name  ORDER BY data_kb DESC

Simple query for Onepoint or SCDW to dump a perfcounter to a table output.  Modify Computername and timestamps:

DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2006-10-18 05:45:00.287'
SET @EndDate = '2006-10-19 05:45:00.287'
SELECT * FROM dbo.SDKPerformanceView WHERE Computername = 'EXCH1' and PERFORMANCECOUNTERNAME = 'Local Queue Length' and TimeSampled BETWEEN @BeginDate AND @EndDate ORDER BY TimeSampled DESC

Database Performance:

The System Center DW does not come with any maintenance. 
I really like the maintenance plan at:  http://systemcenterforum.org/wp-content/uploads/scdw_reindex1.zip

To get better performance manually:

Update Statistics (will help speed up reports and takes less time than a full reindex):

EXEC sp_updatestats

Show index fragmentation (to determine how badly you need a reindex – logical scan frag > 10% = bad.  Scan density below 80 = bad):

DBCC SHOWCONTIG
DBCC SHOWCONTIG WITH FAST (less data than above – in case you don’t have time)

Reindex the database:

Onepoint:

USE OnePoint
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"
            SystemCenterReporting:
            DBCC REINDEX (‘TableName’) 
            DBCC REINDEX (‘SC_SampledNumericDataFact_Table’)

Update usage (use when SQL reports a table size that you know not to be correct):

DBCC updateusage ('systemcenterreporting')
DBCC updateusage ('systemcenterreporting','sc_samplednumericdatafact_table')

How to identify your version of SQL server:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL 2005:

SQL Server 2005 RTM                           2005.90.1399
SQL Server 2005 SP1                           2005.90.2047
SQL Server 2005 SP1 plus 918222         2005.90.2153
SQL Server 2005 SP2                           2005.90.3042

SQL 2000:

SQL Server 2000 RTM               2000.80.194.0
SQL Server 2000 SP1                2000.80.384.0
SQL Server 2000 SP2                2000.80.534.0
SQL Server 2000 SP3                2000.80.760.0
SQL Server 2000 SP3a              2000.80.760.0
SQL Server 2000 SP4                2000.8.00.2039

SQL2000 Reporting RTM             8.00.743.00
SQL2000 Reporting SP1              8.00.878.00
SQL2000 Reporting SP2              8.00.1038.00

Comments
  • PingBack from http://ianblythmanagement.wordpress.com/2008/04/28/reporting-database/

  • Do you know why SDKPerformanceView show sometimes two rows to the same counter in the same timestamp?

    DOMAIN        COMPUTER        _Total        Processor        % Processor Time        1        1        2007-12-29 15:45:00.007        2,99512093230315

    DOMAIN        COMPUTER        _Total        Processor        % Processor Time        1        1        2007-12-29 15:45:00.007        2,97123393641123

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs