If our processes run every 2 minutes on 3 DBs simultaneously, everything is fine... why are we encountering problems if we run our processes every 2 minutes on more than 3 DBs? Assumption: We have more or less 20,000 SQL statements to execute for each DB every 2 minutes?
Our goal is to finish the process within 2 minutes for 40 DBs and more.
Any recommendations to improve performance or what to check that cause it to slow down when we increase the # of database to process? Will using SAN disk be of help?
- SQL Server 2008 R2
- data and log files are currently located in drive C:
- bytes per cluster on drive C:is set to 4kb.
First priority would be separating the data from the OS activity, given the C: is the OS / page file drive. After that one can start gathering statistics and ironing out other bottlenecks, the average of 170 statements per second doesn't sound much but it mounts up, 40 dbs and its approaching 7000 per second.