SQL Performance
-
Hello All,
I support a SQL 2k server that runs on what I think is a pretty good piece of equipment:
4 x 2.0GHz
8GB RAM
300GB Disk Space
The database on that server is connected to via a front end program running from a Citrix server which is on the same subnet as the SQL server. I have a user that has been complaining that the program is running very slowly and what normally takes about 30 minutes to run to (so she says) about 6 hours to run. Whenever she complains, I usually log onto the server to check the resources and all the vital signs look good. There is no CPU and the memory is usually low.
Is there anything else I can check to see if the problem is on the SQL side of the house vs Citrix? Should I setup Perfmon or do you recommend something else? Any recommendation for troubleshooting this issue?
-
With Citrix all the fun is happening on the server.
I would start with running whatever process/task/report using the parameters she is supplying and evaluate the performance of that task.
It is possible that there is some blocking, poor query performance, or bad/missing indexes.
-
We also have a couple of applications running on citrix for the front-end applications, with sql2000 on an dedicated SQL-cluster-node (running only that instance).
Most of the times when users are complaining, there is a leak in the app at citrix side. So not citrix is to blame, but the app-vendor.
Sometimes, there are heavy updates running at sql-side, but they don't cause that much enduserproblems.
We have one case of these (erp) applications where problems are being caused by queries used in "selection"-screens. e.g. select all articles containing "whatever". The problem there is that the application does not use read-uncommitted, causing heavy locking in cases where it has to return >250000 (yep, thats a bunch) rows. I've advised, because it is only an overview screen, to change the application, but the vendor is not willing to fix this.
This is causing the app at citrix side to consume over 1Gb ram
causing overall problems for other app-parts running overthereJohan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks guys for the info. The only thing that bothers me about all this is the vendor said that they tested the same import (it's probably an insert) function and it took them only 30 minutes to complete (about 40K rows) where my user states that it took her 6 hours. And yes, we all know that vendors will sometime stretch the truth to avoid blame.
But my user also stated that it took her 30 minutes to run the job once as well.
What did you do to determine that the problem was not on the SQL side?
Even if it's Citrix, at least I can have my team members who support the Citrix side of the house to start looking on their end. I believe they turned on Perfmon, but I don't know if that will identify a problem with the app.
-
do you have scheduled jobs for DBCC DBReindex ?
or indexdefrag ?
If not, reorganize the data and test again.
Maybe it's time to do this on a scheduled/planned basis.
Keep in mind this may interfere ongoing operations !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I'd take a look at your Citrix side. Keep in mind that Citrix is intended to be a shared environment, meaning resources may be eaten up on that side. Running the standard performance monitoring on the Citrix server the application is deployed to would be helpful, but you'll still need some correlation with your Citrix admins to determine exactly what system she was on at the time.
Also, has anyone tried to run the import on your side from a system that wasn't on Citrix?
K. Brian Kelley
@kbriankelley -
Yes - I have a scheduled job that reindexes the database on a weekly bases.
I questioned if she could run her app bypassing Citrix and was told no. But I really wanted to make sure that the bottleneck wasn't on my side of the house and wished there were some numbers or proof that I could provide (even if it was only for my self gratification) that shows it's not SQL.
Do you think running Perfmon would help? That's even a grey area for me with trying to analyze the results. That's one area of administration that I really need to understand and master.
-
Perfmon is designed to track how systems are performing through the use of performance counters. It's often an admin's best tool for troubleshooting issues like these. As to help on how to do it, I'll blow my own horn a little. If you click on the link to my professional site, you'll see a link to my eBook on Amazon.com about this very thing.
K. Brian Kelley
@kbriankelley -
Thanks Brian - I actually brought the book and will check it. Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply