April 13, 2014 at 11:54 pm
Hello,
For months, we are wondering why one of our CLR stored procedures is running slowest on a server than on a workstation
To explain, we've written a CLR stored procedure on an 3 years old XP PC also running a development edition SQL 2008 R2
This PC is quite smaller both in processor and RAM than the server which has 4 CPU and 12Gb RAM
This procedure generates about 10.000 labels from local databases and stores them also in local but it's running 2 times faster on the workstation than on the server
We change the standard edition on the server to an enterprise one, we compile the CLR in 64bits, we "Windows update" our server to the latest versions but we didn't reach the performance of the workstation
Does someone have the same problem and how do you solve it ?
Thanks in advance
April 14, 2014 at 12:10 am
There's no magic involved. One likely doesn't just run faster than the other. If I saw that my first impression would be that the local database is set up differently to production. It could be as simple as the tables might have different indexes. Or maybe the production server is under heavy load compared to development where nothing else is going on?
There may also be a more complicated set of factors. For example, development usually runs in simple recovery mode while production runs in full recovery mode. If you have production automatically shrink logs, or backups don't run often enough, it could be slowing down because it has to extend the logs on those imports, and if auto-growth was set to a large value...
April 14, 2014 at 1:49 am
For more explanations, I can tell you that the workstation databases are backups of the server
There isn't any CPU load on the server because it is a test one, and its databases indexes are reorganize or even rebuild every week
On the other way, there isn't any maintenance on the workstation
On the server, databases have been changed from full to simple mode
Unfortunately, all these changes (and many other) doesn't increase the performance on the server
Any auto-growths in our last test haven't been detected
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply