|
|
 
1 |
Updated 8/2/2010
Added the definition for creating the stored procedure...
Updated:7/25/2010
Added (NO LOCK) to the select for performance. Also included a row of zero'd out data.
--------------------------------------
Updated: 7/21/2010
I found, while looking for something else, that microsoft has 2 tables in which they already keep the MAX and MIN values for datetime in each of the tables. I replaced the original code with the updated code. The updated code does not require SQL to figure out the max and min values since the SCOM DW already has them in dedicated tables. (StandardDataset and StandardDatasetTableMap. The start/end is in the tablemap table)
I also return a single row of data if no tables are found.
----------------------------------------------
I'm not sure what determines how many PerfHourly_<guid> tables are used but as time goes on in a big environment, there are more and more which is obviously to improve the grooming process. An article I read says that this is done every 10,000,000 records. (forget where I found it)
When you use the vPerfHourly view, it is a UNION of all of the perfHourly_<guid> tables. In my case, we are up to over a billion rows in that union.
I noticed when I did a query for 10 records or 10,000 records, there wasn't much difference in the performance when I used date specific ranges given I have 1 billion records in the view.
So in an attempt to improve performance I tried the following concept. The basic concept is to only include perfHourly_guid tables that you actually need. I saw one of my reports go from 30 minutes down to 3 minutes.
I first created a stored procedure which would return query text for me which would perform a UNION of only the tables where the data contained was within my date range of interest. I have listed the stored procedure below (NOTE: anything you do with this query is at your own risk. I'm only sharing for you to play in your test environment and perhaps improve report performance. If you find a bug and figure a fix, I'd love to hear back from you. )
There is one gotcha at this point... I have not created a dummy SQL return in the case where there are no tables that actually have data in it due to bad date/time ranges. I'll probably just add an if in the case where @firstTable still = 1 (means no table has been processed) and have the select text as returning an empty row of data. I haven't decided yet.
You call the stored procedure like below:
|
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @returnedQuery nvarchar(max)
SET @StartDate= '6/1/2010'
SET @EndDate= '6/7/2010 23:59:59'
exec [dbo].[SP_GetHourlyRecords]@StartDate,@endDate,@returnedQuery OUTPUT
|
The output will be similar to the following:
|
Select * from (select * fROM perf.PerfHourly_188494F8A92846139D5A3823C4725459 union all select * from perf.PerfHourly_33E0C0B24FF9404797FCC0E76A9A5EE4 union all select * from perf.PerfHourly_9327B784353D4C6AAEF4F70E05EEA0F4 union all select * from perf.PerfHourly_0005A759EEE04626B9B881BD3E785750) T Where [dateTime] >= '2010-06-01 00:00:00' and [dateTime]<='2010-06-07 23:59:59'
|
Then you build your query into a variable for execution including the returned query in @returnedQuery
|
Declare @ReportQuery nvarchar(max)
set @ReportQuery = 'Select * from (' + @returnedQuery + ') where <clauses>'
|
And finally you execute it via the sp_executeSql procedure.
|
exec sp_executeSQL @ReportQuery
|
CREATE PROCEDURE [dbo].[SP_GetHourlyRecords]
@ReportStartDate datetime
,@ReportEndDate datetime
,@dataQuery nvarchar(max) output
BEGIN
AS
SET NOCOUNT ON;
declare @firstTable as bit
Declare @MinDate as datetime
Declare @MaxDate as datetime
declare @SqlQuery as nvarchar(max)
declare @tmpDate as datetime
if @ReportStartDate>@ReportEndDate
Begin
-- Just in case someone put the dates in backwards
set @tmpdate =@ReportStartDate
set @ReportStartDate=@ReportEndDate
set @ReportEndDate=@tmpDate
End
set @firstTable=1
set @dataQuery = 'select * fROM '
Declare @TableNameSuffix as varchar(8000)
Declare TableCursor cursor
for
SELECT TM.TableNameSuffix
FROM [OperationsManagerDW].[dbo].[StandardDatasetTableMap] TM
join dbo.standarddataset SDS on TM.DatasetId=sds.DatasetId
where SDS.SchemaName='Perf' and TM.AggregationTypeId=20
and(
(
(@reportstartdate >= StartDateTime and @reportstartdate <=EndDateTime )
or
(@ReportEndDate <= EndDateTime and @reportenddate >= StartDateTime)
or
(EndDateTime >= @ReportStartDate and EndDateTime <= @ReportEndDate )
or
(StartDateTime >= @ReportStartDate and StartDateTime <= @ReportEndDate)
)
or
(StartDateTime is null or EndDateTime is null)
)
open TableCursor
Fetch Next FROM TableCursor into @TableNameSuffix
while (@@FETCH_STATUS<>-1)
Begin
if @firstTable=1
begin
set @firstTable=0
set @dataQuery= @dataquery + ' perf.PerfHourly_' + @TableNameSuffix + ' with (NOLOCK)'
end
else
begin
set @dataQuery= @dataquery + ' union all select * from perf.PerfHourly_' + @TableNameSuffix + ' with (NOLOCK)'
end;
fetch next from tableCursor into @TableNameSuffix
End
Close TableCursor
DEAllocate tablecursor
if @firstTable=1
begin
Set @dataQuery='
Select 0 as PerfHourlyRowID
,getutcdate() as [DateTime]
,0 as PerformanceRuleInstanceRowID
,0 as ManagedEntityRowId
,0 as SampleCount
,0 as AverageValue
,0 as MinValue
,0 as MaxValue
,0 as STandardDeviation'
end
else
begin
set @dataQuery = 'Select * from (' + @dataQuery + ') t where datetime >=''' + CAST(@reportstartdate as varchar) + ''' and datetime <= ''' + CAST(@reportenddate as varchar) + ''''
end
--print @dataquery
END