SQL Help

it_consultant

OK, I am not a DBA, but I am also not a total noob at MS SQL either. We are putting in a new EMR / EHR system at my client which has 2x apache tomcat based app servers (hardware load balanced) that talk to a MS SQL database backend (2 node SQL cluster) for about 100 concurrent connections. We had a problem with one of the app servers which the vendor fixed. Then they told us to do this:


- Set sql server memory in such a way that OS has some memory for its own processes. (Atleast 4GB for OS)
- set “optimize for Ad hoc workloads” to “true”
- set ‘Max degree for parallelism” to “1”
- Set parameterization to "true" for each database.
- Disable Hyperthreading on each cluster node
- Set power options to high performance on each cluster node
- Set TCP chimney offload to disabled on each cluster node

Our database servers are half height dell blades with 2x xeon 6 core processors and 48 GB of memory. The server doesn't breath hard all day. Will those things truly do anything for us?

RobertKaucher

A lot of the answers here will be it depends. I would expect that their engineers would research this and test it. However one of these things make me uncomfortable... Max Degree of Parallelism to 1? "

To suppress parallel plan generation, set max degree of parallelism to 1." Hyperthreading off, ok I get that. Setting the memory for the OS to have some room to breathe, sure, sounds fine. Just don't forget you set this if you ever add more RAM. I can only assume they have a reason for turning off parallelism. Sadly all I can say is that it really just depends on how the DB is designed. A poor data model can cause vendors to try and do voodoo to optimize the system... But in general I think most of that is likely fine getting rid of HT and setting the memory are pretty common. But I would probably just do 2 GB for the OS, if these are just running SQL and nothing else.

it_consultant

So with 48 GB of memory and about 11 GB used right now (under regular load), are any of those things going to help? I feel like they have just copied and pasted their normal "make it faster like this" recommendations. Oddly enough, the original problem was with one of the app servers not displaying medications correctly. The other app server worked fine, they both connect to the same DB server so...

erpadmin

What version of SQL Server are you using (and edition)? Can I assume SQL Server 2008 R2 Enterprise?

Also, while CPUs/RAM is very important (and sounds like you have plenty of both), your storage/RAID configuration/type of disks are just as important? What's the storage looking like? Are you on RAID 10, RAID 5? What speed are your hard disks?

Sounds like your original problem wasn't with the database at all. I deal with the same type of issues myself, and was the one who spec'ed out my current production infrastructure. We originally were on a SQL Server 2005 cluster, and then in-place upgrade to 2008 R2 (we had to do it that way, because 2008 R2 at the time we upgraded our infrastructure was not certified by Oracle for PeopleSoft. When I saw that the 2005 exams were being retired, that was clue number one that 2005 was going to be EOLed. I had to test the inplace upgrade twice, and in all of my years, that was the smoothest upgrade ever.)

In any event, sounds like your hardware is fine, but I was just curious about the storage.

it_consultant

Yep, its a 2 node SQL 2008R2 Enterprise edition. I think it is a RAID 50 SAN - the shared volume is 15K SAS and the SAN fabric is 10GB iSCSI.

erpadmin

it_consultant wrote: »

Yep, its a 2 node SQL 2008R2 Enterprise edition. I think it is a RAID 50 SAN - the shared volume is 15K SAS and the SAN fabric is 10GB iSCSI.

Very nice. Yeah, if something is running slow on the database, you're gonna wanna look app server logs (if this application writes them) and then look at what tables/queries are being run against the database [via SSMS]. That's typically what I'd do to determine "why the system is slow." Most times, it's a query that shouldn't be run during work hours. (We typically run into this during the beginning of the semester...especially if it coincides with a payroll week.)

nycid

I'm not a SQL guy by any means but have a solid understanding of the many EMR/EHR out there from Centricity, Allscripts EPIC, Intergy (Medical Manger) etc. etc.... the one thing I have come across is that when I am working with the Centricity clients (GE) there seems to be a memory leak related to SQL where even if the server is just sitting there it will take the full allotted amount of RAM that was given. After many server builds it was brought to my attention anyway that this was a norm..

Also many of the vendors only spec base I would double as much as you could if your at 100 users now with all the incentives the government is pushing for and the providers wanting their piece of the pie you will out grow it in a short amount of time. I have seen it many times. Mainly in the Allscripts side of things.

RobertKaucher

it_consultant wrote: »

So with 48 GB of memory and about 11 GB used right now (under regular load), are any of those things going to help? I feel like they have just copied and pasted their normal "make it faster like this" recommendations. Oddly enough, the original problem was with one of the app servers not displaying medications correctly. The other app server worked fine, they both connect to the same DB server so...

Based on what you have said I think they were blowing smoke... I would also say that the issue is likely at the application level or less likely with the data model. And when ERPAdmin says the problem is not with the database he means with SQL Server. The database could be a piece of crap with no indexes or physical keys like so many poorly done backends I've seen.

it_consultant

nycid wrote: »

I'm not a SQL guy by any means but have a solid understanding of the many EMR/EHR out there from Centricity, Allscripts EPIC, Intergy (Medical Manger) etc. etc.... the one thing I have come across is that when I am working with the Centricity clients (GE) there seems to be a memory leak related to SQL where even if the server is just sitting there it will take the full allotted amount of RAM that was given. After many server builds it was brought to my attention anyway that this was a norm..

Also many of the vendors only spec base I would double as much as you could if your at 100 users now with all the incentives the government is pushing for and the providers wanting their piece of the pie you will out grow it in a short amount of time. I have seen it many times. Mainly in the Allscripts side of things.

We are migrating from Advanced MD to ECW. Centricity and EPIC are too big for my client (they told us we were too small) and Allscripts didn't make the cut and neither did NextGen. ECW is pretty cool but all of their engineering and support is India based and they are VERY hard to understand.

Home - eClinicalWorks
Medical Billing Software, Cloud-based ONC-ATCB Certified EHR & Practice Management Software from a Trusted Name ADP AdvancedMD

erpadmin

RobertKaucher wrote: »

And when ERPAdmin says the problem is not with the database he means with SQL Server. The database could be a piece of crap with no indexes or physical keys like so many poorly done backends I've seen.

Thanks for the clarification, that's exactly what I meant (in it_consultant's case, the database level.) I have dealt with customized queries from my own database that was at the database level of the n-tier app that I had to work with the developer on to fix. Other times, it was user error (which caused millions of dollars in loans to not be disbursed...that was certainly my coup de grace that I like highligting on interviews because the user who caused the error blamed IT for it [and by blaming IT...this user was blaming ME...the look on this user's face when we ascertained she committed the same mistake she had been reprimanded earlier for (unbeknownst to me, at the time) was priceless. ]

Working on n-tier applications is great. From reading this thread, I can see that electronic health care records are no different than ERP support, since EHRs are also based on the n-tier model.

qcomer

Sounds to me like some horrible programming maybe as mentioned above or the issue is at the application level.

Your SQL server sounds pretty darn adequate, it sounds like they dont know what the issue is or they do and theyre trying to play it off like its something on your end. I would have the issue escalated to a developer or programmer on their end rather than their technical support. Most vendor tech guys for programs that run on SQL just repeat what they are told and dont even understand what it means..."but it worked one time...." lol

I run several SQL applications (on their own servers, virtualized in fact writing to iSCSI SAN) that thousands and thousands of people read and hundreds of people write to everyday on much less configurations without issues. Everything from transportation software, student information systems, library software, etc.

nycid

ECW is OK... your right the support aspect of it sucks however. Not sure what your timeline is for training/implementation but whatever you do do not let the trainer get out of your sight until you have everything in place that includes templates and setting up labs etc. If they say I can do this next week while I am at my office make them do it there. Once they leave all you hope rely on the support in India and this is where 90 % of the trouble is and will cause nothing but issues and aggravation then the providers will turn and then it will be a lost battle. I have taken over sites that blame it on the IT guys (local) and explain its the support and try to give a sense of expectations as to not get a bad rep for the company.... prepare for 24-48 hours too for any response to your questions....

Good product for the $$ however

it_consultant

It is funny that you mentioned templates (for letter merges), we spent a day trying to figure out why half of the computers weren't launching MS word properly. After hours on the phone trying to explain that Word 2007 wasn't really a new version of word and it should work fine, we discovered that the person who developed the templates used the wrong "letter tags". Not REALLY her fault, the bad letter tags were available and no one told her she couldn't use them.

I had a similar experience with support in this last case - since one app server was working and the other one wasn't our providers attempted to put the blame on me. Years of consulting experience lets me dodge this crap easily. I told them the hardware was fine (both app servers are on the same ESX host so no its not hardware) and that the problem was most likely the tomcat configuration. Which it was...and they fixed the tomcat server, then told me to disable hyperthreading on my SQL server...