Written by: admin
Tuesday, July 15, 2008 

THE DCP
The DCP Will add to workload but I have optimised code so that the load will be small.
There are about 12 queries that will run every 60 seconds or when a new patient is opened. Each query runs on a new connection and lasts a few miliseconds. Since the queries are the same (unless a new patient), SQL Server will usually return cached information from memory and no disk access is required. A few kilobytes transferred in this way will not noticeably affect SQL server performance. You could run hundreds of DCP's with minimal performance degradation on your overall system.

The statistics form is not able to be optimised and runs thousands of queries. It will function like a stress test on your server. If you want to see if there is any degradation of your system due to SQL Load then try the statistics while opening patients in MDW. I have noticed minimal impact.

Most degradation in performance arises from MDW  running on a slow machine OR  'RUNNING MULTIPLE SESSIONS OF MDW IN TERMINAL SERVICE MODE , ALL CONNECTING TO THE ONE SERVER, WITH THE SQL SERVER INSTANCE BEING HOSTED ON THE SAME TERMINAL SERVER'. This will kill the server performance faster than an electrical 'spanner in the works'. If you think that having a faster server with a bucket load of memory will fix this , think again. Distributing load over several Windows Machines is vastly superior.

As a minumum - Separate the Terminal Server from the SQL server instance on different machines. (The SQL Server instance can be run on Windows XP).

If the load is high on the terminal server (running more than 5-10 Sessions concurrently), and the Terminal server has 3Gig RAM, then add another machine as a terminal server.
Also try to reduce the amout of ram required by each terminal server user - limit number of programs open concurrently, dont use memory intensive applications(other than MDW).

Adding another Terminal Server means adding another Windows server box configured to act as a terminal server. There are load distribution mechanisms for Terminal server that I have not had the displeasure of having to configure so I cant offer experienced advice here.

Theoretically, changing to the full SQL server will probably never become an issue for you unless you approach the limits of memory or file size.



FROM THE MICROSOFT PROMOTIONAL BLURB

THERE IS NO WORKLOAD GOVERNOR IN SQL SERVER EXPRESS EDITION.
SQL Server Express uses the same reliable and high-performance database engine as the other versions of SQL Server 2005. It also uses the same data access APIs such as ADO.NET, SQL Native Client, and T-SQL. In fact, it is differentiated from the rest of the SQL Server 2005 editions only by the following:

* Lack of enterprise features support
* Limited to one CPU
* One GB memory limit for the buffer pool
* Databases have a 4 GB maximum size

SQL Server loves using memory and can cache more data if you throw in more memory. If you want faster SQL Server performance this is generally the way to go(Allows serving cached information , obviating frequent disk access). SQL Express cannot use more than 1 gigabyte, however this is more than adequate for the type of data accessed by MDW(i.e. sequential access to individual patient information).
If you use the full version SQL server, it could theoretically cache the entire database in memory, (all information for every patient). You would need ~4-16Gig RAM (only possible in the 64 BIT Edition of WINDOWS SERVER/SQL SERVER). But this will NOT provide any realised benefit for typical workstations using MDW. For high performance web servers and stock market applications this can be a bonus.

Trackback Print

Tags:

Categories:

Location: Blogs All Categories DCP Development