September 11, 2012 at 2:56 pm
What I'm trying to do is to build a set of results that can show temporal measurements on report requests to include things like how long on average it took for a specific report to run, whose reports used the most available time and how long the backlogs (the wait time) were if any within a specified time frame.
My table as of current contains the following details (All of which are report requests processed by a report server): All Date/Time values are DATETIME data types including time unless stated other wise.
TimeIn - Date/Time When the request was swubmitted by the Requestor (by the user)
TimeStart - Date/Time when the system tsarted processing the report
TimeComplete - Date/Time when processing was done
Report Name - User friendly name like Cash Flow, Income Statement, ect
Source Name - The technical name of the item use to create the report. While the same Report Name may be used by multiple variations (i.e. Cash Flow) the combination of Report Name and Source Name ensure uniqueness of a Report
Host Name - The name of the Rep[ort Processing system that created the report that was requested; there are several of these and they are not always turned on all the time; it varies. One day all 4 may be running and sometimes only 2 or 3 are running. We can;t assume 8 hours * 4 is the total time available to process report requests.
Requested By - Name of person submitting report request
Criteria - Sting of text that stores what criteria the user specified for the report such as accoutring entity, dates and so on
Is there a way using this info that I can build a result set that will show things like
1) when a backlog occurred and how long it remained in effect
2) When is the report queue most busy
3) How much time of the available time each day is the reporting system not used
I assume that some kind of lookup table or complimentary utility table of dates and such will be needed and ifs that so then I do have a table of dates (mm/dd/yyyy) for dates spanning a few decades but it does not include any hour portions.
And help be it a link to some article or story would be most appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!September 11, 2012 at 3:05 pm
By now you know that ddl and sample data go a long way towards a viable answer....
Is there a way using this info that I can build a result set that will show things like
1) when a backlog occurred and how long it remained in effect
What defines a backlog?
2) When is the report queue most busy
Are you looking for a specific minute? Not really sure you want as output for this
3) How much time of the available time each day is the reporting system not used
Again not really sure what you are looking for here. Given a 24 hour period you want know the sum of seconds that are not between any of the start and end times???
Throw up some ddl, sample data and desired output and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2012 at 5:02 pm
Sean Lange (9/11/2012)
By now you know that ddl and sample data go a long way towards a viable answer....Is there a way using this info that I can build a result set that will show things like
1) when a backlog occurred and how long it remained in effect
What defines a backlog?
2) When is the report queue most busy
Are you looking for a specific minute? Not really sure you want as output for this
3) How much time of the available time each day is the reporting system not used
Again not really sure what you are looking for here. Given a 24 hour period you want know the sum of seconds that are not between any of the start and end times???
Throw up some ddl, sample data and desired output and we can help.
Thanks Sean. I did not have the DDL at the time ( b/c of NDA with vendor) but I wanted to get the post on this started so that when I did have it I'd know if there was any other info lacking while I worked on some name changes so I could post this.
The below is the DDL for the table that stores this info. Whenever a user is working in the system and wants a report in some print or distributable format liek Excel or PDF (as opposed to their screens) the progarm puts the item into this table with a value of 0 for iReportStatus. The report servers (between 2 and 4 depending on several factors) regularly check the table for any items with a status of 0 and when it finds them it changes the satus and commences to process the report.
There have been complaints from some users that the system is slow and takes forever and the it side is saying the probelm is with the userts doing things like resubmiting reports multiple times and nopt waiting till teh first one is done before requetsing it again. The report queue is not always in use; there are times when tehre are no reports waiting to be processed and so depending on when you susmit report requests it may seem like teh system is dog slow or that you nevcer have trouble.
The execs/VP's want to anaylze the queue and determnine where the rpobelms are (backlogs) and whether user training is called for or hadrware changes or both. A backlog is when the number of report requests in the queue backup (are set to a status of 0 or Not Proccessed) because far more requests are coming in then can be processed out. Sometimes its because its the end of the month and the accountants are runing reports and at other times its something else.
Let me know if you need more info.
DDL:
CREATE TABLE dbo.REPORTQUEUE
(
hID NUMERIC(18,0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, /*Primary Key*/
sRequestedBy VARCHAR (20) NULL, /* Name of user submitting request*/
iReportStatus NUMERIC(18,0) NULL, /* 0=Not Processed;1=Processing; 2=Processed;3=Error;*/
dtRequested DATETIME NULL, /* Date/time Report request was submitted bu user*/
dtSchedule DATETIME NULL, /* Date/time Report was scheduled to start running - Used for prescdeuled reports*/
dtProcessingStart DATETIME NULL, /* Date/time Report requests started processing by sReportServer*/
dtProcessingComplete DATETIME NULL, /* Date/time Report processing completed;*/
sReportName VARCHAR(80) NULL, /* Common name of report i.e. Cash Flow, Balance Sheet*/
sReportResult VARCHAR(500) NULL, /* Result or outocme; ussually set to Processed except for when the stats is error then this has the error msg*/
sSourceFile VARCHAR(200) NULL, /* Source File used to build report.*/
sReportCriteria VARCHAR(255) NULL, /* string of name=value pairs that represent the crtieris specified for teh rpeort by user*/
sReportServer VARCHAR(60) NULL, /* Id of Report Server that procssed request. Ex: RPTSVR01; RPTSVR02;*/
CONSTRAINT [PK_REPORTQUEUE] PRIMARY KEY CLUSTERED ( hID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Kindest Regards,
Just say No to Facebook!September 11, 2012 at 6:40 pm
YSL,
First you'll need to decide exactly what "measurements" of your sample data will lead you to the conclusions that you seek. That statement sounds a bit ironic but it is true, statistics can be manipulated to produce any desired outcome (usually).
Personally for such analysis I prefer using graphs and I'm most comfortable in Excel. One technique that may help you somewhat is this SQL Spackle article.
http://www.sqlservercentral.com/articles/Excel/91179/
Be sure to look in the discussion thread because a good chunk of the explanation for the last example (the one you'll need to use) was omitted from the article and appears there.
In order to actually produce a working solution, we'd all need some sample data. But here are a couple of hypothetical approaches:
1. Let's say you wanted to measure your back logs by "wait time." So you could construct a simple query that gathers the wait time of each report request and then averages them over a fixed interval of time.
2. Another approach would be to count the number of report requests that are being processed in a particular interval of time.
Either of these approaches could be fed into the scripts proposed in the referenced article to produce histograms by your identified time intervals.
There are literally thousands of possible scenarios you could map SQL --> an Excel chart.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply