slow performance on a new app

hey guys,

I need your guide again. We just upgraded an existing mssql app on our test server to a new version. We also installed the appropriate stand alone client. Doing a report is totally just dead in the water. I have monitored the CPU and it's just at 5%. I've monitored the physical and logical disk, it's also very good. I've conducted a trace to see if there were CPU intensive queries or long running queries, but there were none. We also defragmented the disk since we found it to be 80% fragmented, but it didn't help. Last thing we did is install the client directly on the server, but it didn't help too. This is the first time we experience this type of problem. Everytime we upgrade this app on our TEST server, we don't experience any problem just this week.

So what should I do? Is there any "Event" that I can trace in Profiler that will spit out a problem in the app?

Thanks,

V1rt

Does the app connect at all? Can it do other things? You need to explain a bit more precisely what you mean by "totally just dead in the water".

All DSNs, connection strings, network protocols, security, permissions, set correctly?

Tom Pullen
DBA, Oxfam GB

hi thomas,

The app connects to the server. I do a netstat -na|find "1433" and I get ESTABLISHED. I do see the initial traffic in Profiler. Like what I said earlier, I didn't find any cpu intensive queries or long running queries which means, the app is able to connect to the server. Sorry by the use of "dead in the water". What I really means here is that, the client gets information back from the server but very^5 slow until to a point our testers cancels the client because they've been waiting for more than 30 mins. They were expecting the report to come back after 1 or 2 mins. The testing was also done right on the server too but the behavior was the same.

We always have an upgrade every month. There were no slow performance issues before and this is only the first time it happened. We're thinking that the new dba might have incorrectly refreshe our TEST db using the PRODUCTION data. That's all I can think of for now. He told us that he followed the instructions the previous dba vendor told him. What we noticed though is that, when previous dba vendor refreshes our test dbs, it only takes 8 hours. That was since January to November. But with this new dba vendor, it took about 25 or 26 hours. Something must be wrong there. I would like to help in investigating but still in the phase of learning MSSQL.

Thanks.

I suggest you find out what the report is doing, what SQL statement it's running. Paste this into Query Analyzer yourself and look at the execution plan (estimated is fine).

Make sure index stats have been updated since the upgrade. Ensure "auto-update stats" is on and preferable run a manual sp_update_stats in the db too.

If this doesn't help you need to look for missing indexes, check for table scans in the execution plan, and look for poorly written SQL. "poorly written SQL" is a whole book if not a set of books so don't ask me to explain what that means!

Tom Pullen
DBA, Oxfam GB

Yup, as thomas says, execution plan is your insight into what sql is doing.
Always check that as the first thing you do when you have a performance problem, there are no exceptions!

quote:I didn't find any cpu intensive queries or long running queries which means, the app is able to connect to the server. Sorry by the use of "dead in the water". What I really means here is that, the client gets information back from the server but very^5 slow until to a point our testers cancels the client because they've been waiting for more than 30 mins. They were expecting the report to come back after 1 or 2 mins.

So you don't have long running queries (what is your definition of long running query?) but your client more then 30 mins?!? This is strange. Maybe, the problem is on the client side e.g. client receives thousands of rows and then formatting and/or some other processing of rows received takes so much time.

Having refresh 3 times longer then before might be for different reasons. One of them that you now have 3 times more data on test environment so your report might have to cope with more data. I would exclude this possibility first.

Off course looking at the query and its execution plan is first action I always take in such case, as Chappy and Thomas have already recommended.

Yep, I will look into the trace I captured this morning. I am saving the 5million rows to a table now. I'll get back to you later. Thanks guys. <img src='/community/emoticons/emotion-5.gif' alt='' />

I found the query that was taking 36 minutes on our TEST server. But in the PROD server, it was only taking 4 minutes. I did sp_help reporttbl and index are the same. Any more ideas that I should look at?

Oh, let me remind, the was copied from our production to our TEST.

Thanks.

What does the execution plan look like? you HAVE to start there. best to compare it with live too.

Tom Pullen
DBA, Oxfam GB

Here it is:<br /><br />I did an estimated execution plan and I saw one bookmark lookup on our TEST server running at 87%. Is that bad? I'm not seeing any high values in PROD for the same query<br /><br />Here is the query. And is there other way of writing this query properly? <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br />SELECT <br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT1') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT1' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT1,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT2') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT2' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT2,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT3') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT3' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT3,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT4') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT4' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT4,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT5') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT5' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT5,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT6') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT6' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT6,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT7') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT7' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT7,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT8') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT8' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT8,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT9') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT9' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as CLNT9,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT10') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT10' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as C10LNT,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT11') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT11' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as C11LNT,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT12') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT12' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as C12LNT,<br />(SELECT COUNT(*) FROM customertbl e INNER JOIN customertbl p ON e.lkey=p.lkey AND e.ecode=p.ecode AND e.dateevt=dbo.capturestats(p.lkey,'CLNT13') AND e.dateevt=p.dateevt AND e.client=@client WHERE e.ecode='CLNT13' AND e.dateevt BETWEEN @startdate AND @enddate AND e.client=@client) as C13LNT<br />

I did another estimated plan but this time, I grab each query and guess what I found out

Queries above that uses CLNT5, CLNT6 and CLNT7 have 94% BookMark lookup and 3% Index Seek.
CLNT Queries other than the 3 mentioned above are at 41% Bookmark lookup and 57% Index Seek.

In production, all CLNT are at 41% Bookmark lookup and 57% Index Seek.

Problem is, I don't know which figure is better.

I think if 94% of time is spent in book mark lookup, it's better to have a table scan.

I would recommned trying to reduce the query to a single query as most of the data selected is the same and if temporary table can solve the problem, you won't have to hit the main table again and again.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi ya,

what about the following, would that give you the same result?



SELECT
sum( case when e.ecode='CLNT1' then 1 else 0 end ) as CLNT1,
sum( case when e.ecode='CLNT2' then 1 else 0 end ) as CLNT2,
sum( case when e.ecode='CLNT3' then 1 else 0 end ) as CLNT3,
sum( case when e.ecode='CLNT4' then 1 else 0 end ) as CLNT4,
sum( case when e.ecode='CLNT5' then 1 else 0 end ) as CLNT5,
sum( case when e.ecode='CLNT6' then 1 else 0 end ) as CLNT6,
sum( case when e.ecode='CLNT7' then 1 else 0 end ) as CLNT7,
sum( case when e.ecode='CLNT8' then 1 else 0 end ) as CLNT8,
sum( case when e.ecode='CLNT9' then 1 else 0 end ) as CLNT9,
sum( case when e.ecode='CLNT10' then 1 else 0 end ) as CLNT10,
sum( case when e.ecode='CLNT11' then 1 else 0 end ) as CLNT11,
sum( case when e.ecode='CLNT12' then 1 else 0 end ) as CLNT12,
sum( case when e.ecode='CLNT13' then 1 else 0 end ) as CLNT13
FROM customertbl e
INNER JOIN customertbl p
ON e.lkey=p.lkey
AND e.ecode=p.ecode
AND e.dateevt=p.dateevt
AND e.client=@client
AND e.dateevt=dbo.capturestats(p.lkey, e.ecode )
WHEREe.dateevt BETWEEN @startdate AND @enddate
AND e.client=@client

Cheers
Twan

I will try it once I reach office <img src='/community/emoticons/emotion-5.gif' alt='' /> Thanks buddy

As usual, Twan is our TSQL Expert...

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

<br />yeah it looks impressive, but does it give the correct results <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan

Hi, I was able to run it last night. I tested the older query, it took 4 mins. Looks like they did some db optimization last night. I tried to run your query but I just canceled after 15 mins. I'm sorry.

Do you have a nonclustered index on customertbl( client, dateevt, ecode, lkey )? How many rows in the table? How complex is the UDF?

4 minutes seems too long unless you have a lot of rows, or that UDF is fairly complex...?

Cheers
Twan

If I look at the query ...
FROM customertbl e
INNER JOIN customertbl p
ON e.lkey=p.lkey
AND e.ecode=p.ecode
AND e.dateevt=p.dateevt
AND e.client=@client
AND e.dateevt=dbo.capturestats(p.lkey, e.ecode )
WHEREe.dateevt BETWEEN @startdate AND @enddate
AND e.client=@client
... it really looks like you could rewrite this as ...
FROM customertbl e
INNER JOIN customertbl p
ON e.lkey=p.lkey
AND e.ecode=p.ecode
AND e.dateevt=p.dateevt
AND e.client=@client
WHEREdbo.capturestats(p.lkey, e.ecode) BETWEEN @startdate AND @enddate
AND e.client=@client
Something tells me the dbo.capturestats function can probably be replaced with a derived table/embedded subquery. In a derived table, you may also be able to use the @startdate and @enddate parameters to narrow down the rowset before the JOIN is built.

Finally, I'm not sure that the self-join is doing anything at all for you. The function call might as well get the e.lkey parameter, because that's one of the fields on which you're joining - so the value is the same in any case.

be careful about putting a UDF instead of a column in the where clause... you won't be able to use an index on that column, and it may result in the udf being evaluated for every single row rather than just the ones that are between the values passed

Cheers
Twan

Twan,

I agree about using UDF, but the original query puts the UDF within the JOIN definition, so surely it is already getting called for each row in customertbl.

If I may be frank for a moment, V1rt's query looks like a few good ideas thrown together, rather than a properly designed query.

Not quite sure if QO is able to substitute function parameter with the same value comming from other table. I mean

AND e.dateevt=dbo.capturestats(p.lkey, e.ecode )

can be replaced by:

AND e.dateevt=dbo.capturestats(e.lkey, e.ecode )

and moved to where clause.

Since you have additional condition for e-selection (e.client = @client) I guess you will have less "e-rows" then "p-rows". QO may choose to apply e-conditions first (execept one with udf) have less rows and then test condition with udf only on them. Hope it is understandable and makes sense. Right now I guess udf condition is applied after join on all joined rows after all other conditions are applied.

Sorry guys, been stucked all day with other stuff. I'll try to capture the indexes of the requested tables.

Thanks.

mmarovic,
The outcome of the UDF depends on each single row of data, so no difference if you put it in WHERE or in JOIN. Or does QO evaluate the distinct values of the parameters coming from the given set of rows, and reduce te number of calls to the UDF?

V1rtu0s1ty,
Why is there a self-join in the query? From the details you've given us, it looks like you can simply query the table without any join and with the same results.

Adrian,

my discussion was about making all udf parameters to come from data set aliased with "e". In that case udf can be applied on rows from this data set and after filtering these rows they would be joined with another one ("p").

mmarovic,

That would make sense, but in this case data set "e" looks identical to data set "p", as it is joined on all fields being returned. The whole JOIN thing escapes me - but of course we don't see the whole picture.

It is not identical because of additional condition: e.client=@client
I would try:
SELECT
sum( case when e.ecode='CLNT1' then 1 else 0 end ) as CLNT1,
sum( case when e.ecode='CLNT2' then 1 else 0 end ) as CLNT2,
sum( case when e.ecode='CLNT3' then 1 else 0 end ) as CLNT3,
sum( case when e.ecode='CLNT4' then 1 else 0 end ) as CLNT4,
sum( case when e.ecode='CLNT5' then 1 else 0 end ) as CLNT5,
sum( case when e.ecode='CLNT6' then 1 else 0 end ) as CLNT6,
sum( case when e.ecode='CLNT7' then 1 else 0 end ) as CLNT7,
sum( case when e.ecode='CLNT8' then 1 else 0 end ) as CLNT8,
sum( case when e.ecode='CLNT9' then 1 else 0 end ) as CLNT9,
sum( case when e.ecode='CLNT10' then 1 else 0 end ) as CLNT10,
sum( case when e.ecode='CLNT11' then 1 else 0 end ) as CLNT11,
sum( case when e.ecode='CLNT12' then 1 else 0 end ) as CLNT12,
sum( case when e.ecode='CLNT13' then 1 else 0 end ) as CLNT13
FROM customertbl e
INNER JOIN customertbl p
ON e.lkey=p.lkey
AND e.ecode=p.ecode
AND e.dateevt=p.dateevt
WHEREe.dateevt BETWEEN @startdate AND @enddate
AND e.client=@client
AND e.dateevt=dbo.capturestats(e.lkey, e.ecode )
OPTION (FORCE ORDER)

I used Twans technique before and had good results. I would like to prove it better then original query. Additional problem could be if there are more clients then these hardcoded in query. In that case query would read more rows than original one.

And yes, I agree that query is wierd a litle bit and we don't know the whole picture. We don't know table structure, we don't know data and we don't know udf implementation.

Point taken.