March 15, 2011 at 6:39 am
Our customer replaced server and installed MS Sql Server 2005 Enterprise (SP2) to new one instead of Sql express. When I work with application on new server where Sql server is running, we have no speed problem.
But when I access Sql server through remote computer (connected to VPN), it takes double time to open main window of application (= connect to sql server + around 10 sql commands) than on old one (with sql express). I tried to change provider from SQLOLEDB to SQLNCLI, but to my surprise it was even slower (by debugging only direct connect to sql server takes 22s vs 1.5s in case SQLOLEDB).
I also try to disable/enable TCP-IP or named pipes to force to connect by named pipes or TCP-IP, but there wasn't any difference.
So we advise to our customer to install on new server also Sql express 2005 to find out if there is no network problem. And speed of application was surprisly the same like on old server (that means 2x faster than on enterprise). And speed was the same no matter if we use SQLOLEDB or SQLNCLI.
Could anyone help me where problem could be?
New server has 4GB RAM, Xeon 2.13Ghz, 250GB HD (150 still free)
Database has 500MB
March 15, 2011 at 6:45 am
Were the statistics updated when transferring to the new server? as this could cause performance differnces
I would start by looking at the execution plans of the Ten Queries between the two servers, and see where the difference in plan and time taken is.
March 15, 2011 at 7:08 am
Yeah, I agree. Start at the exec plans and see if there are differences there. But, the biggest difference to me is that one database is local and the other is through a network. The local one is fast, the networked one is slow. Have you timed response so that you know the problem lies directly within SQL Server itself? I know you've messed with the connectivity types, etc., but I didn't see any evidence that you'ved timed each step in the process. I'd get that done too. It's frequently surprising to people just how much network latency can add to poor performance of a system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 15, 2011 at 7:27 am
Grant Fritchey (3/15/2011)
... But, the biggest difference to me is that one database is local and the other is through a network. The local one is fast, the networked one is slow. ...
I haven't written that any of the databases are local. There are two installations of ms sql server on ONE server (1x enterprise, 1x express). When I start application on server (so no network communication is necessary), both sql server are fast and speed is around the same (so I'm sure update statistic will not help and anyway I ran sql command sp_updatestats after database restore).
Problem is only when I test application on remote computer (in VPN), working on sqlexpress is 2x faster than on enterprise (both sql server are installed on the same server not my remote computer). IT administrator discovered that on some remote computers helped to move logged user to computer administrator group. But on my remote computer I can't see any difference (and also some others).
And also I can see any reason why connect by SQLNCLI to enterprise takes 22s and by SQLOLEDB only 1.5s (from my remote computer to enterprise on server). Since if I do the same to express (from remote computer to express server) I get the same result (1.5s which is OK for me).
Note: And there was only enterprise at the beginning on server, sql express was installed later to discover why is enterprise 2x slower than sqlexpress on old server.
March 15, 2011 at 8:37 am
Sorry, just trying to understand. I saw remote and local and inferred. Remember, I'm not there. I'm just going off what I'm told.
When you say remote, do you mean remote desktop? I can't imagine how you could possibly get different performance through RDP.
So, the app is running on the server? Enterprise might not be sharing the memory well, where as SQL Express is limited and less likely to hit conention (or scale at all). But that's a swag. What about using the wait stats to see what the queries are waiting on as they execute?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 15, 2011 at 8:52 am
I would still look at the execution plans to see if there is a difference, and then at least you can eliminate this from your investigation.
There is a possbility that one of the features of Enterprise edition is causing issues (maybe parrelisim) and the execution plan will highlight this
March 15, 2011 at 9:16 am
Remote computer means computer connected via internet to VPN. There is installed application on this "remote computer". This application is accessing (via SQLOLEDB provider) DB on MS SQL (enterprise or express) installed on server (we can call it PC_SERVER) in office.
If application is connected to Sql express on PC_SERVER speed is 2x faster than if it's connected to Sql enterprise on the same server (PC_SERVER).
If I use SQLNCLI provider to access enterprise sql server on PC_SERVER, only connect takes 22s (SQLOLEDB takes 1.2s), if I do the same to access express sql server on PC_SERVER, connect takes around the same (1.2s).
Once more, I don't think it's problem of database since if I install application directly on PC_SERVER, there is not difference what Sql server application is connected to. It has to be problem of SQL server enterprise.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply