sudden slow down
-
...was thinking of large tables, which would make sense to turn off auto update statistics... WHY?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's not the statistics problem because the tables get replaced once a week, so their indexes and statistical information is rebuilt. WRONG!!! Statistical information is only built by explicit declaration or implicitly by use of the table!!! If you have automatic generation turned off, performance will suffer unless you have explicit declarations of statistics in which case you'd be better off explicitly declaring a pot wad of indexes.
Why do people continue to think they're smarter than the boys who spent years developing this stuff? Stop messing around with the default settings.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The advice from MS was to turn off auto-update statistics too. Statistics are independent of indexes so you don't have to create a "pot wad" of them.
If the table has millions of rows it will take a considerable amount of time to update them. On a 5 million row table it takes about a minute.
-
Yes, I know that statistics are independent of indexes... but think of stats as automatically generated indexes...
Can't believe that MS recommended turning them off... Look's like I have a little load testing to do just to see what happens.
Thanks for the feedback, Luk.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
turning statistics off is only good if you have a job to update the statistics manually. if statistics go out of date, execution plans can become outdated and have the perfromance impacts you are describing; like someone else said, autoupdate statistics does not have that big of a performance impact, especially compared to the performance impact if the statistics are out of date.
At the last place I was working, a new assistant dba got fired after three days exactly because of that...he turned off statistics because he "heard" auto stistics were bad,and did not make a maintenance job to do it manually, and performance went to hell three days later...it took a while to figure out found out what happened, then i updated statistics, turned em back on and such, but the higher ups decided to let that guy go because of it.
are you telling me that once-in-a-while one minute statistic update is more troublesome that the performance hit you have been suffering?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The statistics get updated automatically by a process that our developers wrote.
We haven't experienced the problem with statistics just yet, at least we don't think that's the problem as it happens with even fresh stats. The issues we're having is still a big unknown and stats just came up in this discussion.
When you're running several thousand queries against tables, the sql server will pickup and try and update them on demand. If the stat update happens on large tables, then it may cause timeouts, that's why developers wrote the process that exchanges the data/rebuilds indexes/stats on those tables and that happens at night.
In any case, the problem reoccured just few days ago, and we noticed higher disk activity in terms of disk reads. So maybe it is our RAID. The strange thing is the other 2 times it happend the performance counter didn't reflect anything meaningful.
MS is still going through more logs we sent and they haven't responded yet. Some ideas here and there, but nothing concrete.
-
A few more things which might turn somthing up (as we seem to be on a fishing expedition here)...
What other software do you have installed on the server?
Do you use any third party monitoring products or similar?
Have you got any additional extended stored procedures installed as they run in SQL Server's memory space?
What sort of locking are you seeing happening during the slow down?
Are the system tables getting locked for an extended period for some reason?
- James
--
James Moore
Red Gate Software Ltd -
There is no other software running on the same box. It's a dedicated SQL 2005 installation.
No third party monitoring tools. Just SQL Management Studio.
Yes, there are XPs and .net assemblies installed on the SQL Server. Are you hinting they might be causing memory leaks? There were no changes made to those procs in months.
During the slow down SQL Server doesn't report any particular wait type. On occasion we see LATCH_EX and CXPACKET, but not that many. Should I limit maxdop option to 1?
I don't think there is extensive system table locking. We removed all references to select into statements.
-
Luk,
I find it hard to believe that MS told you to turn off auto stats!
2 things you have mentioned though,
1. You have Developers who have written custom code to perform the update of stats? If thats the case, what else has your Developers written to perform manually that SQL erver would do automatically?
2. You have mentioned a possible problem with your RAID? If so, your System Administrators should have some tools to detect hardware issues.
On another note, I don't beleieve it has been asked yet however, does this SQL Server participate or manager Replication?
-
1. Developers handle only few tables. These tables are rebuild/reindexed on a regular schedule.
2. Our raid configuration is kind of bad. It's still RAID5, and I've been trying to upgrade it, but I don't think it's going to happen. The server was acquired well before I got here. tempdb is running on the same drive subset, but the network admins don't want to attach additional storage, so I'm stuck with the configuration as it is. Since the server suddenly slowed down I thought it might have been either a controller or a bad disk, but according to Dell rep this isn't the case. They connected remotely to the box and ran diagnostics.
And lastly this server is a subscriber, participating in transactional replication. However the amount of data that flows is so small, that it can't be it. There are only 3 tables being replicated, with a handful of rows being inserted/updated on weekly basis.
As for the MS they did this to eliminate certain theories -- I think. My problem on this one is that I'm talking to different reps, one in Toronto the other in India and both have different ideas.
-
How is your index management and maintenance?
On large tables with poor indexes or poorly managed indexes you can get this type of behavior.
-
Luk (3/27/2007)
1. Developers handle only few tables. These tables are rebuild/reindexed on a regular schedule.
2. Our raid configuration iskind ofbad. It's still RAID5, and I've been trying to upgrade it, but I don't think it's going to happen. The server was acquired well before I got here. tempdb is running on the same drive subset, but the network admins don't want to attach additional storage, so I'm stuck with the configuration as it is. Since the server suddenly slowed down I thought it might have been either a controller or a bad disk, but according to Dell rep this isn't the case. They connected remotely to the box and ran diagnostics.
And lastly this server is a subscriber, participating in transactional replication. However the amount of data that flows is so small, that it can't be it. There are only 3 tables being replicated, with a handful of rows being inserted/updated on weekly basis.
As for the MS they did this to eliminate certain theories -- I think. My problem on this one is that I'm talking to different reps, one in Toronto the other in India and both have different ideas.
Luk i am not an expert but just my two cents on this. I was facing some similar issue and still couldn't find an answer. When my views were pointing to subsciber table's performance was terrible and when they pointed to publisher performance was amazing. Can you try to point your code to publisher and see if this is the issue?
-
sqldba_icon (3/29/2011)
Luk i am not an expert but just my two cents on this. I was facing some similar issue and still couldn't find an answer. When my views were pointing to subsciber table's performance was terrible and when they pointed to publisher performance was amazing. Can you try to point your code to publisher and see if this is the issue?
Sorry but I think you are 4 years too late.:doze: (Wonder if this is a record?)
-
Maybe it's a record, but I'd still like to know how it got fixed (if it ever did).
-
It did get fixed. It was a third party .net DLL which we used for search. We fixed it by writing our own search engine.
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply