SQL Server causing massive disk i/o

Hi experts,

I have sql server 2008 R2 Express SP2 instance running on a windows xp sp3 machine.

There is only one front-end application making use of this sql instance.

I noticed today that my front end application slowed down to less than 1% of its expected performance.  The xp machine has 4GB memory and is not maxed out.  The CPU averages around 12-15% utilization.

I found though that the %DiskTime on perfmon is flatlining at 100% and has been at 100% for hours.  On the sql activity monitor, I can see that my sql instance is utilizing disk i/o at around 25-32mb/sec. Thats probably why my disk is at 100%.

My database is set to simple recovery mode.  The front end app is not even writing large numbers to the database - roughly 40,000 records over the last 12 hours.

Please help me find the cause of the abnormal disk i/o.

Thanks a lot!
PantoffelSlippersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Can you restart the machine before going crazy to find out what is going on? I mean close the app and shot it down normally then see after restart if you issue went away.
0
PantoffelSlippersAuthor Commented:
Hi

I did that last night (there was a scheduled restart anyway).

I'm starting up everything this morning (actually now) to see how it looks - I'll report back after about 1 hour of monitoring.

Thanks
0
PantoffelSlippersAuthor Commented:
Same problem :-(
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PantoffelSlippersAuthor Commented:
It seems like the log (LDF) file is growing quite quickly and the database is in SIMPLE recovery mode.

I need some help here.

Thanks
0
lcohanDatabase AnalystCommented:
The LDF file cannot grow on its own without any activity against the database and even maintenance tasks like DBCC or reindex will log the transactions therefor LDF growth. Which database has the LDF growing? user or some of the system DB's?
0
PantoffelSlippersAuthor Commented:
It is my user database that has its LDF file growing.  Also, on the activity monitor, I can see that the most significant resource wait is called "Logging".  I didn't think this much logging takes place under simple recovery mode.
0
lcohanDatabase AnalystCommented:
Even in Simple recovery mode there is T-log activity and if you look at the Standard report in SQL SSMS called "Disk Usage" you must see those autogrow events. There must be something running against it as there's no way T-log grows on its own - you must have some INSERT/UPDATE/DELETE or maintenance or indexing/reindexing as mentioned.
0
PantoffelSlippersAuthor Commented:
Hi lcohan,

Yes I do have one front-end application running against my SQL instance.  It can only be this application causing the high disk usage but I need to see exactly what it is.  If it's constant autogrows then I need to set the increment at which the files grow bigger.  If it's indexing then I need try and turn off indexing while the application is populating my database.  if it's updates with WHERE clauses I may be able to improve the situation with additional indexes.

My problem at the moment is that I don't know what's causing it.  For the last 36 hours that database server has been running 20-36 MB/sec Database I/O.

I did not know about the Disk Usage report in SSMS - let me see if I can find it.

Thanks
0
PantoffelSlippersAuthor Commented:
Hi

I checked the disk usage report.  Autogrow and shrink operations are spread about 12 hours apart and usually only takes about 2-3 seconds.

So I'm still looking for the answer.

Thanks
0
lcohanDatabase AnalystCommented:
Well I think the answer to "Please help me find the cause of the abnormal disk i/o."

Is your front end application in my opinion.
Does the T-log of that database grows if your "front end app" is turned off? I bet it doesn't so you need to look at that app and see what is the app doing to cause that much IO. Too bad you are using SQL Express only and SQL Profiler is not included but you can still start a trace without the  SQL Profiler GUI and you can trace all queries against SQL Database to a table or a file level and review it using T-SQL. Please have a look at system procedures like sp_trace_create, sp_trace_setevent, etc

http://technet.microsoft.com/en-us/library/ms190362(v=sql.105).aspx

You could also get the free tool xSQLProfiler instead.

In the end it could be that you have some (bad or not) massive Cartesian queries and no index support so read goes to disk. You may fix your issue by just adding appropriate indexes and/or update stats and see below more about that:

http://technet.microsoft.com/en-us/library/ms345417(v=sql.105).aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PantoffelSlippersAuthor Commented:
Thanks lcohan,

It's definitely my front-end application.   Like you said, I need to find out what the application is doing to cause the I/O.

I'll look into the trace procedures and xSQLProfiler.

Thanks
0
PantoffelSlippersAuthor Commented:
Thank you - not really sorted at all but I'm getting closer - don't want to leave the question open.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.