May 11, 2011 at 2:37 pm
Hi everyone,
Please let me premise this post with the fact I am a junior developer and my SQL knowledge is basic/intermediate.
Myself and a senior developer manage and develop a Silverlight application, essentially a CRM which supports around 50 concurrent users. The deployment environment is a Citrix farm with 2 servers load balanced (this is where the XAP file is run and where the c# code essentially runs)which connects into an IIS box and SQL 2005 box all of which are on different virtual servers. The specs of the physical machine is a Xenon G6 Intels with 4gigs of ram per virtual machine and a core for each. What we see is that when a client runs an intensive series of database calls the silverlight application is still responsive but if we then try to access a screen that does another database call the request waits until the previous request is completed. However during that request if we run another instance of the application in a seperate IE tab on the same Citrix server and perform small database calls it's returned almost instantly. If we tried the same on the intial instance the call waits until the previous one has finished. This to me says that SQL is not the bottleneck. Does the SQL or Citrix give low priority to subsequent db calls? We have coded it so that it shouldn't wait for the callback this is done in the background. Do SQL packets get queued at an IIS or Citrix level. Our next step of investigation is to run everything outside of citrix and run SQL performance monitor. Is there something relating to session ID vs server load. We are scratching our heads here.
Thanks for any suggestions, I hope to update with relevant test results tomorrow
May 11, 2011 at 2:50 pm
Are you saying that your application makes multiple calls from the same client? Same browser?
It's hard to understand what you are saying, and it's hard to read one long paragraph, so a little clearer explanation would help.
SQL only has one open query on a particular connection at a time by default. You can enable something called MARS (Multiple active result sets), but that's typically not done. So if you are pooling or using the same connection that's open and sending a new query, it's waiting on the first query to finish.
If you have separate connection and it submits a connection, it should be executed right away.
You also could have blocking, depending on what you are accessing.
May 11, 2011 at 3:08 pm
Hi thank you for your prompt reply, again appologies for being a novice. Some background might help here and tomorrow I will post some c# LinQ -> SQL sourcecode that might help explain my problem.
We have users on remote ADSL and even 3G connections in to a Citrix environment. They have been complaining that responsiveness is slow and as developers we run the development environment locally on our machines that has a local host webserver that just connects to the SQL server. We have never noticed a problem. However if they click button 'clients' which performs a SQL request that gets all clients based on user security expanding a few tables for instance this will cause the cursor to go to wait state until the request has got to callback, this sometimes takes an unacceptable amount of time. However observing the SQL tool the request is dealt with quickly and if they try to do anything else in that same instance of the XAP file(that requires a db callback) it can wait for up to 20/30 minutes during peak times and any further calls are delayed. When we try and replicate it, what we do is run an intensive series of calls to the database with a concurrent session of the application running and during that time the cursor is in wait state we fire off another call from the second instance of the application and that happily comes back.
Let me talk to the lead developer tomorrow as i might be just confusing matters and get back to you with a more professional response
May 11, 2011 at 3:37 pm
It really does not sound like a problem with the database. I would be leaning toward an issue with the way the VM's have been created. You could easily be running into context switching issues with only a single processor available for the VM.
I normally would not create a VM with a single processor. This may explain why you do not see the issue from your local machines, which probably have at least 2 processors and can easily switch between them.
Just some thoughts - might not be related at all...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2011 at 3:47 pm
Thanks Jeffery,
I'm in agreement there, our dev machines are quad core i5's. The c# 'for each' loops on the returned data do cause the cpu to go to 30% for a couple of seconds spread nicely across all cores and on the live server we do see it boucning around 100%, admitidely these are Nahalam server cores with 12mb of cache but I'm hoping that it is just the Citrix servers under strain from processing multiple instances of the app doing for each loops. We are going to throw more servers at it tomorrow (more cores), I can't see it being SQL at the root cause.
Will post more data tomorrow, for now i've done a 14 hour day and in conjunction with my noviceness it's time to call it a day
Paul
May 11, 2011 at 4:49 pm
I'm still not clear on what is happening in the Citrix environment and what is the concurrency setup.
Can you run a Profiler trace when you get back to this tomorrow in a limited test?
May 12, 2011 at 3:27 am
Morning all,
Todays finding are as follows.
Run the Silverlight application locally on development box which directly connects to SQL, this test completely rules out Citrix environment. We believe there to be a small performance issue relating to CPU load on the citrix server to process such things as for each loops but these delays are going to be a matter of seconds not 20+ minutes.
Each of our LINQ requests use a new data context which we believed should fire off independently of any previous requests. But does SQL Server 2005 have a buffer zone for each instance of IE connecting to it? This would explain why our one hanging session has to wait for a number of previous requests to complete prior to a new one being processed but other users are unaffected.
We ran our report generation code that performs roughly 45000 separate database calls, and these are called in batches of which some are coded to be sequential requests. We watch these ticking along in SQL profiler, each request being dealt with in milliseconds as you would expect. While these are being performed we can access any part of the application that doesn't make any calls very quickly but if we click on for instance the 'Clients' button we see a significant delay until this reaches SQL profiler.
Even if the client button is pressed a few seconds after the report generation code started execution it can still take up to 10 minutes before the client request is processed. Once it does the request is dealt with again in milliseconds. Importantly the previous batch of SQL requests continue after this has been processed and do so for some time afterwards. So it just appears that our new clients request has been slotted into a queue being caused in the background by the previous reporting loops.
If we run exactly the same tests but this time run the clients button from a different session the request appears almost instantly in sql profiler and is returned again in milliseconds.
Obviously our test here is a large overkill to prove a point, end users will never send this many requests to the system but we’re just trying to understand the underlying technology so we can avoid any problems in the future.
May 12, 2011 at 9:13 am
SQL Server has no knowledge of IE, so don't get caught up there. Whether you are connecting from a C# Windows Form app, WPF, Silverlght, etc., you are making a client library connection (OLEDB, ADO.NET, etc). Those connections are not buffered or stored or delayed in SQL Server.
If you make separate connections in SQL SErver (separate SPIDs), then you can send simultaneous and separate queries to each one. If you are re-using a connection, which is possible depending on your framework/architecture, you can only send through one query at a time unless you have MARS enabled.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply