SQL Server Performance

Is 64 bit reliable?

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by vich, Aug 2, 2007.

  1. vich New Member

    Upgrade time. Considering going 64 bit Windows 2003 + 64 bit SQL Server 2005 (Standard) on a single quad-core IBM box with 8GB RAM. I'm concerned that 64 bit SQL Server is too new to trust.
    My alternative: Get 32 bit versions that allow AWE Memory on the same box.
    Suggestions? Advice?
  2. thomas New Member

    You have to be a bit careful with 64 bit Std Edition because the Lock Pages In Memory right only applies to Enterprise Edition (this is the 64-bit equivalent of AWE, essentially). But you can still fix the memory for 64-bit Std Ed.
    I have found it to be equally as reliable as 32-bit. 64 bit isn't that new either, it's been around since 2000.
  3. vich New Member

    Sorry - I only started exploring this a few days ago and am still not well informed about version differences.
    Are you saying SQL Server Standard written for 64 bit, running on 64 bit, still has a 4GB barrier?
    My database is very small (2GB), however it should grow about 2GB per year with the new app being installed. Still small. On a dedicated database server - I wouldn't expect to require a "keep pages in memory" feature to see vast improvement on 64 bit, but that expectation stems from my (naive?) assumption that anything that's 64 bit native won't have a 4GB barrier (really, 3GB barrier).
    I was only expecting to ever upgrade to Enterprise Edition if I had to pass the imposed 4 CPU limit on Standard.
  4. satya Moderator

    I don't see the database size is not that huge and having the increase of 2gb per year can be handled with 32 bit of SQL server anyway. Unless the application or any other requirement I wouldn't suggest to go on X64 bit in thsi case.
  5. thomas New Member

    There are supposed to be some potential performance benefits to using 64-bit but I don't know of any definitive proof of this.
    There is no memory limit for 2005 Std Ed, it is just the OS limit.
  6. vich New Member

    I've read that AWE Memory capability is a special 36 bit memory feature available in the more advanced versions of MS Windows (not the Standard). Also; that it is not a feature available to SQL Server 2000 or 2005 Standard editions. Is this true?
    I understand that the AWE portion of memory isn't usable for some purposes so the benifit is limited, but logic dictates that any time you can use memory instead of disk, it's going to be a lot faster.
    Our 2GB database currently just fits into RAM so my thoughts are for expansion purposes. Also; the application is processing intensive.
    I do believe that if I knew more about DBA stuff, I could tune things to get more from the current setup. In our existing ERP system, 20% of our users initiate background jobs like accounting and sales report that can take up to 1 minute to calculate because it sifts through all data. If I made a replicate database that we could run reporting against, I wouldn't see us needing an upgrade for a long time. As it stands, we're hitting the wall now, without an MRP system.
    Along comes MRP, in particular it's Assembly Build process. I expect it to build about 800 factory line items each hour and that will take about 2 minutes of intensive CPU, intensive Temp Table usage, many threads, tons of index lookups (10s of thousands). It's a noticable drag.
    if I could specify that this hourly Asyncronous (nobody's actually waiting on it) BOM process could run at a lower priority then I could avert my maxed out processing concerns. Does someone know if that's a capability of SQL 2000 or 2005?
    What we have here is that our home-grown system isn't very scalable. It does a LOT of full data summaries. For example, every Sales Transaction (ever) is added together with payments to arrive at a current AR Aging total, rather than having some daily / annual summary tables for including the historical information. It works great when you only have 1000 customers. I'm throwing hardware $$ at the problem.
    Off topic, I know, except as concurrence that I going with 64 bit or AWE isn't the solution I'm looking for, except perhaps to postpone the symptom.
  7. satya Moderator

  8. thomas New Member

    Setting up a replicated copy of the database for reporting sounds like a very good idea, to keep the load down on OLTP system.
    There is no way in either 2000 or 2005 to make a specific process have lower priority. You can limit processes to only use 1 processor using the MAXDOP query hint, but that's about it.
  9. vich New Member

    Thanks Thomas. Gosh; I wish I could run a performance monitor tool for a day, send the results off, and have someone tell me the real problems. Or better; an automated tool so I could rerun it and chip away at the problems. Sigh - I feel like a real flip-flopper who can't seem to nail down the real solutions.
    The the deeper I dig, the more solutions I find. I don't think there's a magic bullet but after a week of intensively hitting the forums, links, and white papers one thing stands out. There are people with not much more hardware than us, on the same OS / SQL platform, running FAR more intense applications and supporting far more users.
    BTW: Thanks for the Processor Limit tip.
    I like the sounds of mirroring since it adds a level of failure resiliance besides performance. we have a "Standby Server", but it's more a spare machine fast enough to run production in a pinch that has the log files copied to it every half hour, plus a full DB backup every day. Not even proper mirroring. A recovery would require a restore plus we would loose data (ie: orders).
    RE going 64 bit: It's just one solution but seems inappropraite for us. I just figured that since we're getting a new server anyway, and the x64 version is about the same price, why not go that route if it's not bleeding edge stuff. When we grow to 10GB or more, it's sure to come in handy.
    I now believe the larger problem lies in misconfiguration in the form of: (for shame)
    • Inappropriate indexing. Example: I was missing a CustomerID index on a 500,000 row Customer Orders table. Adding this changed the Order Confirm process to work in a blink rather than 2 or 3 seconds (it checks recent orders for duplicates). Example: Some smaller tables lacked indexes, yet adding them (even though very small tables of 200 rows) improved it a lot. Example: Making the Line Item index Clustered improved some queries noticably!
    • SQL memory was set to 2.4GB (3.5 on server). However, the +3GB switch not set. I don't understand the ramifications of this (like, did it simply ignore the +2GB portion), but when the system would grind to where users noticed huge slowdowns (the crux of our problem), the SQL Server process was invariably taking way more memory than normal. I've changed it to 1.8GB and will monitor and learn more about reading Page Fault / Memory statistics.
    • I discovered that I'd never moved the log files to the other physical volume (meant to 2 years ago, sigh). I still don't know how to do it, but it's necessary.
    • I'm running Symantec Antivirus on that machine. I was running an older version known to consume (even halt) slower systems. In a single 12 hour period, it logged 3.9million reads - during the same time SERVER.EXE logged 400,000. Further; I'm learning that most DBAs don't even run virus checking. At the least; I could get one that's far less intensive (like ESET NOD 32). Or perhaps just exclude the 3 DB file extensions. That is; after I move the web server off that box and uninstall ASP. I'll also collect more advice on what else to disable or software firewalls to implement to further secure that server (Win 2000 Std).
    • We're only running 10,000 RPM drives even though we have a fairly high end RAID card. Seems wasteful. Also; the RAID 5 only has 3 volumes but we have another slot for 4. So $3000 in hard disks + moving the log files could avert the server upgrade.
    • We're still running the web server off this server. I'm learning what a no-no that is. It runs an older Crystal Reports, and that's a memory leaking hog by itself (I've moved that off to another server now, at least). Note: Most often; our "performance problem" is actually a memory starved web server and resetting the ASP service clears it up. However; we do run a 2nd web server on another machine and occassionally it's equally slow - pointing to the database engine (or the OS it runs on).
    So in short; I'm no DBA rather a knowledgable and busy programmer with some OS and networking experience in a 1-man-shop. It shows.
    It's been fine since we're so small but replacing hardware and getting Enterprise Grade stuff capable of running 20 times our load isn't going to fix the underlying performance issues - rather fix the symptom. Upgrades could be limited to replacing HDDs with 15K RPM, bumping the memory to 4GB, and making our current production server the new Standby and getting a modest replacement (low end IBM Quad core with 2 internal drive arrays). That seems like a no-brainer, but spending $40K on a smoking new server with all 64 bit software just seems wasteful for a single 2GB database supporting maybe 50 simultaneous users.
    Thanks for all your help.
  10. thomas New Member

    Mirroring could be useful for you. If you get 2005 Enterprise Edition, you can make database snapshots of the mirror database and use this for reporting. This saves all the hassle of replication. Enterprise Edition is expensive though.
    Just looking at a few of your points..
    Indexing is one of the key things to get right for your application, but it takes a lot of work, diagnostics & testing. 3rd party products can be helpful but with a bit of extra work you can use SQL Server tools like Profiler, Management Studio (eexecution plans), Index Tunign Wizard, etc., to find this stuff out.
    If SQL Server is taking too much memory (and your server is a web server too!), you probably will need to limit SQL Server's max memory as you have.
    You MUST move the transaction log files to a separate volume - this is VERY important. To do this, you need downtime. You need to detach the database, move the log files, then reattach the database with the new log file location. Ensure you have a full backup first.
    Get rid of Symantec, at the most, run a scan of the drives on your SQL Server from a remote server.
    Get rid of the web server. Even if it's just as slow on another one, the SQL server itself will perform better because of less resource contention (e.g. it'll be able to use all 3GB of RAM without grinding the server to a halt.)
  11. vich New Member

    Up late too? This information gathering is addictive.
    Yeah; I think this is a pretty good hit list to begin with. I'll do the Symantec last however since I need to implement security measures first.
    RE: Mirroring and Enterprise Edition. I don't understand the pricing. These guys: http://www.viosoftware.com/SQL Server/ are selling SQL 2000 EE for only $9K, where I keep hearing it's $19K. Also; if I get a new server; the MS multi-core licensing policy sure makes quad core sensible (ie: 4 processors for price of 1).
    I need to explore how a mirrored license works since I believe a replication DB doesn't require it's own license because it's just a glorified backup (or at the most, a 5CAL) yet a mirored one can't make that claim. Anyhow; I've got a call scheduled with the MS rep at Tech Depot tomorrow - but experience dicatates I shouldn't believe the spoken word and need to dig out the licensing agreements on this one.
    RE: Disconnecting the DB. I can practice on my Standby tomorrow. That should be an easy, no brainer improvement. I know head thrashing can be surprisingly crippling ... I was shocked to discover last week I had failed to implement this 2 (or was it 4) years ago when this server went live. I suppose performance isn't an issue until it's an issue. Sort of like backups and security, lol.
  12. thomas New Member

    No I'm not up late, I'm in the UK. I don't answer forum questions when I'm meant to be sleeping!
    Mirroring is only in 2005 so the pricing for 2000 EE doesn't apply. An advantage of mirroring is that you don't have to license the mirror server. This isn't the case with replication.
    The difference in price you are talking about may be the difference between 2000 and 2005, 2005 is quite a bit more expensive.
  13. vich New Member

    I see. So with Mirror, you just have to pay for the additional processor(s) if that's how the licensing goes. That makes sense, since it's sort of a poor-man's cluster (and I say that from a standpoint of complete ignorance, lol). Makes one wonder if you can take a 1 processor SQL EE license and add a 5CAL license for the mirror - since the mirrored server may well have 2 processors yet only a few actual users, or even just background processing.
    That whole area leaves me out of my depth as far as backup and recovery ramification for when the mirror breaks or a primary-switch is needed. I used to do server support (back in the Novell-is-King days) and breaking mirrors was always tricky business and an area that has true potential to F everything up if your don't completely understand it. Not understanding some DB performance issues has been a little painful, but not crisis material.
  14. vich New Member

    Thanks for responding Satya. I'm not sure what you're referring to when you say "Used to be in ...". Are you referring to x64 reliability?

Share This Page