Post reply

database partitioning

  • January 31, 2006 at 10:06 am

    #364894

    hi,

      We have a very large database which having the performance issue. we have set the index in all the table but due to the daily huge incoming data, retrieve a report in Web is slow. we are thinking to partition into different table to improve the performance. is anyone have a good idea to improve the database performance?

    Thanks

     

  • January 31, 2006 at 11:17 am

    #617981

    We would need more information about your hardware setup.

    There are sooooo many things that could be happening your situation it would be impossible to guess.

    Too many indexes can cause problems.

    First you need to make sure your code is running efficiently.

    You need to go thru and optimize queries that are taking a long time. Make sure your tables are properly normalized. This is often a point that causes queries to be very complex and take too much time to process. Doing this can get the best usage out of your hardware.

    Have you identified the source of the contention? CPU Usage too high? Disk queue length maxed out? What are your symptoms for poor performance.

    Review this link and it will give you a ton of information on where to go.

    http://msdn.microsoft.com/SQL/2000/learn/perf/default.aspx

  • February 1, 2006 at 9:15 am

    #618141

    Thanks for the link.

    My hardware setup is raid 5, intel xeon 3 GHZ processor with 3 GB RAM.

    The CPU usage will hit 100% when there is a lot of user accessing the web. Sometime the % processor time will hits 100% also. I suspect high CPU usage casue the performance slow. any suggestion?

    cheers

    si  si

     

  • February 1, 2006 at 9:58 am

    #618156

    Hardware looks good.

    Hard to say.

    More linkage:http://www.mssqlcity.com/Tips/tipSQL2000.htm

    Not all the recommendations are "Guaranteed" Before you implement a change make sure you test it.

    As I said before

    Make sure your indexes are all healthy.

    Do you reindex very often?

    Make sure you queries are all running efficiently.

    Use sql profiler to help identify long running queries.

    In filters you can isolate a specific event that causes cpu to grow above a certain mark.

    or Duration > than a certain mark.

    I randomly use profile to tell me all stored procedures that take longer that 1/4 second to execute. Then I take that list of procs and optimise them. etc. There is no 1 thing I can tell you that will fix your problem.

  • February 1, 2006 at 2:23 pm

    #618222

    Before you think about partitioning ...

    Here's my 'short list' for tuning:

    Round I

        DBCC UPDATEUSAGE

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

    Round II

        DBCC DBREINDEX

        UPDATE STATISTICS (with FULL scan) for all tables

        exec sp_recompile for all tables

        exec sp_refreshview for all views

    Round III

        Profiler

        Query Hints

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • February 2, 2006 at 9:26 am

    #618430

    hi, from the SQL profiler, it showed the long CPU and duration for Audit logout event. eg 12940 for CPU and 621000 for duration. could it be the different user using the same userid to logon? 

     

     regards

    Si si 

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply