The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Upgrade to SQL 2005
Posted: 17 Dec 07 10:27 AM
|
Hello all, I have a client who just upgraded to MSSQL Server 2005 from SQL2000. They are on 7.0.1 with all hot fixes. All is running fine except the performance has taken a hit.. Are there any tweaks I can put in place to increase performance?
I seem to recall something about the SQL Server Native provider being faster.. Should I change the provider in the connection manager to the SQL native client? Thanks |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 10:45 AM
|
Originally posted by Steve Knowles
I seem to recall something about the SQL Server Native provider being faster.. Should I change the provider in the connection manager to the SQL native client? |
|
Abolutely. Change the provider for the connection in the Connection Manager to "SQL Native Client". |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 10:54 AM
|
| Thanks Ryan. Do the client machines need this provider also? I read a post on ITToolbox that says this should also be done.. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 10:57 AM
|
| Yes. The SQL Client Provider will need to be installed on all SLX machines, including workstations. SLX will make some direct calls to the database from the client, using the connection string configured on the SLX server - so it will need the provider in order to make the connection using the configured connection. Make sense? |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 11:02 AM
|
| Makes sense.. so if I change the connection manager to the native client, but a machine does not have it yet, that machine will experience errors, or just the same slow connection? I can guess the answer, but doesn't hurt to ask.. thanks |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 11:09 AM
|
| I've not tried that (no provider on the client), but I would guess they'd get errors. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 12:31 PM
|
One other thing to be aware of when working with SQL 2005. The database engine (doesn't matter which client you use) does not like the "IN" clause in a sql statement. I ran into this with our integration with our ERP system. We had to change the SQL clause from an "NOT IN" to an "NOT EXIST" statement. As an example a query that would take 3 seconds on the SQL 2000 server (less processor and less memory) took over 5 minutes on a SQL 2005 server (more processor and more memory). We had this researched and it is a known issue with SQL 2005. Here are some links:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11869
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19438
One thing to note as well is that not all "NOT IN's" have problems; in this case, it is a problem when the two columns are concatenanted in the query.
Hope this information helps. It was a bit of a pain to troubleshoot and took some help from our ERP provider to help locate the problem with the queries.
Ted
|
|
|
|
Re: Upgrade to SQL 2005
Posted: 17 Dec 07 4:45 PM
|
If your Connection Manager is configured to use it and the Native Client has not been installed on the client, you will get an error when you try to connect (something unhelpful about the Data Dictionary, IIRC).
PP |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 7:15 AM
|
| Thanks all. Do the connections at the remote office and remotes clients need to be deleted and recreated to use the Native client? Will errors occur otherwise? Thanks |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 8:44 AM
|
Two Questions come to mind regarding a change to the SQL Native Client:
1. Do I have to convert Crystal Reports which were defined using the Slx OLE-DB provider?
2. How is generation of Remote User Databases affected? The MSDE used even in Slx 7.2 is a cut down SQL2000 model and not based on SQL 2005. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 9:00 AM
|
| The client is experiencing performance issues since the upgrade even using the native client. Any other performance enhancing tips? It is very slow on login and when performing lookups etc.. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 9:44 AM
|
| We just renabled 'Array Acceleration Mode' on the DB server and it improved things dramatically.. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 10:40 AM
|
Array Acceleration Mode? I've never heard of that. I'm excited to take a look and see what that's all about. Thanks for the information.
One thing we did (which may not help you a ton) is to set the checkbox "Use AWE to allocate memory" for the server properties, menu section. Since we have Windows 2003, it works really well, and SQL server now uses more of the available RAM - before I did this, SQL Server was restricting it's memory usage to 1.7 Gb of memory, causing it to use the hard drive constantly and heavily. We were only using one 10k RPM hard drive for our 9 Gb database, so allowing it to use more RAM was a huge benefit to our performance concerns. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 2:09 PM
|
| I just googled "Array Acceleration Mode", but the only thing that came up was this thread. I also looked in Books Online and found nothing. Is there any documentation, or is it known as something else in SQL Server? |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 7:04 PM
|
Jeff, the system admin performed the miracle - I believe it is a function of the array controller.. The problem persists anyway. It was strange - the performance jumped for about a half hour then bogged down again.
Tech support sent me a reindex script. Has anyone run this script before? Any gotchas if I run this?
exec sp_msforeachtable @command1="print '?' dbcc dbreindex('?','',80)"
Still looking for the cause of this slowness.. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 18 Dec 07 8:16 PM
|
| Tried the reindex - don't think it helped but I did notice if hide the notes history tab that alleviates the problem - specifically on the opportunities. Same with the summary tab.. They both seem to bring things to a near halt for about a minute or so.. very painful. |
|
|
|
Re: Upgrade to SQL 2005
Posted: 19 Dec 07 8:15 AM
|
The reindex script is fine - don't know of any gotchas. In fact we run this on a regular basis without issues.
Have you run profiler to see what statements are possibly causing the slowness?
Ted |
|
|
| |
|
Re: Upgrade to SQL 2005
Posted: 19 Dec 07 3:22 PM
|
Resolved! We moved the .ldf to a different drive than the .mdf and all is working as before the upgrade to SQL2005.
I found a lot of info regarding poor performance in SQL server being fixed by running sp_updatestats against the db. Is anyone familiar with this stored procedure? Is it something that should be run after SQL an upgrade?
Thanks for everyones input. |
|
|
|