March 31, 2008 at 5:24 am
We just did the migration from sql 2000 sp4 to 2005 sp2 and now application team is complaining about performance issue. According to developer’s we might running into the following issues, Is there any known issue on 2005 for Table Variables and Temporary tables.
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
March 31, 2008 at 5:29 am
no big issues unless you load the temp tables or temp variables with huge amount of data.
if loaded heavily then convert those temp tables into a permanent temp table or put your temp db in a separate fast disk drive.
March 31, 2008 at 5:32 am
March 31, 2008 at 5:34 am
Two things spring to mind.
First of all after migrating to SQL2005 all statistics become unvalid and you should run DBCC update statistics on all tables/indexes.
Second, depending on the hardware configuration, you might want to add additional files to your tempdb. MS advise to create one file for each 1 or 2 processor core. So on a 4 core machine, create at least two datafiles for your tempdb.
I'm not aware of any specific issues involving temp tables or variables. Maybe you can be more specific about the problems you have. Also give more info about your configuration like which version, how much RAM and CPU, etc.
[font="Verdana"]Markus Bohse[/font]
March 31, 2008 at 5:35 am
Most important and only thing you have to do after an upgrade is to update the statistics of your databases with
the command sp_updatestats on all databases.
March 31, 2008 at 7:50 am
There are some steps while doing migration. We may need to make sure to follow the same.
Migration/Upgradation is the only way where you can make your server with the best practices.
1) Remove all orphend users from the database,
2) Create MP's for Daily Backup/Integrity Check and Optimization,
3) Run DBCC Checkdb for all DB's
4) Run sp_updateusage()
These are the best practice immediate after migrationfrom SQL 2000 to 2005.
Manoj
MCP,MCTS
April 1, 2008 at 6:45 am
In database properties under options, check the setting for Auto Close. The default for 2000 and 2005 is false, BUT upgrading DB compatibility from 2000 to 2005 ("EXEC SP_DBCMPTLEVEL Ensite, 90") sets it to true for some reason. A setting of true will take a huge performance hit.
April 1, 2008 at 10:40 am
To be most correct, you should update all statistics using the fullscan option.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 1, 2008 at 12:00 pm
We found out that some of our queries ran very slow on 2005 compared to 2000. A query that ran in 5 minutes in 2000 took 8 hours in 2005. We re-wrote the query and it ran in 2 minutes.
A developer here figured out the following:
Table A: has 290K rows
Table B: has 105K rows
A query that joined these two tables resulted in 35 million rows. Here's an example of the where clause that was used:
from db.dbo.table1 tb1
join db.dbo.table2 tb2 on (tb1.col = tb2.col)
This was changed to the following to make it run in 2 minutes:
from (select tb1.col1, tb2.col1, tb2.col2, tb2.col3 from db.dbo.table1 tb1
join db.dbo.table2 tb2 on (tb1.col = tb2.col)
group by tb1.col1, tb2.col1, tb2.col2, tb2.col3) a
As to why 2005 misbehaved, I'm not sure.
April 1, 2008 at 12:27 pm
The code for SQL2005 is very interesting. Do you have query execution plan? Any difference between these two plans?
April 1, 2008 at 3:02 pm
Vivien Xing (4/1/2008)
The code for SQL2005 is very interesting. Do you have query execution plan? Any difference between these two plans?
Execution plans are not entirely the same. I can post the execution plans if someone can tell me how to get a text ESTIMATED execution plan. I don't want to run the 8 hour query just to get an execution plan. 😀
April 1, 2008 at 4:46 pm
set showplan_all on
set showplan_text on
select ... from tablename
April 2, 2008 at 10:36 am
from (select tb1.col1, tb2.col1, tb2.col2, tb2.col3 from db.dbo.table1 tb1
Is there a significant difference between the total columns in table1 and the columns listed in the new derived table?
April 5, 2008 at 10:23 am
We got the same issue insql 2005, a compicated SQL statement stuck the process for ever. you can update statistics, then it will get faster. SQL 2005 is very sensitive to statistice data.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply