June 17, 2004 at 5:49 am
Hello everyone,
I need a quick and simple idiots guide to what settings affect the query optimizer within SQL Server 2000.
My problem is :
-I have two machines both running sql server 2000 each with a database identical to the other(both were restored from the same backup file).
-When I run a particular sproc (that also uses views) on one machine the query takes almost 20 seconds.
-When I run it on the other the query takes less than a second.
-The execution plans between the two are quite different and there seems to be a couple of bottle necks in the slower one. Seeing as the two DBs come from a common source all the data contained in them and the table indexes are identical which leaves me to assume the problem lies in the actual installation of SQL server and some setting that makes the query optimizer work properly. Unfortunately I had nothing to do with the install of either SQL Server so I don't know what query optimizer settings were selected (if there are any available during install).
-Just in case anyone was going to suggest this - The views being used aren't indexed but I'm not able to do this anyway due to our client's current SQL Server installation
Any suggestions. I'm hoping that there's a system stored procedure sp_makequeryoptimizerrunproperly or something
Thanks
June 17, 2004 at 6:21 am
You could use the Showplan (see BOL) to give better estimates of the number of reads etc that each is using, however you'll probably see something easier by a quick look at the servers themselves. Check that both have the same cpu speed and quantity, check the amount of RAM in each, then the subsystem for the disks.
June 17, 2004 at 7:24 am
The server that's executing in under 1 second is a better spec all round to the laptop however I don't believe there is enough of a difference in the specs to warrant such a large difference in the query time and it's not like the query should be taxing anyway, the three largest tables being used contain 35'000, 25'000 and 9'000 rows. I should also note that I've tried this on four machines -
the client's main server running personal edition of all things where the query is taking over 30 seconds;
my colleagues laptop running developer edition where the query takes 20 seconds;
my workstation running enterprise edition where the query takes less than a second and
another colleagues workstation also running enterprise edition where the query again takes less than a second
The client also has a very slow network (and an even slower network administrator) which compounds the problem and it wasn't until I tried the query on the laptop and saw the different execution plan that I actually thought there may also be a problem with the SQL Server installation itself.
I'm just trying to narrow down the reasons for the differences between execution plans and times as it's quite difficult for me to optimise a query on my workstation when it appears to be running perfectly well. And I'd rather not have to go down to the client's office to work on their server and I'm sure they'd rather not have to pay me to do so.
Any other suggestions?
June 17, 2004 at 7:33 am
Aside from potential licensing issues running personal edition on a server, they could be running into memory problem, are you able to get an idea of the other processes running on the server?
June 17, 2004 at 7:41 am
Not readily but their network is grinding to halt despit the network admins assurances that everything's working fine.
Do you think it's to do with the differences between the different editions and how they optimise?
June 17, 2004 at 9:04 am
Is it possible for you to get their admin to send you the machine specs and some perfmons of the basics (disk, cpu, memory) on the box as well as a list of the processes that are running?
The thing is that your query obviously runs fine as you are getting fast results, indicating that the trouble lays either in their hardware, or their configuration somewhere. It's just a matter of trying to pin it down.
June 18, 2004 at 7:53 pm
I reckon it'd be well worth checking out the indexing, maybe run a sql profiler and save a trace file and use the index tuning wizard if you're not fully up to speed on indexing. Also maybe the indexes need to be rebuilt?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply