SQL 2005 problem
-
I have a problem with a new install of SQL server 2005 Standard SP2 on Win 2003 server, x86_64, 8GB, 4 CPU. There's no RAM limitation for the SQl server as 64b.
The server behaves very strange. Compare to install of SQL 2000 on the same box, it is a disaster. The same code, on the same data like the SQL 2000 instance runs 10 times slower, picks all wrong plans with FTS (like the indexes do not exists)although we have rebuild the indexes, shrink the DB, updated all stats. The Sqlserver exe shows up to 7.5 GB memory usage in task monitor, although if we sum the memory used by the several processes running on this SQL server, there will be no even 1GB (in this case Sql server 2000 is not running). We do not see locks, or anything unusual, but the users are complaing of slow running queries and hard to connect. No hardware problems encountered either.
Made a trace, but again, nothing that looks suspicious there.
What can I do to troubleshoot and resolve the problem?
Thanks a lot, mj
-
As a start (if you have no idea) - you can install SQL2000 on a virtual server and then run the upgrade advisor to see if obvious code errors apply. How has the upgrade process proceeded? Did you follow the steps for upgrade or are you shooting from the hip and crossing fingers?
-
Mj,
since you write that the server uses the wrong plans, my first hinch would be that the statistics have not been updated after the upgrade. All stats from SQL 2000 become invalid after the upgrade. If I remember coorectly you also need to rebuild any full-text catalogs.
Markus
[font="Verdana"]Markus Bohse[/font]
-
you must fix maximum memory on 64bit sql server otherwise you will have real problems.
As Markus says you must also update stats and I'd suggest rebuild all indexes and run dbcc updateusage on any database you port from sql 2000 to sql 2005.
You'll have to compare query plans to understand what is going on with you code, in general sql2005 is less tolerant of poor T SQL and so queries may not run so well. Good code is fine, everything I've looked at like this is poorly written, lacks indexes etc. try your query on the DTA and see what it recommends.
I can't remember if you need to lock pages in memory for STD edition, I only use ENT, but you might want to check that out too.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Any advice on how exactly to configure the memory on 64 bit?
So far I have 8GB for the OS and did not limit then after reading (or misreading) the SQL server for 64 bit papers.
Al stats are updates - that was the first thing that I did. Also, no knowing if I could trust Rebuild e=index procedure in 2005 after I useed it and saw that there's no difference, I manually dropped and recreated the indexes on all affected in the queries tablesl and runt he stat again. No luck though.
Running wizard to advice for indexes did not bring wnything more then combining some of the single indexes that I already had.
Thanks a lot ,mj
-
Is anyone know what the Oracleora92Agnet service for? I have problem to start the Oralceora92Agent service. I don't know it will be a problem work without this service off. Thanks
-
SQL 2000 SP4 and SQL 2005 are both less tolerant of data type mismatches than earlier versions, and this can make queries run much slower.
If you have a WHERE clause comparing an int with a bigint, in SQL 2000 SP3 and below this join could be indexable (SARGable), but in SQL 2000 SP4 and above this is never indexable. This is because SQL now converts the int to a bigint for every row retrieved before it does the compare. This applies when comparing 2 colums or a column with a SP variable. You may need to review all your WHERE clauses and ensure the data types are compatible.
Google will help you find the full details of this behavior, which also affects other data types.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Something else that I've noticed in 2005 vs. 2000 is the optimizer in 2005 appears to try to avoid a sort operation in the query plan at any cost. This means that if you have an order by clause, the optimizer will chose an index that pulls the data already sorted, even if that index has nothing to do with the where clause! Obviously, I didn't build it, but my hunch is that the optimizer isn't taking into account the amount of rows it estimates when it is designing the query plan. The sort operation is one of the costliest you can have in a query plan, so I understand why the programmer tried so hard to eliminate it. However, how costly it is is directly proportional to how many rows are involved in the sort. If you are dealing with a few hundred thousand rows, then chosing the wrong index in order to avoid the sort will be more efficient. In most cases, however, we are dealing with queries that return one hundred or less rows. In this case, chosing the wrong index is a very costly mistake. Instead of initially pulling a narrowed down list in accordance with the WHERE clause, SQL Server pulls all rows, properly sorted, and then applies Filters to accomplish the where clause. Depending on how many rows are in the source table, this can cause horrible performance.
The only way I have been able to work around this issue is to see the correctly optimized plan on SQL 2000 or to tune it myself and use an index hint to force the 2005 optimizer to use the sort operation at the end, but with the correct index.
So this means that migrating from SQL 2000 to SQL 2005 does require some real testing ahead of time especially for performance.
Dan
-
We too had a similar issue and couldn't figure out what was happening. The base install of SQL 2005 sets SQL to manage all of the memory something like Max Memory 2TB. Our server had 4GB and that was twice the size of what we were using on SQL 2000. We did switch from 32bit SQL to 64bit and boom all of a sudden we would loose connectivity to the DB for about 3-5 minutes and then connectivity would just start again. MS said that they system was allocating virtual memory faster than it was releasing and eventually, it lost and had to free the memory (making sure everything was written) and once that we done, our connectivity started again.
We spoke with Microsoft PSS and come to find out we needed to
1. Reindex and update statistics more frequently,
2. Limit our RAM (we now have 3GB for the SQL to manage leaving 1GB for the OS and our Page file was 1.5x the Memory at 6GB) on the current instance (We have also ordered more RAM),
3. We went back and looked at all of our queries/indexes as the optimizer did change and we are still in the process of doing that.
So far that has helped us keep the gremlins away.
SJ
-
for 64 bit ent ( I assume std is the same ) you must enable lock pages in memory for the sql service account. You don't need awe enabled. You must set server max memory to be around 2gb less than total server memory - you'll have to experiment but 2gb seems ok with 32gb of ram. You'll also want to be at sp2 and either 3161 or 3175 rollup - it does appear the engine / optimiser are buggy - hopefully sp3 will bring some stability.
watch your level of ad-hoc queries and effective parameterisation, or lack of it.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Ed,
You said "SQL 2000 SP4 and SQL 2005 are both less tolerant of data type mismatches than earlier versions, and this can make queries run much slower." Can you cite a source for that info, a KB article or link?
I have a customer using a 3rd party app where they do a join between an int and a varchar, I know that's poor design & told the customer that, but the vendor just shrugged it off. I was actually surprised that it does work that way, but I'd need some kind of evidence to prove it is bad enough for them to redesign their .NET application.
-
This is my SQl server version after I have applied SP2 - 9.00.3042.
I saw some higher numbers in the previous replies - like 3061. How to get to this numbers - hot fixes, patches?
My main problem is when there is an index on column 1,2,3, but the query includes columns 4 and 5 as well, SQL server 2005 makes full table scan. Sql 2000 on the same query bookmarks it and row id look up is performed.
If I create an indexs on all 5 columns - SQl server 2005 is happy and do not scan.
(same code, same data loaded, indexed recreated and new stas run on 2005)
Any idea on how to fix that?
Thanks a lot, mj
Thanks a lot, mj
-
Can you post the query, or an approximation, including the where clause an order by, if there is one? Also, could you post the create script of the Index? I'm still willing to bet that the 2000 plan has a sort at the end, and the 2005 plan eliminated the sort operation.
-
2005 comes in four flavors. I would not expect all four versions to be at the same level. So be careful you are not comparing apples to oranges. standard enterprise, etc.
Accepting all invitesViewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply