Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Hello - DBserver runs slowly, finding the bottleneck, tips?

    Hello

    A DB-server is now running slow, even now when the summer holidays are here and only 10% of the employees are at work.

    I am trying to find out where/what the bottleneck is, CPU, Indexes missing, badly written queries, memory, disc.

    A few images:
    CPU
    http://s22.postimg.org/swhzsvu1d/Performance_CPU.png

    Performance
    http://s24.postimg.org/7sq3xtalx/Performance.png

    SQL-profiler
    http://s18.postimg.org/k2wudtquw/Profiler.jpg
    Note: The longest running query, is an UPDATE STATISTICS, which is ran each night, so I think I'll ignore that. Same goes for the "BACKUP DATABASE" which is ran each night too. I've ran the profiler over night, and theres at least 200 queries that takes more than 5.000, and as you can see in the picture, there has been about 25 queries that takes 40 seconds each.

    One query that runs for a long time is really long (19561characters), heres a chunk of it:
    Code:
    p_executesql N' SELECT t22.*, t24.[MetaInfo] AS c50, 
    t23.[ntext4], t23.[ntext1], t23.[ntext2], t23.[ntext3], t23.[ntext5], t23.[ntext6]
    FROM( 
    SELECT DISTINCT t1, t2...t3...
    FROM 
    (
           SELECT TOP 2001 t1.Type, ....
           CASE WHEN...
          FROM UserData
          INNER MERGE  JOIN Templates AS t1 WITH(NOLOCK) ON
          ...then a bunch of joins, joining all the tables, 31 tables...
    )
    )
    1. would this "structure" relate to things being slower? Ehm, I do know that selecting tables with a sub-selection of tables which again has a sub-selection of tables merged on a bunch of tables with NOLOCK on all of them, depends on many factors, such as CPU, indexes, amount of rows in each of the tables, what they are joined on... But I've never seen the word "NOLOCK", but can understand it easily without even googling that it locks a table for writing, but unlocks for reading, so while one thread is reading the table, any other thread can also read the table meanwhile, so this can give a performance boost, for reading at least, any other negative effects? (Googling it atm ).

    Task-manager (IO, memory, ...)
    http://s24.postimg.org/kevxgwnp1/sqlserver.png

    Now I am sad to say, that I am not the one who created the database and the queries, and it contains a lot of data (its usage is: document-version-history-backup).

    2. Is there anything more I can do apart from contacting the ones that created this database?

    3. Found a few tables without any indexes at all, is this common? The tables do have a lot of data, some have about 20.000 rows, and it uses the "main primary key (document number)"... Is this common, a table without any indexing at all? Common as in: its not a mistake, but by design. (Myself is always having at least one index, no matter how the table looks like)

    SQL Server 2005, on Windows server 2003 R2

    Suggestions on what to check, where to check it are more than welcome.

    PS: I am well aware of that it "all depends" on factors like CPU, memory, disc, network, other tools running on the server, amount of data within tables, how long they are,indexes, clustered or not... It is just my little effort of trying to make myself a good reason for contacting the people who developed the database.

    PS2: Cannot take the server down to upgrade its memory/CPU, unless it is 100% guruanteed it will help. So...

    PS3: Getting so insecure when digging into a large database, that was created by an even larger company, by probably a lot of good developers over decades... So...
    Last edited by ManyTimes; 07-12-13 at 06:33.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ManyTimes View Post
    I am trying to find out where/what the bottleneck is, CPU, Indexes missing, badly written queries, memory, disc.
    Almost always the cause is poor indexing and poorly written queries, but people tend to want to throw hardware at problems first.

    Quote Originally Posted by ManyTimes View Post
    ...by probably a lot of good developers over decades...
    Let's not make assumptions.

    Quote Originally Posted by ManyTimes View Post
    Is there anything more I can do apart from contacting the ones that created this database?
    If they knew how to design a good application, they would already have done so.

    Quote Originally Posted by ManyTimes View Post
    Found a few tables without any indexes at all, is this common?
    Do they not even have clustered indexes/primary keys? Because that's pure amateur hour design.

    The query you posted looks as if it could be a real travesty.
    Any chance you could get a professional DBA to come in and review the entire database for half a day?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    >>Let's not make assumptions.
    Hehe... of course.

    I've sent a message to their support department, with a few images...and taking it from there.

    Thanks for your thoughts!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Developers frequently have poor knowledge of scalability.
    They develop their code using small datasets, so efficiency issues don't necessarily show up until the application is used by an actual enterprise-level organization.
    As far as developers are concerned, if the logic is correct than their feature is complete.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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