Perf Report Query

Sep 22, 2005

CREATE TABLE [Perf]
(
[TransId] INTEGER
,[FileNo] VARCHAR(80)
,[TimeInSeconds] INTEGER
,[FileSizeMB] INTEGER
,[FileName] VARCHAR(255)
,[StartDate] datetime
)

Ok!! Here's the Problem. I am working on a perf stats report. FileType is First 9 chars of the field FileName. I need to compare a similar filename from this month to last month or before ordered by file size.

Maybe this is a very simple query but currrently my mind refuses to work. Seeking F1.

View 3 Replies


ADVERTISEMENT

Perf Mon

Mar 15, 1999

On an installation of sql 6.5, when I go to performance monitor, there are no sql counters. What gives? How can this be fixed,and do I have to reinstall,(wipeout data) to do it?

View 2 Replies View Related

SQL Perf Mon

Nov 9, 1998

The User Defined Counters Object has disappeared from me Performance Monitor.
Any ideas why or how to get it back? Thanks, Mike

View 1 Replies View Related

Perf Monitor

Feb 24, 2004

Is there anyway that you can automate performance monitor from the cmd line. I.e feed it some parameters like server and counters and receive a log file at the end.

Cheers

View 5 Replies View Related

SQL Network Perf Issues

Mar 19, 2003

I have a SQL2000 db running a vendor custom app on a Win2000 sp3 server. The front-end is run on Citrix to the client. Can't change the app or the somewhat poor infrastructure of client. users starting to complain about slowness ( we can shadow them and this seems the case). On the SQL box all the standard perf counters look good (buffer cache at 100%, queue length always less than 1, memory and disk look good. The server currently has 2 nics doing load balancing. The network counter bytes/sec averages over 100,000. The network counter output queue length shows an average of 4,294,967,251 (That seems high but in reading on another sql perf site this counter doesn't always work). Any other ideas where I might look. Do these numbers look high?
Thanks

View 1 Replies View Related

MSDE And Missing Perf Counters

May 15, 2002

The SQL performance couters do not get installed with MSDE. Does anyone know how to install them??

View 2 Replies View Related

Sql 2000 Perf Monitoring Books

Mar 23, 2004

Any recommendations from DBA's on good books for performance monitoring for sql 2000

View 2 Replies View Related

Perf Tuning Question - Gurus Only

Feb 19, 2004

Howdy

I have a server that has the following average readings :

No. CPUs = 1
% CPU = 2
SystemCPU Queue Length = 2 to 4
SQL Server:Buffer ManagerBuffer Cache = 99.85%
RAM in the box = 1 GB
MemoryPages/sec = 1 to 5
SQL memory in use ( using Task Manager ) = 250 MB
Max worker threads = 255
Average number of connection = 60

So...........all indicators are that the CPU is idling, there is way enough RAM but we still have a ( in theory ) a congested CPU as the queue length is over 2 consistantly. Thing is, I need to work out if the CPU isnt working hard as the queue is long, or whether we can put extra databases/load on the box.

As the max worker threads are greater than number of connections ( 60 vs 255 ) we could reduce these as the number of users doesnt seem to alter much. BUT.......would this make much difference as if the 255-60=195 worker threads arent doing anything much, they shouldnt put any load on the server, right?

Any thoughts much appreciated.

Cheers,

SG.

View 13 Replies View Related

High Value For The PagesSec Perf Counter

Feb 26, 2008

Hello All,

I have a 64-bit SQL Server running on 64-bit OS having 12GB of RAM. The Server only hosts our Application database (its a 30 GB database). There are no major process running on it. Despite of this, the Avg Pagessec shows a count of 1500 and
the Avg Page Faultsec shows 22000. Is there any specific reason as to why this is happening? The %idle time of the disk is 87%. Also there is no major load on the server. Do let me know if you need any other input.

Thanks in Advance,
Mitesh

View 2 Replies View Related

Perf On Read Uncommitted Isolation Level

Jul 5, 2005

Are there really any benefit on using Read Uncommitted Isolation Level or having a NOLOCK hints for retrieve queries when the default Isolation level just Read Committed (not using COM+).  I'm confused why the Community Server uses this technique perhaps for perf issues but I couldn't see any reason why...

View 1 Replies View Related

SSIS Perf Tuning - Tables Of 15M+ Rows

Feb 28, 2008

The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.

I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.

I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?

I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

The design of the data flow at the moment is:


..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4

any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?


*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

View 7 Replies View Related

2005 Perf Much Worse Than 2000... Suggestions Please..

Nov 20, 2006

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas??

Here is the procedure...

declare @srch_field varchar(8000)

declare @row int, @productid varchar(25)

DECLARE @title varchar(150), @actors_keyname varchar(1200), @directors_name varchar(400)

Declare @genres varchar(700), @theme varchar(1500), @type varchar(1500), @studio_desc varchar(100)

DECLARE @media_format varchar(50), @artist_name varchar(100), @dev_name varchar(100)

DECLARE @flags varchar(256), @starring varchar(256), @esrb varchar(100), @esrb_desc varchar(500)

DECLARE @ptrval varbinary(16), @text varchar(max)

declare @productlist table(product_id varchar(25), IDNUM int identity)

insert into @productlist (product_id)

select product_id

from music_load..globalsearch

select @row = @@rowcount

while @row > 0

begin

select @productid = product_id

from @productlist

where idnum = @row

SELECT @title = rtrim(title) ,

@actors_keyname = actors_keyname ,

@directors_name = directors_name,

@genres = genres ,

@theme = theme ,

@type = type ,

@studio_desc = studio_desc,

@media_format = media_format ,

@artist_name = artist_name,

@dev_name = dev_name,

@flags = flags ,

@starring =starring ,

@esrb = esrb ,

@esrb_desc = esrb_desc

FROM globalsearch

where product_id = @productid

Set @srch_field = isnull(@title,'')

if @actors_keyname is not null and @actors_keyname <> 'unknown'

Set @srch_field = @srch_field + ' ~ ' + rtrim(@actors_keyname)

if @directors_name is not null and @directors_name <> 'unknown'

Set @srch_field = @srch_field + ' ~ ' + rtrim(@directors_name)

if @genres is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@genres, 0,''))))

if @theme is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@theme, 0,''))))

if @type is not null

Set @srch_field = @srch_field + ' ~ ' + (ltrim(rtrim(replace(@type, 0,''))))

if @studio_desc is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@studio_desc)

if @media_format is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@media_format)

if @artist_name is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@artist_name)

if @dev_name is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@dev_name)

if @flags is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@flags)

if @starring is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@starring)

if @esrb is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb)

if @esrb_desc is not null

Set @srch_field = @srch_field + ' ~ ' + rtrim(@esrb_desc)

update globalsearch

set srch_field = @srch_field

where product_id = @productid

SELECT @ptrval = TEXTPTR(srch_field),

@text = credits

FROM globalsearch

where product_id = @productid

UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text

SELECT @ptrval = TEXTPTR(srch_field),

@text = track

FROM globalsearch

where product_id = @productid

UPDATETEXT globalsearch.srch_field @ptrval NULL NULL @text

set @row = @row - 1

end



View 5 Replies View Related

Loading Data Warehouse (Perf. Tuning)

Feb 25, 2008



Hi all,

I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:

1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.

OR

2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.

The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?

View 7 Replies View Related

Webinar On High-perf Extracts From Oracle, DB2, Teradata, Etc.

Jul 12, 2006

This webcast from our partner, ETI, may be of interest to readers on the forum - we see questions quite often about high-performance interaction with other databases.

Donald

View 4 Replies View Related

2005 Developer - Perf Monitor Counter Check Failed Message

Nov 11, 2006

Trying to install 2005 Dev edition in xp pro, sp2.

Performance Monitor Counter Check Failed Error.
Had to stop the install twice at the point it verified what components were to be included as
the docs were not going to be installed. Resolved the issue.

Now it won't pass the system config test because the registry is not the way it wants it.
I look up the messages and the only solution is to hack the registry and risk my system.

This is nuts.

Isn't there some way to restart cleanly without hacking registry keys ?

Help.

View 3 Replies View Related

Have SQL Report IP Of Query-er

Sep 17, 2007

I am trying to come up with a way to save the IP of the last computer that ran an update in our servers. I don't want to have to re-write the .net code, so I am wondering if there is a way, using functions available in SQL 2000 that I can get the IP of the machine running the update.
I know that SQL server 'knows' this information because it can log it in it's log files, but I don't know how to get at it from a stored procedure.
Thanks.

View 1 Replies View Related

SQL Query Help -for Ad Hoc Report

Nov 18, 2005

Need your help to write a SQL query:

My mgr wants to know
- # of times a particular kind of service (Service ID - AAA) was done on a particular kind of program (Program ID - SP) in 2005
- what is the Service percentage? For example, service was done 40% of the time
- Average cost of the service for those programs that had it

There are more than 100 tables linked together.

Please help..

View 3 Replies View Related

Report Query

Jul 27, 2005

I am trying to create a query which should return 4 columnsFacility | NoOfActiveApplicants | NoOfArchivedApplicants | TotalApplicantsI would be glad to even have just 3 columns since, the Total can be computedin the display table (ColdFusion interface)Facility | NoOfActiveApplicants | NoOfArchivedApplicantsSo far I have the following query, which returns just 2 rows with bothNoOfActiveApplicants & NoOfArchivedApplicants under the same column.Am stuck here, any help is apprecialted. Thanks in advance!selectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblapplicant a, tblJobDB b, tblfacilities cwhere a.jobid = b.jobidand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityunion allselectNoOfApplicants= sum(case when a.id <> 0 then 1 else 0 end), Facility = case when c.facility is null then c.JobDBFacilityName else c.facility endfrom tblArchiveapplicant a, tblArchiveJob b, tblfacilities cwhere a.JobVacancyNumber = b.JobVacancyNumberand b.facility = c.facilityidgroup by c.facilityid,c.JobDBFacilityName,c.facility,b.faci lityorder by facility

View 2 Replies View Related

Query For A/R Aging Report

Aug 20, 2001

How can I write a query to output the balances of unpaid invoices into aging columns? I have invoices, lineitems, and payments tables. I have a query that will join the 3 tables to determine which invoices are unpaid. I need to write an aging report in Access 2000 with the unpaid invoices listed with the balance under the proper heading (ie:<30, 30-60, 60+). Any ideas on how to do that? I can write a query to pull one date segment at a time but I can't get them all into one query with different headings to base a report on. My customer had it working in Access 2000 but he upgraded to SQL Server 7 with an Access 2000 Project as a front end and we can't get it to work. It's driving me crazy! TIA!!!

View 2 Replies View Related

Query Report By HOUR

Sep 11, 2014

I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.

AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230

View 13 Replies View Related

Query/Report Help Needed

Jul 23, 2005

Hello all,I need to do the following task repeatedly. I have been doing itmanually until now and I am thinking if there is any way I couldautomate the whole process.Here is the task:For a selected group of tables, I need to create an Excel file with oneworksheet per table. The worksheet contains information about thetable.Here is an Example of Person table with fields PersonId, LastName,FirstName ...Column Name -> PersonId LastName FirstName ...Type -> char char char ...Length -> 6 50 50Data -> P00001 Smith John .... P00002 Smith Joan ...

View 1 Replies View Related

How To Query From Report Model

Jan 31, 2008

Hi, it's my first time for using report model as the datasource of reporting service. But I don't know how to build the query string to get data from the report model.
Can anyone help me?

Thanks

View 3 Replies View Related

Report Builder Query

Feb 15, 2007

Can we view the query that is fired on the Report builder to debug as i am finding some error in data...

View 3 Replies View Related

From Query - Report Parameter

Nov 28, 2007

What needs to be done to allow a user to input data in a "From query report parameter"?

The "From query" option was used to show all possible selections the user can do but what if the user would now like to select for partial key...i.e.: like '%tss%'

I would like to avoid the Multi-Value parameter option.

NOTE: The report is done through Microsoft Visual Studio / RDL.

Thanks,

View 2 Replies View Related

Query/Report For 'Owner' Of All SQL Jobs

Nov 3, 2006

Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?

If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?

View 2 Replies View Related

Query To Create View / Report?

Apr 27, 2012

I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.

ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio

[code]....

Query in DESIGN View

SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication

[code]....

Results from QUERY in DESIGN VIEW

TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972

[code]....

RESULTS FROM QUERY in SELECT TOP 11

TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000

[code]....

QUERY in DESIGN VIEW

SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN

[Code] .....

QUERY RESULTS IN SELECT

csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463

[Code] ....

View 12 Replies View Related

Query To Produce A Report In Apex

Jan 24, 2014

I am trying to add to a query to produce a report in Apex. Currently I have this

SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,

[Code] ....

I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?

View 3 Replies View Related

No Query-results Shown In My Report

Oct 17, 2007

hi,

i have a problem with a report. if i try it with vs2005 and the preview it works.
if i call the report from my web-application i get no error-message, but i only get my
headlines and no query-results.

i use reportingservices 2005, vs2005 and .net 1.1
my datasource is an olap-cube.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ReportExecution.ExecutionInfo ei = repExecution.LoadReport(ReportPath, historyID);



repExecution.SetExecutionParameters(convertToReportExecutionParameter(Parameters), "de-AT");

result = repExecution.Render(Format, devInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

do you have any idea?
thank you

harald

View 1 Replies View Related

Writing Query In Report Builder

Oct 15, 2007



Hi,

I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.

Thanks,
S Suresh

View 3 Replies View Related

Report Builder Query Timeout?

Dec 29, 2005

Is their a way when creating a Report Builder model to set a query timeout?  Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
 
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance.  This has nothing to do with a report being published to a report server and setting a reports execution time limit.

View 6 Replies View Related

SSRS Report Query Problem

Apr 9, 2008

Hi,

I am newbie and i am working on SSRS to create some reports.

I have a reprot where i am displaying ProviderNos in the first column and based on provider i need to get some other information from another database and display it in the same line with Provider.

Below is my report format.

ProviderNo Speciality
1111111 A
2222222 B
3333333 C

The problem is, PrivderNos are from different database so i am getting it in separate Dataset. And Speciality is in Different Database so i am getting it in separate dataset. But i want both of them to be displayed in same table on report.

Below is the query for ProviderNos:
Select Prv_No from server1.database1.dbo.Providers where MemberNo='xxxxxx'

Below is the quer for getting Speciality:
Select Speciality from server2.database2.dbo.Prv_SP where prv_no IN (@prvno)

If i am assigning SpecialityDS to the table, it displays correct values for each n every provider. But i dont know how do i make it in such a way that it displays the result as above format.

View 3 Replies View Related

Displaying DataSet Query In Report

Oct 16, 2007

Is there a way to create an expression that references the DataSets / Queries? I'd like to be able to create an expandable text box that shows the raw sql query used to generate the report.

View 4 Replies View Related

Report Distribution Based On SQL Query In RDL

Apr 6, 2007



I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager:






Mgr ID
Manager Name

1
John Q Manager

2
Jane Q Manager

3
Jack O Lantern



I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution.



Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved