Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
H_BimBim_H
Starting Member
1 Post |
 Posted - 2011-03-08 : 00:09:06
|
| Hi all,I am currently doing a computerized accounting system that supports retail, Windows Server 2003 - Enterprise Edition - SP2, the technology used is C #, SQL 2005 database.Server configuration as follows:DELL X3440Intel Xeon Quad Core Nehalem X3440 2.53 GHzHDD 2 x 250GB SATAX3440 @2.53GHz2.53GHz, 8GB of RAMPhysical Address ExtensionThe software I was deployed in a pharmaceutical business unit, to generate about 10,000 to 15,000 records daily. Has 4 points of sales, fiber optic transmission lines.1. How to organize data as follows:- There are two tables store the data in (vouchers)- After saving finished in 2 Tables, data is transferred to Ledger Ledger Accounting and Inventory Ledger.- When saving to Ledger Accounting and Material Ledger, I have used triggers to transfer data to Table Balance accounting and materials (two tables are organized by month, which means that a field of the month, in any format "201011 - year, month after")2. When entering data, I wrote a paragraph command (the command is written to open each material, account, object. That is, each entering a new line, I will create the commands sent to the server to get up results), returned the balance of an account instantly, instant inventory of an item.3. If a small number of machines (usually less than 10 machines), the program runs fine (can say is great) but the number of machines increases, the program runs very slowly (or rather slow flow).4. If restart or Detach Database Server Attach Database and then back again, then ran about 30 minutes.5. Basically the only slow data. When data entry is very good. Means that when adding or editing a new document, edit it took a while to check the balance (this procedure does not affect the speed of data entry, why I do not understand.) To store data in the database when the new delay.6. I totally use Stored Procedure in working with Database. I also do not use view and do not use the Select with nolock.7. Connection Pooling and Transactions There used to store dataIn summary, as follows: When you save data into the database, if few people use it up very quickly, if many users are very slow flow.We're looking for comments or instructions to help me.H_BimBim_HNew to SQL Server |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-08 : 01:33:18
|
| You've provided a lot of detail, however it's not the detail that we need. You will need to run SQL Profiler, sp_who, and PerfMon to determine where the bottleneck is. You'll need to check blocking, wait stats, PerfMon counters, and identify the slow running queries in a trace. You'll also need to identify missing indexes by looking at the execution plans as well as the missing indexes report. What kind of maintenance plans do you have in place for things like statistics and fragmentation?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-03-08 : 01:42:14
|
| First of all is your SQL Server 32 bit or 64 bit?Also how many threads have you set for the Server?Also have you tried to monitor when your flow runs slow?PBUH |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-03-08 : 02:19:22
|
We'll have to see the code for insert.How long are your transactions? Locking?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|
|
SQL Server so slow when over 10 users input data