SQL Server Performance

slow performance on a new app

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Dec 5, 2004.

  1. v1rtu0s1ty New Member

    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
  2. thomas New Member

    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
  3. v1rtu0s1ty New Member

    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.
  4. thomas New Member

    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
  5. Chappy New Member

    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!
  6. mmarovic Active Member

    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.
  7. v1rtu0s1ty New Member

    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=';)' />
  8. v1rtu0s1ty New Member

    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.
  9. thomas New Member

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

    Tom Pullen
    DBA, Oxfam GB
  10. v1rtu0s1ty New Member

    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 />
  11. v1rtu0s1ty New Member

    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.
  12. gaurav_bindlish New Member

    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.
  13. Twan New Member

    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
  14. v1rtu0s1ty New Member

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

    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.
  16. Twan New Member

    <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
  17. v1rtu0s1ty New Member

    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.
  18. Twan New Member

    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
  19. Adriaan New Member

    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.
  20. Twan New Member

    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
  21. Adriaan New Member

    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.
  22. mmarovic Active Member

    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.

  23. v1rtu0s1ty New Member

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

    Thanks.
  24. Adriaan New Member

    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.
  25. mmarovic Active Member

    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").
  26. Adriaan New Member

    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.
  27. mmarovic Active Member

    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.
  28. Adriaan New Member

    Point taken.

Share This Page