Post reply

What is the capacity of SQL Server 2000/7?

  • May 4, 2005 at 2:52 am

    #88903

    Friends,

    I m facing a strange problem. My solution uses SQL 2K and stores around 70,000 records in one of the transaction tables. Master tbl for this transaction stores around 10% of the total records in transaction tbl. There are around 6-7 processes which use both these tables from 7 workstations.

    Two processes , P5 and P6 - when they are started - they make all the other workstations hang.

    P6 - which takes the processed data out of the system and dumps them into files on local drive faces challenge - as in the moment it gets started - all the workstations hang. It takes sometimes around 20 minutes to extract these 70K records from the system. All the other processes get blocked. Sometimes, processes P1..P5 get blocked and they wait until P6 finishes.

    Its a burning issue and I am looking for all possible performance tuning options. Database do hv indexes on these M/T - tables and we hv kept a fill factor of 30 (previously it was 90).

    Pls help.

    Thanks in advance,

    Shash

     

  • May 4, 2005 at 10:24 am

    #556833

    What are the processes doing? Have you run profiler and perf mon on the server when things hang?

  • May 4, 2005 at 2:57 pm

    #556918

    I think this is recarding to lock not performance. Please check if is there any lock when P6 dumping data.

  • May 5, 2005 at 3:39 am

    #557010

    Friends - Thanks for your help , Here is what a brief expln of what I am doing:

    • The whole data which gets processed - is divided into batches - so a batch has got data for itself and a no of records in the table. Only one transaction table contains all these records (both batch/child-record). The master table contains the meta data for all these batches - and a lock information field. The lock information field helps in a multi-user scenario.
    • All these processes (p1-p6) hv their specific processing of the data present in the single table. For example, P1 might enrich some parts of the child records' data and so on and so forth. One batch can only be processed by one process at a time.
    • Finally, after all the processes (from P1-P5) hv done their job - P6 will get up -take this data and create certain files based on some pre-defined business logic.

    I hv run the profiler and perf mon - to see - I can see some incresed cache-hit-ratio (speficically the ad hoc queries - graduates to 100% in sometime) and buffer-hit ratio (always 100%)  - IOs and some other readings.

    I hv tried setting the fill factor for these indexes between 20-40. I am nt sure if thats really helpful - but now i am developing a kind of system init module - which should update stats / re-indexes the tables before we start the processing.

    Not quite sure of what to do at this moment, any help is appreciated,

    Warm Regards,

    Shashi

Viewing 4 posts - 1 through 3 (of 3 total)

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