August 11, 2010 at 10:00 am
Hi
I work for the NHS and we have a server running Server 2005 R2 Standard x64 Edition on a Windows NT 5.2 (3790) PC. It currently has about 3 databases running on it and nothing else. Our clinical system's diary is taking forever to load up and change between dates, add appointments etc and our system support team have said it is nothing to do with the query times but is more likely a memory problem on the server (user query times in the diary are about 20secs and the query time using sql profiler was only about 2 secs).
On looking into the Task Manager on the Server the following settings were there:
CPU Usage fluctautes between 1 and 90% depending on usage
Total Physical Memory is 3931MB
Available memory 418MB (Has been as low as 171MB at peak)
System Cache 561MB
PF Usage is around 3.11GB
Some additional settings are:
No AWE
Min Server memory 0MB
Max Server memory 2147483647MB
Index Creation memory 0KB
Min memory for query 1024KB
Does any of this initially point towards why certain queries in our system should be painfully slow? Our 3rd party system guy thought available memory was too low and for diary queries we were probably using PF memory which is much slower. He thought available memory needed to be about 1-2GB.
Any suggestions as I am a bit lost with this?
August 11, 2010 at 4:42 pm
What does SQL Profiler tell you about the query? Use that and it will tell you what the query will do.
There is always room for improvement in a database. Do the column(s) that you are updating have an index on them? If so, and your update will involve many rows, delete the index and then create it after the query completes. You can do all if this in the stored procedure.
Run each stored procedure with Profiler running, and you will see all kind of things that will point you in the correct direction.
BTW, I have not heard of Windows 2005. Something I missed? I went from 2003 to 2008. Did you mean SQL 2005?
Why are you using a 64bit system?
Andrew SQLDBA
August 11, 2010 at 4:47 pm
I just noticed this in your post. The CPU goes from 1 to 90 percent. What else is running on that box? Is this a spike? or a constant? What is happening during the high spike times? What kind or processors are you using? What kind of drives? Did you purchase this box off the shelf from some company, and is this box meant for home use?
Are the databases sitting on a separate spindle? Are the logs and the data on the same spindle?
I have databases server running on an Intel P4 processor laptop that runs better than that.
Andrew SQLDBA
August 11, 2010 at 5:52 pm
chris.lawrie (8/11/2010)
user query times in the diary are about 20secs and the query time using sql profiler was only about 2 secs.On looking into the Task Manager on the Server the following settings were there:
CPU Usage fluctautes between 1 and 90% depending on usage
Total Physical Memory is 3931MB
Available memory 418MB (Has been as low as 171MB at peak)
System Cache 561MB
PF Usage is around 3.11GB
Some additional settings are:
No AWE
Min Server memory 0MB
Max Server memory 2147483647MB
Index Creation memory 0KB
Min memory for query 1024KB
Available Memory is misleading, SQL is memory hungry and will use all the memory it can.
Some questions:
How many CPUs?
How big are the relevant tables?
Is the application running on the same server as the database? This one is critical as you have SQL configured to use all the RAM, which means it can force the app out of the system. You probably want to limit SQL to 3GB (Max Server memory = 3000 MB) to leave 1GB for the OS and the App, since it sounds (between the lines) that the app may be struggeling.
I'd run profiler, see what queries are being run, run those queries in Managment Studio and see what is being returned. May be the app is ding a lot of filtering and other work that could be done in the database, but because of memory issues the app can't manage.
Without know a bit more of what's going on it's hard to say more.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 12, 2010 at 2:19 am
AndrewSQLDBA (8/11/2010)
What does SQL Profiler tell you about the query? Use that and it will tell you what the query will do.There is always room for improvement in a database. Do the column(s) that you are updating have an index on them? If so, and your update will involve many rows, delete the index and then create it after the query completes. You can do all if this in the stored procedure.
Run each stored procedure with Profiler running, and you will see all kind of things that will point you in the correct direction.
BTW, I have not heard of Windows 2005. Something I missed? I went from 2003 to 2008. Did you mean SQL 2005?
Why are you using a 64bit system?
Andrew SQLDBA
Our application guys say that the Profiler trace showed that the queries were only taking about 2 secs to run whereas the user experiences about 10-20 secs wait. They said that this indicated the issue was with server setup not the queries/tables themselves.
SQL Server 2005 R2 Standard x64 Edition - I think this is for 32-bit machines like the one we have not sure though. They may be using the wrong software?
August 12, 2010 at 2:30 am
AndrewSQLDBA (8/11/2010)
I just noticed this in your post. The CPU goes from 1 to 90 percent. What else is running on that box? Is this a spike? or a constant? What is happening during the high spike times? What kind or processors are you using? What kind of drives? Did you purchase this box off the shelf from some company, and is this box meant for home use?Are the databases sitting on a separate spindle? Are the logs and the data on the same spindle?
I have databases server running on an Intel P4 processor laptop that runs better than that.
Andrew SQLDBA
All that runs is SQL Server 2005 databases (2 or 3) that applications run from.
CPU spikes up and won from 1 to 90% I guess depending on queries being run via the databases.
There are 2 Quad Core AMD Opteron processor 2384 3.39Gz.
Not sure how the box was purchased or what spindle as I dont have direct access to the box.
August 12, 2010 at 2:38 am
Leo.Miller (8/11/2010)
Available Memory is misleading, SQL is memory hungry and will use all the memory it can.
Some questions:
How many CPUs?
How big are the relevant tables?
Is the application running on the same server as the database? This one is critical as you have SQL configured to use all the RAM, which means it can force the app out of the system. You probably want to limit SQL to 3GB (Max Server memory = 3000 MB) to leave 1GB for the OS and the App, since it sounds (between the lines) that the app may be struggeling.
I'd run profiler, see what queries are being run, run those queries in Managment Studio and see what is being returned. May be the app is ding a lot of filtering and other work that could be done in the database, but because of memory issues the app can't manage.
Without know a bit more of what's going on it's hard to say more.
Cheers
Leo
There are 2 CPUs see above for details.
Not sure how big the relevant tables are. The application company says that we should not be getting these delays based on other clients with larger databases not getting them on different server setups.
The application is installed on each client PC and links in using ODBC connections to the database on the server. Does this mean the application runs on the server?
Are there any other diagnostics I can run to confirm any of this other than task manager that you might find useful?
August 12, 2010 at 8:43 am
You can get rid of the old fashioned ODBC connections and use the more modern OLEDB connections. That could be part of the problem in transferring data back and forth.
What language are the application written in? Do you have the code for that? Are there a large usage of temp tables in the database? Is the front-end app doing all the ordering of the data returned?
AMD processor boxes should be left for home use. If you want good performance use Intel ZEON processor boxes. I have never heard SQL running relaly great on AMD, and only 4 Gigs of memory is not good. What operating system are you using?
Without anything else, there is not much that we can tell you. You need to do more investigative work on your own servers and applications.
Andrew SQLDBA
August 12, 2010 at 9:06 am
AndrewSQLDBA (8/12/2010)
You can get rid of the old fashioned ODBC connections and use the more modern OLEDB connections. That could be part of the problem in transferring data back and forth.What language are the application written in? Do you have the code for that? Are there a large usage of temp tables in the database? Is the front-end app doing all the ordering of the data returned?
AMD processor boxes should be left for home use. If you want good performance use Intel ZEON processor boxes. I have never heard SQL running relaly great on AMD, and only 4 Gigs of memory is not good. What operating system are you using?
Without anything else, there is not much that we can tell you. You need to do more investigative work on your own servers and applications.
Andrew SQLDBA
Im not sure how you add OLEDB connections we were just following what the company told us to do. Every client PC we go to Admin Tools > Data Sources ODBC > System DSN > and set up an SQL Server connection. Is there another way?
Dont have access to details on the Application but as there arent the same problems on other servers that run bigger dataabses with same application.
Realistically the processor wont be getting changed it looked fairly decent spec to me with 2 quad cores. The RAM may well be an issue but I think it is linked to the operating system maximum. As mentioned above we are using SQL Server 2005 R2 Standard x64 Edition with no AWE.
Being not clued up in these things my guess would be to upgrade the operating system and put in extra RAM and see how that affects things? Unless there is an obvious solution staring us in the face here?
August 12, 2010 at 9:19 am
You have not stated what the OS actually is. SQL Server 2005 is not an OS.
Is this a web app? or a GUI app? What language is the app written in?
As you can see, there are so many things that can cause this kind of issue. Are you see this issue on only a couple workstations? Have you tested server to server? Is this is happening on each workstation, then there may be a design issue with the app or the database.
ODBC is the absolute slowest way to connect a modern data database with an application, of any kind. The connection remains open for ever, whether it is being used or not. That takes valuable time on the processor and memory on the server, the more workstations hitting the server, the slower it gets. ODBC died in the 1980's. It should never be used to modern day apps.
This is what happens when you purchase software. You do not know how it was written, or what coding processes were followed, if any.
I own a med sized cake decorating company, I have written all the data related software, so it fits exactly, and I have all the code. Most things are better to coded in house. Especially if it is some like a simple data app, form and reports.
August 12, 2010 at 9:43 am
AndrewSQLDBA (8/12/2010)
You have not stated what the OS actually is. SQL Server 2005 is not an OS.Is this a web app? or a GUI app? What language is the app written in?
As you can see, there are so many things that can cause this kind of issue. Are you see this issue on only a couple workstations? Have you tested server to server? Is this is happening on each workstation, then there may be a design issue with the app or the database.
ODBC is the absolute slowest way to connect a modern data database with an application, of any kind. The connection remains open for ever, whether it is being used or not. That takes valuable time on the processor and memory on the server, the more workstations hitting the server, the slower it gets. ODBC died in the 1980's. It should never be used to modern day apps.
This is what happens when you purchase software. You do not know how it was written, or what coding processes were followed, if any.
I own a med sized cake decorating company, I have written all the data related software, so it fits exactly, and I have all the code. Most things are better to coded in house. Especially if it is some like a simple data app, form and reports.
As i said in my initial post the oprating system is (Windows) Server 2005 R2 Standard x64 Edition.
This is a GUI application which looks to be written in Visual Basic perhaps.
The issue is on every workstation that connects to the database. The application runs ok but the diary part is very slow. I am guessing these queries take up the most memory and are pushing the server beyond its limits perhaps into its paging file which is much slower. The queries are not going to be changed so we need to speed the server up in order to access them efficiently.
So how would we go about using the alternative if it is so much better than ODBC?
As we work for a large public sector organisation and not a small business it is not appropriate to be spending all our time coding and 3rd party solutions are more cost effective.
August 12, 2010 at 2:57 pm
There are so many possibilities here, and it may not be possible for the forum to help you as they can't get enough detail and aren't hands on. You may need to get the vendor in to look at it. Surely they have some sort of support.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 13, 2010 at 12:52 am
chris.lawrie (8/12/2010)
As i said in my initial post the oprating system is (Windows) Server 2005 R2 Standard x64 Edition.
Which does not exist. Microsoft Server OSes go Windows 2003 Server, 2003 R2, 2008, and 2008 R2--there was no 2005.
August 13, 2010 at 2:25 am
Hi my mistake it is 2003 not 2005. What is is likely to be the main thing we need to look at based on the settings I have posted? My guess would be to upgrade the version and then the RAM and let SQL Server allocate it dynamically should help?
August 13, 2010 at 11:46 am
Not sure why everyone was hung up on the OS version, typo! Much to look at as far server configuration and SQL configuration! If you don't specify memory, SQL Server will take it all so to speak of! Yes, spindles are great the more the better for you data files.... Many great forum topics do your homework before posting...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply