Meaningful statistics

Question

Greetings,

We have a number of spatial data-sets running on Oracle (two cores with 32 MB of RAM).

The plan is to migrate to SQL Server 2012 (16 cores with 65, 491 MB RAM) but have a number of performance issues, that we don't experience on Oracle??? 

I have run a number of reports with a range of statistics after running ANALYZE, rebuilding indexes. Are there a range of values to determine what is meaningful and what can be ignored??  

There are a number of Spatial queries that have Clustered Index Scans that are resource intensive, cannot see how to resolve this??

>> (@P1 varbinary(max))select  SHAPE,  STYLE_CODE,  OBJECTID,  SQLOSDATA.DBO.OS_MMT_AREA.GDB_GEOMATTR_DATA  from  sqlosdata.DBO.OS_MMT_Area  WHERE  OS_MMT_AREA.SHAPE.Filter (@P1) =  1

---

Clive

Answer 1

You are performing spatial operation for each row of the table (CI Scan). This would be slow on the large tables. Your query does not have any other predicates to benefit from nonclustered indexes so statistics does not matter.

Did you try to create spatial index on OS_MMT_AREA.SHAPE column?

---

Thank you!

Dmitri V. Korotkevitch (MVP, MCM, MCPD)

My blog: http://aboutsqlserver.com

Answer 2

Hi Dmitri,

Thanks.

I updated the Spatial Index (SI) again, seems it had lost the SI. The query is using the min X,Y; max X,Y now.

The CPU utilization has gone down from 50% to 20%. But 20% for a single user seems VERY high, what happens when 20 - 30 people hit it???

However!! SQL Server with 10 cores in the Test environment, is still slower than Oracle (2 cores) in the Production environment. Any other suggestions??

Thanks

Clive

---

Clive

Answer 3

Clive,

Unfortunately, it is impossible to answer your question without deeper analysis. I need to see DDL, queries and know about your data distribution to provide meaningful answer. 

Speaking of SQL Server vs. Oracle, let's not start the holy war :) Both platforms are good - you just need to design the system in the way that utilizes them properly. I've built the systems that handle tens thousands of TPS and thousands of users on 8-vCPU SQL Server. I am not sure if Oracle or any other DB platform would support it if I just migrate the database "as is" without any platform-specific refactoring. 

---

Thank you!

Dmitri V. Korotkevitch (MVP, MCM, MCPD)

My blog: http://aboutsqlserver.com

Answer 4

Hi Dmitri,

Thanks for the reply.

I wasn't comparing vendors/products. Trying to use a baseline if Oracle has two cores and SQL Server has 10 cores both running on the same environment/network, I would expect better results from SQL Server.

Some more info, hope this is more useful..

Thanks,

Clive

---

Clive