SQL Server Performance

Upgrade from Developer Edition

Discussion in 'Performance Tuning for DBAs' started by baggy, May 4, 2004.

  1. baggy New Member

    I have inherited a SQl environment and one of the issues was an installation of Developer Edition instead of Standard Edition. This server has a performance issue with a particular application.

    In order to put it to standard edition I used the original CD to 'Upgrade' the server rather than re-install. It was rather a quick process and I'd like some info on whether this method of going developer to standard is ok or If I should scratch & re-install.

    Also, is there a potential performance issue with running Developer edition as opposed to standard i.e. may it be some of the cause of my performance issue?

  2. Luis Martin Moderator

    I think no problem, but what is performance issue now?

    Luis Martin

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. gaurav_bindlish New Member

    Ideally developer edition should not havw any issues as sompared to the standard edition as Standard edition is subset of enterprise edition and developer edition is the same as enterprise edition but with a EULA.

    It would be intersting to know what issue are you facing.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  4. baggy New Member

    The client application utilises an access database and linked tables to SQL server.
    The main detail screen can take 6 to 8 minutes to load from the SQL server which was upgraded. (It was this slow pre-upgrade, was hoping the upgrade would help as I was told that developer edition was throttled back in some fashion).

    Putting the database on a different server which was a straight SQL Standard install and pointing the application to this performed much better, within 2 minutes to generate the screen. The second server is more powerful, but I still wouldn't expect the poor performance on the first one.

    The database is only 53Mb in terms of data space. It doesn't take that long to copy whole files much larger than this!

    I think the app may utilise cursors quite a lot (may be a result of linked tables, I'm not sure), I need to profile it to be sure.

    Rick (aka baggy)
  5. Argyle New Member

    Did you run dbcc reindex or sp_updatestats on the databases after the upgrade? If not, do that and try again.

    Also investigate the cursor usage you have. Cursors are often an issue when dealing with linked servers.
  6. Adriaan New Member

    To rule out network issues, can you move a 'large' file from the workstation to the SQL Server drive and see how much time that takes? Usually a good indicator if the connection is bad, typically it's too many hubs.

    We install SQL Server databases with an Access front-end all the time, and sometimes you will see that people on one side of the building have excellent response time, and people on the other side get utterly frustrated.

    If the slow connection is over LAN then you should really start using Citrix or MS Terminal Server.
  7. baggy New Member

    The app seems to fire lots of sql returning small bits of data, some of it the being used to build the next statement. The server Network Packet size is 4096 , might it be of benefit to improve responsiveness by lowering this value?
  8. satya Moderator

    One of the KBAs mention:
    Clients using a 512-byte packet may see some performance degradation due to the smaller network packet size. The degree of the performance impact depends on the application's data access patterns. "Older" clients that will default to a 512-byte packet size include all versions of DB-Library and any versions of the SQL Server ODBC driver prior to Microsoft Data Access Components (MDAC) 2.1. Versions of the SQL Server ODBC driver or OLEDB provider after and including MDAC 2.1 version 3.70.623 will use a 4096-byte packet. The 4096-byte packet size is unlikely to cause any performance problems (4096 bytes is the default sp_configure value for 'network packet size').

    Just for a check what is the level of service pack on SQL server?
    Better to test the option before deploying on the production server.
    I don't see any issue on the upgrade part, and to know the information take help of SERVERPROPERTY ( propertyname ) and return for EDITION property name.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Adriaan New Member

    When you see lots of small chunks of data being returned to an Access front-end (I assume you've been watching the profiler?) it's usually in response to queries behind comboboxes on a form.

    Also with a Access front-end using ODBC-linked tables, we noticed that batch operations done locally by the Jet engine are often handled one record at a time, not as a batch. This came out when we were troubleshooting some triggers.
  10. baggy New Member

    Sorry for the lack of response, but I've been lumbered with more urgent stuff than the previously urgent stuff ! I'm sure you all know what it's like.

    Anyhow, thanks for the input, it's appreciated. I hope to get back to focusing on it next week , profiling, perfmon etc. As I really do need to know why the app with such a small database performs so badly.

    The server is SQL7 SP4, as mentioned up front it was developer upgraded (not re-installed) to standard edition. I'll dig out the Spec of the box but I think it's single CPU, 1 maybe 2Gb memory, I'll check to be sure. I know it was below the company standard, but the sub division involved won't spend any money on upgrade unless we can definitely state it will solve their performance issue.

  11. satya Moderator

    As referred no issues with the upgrade and on the memory part leave the settings as DYNAMIC and ensure no other application are sharing resources on the server. If so then this would be the first level of degradation of performance during the application processing.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page