Results 1 to 13 of 13
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: Questions about SQL server hardware config

    Hey guys,

    I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.

    Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.

    I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.

    I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?

    I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?

    I would love to hear what you have to say and suggest. Thank you in advance!

    Chris.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Hard to say with the information given. What is the current box constrained by? Is the CPU railed? Is memory paging out at unimaginable rates? Is the disk spinning off its spindle?

    Probably the bigger question is do you want to go to a 64-bit architecture, or can this live on 32-bit architecture. To answer that question, we would need to know how large the database is.

    All of this is somewhat secondary to the primary question which should be asked. Have you tuned the queries that are being run? Are the tables properly indexed? I am sure that any one of us could come up with a 1 GB database that would not be able to support 20 users on any hardware platform.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Oh, and SQL 2005 should be the way to go, as support for SQL 2000 will be dwindling soon. SQL 2008 is almost around the corner, you know.

  4. #4
    Join Date
    May 2007
    Posts
    3
    I wasn't aware of SQL Server 2008. To answer your other questions;

    First - performance - the CPU usage is 65-80% constant when I am running queries, memory usage is pretty low (although the SQLServer.exe process is using 1GB physical and 1.5GB's virtual) and the hard drives are 100% usage.

    Second, I'll come back to the 64-bit question at the end, but the database is 10GB in its early stages. I am expecting it might grow up to 100GB's.

    Third, yes, the queries are tuned and indexes have been created on all necessary tables.

    As for 64-bit, I have read some of the documentation that Microsoft has put out on the advantages of 64-bit over the wider accepted 32-bit. Have you had any real world experience in a 64-bit SQL Server environment? If so, what were your impressions of it?

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by MCrowley
    Oh, and SQL 2005 should be the way to go, as support for SQL 2000 will be dwindling soon. SQL 2008 is almost around the corner, you know.
    Probably a good topic for a separate thread. But what are your expectations for SQL 2008? I'm think it's going to be something like the transition from SQL 7.0 to SQL 2000 (ie, not a huge difference between SQL 2005 and SQL 2008).

    What do you think...?

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Of course there will be some differences between 2005 and 2008, but 2008 is considered a minor release. Most of the differences appears to be in the BI part of the product, but we are also expecting some nice features in the database Engine. Among those are the MERGE INTO command, datatypes for only data and only time, not to mention more complex datatypes (in 2005 the only "complex" datatype is XML).
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Personally, I am expecting something similar. Perhaps SQL 2005.5 would be a more accurate name, but the marketing guys must have objected to it for some reason. Not being a MS employee, or even an MVP, I have no way of saying that for sure, so be sure to read the disclaimer.

    Back to the original question. This thing is racking up 60 - 85% CPU with just 5 users? If there is a lot of aggregating of data in the average query, you may want to consider using Analysis Services to take some of that sting out. If it is not that, are there many joins among the tables? DBAs like me may not like the look of it, but denormalizing can have a beneficial effect on performance.

    I have not had the opportunity (or test box) to play around with 64-bit SQL Server myself, but from all I have read, it acts very much like 32-bit SQL Server, just with twice as many bits in the background.

  8. #8
    Join Date
    May 2007
    Posts
    3
    Hey MCrowley,

    Here are a couple samples of queries running;

    This one is a summing each column on almost 500 000 records:
    SELECT
    custNameShipTo,
    sum(netRev*(1-abs(sign(slsYear-02)))) as Rev02,
    sum(netRev*(1-abs(sign(slsYear-03)))) as Rev03,
    sum(netRev*(1-abs(sign(slsYear-04)))) as Rev04,
    sum(netRev*(1-abs(sign(slsYear-05)))) as Rev05,
    sum(netRev*(1-abs(sign(slsYear-06)))) as Rev06,
    sum(netRev*(1-abs(sign(slsYear-07)))) as Rev07,
    sum(netRev*(1-abs(sign(slsYear-03))))-sum(netRev*(1-abs(sign(slsYear-02)))) as Var03,
    sum(netRev*(1-abs(sign(slsYear-04))))-sum(netRev*(1-abs(sign(slsYear-03)))) as Var04,
    sum(netRev*(1-abs(sign(slsYear-05))))-sum(netRev*(1-abs(sign(slsYear-04)))) as Var05,
    sum(netRev*(1-abs(sign(slsYear-06))))-sum(netRev*(1-abs(sign(slsYear-05)))) as Var06,
    sum(netRev*(1-abs(sign(slsYear-07))))-sum(netRev*(1-abs(sign(slsYear-06)))) as Var07
    FROM tordHistESF
    WHERE slmno = 'gSlmno' AND FLBL LIKE 'gFLBL%' AND txRef LIKE 'gtxRef' AND origin LIKE 'gOrigin'
    GROUP BY custNameShipTo
    ORDER BY 'gOrder' gDir

    This one is calculating based upon field values:
    SELECT custNameShipTo, slmno, sum(netRev*(1-abs(sign(slsYear-02)))) as Rev02, sum(netRev*(1-abs(sign(slsYear-03)))) as Rev03, sum(netRev*(1-abs(sign(slsYear-04)))) as Rev04, sum(netRev*(1-abs(sign(slsYear-05)))) as Rev05, sum(netRev*(1-abs(sign(slsYear-06)))) as Rev06, sum(netRev*(1-abs(sign(slsYear-07)))) as Rev07, (sum(netRev*(1-abs(sign(slsYear-03)))))-(sum(netRev*(1-abs(sign(slsYear-02))))) as Var03, sum(netRev*(1-abs(sign(slsYear-04))))-sum(netRev*(1-abs(sign(slsYear-03)))) as Var04, sum(netRev*(1-abs(sign(slsYear-05))))-sum(netRev*(1-abs(sign(slsYear-04)))) as Var05, sum(netRev*(1-abs(sign(slsYear-06))))-sum(netRev*(1-abs(sign(slsYear-05)))) as Var06, sum(netRev*(1-abs(sign(slsYear-07))))-sum(netRev*(1-abs(sign(slsYear-06)))) as Var07 FROM tordHistYTD WHERE custNameShipTo LIKE '%%%s%%' GROUP BY custNameShipTo, slmno ORDER BY '%s' %s", $gcName__rsRev,$gOrder__rsRev,$gDir__rsRev

    These are probably two of the less demanding queries running against the database. So you think that the Analysis services would improve performance?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Difficult to be precise about exactly what is the cause of it but we went from n 100GB+ databases in SQL 2000 to SQL 2005 64bit and found that the new engine was a memory hog. We ended up quadrupling our memory on all servers. We have realised real improvements though.

    If you are upgrading make sure you do some research, test & test before going to prod and read the below:
    .....
    Oops - I have is bookmarked at work I will post tomoz.
    {EDIT - found it! http://rentacoder.com/CS/blogs/real_...04/28/477.aspx}

    heh heh - wasn't Merge part of the 2005 beta? Why did it get dropped?
    Last edited by pootle flump; 05-23-07 at 16:13.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL 2008 - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81260

    Note that a few MVPs, ex-MS employees and current MS employees wade in.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    SQL 2008 is almost around the corner, you know.
    I can't help but feel that it will be round the corner for a little while yet.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by frozenf2
    These are probably two of the less demanding queries running against the database. So you think that the Analysis services would improve performance?
    If those queries are representative for the tasks you're doing, you should definitely have a look at analysis services. You might end up realizing that your server is powerful enough.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ... finally (because I don't think it is mentioned in the link) - make sure you limit the max memory SQL server can use when you upgrade. Something between .5 - 1 gig reserved for the OS to use.

    ... and finally (2):
    fancy posting ddl for tordHistESF and tordHistYTD?
    Code:
     
    WHERE slmno = 'gSlmno' AND FLBL LIKE 'gFLBL%' AND txRef LIKE 'gtxRef' AND origin LIKE 'gOrigin'
    based on the values, these look like the fields are not too selective. Correct?

    Code:
     
    WHERE custNameShipTo LIKE '%%%s%%'
    No need for so many %'s. Also, the second query will ALWAYS scan the whole table irrespective of indexes. You know that and cannot restrict the return any better right? Or is tordHistYTD a view?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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