June 2008 - Posts

0
Comments

Parallelism in Index Scan by Kasim.Wirama

Parallelism in Index Scan By : Kasim Wirama, MCDBA Previous articles, I post some information about internal of parallelism. Now take a look parallelism in index scan. Is that bad if you have parallelism in index scan? Not really, I think. If the query...
Filed under:
0
Comments

Internal of Parallelism by Kasim.Wirama

Internal of Parallelism By : Kasim Wirama, MCDBA If you notice in execution plan, probably you would find out parallelism in some operators of an execution plan. Let’s look inside parallelism behaviour in general. Parallelism of an operator is made up...
Filed under:
4
Comments

SQL Internal Viewer Tool by Kasim.Wirama

You would like to know internal structure of your SQL Server? Now, you can view how fragmented your database visually or other information regarding to allocation map through freeware tool for SQL Server, namely SQL Internal Viewer ( http://www.sqlinternalsviewer...
Filed under:
0
Comments

Table Spool in Equality Correlated Subquery by Kasim.Wirama

Table Spool in Equality Correlated Subquery By : Kasim Wirama, MCDBA Recent posting, I examine spooling characteristic in nonequality correlated subquery . In nonequality correlated subquery , optimizer generate index spool which created index on the...
Filed under:
1
Comments

Parallelism Overview by Kasim.Wirama

Parallelism Overview By : Kasim Wirama, MCDBA When you observe execution plan for some query that processes many rows, you will find operator with small arrow at graphical execution plan. For example, you have Index Scan operator with double small arrow...
Filed under:
0
Comments

Degree of Parallelism, Max Degree of Parallelism, Affinity Mask and Thread Allocation by Kasim.Wirama

Degree of Parallelism, Max Degree of Parallelism, Affinity Mask and Thread Allocation By : Kasim Wirama, MCDBA You can view parallelism in your execution plan by viewing either graphically or textually. Optimizer decides between parallelism and serial...
Filed under:
1
Comments

Decorrelating Correlated Subquery by Kasim.Wirama

Decorrelating Correlated Subquery By : Kasim Wirama, MCDBA I have written posting about correlated and noncorrelated subquery. Now let’s take a look correlated subquery but optimizer generated noncorrelated query-like execution plan. Here is query to...
Filed under:
0
Comments

Skip Stored Procedure Recompilation by Kasim.Wirama

Skip Stored Procedure Recompilation By : Kasim Wirama, MCDBA Stored procedure (and also function) is preferable to be implemented in SQL Server because its compilation code is cached, so next execution will take the compilation form in cache. Imagine...
Filed under:
0
Comments

Rebind and Rewind on Non Equal Correlated Subquery by Kasim.Wirama

Rebind and Rewind on Non Equal Correlated Subquery By : Kasim Wirama, MCDBA This posting, I would show other execution plan operator that has caching capability regarding to non correlated subquery that involved non equality relationship between inner...
Filed under:
1
Comments

Execution Plan for Noncorrelated Query by Kasim.Wirama

Execution Plan for Noncorrelated Query By : Kasim Wirama , MCDBA This posting, I would like to show various possible execution plan for non correlated query and possible action to rewrite query for better execution plan. Let’s run the query at Northwind...
Filed under:
1
Comments

Index Union Analysis by Kasim.Wirama

Index Union Analysis By : Kasim Wirama, MCDBA When there are 2 or more where criteria which are joined with OR, each columns is different among others, they are indexed and search selectivity is high, optimizer could consider multiple indexes and join...
Filed under:
0
Comments

Peer-to-Peer Replication in SQL Server 2008 by Kasim.Wirama

Peer-to-Peer Replication in SQL Server 2008 By : Kasim Wirama, MCDBA One of interesting enhancements made in SQL Server 2008 February CTP is in area of replication, peer to peer replication particularly. Now with SQL Server 2008 February CTP, a SQL Server...
Filed under:
0
Comments

Dynamic Index Seek Review by Kasim.Wirama

Dynamic Index Seek Review By : Kasim Wirama, MCDBA Other execution plan pattern that is noticeable is dynamic index seek. A plan is called dynamic index seek when the optimizer doesn’t know variable values at compile time, and the plan generates index...
Filed under:
0
Comments

Concatenation, Merge Join Concatenation and Hash Match Union by Kasim.Wirama

Concatenation, Merge Join Concatenation and Hash Match Union By : Kasim Wirama, MCDBA Other operators that you might encounter in execution plan are concatenation, merge join concatenation and hash match union. Usually these operators are generated when...
Filed under:
0
Comments

Looking into Hash Aggregation by Kasim.Wirama

Looking into Hash Aggregation By : Kasim Wirama, MCDBA This article, I would like to look into possible execution plan for hash aggregation. Query for hash aggregation is same as that for stream aggregation, the difference is that stream aggregation using...
Filed under:
Copyright © INDC, 2006. All rights reserved.
Powered by Community Server (Commercial Edition), by Telligent Systems