October 25, 2006 at 9:13 pm
Recently I upgraded my SQL Server from 2000 Advanced Server to 2003 Standard Server R2.
I increased the server's memory from 3 Gig to 4 Gig. I also increased the network speed from core of our network to the SQL Server from 100 Mbit to 1 Gbit.
On the 2000 Advanced Server, the thing was on a 3-drive RAID-5 array.
Initially on the 2003 Standard Server r2 it was on a 4-drive RAID-5 array, but I've just switched it to a 2-drive RAID-1 mirror.
On it I have SQL Server 2000 and in that I have an 8 GB database in which there is an inventory table that is hit constantly 24x7. The inventory table hangs around 85000 records with records being added and updated every minute, and groups of them deleted every 10 minutes, so its always changing.
Since the upgrade, the inserts have been tripping over each other more, with some being blocked. The logical fragmentation of the indexes of this table have been creeping up over the 10% range at shorter and shorter rates. It was a couple weeks, then it was a few days, then a day, now its down to hours.
The programs, SQL statements, and the indexes are the same. They may not be the most efficient, but they haven't changed.
Doing DefragIndexes helps most of the time, but we're having to run it more and more, and we didn't used to have to do it at all.
Does anyone have a suggestion to help, or experience with a similar situation?
We're past my expertise and I'm grasping at straw here.
Thanks.
October 26, 2006 at 3:40 am
just a shot in the dark ...
- did you format the volumes with blocksize to max in stead of default (4k) ?
- maybe your clustering index isn't the right one based on your usage.
Investigate and alter the clustering index ! (maybe there has been a CLIx in the past that has been dropped and the primary key (created / altered afterward) didn't mention non-clustering ! )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 26, 2006 at 4:43 am
Hmmm .. adv server to std server .. well, I think you'll lose a bit of performance. You don't say which editions of sql server you're using. A 4 disk raid 1 ( 10 ) will outperform a 4 disk raid 5 for writes but will be a tad slower on reads, but will equal ( or better ) your old 3 disk raid 5.
It's strange that inserts are getting blocked, that's slighlty more difficult to block over an update. I figure you've got everything on the same disk ? that won't help you much.
You need to establish where the problem lies - use disk counters to check for excessive i/o completion time and disk usage ( use disk idle time and deduct from 100 to get usage ) Check also the transfers/sec and break down by reads and write to get the %age balance. If your app is more reads than writes you might want to consider going back to raid 5. ( although I personally hate raid 5 < grin > )
Check your cpu, context switches, processor queue and obviously usage.
Check page life expectancy, cache hit ratio.
Get the hardware out of the way initially. Now capture your offending sql statements which are blocking and being blocked and make sure they have useful indexes ( just having indexes doesn't mean the optimiser will use them )
Make sure your stats are up to date and run dbcc updateusage too ( i'd do this every day at a quiet time ) Make sure all tables have a clustered index.
purchase SQL Server 2000 Performance Tuning - ms press.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 26, 2006 at 5:08 am
I forgot to mention you only defrag the indexes.
Defrag only does index-level-optimisation.
Check BOL.
After every upgrade, sp, restore, attatch_db it is advisable
to perform full dbmaintenance.
dbcc dbreindex (or configure a maintenanceplan)
sp_updatestats
dbcc updateusage(0) with count_rows
Indeed dbreindex has more impact on your current load, and may even lockout ongoing activities, but the maintenance is to the optimum !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 27, 2006 at 8:29 am
Are you seeing some underlying application issue that is now causing an actual problem. Your new environment may be running more things in parallel, allowing an application fault to show up as locking.
For example INSERT table (id) Values((Select max(id) + 1 from table)) will give unique id values in a single-threaded system, but will allow duplicates if multiple threads run at the same time. Look at the application code related to the INSERT to see what locks are held, and work out if increasing the number of parallel insert operations could cause the problems you are getting.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply