sudden slow down
-
What about autogrow on the database? Is it have to expand every 5 minutes.
Monitor Page Splits on tables and indexes. Use a Fill Factor.
ThomBeaux
Thomas LeBlanc, MVP Data Platform Consultant
-
No there are no page splits and the db is not growing. tempdb isn't growing either.
So no page splits and no full scans happening. The system seems very normal, it's just that transactions are slowing down to a crawling speed. Hopefully MS will have some answers.
-
Me, too... let us know what they say, 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) -
I agree with Jeff Moden.
What on earth would make you think Oracle is going to do any better. I'm an Oracle DBA as well as an SQL Server DBA and let me tell you, apart from some of the items that Jeff has already spoken about, in terms of performance & simplicity (to much to discuss in this thread), SQL Server blows Oracle out of the water big time! In particular SQL Server 2005.
The problem with this sudden slow down is happening for a reason, for what I'm sure we will find out sooner or later, but gee whiz! Stating that you will switch to Oracle because of a sudden slow down on an SQL Server? Wow, now thats extreme!
-
Just a shot in the dark, but are you transaction logs being backed up every 10 minutes?
Steve
-
Thanks for the tip, but no, we don't backup transaction logs as we run in simple recovery. We do backups on nightly basis, but the db gets rebuild in the most part once a week. There are few large tables that pretty much get replaced.
-
Luk
Do all your tables have a clustered index?
John
-
No, we do have heap tables as well. But all the tables on which we do selects on, are basically 1) read-only 2) have clustered index.
Anyway, working with MS on it now. MS hasn't given us a definitive answer, just suggested a reboot if it happens again.
-
The auto update statistics is off.
Luk... Sorry I missed that comment... Like I said... that's a form of "Death by SQL" (still waiting for gilbertorosa to tell us why he recommends turning it off)... you need to read the "statistical information, distribution statistics" and similar entries in BOL. Auto update of statistics has very low overhead for what it does and the benefit it returns. Turn it back on and see if your problems don't just up and go away.
--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) -
Luk,
In light of what Jeff has just mentioned, if this does appear to be a statistics issue with your Tables/Indexes then you should be able to see long running queries with the simple SP_WHO
Once you establish the long running query/s then it's easy to establish what the problem maybe!
-
It's not the statistics problem because the tables get replaced once a week, so their indexes and statistical information is rebuilt. These tables have no inserts/updates running throughout the week -- they are read-only.
MS suggested adjusting memory; leaving more to the OS.
-
I think gilbertorosa was thinking of large tables, which would make sense to turn off auto update statistics. We do have them, the largest table in our db is over 200 million rows, but the internet processes have no access to these tables, they operate on much smaller subsets.
-
All of our machines are dedicated. Application is .net web site and runs on a different web server/box.
-
spot on analysis of some of the detriments of Oracle vs SQL Server...my shop has to support both as well.
we often copy the same identical database to SQL and Oracle to compare performances. IDENTICAL right down to PK's and everything.
More Oracle annoyances I encounter:
don't forget restore times: a simple database in SQL server, 100megs in size, takes sub ten seconds to restore on SQL Server whether via GUI or script.... on Oracle, besides the fact that you have to drop and recreate the user, takes minutes to restore the identical database. bigger databases? I've sat around waiting an HOUR for a bigger database to finish restoring a copy of a 10 gig database on oracle, that too about a minute on SQL server.
Or what if you have a database from a different oracle instance, and you want to restore it on yours? you have to create tablespaces with identical size with at least the same amount of space in order to restore...very annoying. create a tablespace of 10 meg, and try and restore something that is too big for it..it doesn't auto expand on restore, just errors out hte import and you have to fix it and start again.
All our clients that have oracle have a full time Oracle dba, because the job is purposefully opaque and difficult to do; things that SQL server takes for granted, like the IDENTITY property of a column, takes a lot more work to do the same thing...create a sequence to generate the number, create a trigger to get and use the sequence, create grants on the table,trigger and sequence so that a different user can use it.
lots of code that has to be written manually (alright, copy and pasted with new names, because you DID do it before) which is just built into SQL Server. In my experience, while oracle gives the dba more control, it doesn not streamline the basic jobs as well as SQL Server does.
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!
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply