Should Sybase v10.0.1.3960 running on VMware still be able to use parallel table scans ?

I have some fairly simple SQL that performs a couple of joins from a small customer table to a reasonable head table then onto quite a large lines table. Some of the where clause is using fields that aren't in the index, but on my development machine it takes a few seconds to run, on our live servers it takes even less time to run, however on our test system (which uses Vmware) it runs for at least 15mins before I get bored and kill it.

I've checked the plans on all the machines and the test server doesn't try to use parallel table scans but the others do. I've not had much experience of VMware or parallel table scans (I didn't even know about them until I checked the plan), Is there anything obvious I should be looking into ?

asked 12 Oct '10, 14:43

Daz%20Liquid's gravatar image

Daz Liquid
861182338
accept rate: 28%

edited 12 Oct '10, 21:03


Several possibilities jump to mind; seeing the graphical plan would help.

Some things to consider:

  • what is the connection option optimization_goal set to?
  • what is the connection option max_query_tasks set to?
  • how many cores does the VMWare instance make visible to the guest OS?
  • are you running dbeng or dbsrv?
  • how much memory is available to the server? How was the server started?
  • What is the server's multiprogramming level (-gn switch on the command line)
  • are you running the query in question in isolation, or are other connections active?
  • is it possible that you have inaccurate statistics with the database on the test server, causing a poor plan choice by the optimizer? (ie are the estimated and actual statistics for some particular predicates completely out of whack?)
permanent link

answered 12 Oct '10, 17:11

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

2

It sounds like the short answer to the first question on the first line is yes, the query engine is able to use parallel table scans under VMWare.

(12 Oct '10, 18:28) Breck Carter

'drop statistics' on the tables we were using worked for our test server.

Our test db was running a backup of the live db with the same startup options as the live server except for dbeng instead of dbsrv, vmware had 2 cores available.

At a guess maybe after moving the db from one server to another it didn't have enough time to generate some meaningful stats ?

(12 Oct '10, 20:51) Daz Liquid
Comment Text Removed

I did wonder why the plan was only using one table scan now rather than the 2 parallel ones used by the live server, the speed increase 15 mins down to 2 seconds must have come from the rest of the plan changing after the statistics changed. I just assumed (wrongly) that it was the parallel part that was making it go so much faster on the live. Wonder what the reasoning for dbeng only using 1 cpu is?

(12 Oct '10, 21:50) Daz Liquid
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11
×2
×1

question asked: 12 Oct '10, 14:43

question was seen: 983 times

last updated: 12 Oct '10, 21:03