Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: Need help debugging performance issue

    We have this large enterprise system we purchased and are having performance issues when it comes to the database. One thing in particular about this DB is that it is very normalized. Currently there are over 12,000 tables. To bring things back into a comprehensible state, they use 2100 views. Only the direct updates to the DB take place at the table level. We're also at around 160 GB for the full DB.

    The main data-mining piece for users is their "query tool". All the views are registered with the system and a hierarchical relationship is defined. This query tool works with that and presents the user a simplistic way to build out queries and gather information. But, the SQL it produces is less than stellar most of the time. A lot of my job is spent reworking the SQL it produces and finding some way to present that to a user. I'm a programmer, and my query skills only go so far. My DBA skills go even less. I've come to a place where I am stumped.

    Here is a query that it produces:

    Code:
    /* works somewhat quickly, 522 rows in 30 seconds. */
    
    select distinct 
          [CONSTITUENTGROUPS].[KEYNAME] as [Household name], 
          [CONSTITUENT].[LOOKUPID] as [Lookup ID], 
          [CONSTITUENTMARKETING].[NAME] as [Name], 
          [REVENUE].[LOOKUPID] as [Revenue ID], 
          [REVENUERECOGNITION].[TYPE] as [Revenue Type], 
          [REVENUE].[AMOUNT] as [Amount], 
          [REVENUE].[DATE] as [Date], 
          [REVENUE].[TRANSACTIONTYPE] as [Transaction type], 
          [REVENUESPLIT].[APPLICATION] as [Application], 
          [CONSTITUENTMARKETING].[CONSTITUENTID] as [QUERYRECID] 
     from [V_QUERY_CONSTITUENTMARKETING] as [CONSTITUENTMARKETING] 
          left outer join [V_QUERY_CONSTITUENT] as [CONSTITUENT] on [CONSTITUENTMARKETING].[CONSTITUENTID] = [CONSTITUENT].[ID] 
          left outer join [V_QUERY_CONSTITUENTGROUPMEMBERSHIP] as [GROUPMEMBERSHIP] on [CONSTITUENT].[ID] = [GROUPMEMBERSHIP].[MEMBERID] 
          left outer join [V_QUERY_CONSTITUENT] as [CONSTITUENTGROUPS] on [GROUPMEMBERSHIP].[GROUPID] = [CONSTITUENTGROUPS].[ID] 
          left outer join [V_QUERY_REVENUERECOGNITION] as [REVENUERECOGNITION] on [CONSTITUENTMARKETING].[CONSTITUENTID] = [REVENUERECOGNITION].[CONSTITUENTID] 
          left outer join [V_QUERY_REVENUESPLIT] as [REVENUESPLIT] on [REVENUERECOGNITION].[REVENUESPLITID] = [REVENUESPLIT].[ID] 
          left outer join [V_QUERY_REVENUE] as [REVENUE] on [REVENUESPLIT].[REVENUEID] = [REVENUE].[ID] 
          left outer join [V_QUERY_CONSTITUENTGROUPMEMBER] as [GROUPMEMBERS] on [CONSTITUENTGROUPS].[ID] = [GROUPMEMBERS].[GROUPID] 
    where [CONSTITUENTGROUPS].[KEYNAME] like '%Reunion Committee%' 
      and [REVENUE].[DATE] between '2010-01-01T00:00:00.000' and '2010-12-31T23:59:59.997' 
      and [GROUPMEMBERSHIP].[ISCURRENT] = 1 
      and [GROUPMEMBERS].[ISFORMERMEMBER] = 0;

    In the above query, it works somewhat quick (compared to the rest of this product).

    There was one key piece left out of the above query. The query tool does a TOP 100 on it to show a preview. Putting TOP anything (except 100 percent) on here will essentially kill the SQL server. All resources pour into this and it never seems to complete.

    There are ideas being thrown around. One, indexes - either poor or missing. Second, fragmentation - either indexes or files. Third, physical file system is screwed.


    There are a couple of other things I tried to see if they had any meaning in identifying the problem.

    I tried isolating this into a CTE then putting the TOP on the select into that CTE. It still spins out of control.

    I tried dumping the raw query into a table variable. I didn't even get to use TOP on that one. It just spins out of control just going into a table variable.

    The same thing happens when attempting to dump the "pre top" results into a temp table or even a real table set up and put into the primary file group.


    I'm missing some necessary skills to debug this further and was wondering if anybody has any insight into this, like a gut feeling. We indexes on everything (the system automatically does that) but historically things have been a bit "iffy" with those, either too many or not enough. Also, I'm not even sure that this system is set up correctly. We've sent the vendor the database so they can debug some things on their servers and they always report back with, "we cannot reproduce the problem. It works OK here."
    Last edited by Greendot; 07-26-10 at 11:48. Reason: Didn't know what the code block did. Removed some comments around that.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If we debug this one query, will that resolve the issue, or will you then need to work on the others?

    I ask because the scope of this sounds beyond what can realistically be achieved on a forum and may require professional * help.

    * By professional I mean on site, in depth, paid for, as opposed to professionals giving up a bit of time over the internet to cover very specific issues.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    3

    Options of professional help

    I'm in the process of really pushing hard to get a "Guru" on site to look at our system. Our current "DBA" was assigned to us our IT office and is more of an NT admin and everybody is starting to realize something is wrong.

    So if it does start pointing to the fact that our database needs some medical attention by a skilled professional, my boss will probably be OK with that.

    I'm not the one with the checkbook, only the guy who's been sent to "figure it out".

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You have a non-SARG-able condition in the where clause.
    Code:
    where [CONSTITUENTGROUPS].[KEYNAME] like '%Reunion Committee%'
    This is effectively like asking somneone to go through a phonebook and look for anyone with the letters "SON" in the name. So Albertson, Robinson, Swanson, Sonntag, etc.. Needless to say, this will take time. Any way they can remove the first wildcard, so it reads like this?
    Code:
    where [CONSTITUENTGROUPS].[KEYNAME] like 'Reunion Committee%'

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We would also need to see all the view definitions. It might be that they contain SQL that results in scans (as MCrowley has shown) too.
    Also all the relevant the query plans.

    Is the DB 160GB used space or is that the file size? How much RAM? Is this 64 bit? Was this database upgraded from SQL 2000?

    You should account for fragmentation as part of your standard maintenance.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Posts
    3
    I just tracked this one particular problem down to a calculated field. The NAME field was doing some calculation against another table that has 8 million+ rows. I take the NAME field out, and TOP 1 returns in 5s.

    So, now my question is not so much about this specific query, but more now towards what would cause the database to spin out of control when TOP is used?

    It returns 522 rows w/out TOP. Even if I say TOP 600, it spins out.

    What would be causing the slowdown to occur when dumping the regular 30 second "non top" query into a table, temp table, or table var?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    One thing you can do to look into the differences between the two plans is to examine the query plans generated by the two queries (with and without TOP). Management Studio will give you a nice graphical depiction of the query plan. If you want to post it, it may be best to use SET SHOWPLAN_ALL ON, then run the query (which will only show the plan, not run the query).

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    TOP implies ordering, meaning that the whole result set (every row) must be returned and sorted before a single row can be sent to the client. That tends to make TOP expensive in terms of time and resources.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, a few of the outer joins are being turned back into inner joins, is that really what is wanted. This type of SQL can, also, be a big contibutor to longer run times. What I mean is that you have predicates in the WHERE clause that are for tables where you have LEFT OUTER JOIN. If you really want an inner join, then state so. If you really want left outer joins, then move these conditions to the on clause. Either way it makes a real difference in what data is returned by the query.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •