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 |
|
yavvie
Starting Member
46 Posts |
 Posted - 2009-10-07 : 03:27:31
|
| We are running identical databases and processes on 2 servers on SQL 2008, both of them with on Windows Server 2003.On the newer (and so faster/better/stronger) server we are experiencing very serious problems with performance. The servers are supposed to process an identical data load at 5AM, which normally takes about 40 minutes.The load consists of importing about 50 files into tables (step 1), processing them (step 2) and processing those tables once again for use with reports (step 3). All importing and loading is done via SSIS packages (created on server 2005 and migrated to server 2008, migration took place 11 months ago).Presumably there are no or minimum connections at the time the processing starts, and even if, they only access tables used in step 2 and step 3. However, the last 2 days the load has been VERY slow even in step 1 - instead of running 6 minutes, it took 3 hours. This slowness was ONLY on the new server - the old one ran fine the day before.What is curious - when we yesterday found out that the load is running so slowly and hasn't finished yet we restored the previous day's backup on the old server and started the load - and it also run very slowly (we tried a day older backup and it was also slow). Then we restarted SQL server and SSIS services and it run in normal time. (Any idea if there are some monthly checks of certificates from those SQL servers? I know there was some issue with SQL 2005, which needed to connect to microsoft to check certificates...)So we restarted those two services on the new server and expected the load tonight to be in normal time, but again it took over 3 hours and step 1 didnt even finish - it hung 70 minutes on one package (again, no other processes access tables used in step 1).Now I used the data processed by the old server and restored them on the new one, which is used as datasource for reporting services (reporting services run on a different server) and the reports are also terribly slow.Please any ideas what could be set wrong on the new server? We reinstalled it 3 weeks ago and it worked till now (we had some issue with copying files (http://support.microsoft.com/default.aspx/kb/304101) which we fixed in registers in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management).Every day at the end of processing we run a maintenance task which corrects indexes on tables and checks data integrity.The databases are some 20G together, and there hasn't been any rapid/unusual growth in the last 2 days.here are our servers:OLD server: Windows server 2003 R2 Enterprise, SP22.67 GHz, 16 GB RAM SQL server 2008, v. 10.0.2531NEW server: Windows server 2003 R2 Enterprise, SP23.33 GHz, 32 GB RAM SQL server 2008, v. 10.0.2531 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-10-07 : 09:31:18
|
| have you had a look at your hardware logs? any errors? have your network guys been playign around with cables or the network cards?can you reverse to a similiar starting position each day and do a series of simple benchmarks on each server in parallel....not using these datasets, but using something made up for the purpose.trace the routes of each query/resultsets..pc->network->server->san->spindle->database->table, etcwhen all else fails to explain a difference, look for and eliminate each outside factors which may be causing a difference. |
 |
|
|
yavvie
Starting Member
46 Posts |
Posted - 2009-10-07 : 09:51:21
|
| thanx for the suggestions, we looked at the system logs where there were no errors. tomorrow we are going to do a hardware test, and I will run some performance tests in the morning."trace the routes of each query/resultsets..pc->network->server->san->spindle->database->table, etc" I'll need a hint to what you mean by this one or how to do it :)just by the way, when we restarted the server (not just SQL server service, that didnt help) the response times from the SQL server started to be okay. We will see tomorrow morning how the load runs, but I don't believe that restarting a production server is the best solution :) |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-10-07 : 11:45:00
|
| pc->network->servercopy a large file from a pc->same pc...measure the "file copy" speed. this is your benchmark.now copy the same file onto a server (or pc elsewhere on the lan) and measure the speed. this measures your lan speed/latency. work out how fast your network cards are delivering data across the machines.seperate test...BCP a raw text file into a table on your server...with the source being a file on your server. now try moving your source away from the server and see if there is a network problem betweeen the server and the datasource.re spindles...can you move the database onto a different SAN or set of drives? again test to see if one san arrangement works better than another.there has been mention on this site of "throughput performance measurement" tools. i haven't used any but the principles behind same are straight forward.... |
|
|
|
|
|
|
|
|
SQL server performance issues