Bw Liko the Mayor....at least I enjoyed reading the below. Thanks for the useful tips. I enjoy reading the MSDN links and I'll surely look up the ones below. See my responses below in red? Cheers On Thu, Dec 1, 2011 at 7:56 AM, Agosta Liko <agostal@gmail.com> wrote:
Hey
Here is a good checklist
a. Check MaxMem for SQL Server - most times, SQL will only utilize 3GB mem even though the server has 40GB. You need to set it to use lets say 32GB so that when the instance starts, it grabs 32GB
Assuming the code is the issue....how does an over allocation of memory help (unless B below answers it?)
b. If you have big tables that you hit often, you can use DBCC pintable so that the table it pinned onto memory and you dont have IO expenses when you are getting data. This means that if you have 32GB RAM and the database is 10GB, you can pin ALL the table on RAM and speed up stuff by lets say 500% on the IO side...
(http://msdn.microsoft.com/en-us/library/aa258284%28v=sql.80%29.aspx)
Note: I have not used this feature in SQL 2005 and SQL 2008 but it was pretty popluar in high traffic environment
c. Please ran a DBCC checkdb - this will ensure that there is not corruption on the db, tables, indexes etc. This is usually a nice starting point with SQL Server
Sawa
http://msdn.microsoft.com/en-us/library/ms176064.aspx
d. Look into DBCC dbreindex to rebuild your indexes - this should be ran like daily ...
http://msdn.microsoft.com/en-us/library/ms181671.aspx
Rebuilding of indexes on a daily basis. Don't you think this would be too
much? Takes quite some time to rebuild these. Rebuilding is being done once a month tho.
Some more thoughts.
You issue as described is most likely because of the reports which usually have to read all the data. I would review the SQL in the reports and ensure I have the right indexes so that I am not scanning a whole table to find a record. Usually, when searching for a record, if there are no indexes, the database engine has to do a table scan... that means if you have 1 million records it will have to read all ... if you put indexes, they the query does not have to do a scan and it finds the data faster.
Understood . I think after all remedial measures fail I'll schedule a code review.
Please spend some time on http://www.sql-server-performance.com/ and you can start with
- http://www.sql-server-performance.com/2007/optimizing-indexes-general/ - http://www.sql-server-performance.com/2007/clustered-indexes/
For most reports - look into covering indexes but dont weka too many because they will slow down "writes"
OK
Thats that ....
I have inboxed my CV if you feel like sending some money my way (Christmas Imefika bana)
On the CV..........am looking forward to receiving it.
oh .. the issue is not the network ..
mmmmh....
On Tue, Nov 29, 2011 at 10:34 AM, muskiv <kulebak@gmail.com> wrote:
Skunkers,
Here's the scenario:
There is a core application that is build on a 2 tier client/Server architecture.
Total number of users at any one time over a WAN is approx 100.
The app runs on a MS SQL 2005 DB that resides on a Primary server whose specs are as follows:
1. RAM: 40 GB 2. CPUs - 8 Proliant QUAD Core E7320 2x2M cache processors 3. Network Interface: Gigabit ethernet card 4. RAID 5 config for the main DB drives - where the main DB files reside. 5. RAID 1 + 0 config for the log files on a seperate disk 6. WAN traffic is on 256k/256k links from satellite locations
The problem:
1. As users are being added into the WAN, performance has been deteriorating. 2. On report generation, everything seems to crawl to a halt.
*What are the likely contributors to performance degradation*?
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke