April 1, 2008 at 6:15 am
Hi,
I have migrated database from SQL SERVER 2000 to SQL SERVER 2005.Then through DTS designer,migrated DTS.and created jobs.
But job that runs on SQL SERVER 2000 in 45-50 sec.,now taking 18min. in SQL SERVER 2005.
I have searched through .net,yet did not find any solution that will work for performance issue.
I rebuild all indexes for all table,also update staticstics.even i created new indexes on tables.
But quries takes a lot of time as compared to SQL SERVER 2000.
I am trying from 1 months but yet did not get any solution.
Please suggest solution...
Quries are like --
select ACC_NO,SOURCE_SYS_ID,CUSTOMER_ID,SOURCE_LOC,BNAME,
SOURCE_ACC_NAME,BNAME2,ACC_ADD1,ACC_ADD2,ACC_ADD3,ACC_ADD4
ACC_LOC,ACC_CITY,ACC_STATE,ACC_COUNTRY,ACC_POST_CODE,ACC_COR_ID,Q_DATE,Q_RESN,VALID_ADDRESS,ACC_OUTSTANDING_TOT,
REMOVE,CREATED_DATE,UPDATED_DATE,AUDIT_LOG,NOTE_ID,WORK_PACKAGE_ID,PAID_FLAG
from JV_ACCOUNT_CYL_TEMP
WHERE ltrim(rtrim(SOURCE_SYS_ID))+ltrim(rtrim(ACC_NO)) not in
(SELECT ltrim(rtrim(SOURCE_SYS_ID))+ltrim(rtrim(ACC_NO)) FROM JV_ACCOUNT)and ltrim(rtrim(SOURCE_SYS_ID))+ltrim(rtrim(ACC_NO)) in
(select ltrim(rtrim(SOURCE_SYS_ID))+ltrim(rtrim(ACC_NO)) FROM JV_INVOICE WHERE SOURCE_SYS_ID='CYC')
-- Code to add acc_no = 'CYCS' into JV_ACCOUNT
April 1, 2008 at 2:02 pm
Is it possible that your tempdb is not sized properly? Use profiler to see if the tempdb is going through autogrowth while the query is running. If it is, increase the size of tempdb.
April 2, 2008 at 3:47 pm
avinash repe,
Along with cbaker's recommendation about tempdb size tuning, there are two areas that should result in better performance times, with near immediate results:
1) Indexes: You will also need to perform some analysis to determine whether or not your existing indexes work most efficiently with the SQL Server 2005 Engine. Based on your time of up to 50 seconds on SQL 2000, and now 18 minutes in SQL 2005, that there is work to be done.
The first step is to collect a set of information with SQL Server Profiler:
* START >> All Programs >> Microsoft SQL Server 2005 >> Performance Tools >> SQL Server Profiler
* File >> New Trace >> Connect to the Server\Instance that will have the data collected.
* General Tab >> Trace Name >> Give it a name you can reference later.
* Use the Template: Choose "Tuning"
* Check "Save to File" option >> browse to a windows folder and give a descriptive file name, again for future reference.
* Set maximum file size (MB): Leave the default as 5
* Enable file rollover >> Leave the default as checked.
* Optional: Enable trace stop time: Choose a time when you want the trace to stop. (Note: you must start the trace manually.)
* Click the "Run" button.
* Click the "Stop" button to complete the data collection or let the timed job finish.
Once your Tuning data trace is finished, you can begin your analysis with another tool, the Database Engine Tuning Advisor or DTA (this is the new version of the SQL 2000 Index Tuning Wizard).
* START >> All Programs >> Microsoft SQL Server 2005 >> Performance Tools >> Database Engine Tuning Advisor
* Login to the Server/Instance that you will be analyzing.
General Tab:
* Workload >> Use the Binocular Icon to browse to your .TRC file save when using the SQL Server Profiler.
* Database for workload analysis: choose your database.
* Select databases and tables to tune: Check the database(s) and table(s) you want to be analyzed.
Tuning Options Tab
* Limit Tuning Time: Decide as to whether or not your want your analysis to end after some time, the default is 60 minutes (1 hour) after the session was opened, uncheck the box for an unlimited amount of time of analysis.
* Physical Design Structures (PDS) to use in database: Choose the best option here for your database.
* Partitioning strategy to employ: Change the default ONLY if you are using partitioning.
* Physical Design Structures (PDS) to keep in database: For your situation, I recommend you check Do not keep any existing PDS. (This will ignore all existing indexes and indexed views in your system!)
* Advanced Options: NOTE: you may only build the recommended changes online if running against SQL Server Enterprise Edition, and no Developer Edition won't let you, I tried.
* Click the green arrow labeled "Start Analysis" near the top of the screen to begin the analysis.
* When the analysis is complete you will be presented with a report and you will have to click the blue link to access the DML that will generate the code for the new Index or new Statistical object that is recommended for your performance increase. Yes, you can copy/paste, but you must open each one individually and run in a Query window. Also note that you can change the names of the index/statistics objects, SQL Server will give an obscure alphanumeric string for the names.
Technically, at this point, you can make the judgement call to drop your existing Indexes and Statistics, before implementing the new objects.
Once your indexes are recreated, you can now rebuild the indexes online, without having to recreate them, as an option in the new Maintenance Plan Wizard. This can be created and scheduled as a SQL Server Agent Job.
2) Statistics: You will need to recreate your statistics, which will help the optimizer do it's job better.
Statistics can be rebuilt, and automated on a schedule as a SQL Server Agent job as a Maintenance Plan.
There is a lot of work involved here, but you have stated that you have already been working at this issue for over a month, and have not been able to make things execute faster. After rebuilding your indexes and statistics, you should see times for your queries in SQL Server
2005 as executing faster than run times with the old SQL Server 2000 system. Keep us posted on your progress and timing results.
Best Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
April 2, 2008 at 11:24 pm
I have checked tempdb size.Even i changed it to 2 GB from 1 GB.But still problem persist.
Do u have any idea??
April 3, 2008 at 12:32 am
Damon Wilson,
Thanks for your reply...
I have tried a way that you suggested.As i used Database tunning adviser,it suggests to remove all indexes.I followed same way.But yet it did not work.I am confused with problem.As query on 2000 runs fastly but on 2005 it did not.
What will be problem??
April 3, 2008 at 7:54 am
avinash repe,
One thing I forgot to mention in my really long posting. When you are performing your Tuning session, you will need to run your 18 minute query, so that the query information is captured in the .TRC file(s).
To specifically focus on this one query 18 minute long query, you can get the Tracing session set up for everything except clicking the start button, in preparation. Next prepare to run your 18 minute query. Quickly switch back to the Tracing tool, and start the Trace. Pop back over and start your 18 minute query. Once your query is finished, you can stop your Trace.
Take your new Trace file and run it against the DTA. You can choose at that point to only have the trace analyze objects affected by your query or for the activity for the entire database while your trace was running, by choosing the database, and tables to be analyzed.
If you ran your trace with little or no database activity, then it would make sense that no new Indexes or Statistics were recommended for creation.
Again, please keep us updated on your progress.
Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
April 3, 2008 at 1:57 pm
I'm sure you've checked the execution plan on that long-running query. Does it point up any problems on index/table scans, missing statistics, etc?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 11:26 pm
Damon Wilson,
Thanks once again for your reply.
This time i followed same way as you suggested.I have executed query,and start for trace file.Then through DTA,i got some new index script.I executed these scripts.And tried to make run query again.But it's
perfomace not improved.
For your refernce i have attached execution plan of query.
It seems from execution plan that table scan of JV_SACCODE_CSS table takes around 59% cost....This table has 10 millions records with no primary key constraint. Also no identity column or non-clustered index... we tried creating non-cluster indexas suggested by tunning advisor and query now takes 7-8 minutes ..still an issue. Can you please suggest something after verifying execution plan ?? Your help is greatly appreciated. Thanks for all your help...
April 4, 2008 at 6:08 am
Well, looks like there has been some progress made, at least. I have downloaded your Execution Plan and can evaluate it sometime today. I have a couple of mandatory meetings, I think I will take your plan with me and work on the laptop so I don't get that glazed look at the meeting.
I will post back with what I can recommend.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
April 4, 2008 at 6:48 am
The problem is the number of where clauses that are using the Ltrim Rtrim function, this means sql server must run the function on each row before it determines a match.
sql 2000 and 2005 deal with this differently, 2005 has parrellel processing which means that the order can be different each time so the matching takes longer as it has to be re-ordered.
I remeber reading an article on here about this, i will post a link if i can find it.
fix up the tables so the fields do not have to be trimmed each time and this should speed up things..
April 4, 2008 at 7:23 am
I'd convert the query as follows:
Change the "Not In" part to a left outer join and get rid of the string functions in it.
Change the "In" part to an "exists" query and get rid of the string functions in that too.
Then make sure you have a clustered index, a correct primary key, and an index that covers the join columns and where columns (at least), on the tables being queried.
Do those things, and you'll have a query that will run much more quickly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 9:42 am
avinash repe,
I agree with the recommendations from both steveb and GSquared. I am not sure how much control you have over being able to change the structure of your table(s). Obviously the more you can change the table structure to a more efficient shape, the better your query will run. Of course, once you make your changes, create another SQL Trace and use the DTA again, as well as create a new Execution Plan.
Another question is: How many of these queries do you have in this database that have had this noticeable of a performance problem since upgrading to SQL Server 2005?
For a longer term solution it would be in your best interests to perform a top to bottom tuning audit on your database server. There is a very comprehensive article about how to perform this process here:
How to Perform a SQL Server Performance Audit
http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/
Although this was written before SQL Server 2005 and Windows Server 2003 (or 2008) were released, much of the content is still valid, or requires a little research on the newer components of the respective Microsoft system.
Hope This Helps,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
April 4, 2008 at 10:54 am
Thanks for your suggestion..we will keep you updated with the status as I am going to implement your suggestion...Thnaks again...
April 5, 2008 at 9:28 am
The suggestion of removing trim functions did a great job for us....now the query run in 30 sec instead of 20 mins ...Great ..Thanks a lot for suggestion. But my question is if we want to have same functionality of trimming any column values ..how do we do that and that too with some millions of records...
April 6, 2008 at 10:17 pm
Hi,
Thanks for your reply.As removing trim functions,query is working fine.
But we found one more problem -
Query -
Update JV_INVOICE Set Paid_Flag = 1
Where SOURCE_SYS_ID+ACC_NO+INV_NO not in
(Select SOURCE_SYS_ID+ACC_NO+INV_NO from V_INVOICE_CSS_TEMP)
and source_sys_id = 'CSS'
In above query before "not in" we have concated columns like SOURCE_SYS_ID,ACC_NO,INV_NO.When i executed this query,i am getting error
A transport-level error has occurred when receiving results from the
server.
and server machine also hanged.
Can you suggest what will be reason for error??
Is there any other way to cancat columns??
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply