March 28, 2011 at 9:45 pm
Hi Biggies,
We are having a server with 8 GB of ram with 4 cpu in which 5 gb is given to sql.
Windows server 2003
Clustering implemented Active/Passive
Please find the attached dmvs output and provide me ur valuable suggestions.
As one of the process(data uploaded in some 7 to 8 tables no changes in indexes and nothing has been done from the application side) which was taking only 3 hrs now it is taking more than 10 hrs.
within 7 days only 1 time it took 3 hrs.
March 29, 2011 at 1:51 am
Gail ,Jeff, or any one???? please
March 29, 2011 at 2:35 pm
I'm neither Gail nor Jeff nor do I consider myself a "biggie" but here's what I think might be the issue:
Most of the high counters are related to SQL Plans, especially to SQL plans only used once or twice but require a significant amount of memory.
My guess is you're using NHibernate or any other "sophisticated tool" to communicate with SQL Server. If that's the case you might want to talk to your developers after reading Grant's article.
This might or might not have anything to do with the slow data upload though...
In order to help any further you should provide a lot more details about the upload process itself:
How is it done (BCP, BULK INSERT, SSIS, "RowByHibernatingRow" (sorry Jeff for abusing your RBAR definition 😉 ),... )?
How many source tables and how many rows each (same for target tables)?
Is it possible to post the upload/update code (at least the significant part of it)?
Did a server side (profiler) trace provide any hints where the major problem is?
I think there will be more questions as soon as the problem is narrowed down or a "biggie" shows up...
As a side note: information about server memory usually is not the first place to look at if there are performance issues. Analyzing a server side trace is a better point to start...
March 29, 2011 at 9:39 pm
Thankz LutzM they are using the .net as the front end
About the upload it gets uploaded from the text file one by one(here the data gets update or insert).
on Friday it took 10+ hrs to complete and on sat it took 3 hrs and again on monday it took 10 hrs to complete.
nothing has been found in the profiler.
March 30, 2011 at 6:20 am
samsql (3/29/2011)
...About the upload it gets uploaded from the text file one by one(here the data gets update or insert).
...
If you're talking about a row by row approach then: OUCH!
Get the whole file into a staging table using BCP (or an SSIS package if you'd like to) and handle the upsert (update or insert) using a set based T-SQL method.
You might get down to minutes instead of hours...
March 31, 2011 at 7:12 am
1) use sp_whoisactive to check for blocking, resource usage, etc.
2) do a waitstats analysis during the run to find out which waits are causing slowness
3) do a file IO stall analysis to see if IO is a problem
4) use sqljobvis to check for overlapping agent jobs during the long runs, especially backups, index mx or dbcc checkdb.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2011 at 8:27 am
TheSQLGuru (3/31/2011)
1) use sp_whoisactive to check for blocking, resource usage, etc.2) do a waitstats analysis during the run to find out which waits are causing slowness
3) do a file IO stall analysis to see if IO is a problem
4) use sqljobvis to check for overlapping agent jobs during the long runs, especially backups, index mx or dbcc checkdb.
Am I missing something? What is sp_whoisactive? Did you mean sp_who2 active? or is it a custom written procedure?
But do agree that it sounds like a locking/blocking issue.
March 31, 2011 at 8:58 am
a simple web search for sp_whoisactive will get you what you need. Amazingly powerful free piece of code by Adam Machanic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply