Need help tuning SQL Server 2000
-
I'm going to have to do the BCP over the weekend with no activity on the current system, and that is why I havent' said anything about it.
I don't know what the MAXDOP is set to. Is that a system-wide setting, and, if so, what do I use to change it in T-SQL?
Hyperthreading is disabled on the dual processors, I did check that.
-
No, wait, I do see the MAXDOP setting in sp_configure. It's set to 0 on both machines. I didn't notice it before.
-
If your system recognizes 4 processors, I would try setting the MAXDOP to 1. If it only sees it as 2 (depending on hyperthreading/dual cores, etc.) I wouldn't change it, as it is the same as the other system.
It is a setting that can be changed on the fly, no restart needed, so it should be easy to test. You will need to RECONFIGURE WITH OVERRIDE.
-
I'll try patching SQL Server, setting maxdop to 1 (I'll try it anyway, even with HT disabled, can't hurt), dropping the server memory to 2048MB and doing the BCP this weekend. If it works okay, I might take a step back and do a restore from backup, which I honestly would rather do anyway, just because I'll have to do this again when I promote the new server live (we don't have replication or logshipping set up yet because the existing system was the only thing that could handle the load of the Java application, so we were praying for some time, as you could well imagine).
I still need to know how to unpatch back to SP4 revision 2039 if need be.
Thank you guys for all your help. I'm not a DBA (I program in VB, so I don't even know Java), though I would like to start training to be one at the very least in SQL Server. Right now, I'm doing the equivalent of taking swimming lessons by being thrown out of a boat in the middle of a lake, if you will.
-
Yeah I appreciate that you're not a DBA - I'd really advise you to find one!!
Leave parallelism alone - check your i/o's what is the difference between the two queries?
Memory - the max memory std can use is about 1.6Gb, I suggest you either set both max and min to around 1.5Gb or leave it to dynamic.
I'll have a rant here !! to all the "people" who keep wittering on about maxdop and parallelism - IF and I say IF there is parallelism involved it will show in the graphical or text query plan ( it doesn't hide or anything! ) so if either of your query plans show parallelism then add a maxdop 1 hint to your query NOT to the entire server. Don't turn off HT, why do you think intel put it there in the first place? ( rant over!! )
The service pack changes to the optimiser may well have caused the change to the plan - there's not enough room or time to post on precise query plans .. on sp4 apply the 2187 rollup and see if the same happens - then apply the same service packs to your other server and see what happens.
Test like for like.
Finally if it's scanning it ( the optimiser ) considers your index less than useful - see if you can't create a more useful index, probably with more than 1 column.
As someone who does this for a living i'd have been changing indexes to optimise the query - fact is queries change, data changes, optimiser changes - it's not a precise art.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
In regards to removing a service pack, take a look at this link http://support.microsoft.com/default.aspx?scid=kb;en-us;314823&sd=tech
In regards to MAXDOP, unless you have the specific information from the query plan that indicates no use of parallelism, DEFINITELY try it. It is not worth skipping for the small time it will take.
Colin -- please notice from the previous posts that Brent is working with a third party application with out access to the source queries (as of the last posts), so some of the information that we would normally have we don't.
I agree with Colin, unless there is a reason specifically for you to turn of HT, I would recommend against it as well.
Do you have any experience with Performance Monitor or SQL Profile? If not, do you have a resource available to you that does? These are the tools that are going to give you the information needed to solve this issue.
-
I have a query where the optimizer does not choose any parallelism in the execution plan which runs in about 45 seconds.
then I put the option (maxdop 1) hint in the query and an entirely different execution plan is chosen and runs in about 15 seconds.
I can also explicity force an index hint and the optimizer will chose the better execution plan and run in 15 seconds.
However, on another identical server without the hint and without the maxdop option the query runs in 15 seconds.
go figure.
-
Cool isn't it? Welcome to my world!!
You don't need access to code to get the query plans - queries can be extracted by profiler or third party tools such as teratrax or sql disgnostic manager and then pasted into QA for graphical analysis. Profiler will produce excellent query plans although the downside is you can't save them and for the beginner they can be a bit daunting.
I'd either play with the indexes to make them more useful or add the hints , making sure that you keep a check on progress. I figure it's probably the optimiser changes in the sp and your query is on a borderline on cost.
The database on your new server was placed there by restore or attach and the collations of the two servers are identical I hope?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
How do I add hints without having access to the code that is generating the queries? The indices being skipped are the primary keys of the table in both traces.
The database on the new server was placed by restore from backup on the old server. What would I look for to find the collations you are talking about.
-
Also, I don't want to remove SP4 (as it was preinstalled), I just want to make sure if I patch it to 2187 and it messes something else up, I can remove the patch and revert it back to 2039.
-
Check this link out to read more about it. This link gives you the package that should get you to build 2187 of Sp4
-
Mike, how do I remove the patch if it doesn't go right? I've got the installation program for it.
-
Query hints have to be added to the source code, so that isn't an option for you.
The link with info on removing a service pack (patch) was in a post above, here it is again. http://support.microsoft.com/default.aspx?scid=kb;en-us;314823&sd=tech
What were the results of the BCP process this weekend? (Guessing not good, since you are looking for more help.
) -
Update: I have set the MAXDOP to 1 and wanted to see if that helped. So far, yes, it has. I'm going to run a trace to verify that the index we were expecting was being used (at the least, we aren't having data out of order in this one screen I tried, which led the application vendors and me to believe it was ignoring the primary key), then patch SQL Server to 2187 from 2039. I'm going to try turning HT on and maybe increasing the threshold for parallel execution and see if returning MAXDOP to 2 causes a problem.
What I didn't understand (which was why I kept asking) was that the instructions showed how to revert to a previous SP, rather than the same SP level with a different revision, hence my confusion. Would these instructions also work if I wanted to unpatch the SQL Server and go back to SP4 2039?
-
MAXDOP didn't help like I thought it would. Trace still shows index scans against the non-primary key. The good news is the rowset returned isn't out of order any more.
I didn't use BCP to transfer the data. I used a DTS package to transfer objects and data from the existing server's instance to the new server's instance.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply