May 13, 2007 at 1:15 am
I have a SQL 2k with an ERP system running on it.
The performance is worse and worse.
I found scan density was very high on most tables. Several processes were blocking and disk I/O can't follow (reads)
After running DBREINDEX there is no difference at all (using SHOWCONTIG to check)
I increased db file size to double already but with no results.
Already for 2 weeks I'm searching the net and books but can't find any solution.
Hope somebody can help me out here.
May 13, 2007 at 11:15 pm
A high scan density is good. A perfect situation would be to have a scan density of 100. So if the indexes are in really good shape, then DBREINDEX is not going to help.
Your problem lies elsewhere. The possibilities are too numerous to fathom a guess with this small amount of information. I would start at finding the root cause of the blocking. What processes are blocking? Run SQL Profiler for the span of a typical day if need be and if deadlocks are ocurring, turn on Trace Flag 1204 towrite deadlocking info to the log file.
Possibilities:
May 14, 2007 at 1:25 am
Defragmentation ?
May 14, 2007 at 7:38 am
Blocking is not bad. It's a sign that you have concurrency issues and the server is preventing someone from reading while data is being changed.
I'd use Profiler and really dig into what performance is bad. Figure out which queries are giving you trouble, and check on plans for those queries. I'd start with the blockers since they usually make life hard on users.
Also watch the System Monitor for performance and ensure you have enough hardware.
May 14, 2007 at 11:42 am
Provide more info about which ERP is running on SQL2K is it Peoplesoft or SAP. as both ERP as there on way of doing Indexes. If it is peoplesoft then as SQL DBA you don't have more controll or i can say not much to do. If it is SAP then there is room to do but still need to understand how SAP handles. ERP Index maitenence is very different than regular database application. If you are not Oracle DBA & your ERP is Peoplesoft i highly recommend talking with one of your Oracle DBA in reference to peoplesoft. ERP are not dependent on default isolation levels they have there on locking algorithm which work in context to RDBMS. try to see if either of ERP provides any troubleshooting tips or tools from performance standpoint. i am sorry i was not that helpful.
May 14, 2007 at 8:30 pm
I will add a few "guesses/hints" here too:
1) As already mentioned, fragmentation. This includes data/indexes inside sql server as well as OS FILE fragmentation. If this database was created with improper initial size/growth parameters you could have thousands of file fragments and it will DEFINITELY affect performance. Since you are on sql2k you are a bit limited in the information inside the database, but dbcc showcontig will get you the best data you can get I think.
2) Check dbcc sqlperf(waitstats) and various perfmon counters (locks/waits categories) to see what really is the bottleneck.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2007 at 9:24 am
Some time ago we have got some performance issues with our big SAP running SQLServer.
The indexes have been mainly OK.
Finallyi t turned out that the problem was with the satistics on our huge tables (several dozens of millions records). The internal update staistics rule is based on percentage of record changed which is obviously resulting very high number of records.
Once we have implemented a manual update statistics method the performance came back to normal.
Bye
Gabor
May 15, 2007 at 10:01 am
Excellent suggestion Gabor! This is especially important for OLTP environments that include dated inserts (such as ERP systems) because the stats will not reflect the newer rows until a relatively large number of them have occurred - meanwhile the optimizer is not seeing the values in the statistics and will possibly choose a bad query plan as a result. I have a payroll client that sufferred from a similar fate. Nightly update stats jobs perked performance right up.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 16, 2007 at 1:05 am
Using Read80Trace you can see what's the most consumming top 15 queries in resources.
It is free a Microsoft product. It have helped me a lot of times.
- You run a profiler and define a trace (see doc, you will need a lot of info)
- Export it as a script and run it through Query analyser to avoid bottleneck of network bandwith with the profiler GUI
- Run the Read80Trace to analyse the trace.
May 16, 2007 at 6:33 am
Everybody,
Thanks a lot for all your replies!
In the mean time I asked support from our ERP to come over.
The problem was:
1) a lost index. (I have no idea how that can happen)
2) Statistics! They ran a tool on the db that create new tables + indexes and then copy all info from the original table in the new one. After dropping the table they renamed the new one to the original name.
I guess that will cleanup a lot.
Anyway I learned a lot reading many blogs and from also a lot from your replies. Thanks again!
The cost of this guy was 300 EUR/day + other expenses. Fair enough I would say...
There once was a guy with a leaking water tap. He tried to fix it many times but it kept on leaking...
He asked a plumber to come over and this fellow fixed it in 5 minutes. Asking 50 EURO + travel kost. The owner asked how come you ask 50 EURO for 5 minutes work...I guess you all know the answer?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply