May 14, 2008 at 3:16 am
Hi Friends,
I have two OLAP database with the same structure in my Development Server. I have a common query which joins 5 tables and the joins follows the correct columns.
When I am running the Query, In 1st Database the query follows a Index seek which Take 4% of my query Cost, where in 2nd Database the query follows the Index scan which takes 44% of my Query Cost.
I checked with the following Steps to resolve the issue:
-----------------------------------------------------
--> As the structure of both database is same, the datatype is also same for index column.
--> As we are running the same query, the where clause is same, I mean to say it contains the same index column for filtration of data.
Can you please tell me what may be the reason?
Cheers!
Sandy.
--
May 14, 2008 at 3:30 am
Do you also have
1) Same edition and service pach of SQL Server
2) Same number of records in all underlying tables
3) Updated statistics
To mention a few insoection points.
N 56°04'39.16"
E 12°55'05.25"
May 14, 2008 at 3:39 am
Peso
--------------------------------------------------------
1) Same edition and service pach of SQL Server
2) Same number of records in all underlying tables
3) Updated statistics
hey Peso,
Yes, Same Edition and Service pack and also same number of records
but not sure about 3rd point, can you clear me please?
Cheers!
Sandy.
--
May 14, 2008 at 6:29 am
Hey Peso,
I got the solution. Its working fine now and its going for a Index Seek.
Cheers!
Sandy.
--
May 14, 2008 at 6:36 am
What was the solution in your case.
I think other users in same dilemma might benefit from the knowledge gained here.
N 56°04'39.16"
E 12°55'05.25"
May 14, 2008 at 7:09 am
Hey peso,
As this is a OLAP database,
I just drop the Index and recreated
very Imp:
Execute sp_updatestats.
now its going for a Index Seek.
Cheers!:P
Sandy.
--
May 14, 2008 at 7:35 am
In these situations , you can use FORCESEEK table hint. For more details look http://msdn.microsoft.com/en-us/library/bb510478(SQL.100).aspx
May 14, 2008 at 8:02 am
hey Hasan,
Cool Man, I was roaming for this....
Thanks a lot, But I have a little confusion on this link topic, the index seek is taking 88% cost, where as the Index scan is taking 52% only.
I am talking about the Execution Plan Graph provided by the links.
Why So?
Cheers!
Sandy.
--
May 14, 2008 at 9:58 am
The percentages are RELATIVE to the query.
88% of 2 seconds are still hell a lot better than 54% of 111 seconds.
N 56°04'39.16"
E 12°55'05.25"
May 14, 2008 at 10:01 am
Hasan Mansur (5/14/2008)
In these situations , you can use FORCESEEK table hint. For more details look http://msdn.microsoft.com/en-us/library/bb510478(SQL.100).aspx
FORCESEEK is available in SQL Server 2008, not SQL Server 2005.
http://sqlserver.ro/blogs/cristians_blog/archive/2007/11/02/forceseek-hint.aspx
N 56°04'39.16"
E 12°55'05.25"
May 15, 2008 at 1:51 am
FORCESEEK is available in SQL Server 2008, not SQL Server 2005.
Thanks Peso,:)
But we can achieve this by running sp_updatestats on the Database.
here I wants to know, Is there any new join concept added to SQL Server 2008. (apart from Inner join, Outer join, Cross Join)
If so, then can you please guide me for this.
Cheers!
Sandy.
--
May 15, 2008 at 2:00 am
hey peso,
The percentages are RELATIVE to the query.
88% of 2 seconds are still hell a lot better than 54% of 111 seconds.
I've a little confusion regards the seconds you have mentioned in the above statements.
Can you please clear me where you got the time variables?
Cheers!
Sandy.
--
May 15, 2008 at 2:13 am
It was just an example.
An index seek is more likely to be faster than index scan.
So when you investigate the query using index seek and see that index seek occupies 88% of the resources from that query, it has nothing to do with investigating the second query where an index scan only occupied 55% of the query's resources.
Because the total time for the index seek can be 2 seconds only and the query using index scan could take 123 seconds.
So you can't compare percentages between two query plans.
For query 1, if the index seek is taking 88% of 2 seconds, it means it spends 1.7 seconds of the 2 seconds total seeking the index.
For query 2, if the index scan is taking 55% of 123 seconds, it means it spends 67 seconds of the 123 seconds total scanning the index.
The percentages of the individaul components within the query is vital, but cannot be compared to indivudual components in other queries.
The two queries total percentage (query 1 is 20% and query 2 is 80%) of the total batch can be compared. But these numbers do not really reflect the real world.
I have seen batches where query 1 is 20% and query 2 is 80% of batch, but when timing the queries separatly stated thatn query 2 was twice as fast and used less reads than query 1.
N 56°04'39.16"
E 12°55'05.25"
May 15, 2008 at 2:31 am
Hi Peso, 🙂
Really nice to here from you this concepts.
Now I am more clear on my topic,
Thanks for the same again,
Cheers!
Sandy.
--
May 15, 2008 at 2:44 am
Sandy (5/15/2008)
FORCESEEK is available in SQL Server 2008, not SQL Server 2005.
here I wants to know, Is there any new join concept added to SQL Server 2008. (apart from Inner join, Outer join, Cross Join)
SQL Server 2005 introduced the CROSS APPLY and OUTER APPLY.
And regarding hints... Only use as a last resort and if you really REALLY know what you are doing.
Because hints override the default query engine behaviour.
It is not often you can beat the query engine optimizer. And if you can and do, DOCUMENT it so that other developers can read about it.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply