performance monitor help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

performance monitor help

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-05 : 05:11:05

hello, not sure if I've posted this in the correct section.
I'm am monitoring my sql server (v7) and when a report is run from an application the processors on the sql server go crazy. (usually between 0 - 30, but when a report is running the processors are between 60 - 100.
does this tell me that we need to replace the processors, or the reports need to be rewritten ?
(the application is Visual Basic and the reports are crystal reports).
thanks for any info on this one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-05 : 12:54:59

Most likely, the reports need to be rewritten. What does the report do, SQL Server-wise at least?

Tara

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-06 : 05:30:06

hello Tara,
the reports are generated from a visual basic app, by running a sql view.
an exapmle of a view would be over complicated sql like :

SELECT ClientDetail.Reference AS [Client Ref], ClientDetail.Name AS [Client Name], Team.Name AS Team, Lookup.Description AS [Care Type],
TempClients.DateTo AS [Care End Date], SUM(AgedDebt.[<30 Days]) AS [<30 Days], SUM(AgedDebt.[30-59 Days]) AS [30-59 Days],
SUM(AgedDebt.[60-89 Days]) AS [60-89 Days], SUM(AgedDebt.[90+ Days]) AS [90+ Days], SUM(AgedDebt.Total) AS Total
FROM ClientDetail INNER JOIN
Placement ON Placement.ClientID = ClientDetail.ID LEFT OUTER JOIN
Team ON Placement.TeamID = Team.ID INNER JOIN
(SELECT ClientDetail.ID AS ClientID, MAX(Placement.DateTo) AS DateTo,
(SELECT TOP 1 ID
FROM Placement
WHERE Placement.ClientID = ClientDetail.ID
ORDER BY DateFrom DESC) AS TopID
FROM Placement INNER JOIN
ClientDetail ON Placement.ClientID = ClientDetail.ID
GROUP BY ClientDetail.ID) TempClients ON TempClients.ClientID = ClientDetail.ID AND Placement.DateTo = TempClients.DateTo AND
Placement.ID = TempClients.TopID INNER JOIN
(SELECT Clientdetail.ID AS ClientID, CASE WHEN cast(getdate() - invoicedate AS integer)
< 30 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [<30 Days], CASE WHEN cast(getdate() - invoicedate AS integer)
> 29 AND cast(getdate() - invoicedate AS integer) < 60 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [30-59 Days],
CASE WHEN cast(getdate() - invoicedate AS integer) > 59 AND cast(getdate() - invoicedate AS integer)
< 90 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [60-89 Days], CASE WHEN cast(getdate() - invoicedate AS integer)
> 89 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [90+ Days], Invoice.TotalValue - Invoice.AmountPaid AS Total
FROM TransactionAccount INNER JOIN
Invoice ON TransactionAccount.ID = Invoice.TransactionAccountID INNER JOIN
ClientDetail ON TransactionAccount.ClientID = ClientDetail.ID
WHERE Invoice.TotalValue - Invoice.AmountPaid <> 0) AgedDebt ON AgedDebt.ClientID = ClientDetail.ID INNER JOIN
Lookup ON Lookup.ID = Placement.AdmissionTypeID
GROUP BY ClientDetail.Reference, ClientDetail.Name, Team.Name, Lookup.Description, TempClients.DateTo)
UNION
(SELECT ClientDetail.Reference, rtrim(ThirdParty.Title) + ' ' + rtrim(ThirdParty.Surname) AS Name, Team.Name AS Team, Lookup.Description AS [Care Type],
TempThirdParties.DateTo AS [Care End Date], SUM(AgedDebt.[<30 Days]) AS [<30 Days], SUM(AgedDebt.[30-59 Days]) AS [30-59 Days],
SUM(AgedDebt.[60-89 Days]) AS [60-89 Days], SUM(AgedDebt.[90+ Days]) AS [90+ Days], SUM(AgedDebt.Total) AS Total
FROM TransactionAccount INNER JOIN
Invoice ON TransactionAccount.ID = Invoice.TransactionAccountID INNER JOIN
ThirdParty ON TransactionAccount.ThirdPartyID = ThirdParty.ID INNER JOIN
ClientDetail ON ThirdParty.ClientID = ClientDetail.ID INNER JOIN
Placement ON Placement.ClientID = ClientDetail.ID LEFT OUTER JOIN
Team ON Placement.TeamID = Team.ID INNER JOIN
(SELECT ThirdParty.ID AS ThirdPartyID, MAX(Placement.DateTo) AS DateTo
FROM Placement INNER JOIN
ClientDetail ON Placement.ClientID = ClientDetail.ID INNER JOIN
ThirdParty ON ThirdParty.ClientID = ClientDetail.ID
GROUP BY ThirdParty.ID) TempThirdParties ON TempThirdParties.ThirdPartyID = ThirdParty.ID INNER JOIN
(SELECT ThirdParty.ID AS ThirdPartyID, CASE WHEN cast(getdate() - invoicedate AS integer)
< 30 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [<30 Days], CASE WHEN cast(getdate() - invoicedate AS integer)
> 29 AND cast(getdate() - invoicedate AS integer) < 60 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [30-59 Days],
CASE WHEN cast(getdate() - invoicedate AS integer) > 59 AND cast(getdate() - invoicedate AS integer)
< 90 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [60-89 Days], CASE WHEN cast(getdate() - invoicedate AS integer)
> 89 THEN Invoice.TotalValue - Invoice.AmountPaid ELSE 0 END AS [90+ Days], Invoice.TotalValue - Invoice.AmountPaid AS Total
FROM TransactionAccount INNER JOIN
Invoice ON TransactionAccount.ID = Invoice.TransactionAccountID INNER JOIN
ThirdParty ON TransactionAccount.ThirdPartyID = ThirdParty.ID
WHERE Invoice.TotalValue - Invoice.AmountPaid <> 0) AgedDebt ON AgedDebt.ThirdPartyID = ThirdParty.ID INNER JOIN
Lookup ON Lookup.ID = Placement.AdmissionTypeID
GROUP BY ClientDetail.Reference, ClientDetail.Name, Team.Name, ThirdParty.Title, ThirdParty.Surname, Lookup.Description, TempThirdParties.DateTo)

IS this good SQL ? to me it looks terrible.
some of the tables involved have approx 1 mill. records

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 12:17:48

I don't even know where to start to figure out what it is doing. There is so much going on. And yes this is where your problem is.

Have you considered having a separate report server? You could replicate your data to the report server or refresh the report server every night with the full backup. Either of these ways is what other corporations do so that reports do not affect the production server or customers.

Tara

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-02-09 : 04:39:14

tara, I already have a reports server which is updated nightly then reports are run.
unfortunately we want to know why we can't run the reports on the live server.
looks like the company that wrote the application need to rethink their reports.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-09 : 12:23:10

It's just the nature of reports. If the customer wants more up to date data then use replication. The report server can be within a couple of seconds of the live server.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources