July 21, 2010 at 10:38 am
Hi,
I have scenario here related to performance problem.
I need to figure out, where is the problem lies?
1. Is that the problem from the sql server side?
2. Is that the problem from the memory of database server?
3. Is that the problem of the Web server machine?
4. Is that the problem with application problem?
5. Is that the problem with the Page file sizing?
6. Is that any looping of the rows in the front - end which is taking time.Meaning, sometimes they l get all the required result to the front end -web server
and run the loop for the result set and start writing row by row in xml format?
7. Is that tempdb issue?
I need a step from where i can start troubelshooting the problem.
Basically, the application all it does is it pull the data from sql server and writes in EXcel format i.e. exxport functionality.
It used work fine for exporting data upto 500 records very fine.
But now the there s no limittation of records to write to the xml file. number of records to be written in xml format can be upto 5000 records.
Oservation.
--------------
1.on database server , checked sp_who2. Nothing is being run. the proccess initiated this ccommand is AWAITNG COMMAND status.
2. Checked the task manager , on database server i could nt find that much load.
3. On the web-server, when i looked into the task manager , and processes tab, it is showing dllhost.exe is being increased more than 1 GB memory.
and system is getting hanged? what is that dllhost.exe? when does it come into picture normally?
4. On web server , RAM is 2 GB and max PAGE File size is 4 GB?
5. OS is Windows 2003 Server Enterprise edition,sp2
6. SQL Server 2000 Standard edition, sp4
How to figure out, where does the actual problem lies which is affecting the overall performance of the export application???
Any suggestions would be greatly appreciated.
July 21, 2010 at 10:41 am
What kind of application is running this process?
Do you get an error message?
Does the process complete?
Does the process output desired results?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 21, 2010 at 10:55 am
What kind of application is running this process?
--- It is a .Net application. It is running running .....
Do you get an error message?
-- no error message. I could not find anything in system event viewer as well!!
How can we know whether any memory leaks / any other problems on each machine.(web-server/db server)?
Does the process complete?
-- No its is running for a long time.do not what exactly they are doing inside the code.
Does the process output desired results?
--- for few records it is working fine i.e upto 200-500 records. After that, if it is 2000 recs and more, it is never ending story?????
What next i can do ? 🙂
July 21, 2010 at 10:56 am
You've told us just about everything except what the problem is. Care to elaborate?
What's slow? A specific query? Everything in one DB? Everything on an entire instance?
Has it always been slow, or has it recently become slow?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2010 at 11:01 am
If it doesn't seem to be taxing the system heavily during use and just runs and runs and runs, you could setup a trace via profiler. Capture the data from the application server and then analyze it. I would set up the trace and then have them kick off the application. Let it run and run and run - then analyze what it is doing.
I would do it for a small result set and a larger result set. Compare your findings.
If you suspect a memory leak, there is an application I use called freeram xp pro. It is an installable and will report on memory usage. It is not conclusive, but if you watch this utility after the app starts, you can see when the memory starts disappearing and if it returns after the app is done.
The next thing to be done, would be to have the developers document what the app is doing for that tidbit. Find out if it is using stored procedures or inline ad-hoc sql.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 21, 2010 at 11:27 am
Makes sense. I will collect the info and keep you posted.
Thanks both! 🙂
July 22, 2010 at 5:49 am
Here is something to move forward.
I will keep u posted more on deeper code - level , once i get clarity!!!!
Seems like there is a problem with Web server hardware resources.
Basically the application written in .NET. Here we have the functionality to generates reports and at the same time, u can
download/export that data into excel format.
Basic architecture of the Application
Client1
client2 <----> web server <----> Microstrategy <-------> database server
client3
client..
client-n
Earlier, the no of rows was limited to 100 records to create the Exported Xcel file(using Excel 2007). Now recently they have removed the limitation. now clients can export upto 2 lakh records as well. There is no restriction to limit rows..
Also, i tried to change the page file size on the web - server. but it is throwing an error when i tried to increase to 5 GB. Earlier the max size is used to be 3 GB . Now i tried to change it to 5 GB but it thrown error.
Then i tried to increase to page file size to 4 GB, then an Warning is displayed. if the pagefile on volume c: has an initial size of less than 2047 megabytes,then the system may not be able to
create a debugging information file if a stop error occurs? continue anyway?
Why it is showing that warning? what is reason for saying that ? what does it indicate? I beleive that there is 50 GB free space and am trying to configure 5 GB for page file on the C: drive , why is it not allowing me to do.
Is that the right way to fix this problem for temporary fix?
How to make that application scalable???
How to start? does we need to baseline the number of current users, number of reports to be generated,
I have also attached screen shots of Task Manager of Web Server and Database Server and the application is accessed by 5 users simultaneously generating/export data on to their machines. Please have a look on that.
Any thoughts?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply