Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

LivingForSqlServer

I am Ramkumar, 34 years old Consultant, trainer, blogger and speaker at SQL Server user group in India. I have more than 10 years of experience as a developer and SQL Server DBA. I love reading, teaching and blogging about SQL Server internals and performance tuning. My Facebook page: https://www.facebook.com/LivingForSqlServer

My Favourite SQL Server Myths and Realities

It has been more than two months since I blogged. I have been blogging in my Organization internal blogging site on Management topics for last couple of months (just a side effect of my new project lead position).
In parallel, I have started giving session on Performance tuning and Storage internals for my Organization (Cognizant - India) Academy.

Currently, I am focusing on understanding transaction logs and how to read t-log records. Let me write an article on this shortly.

In between, I just wanted to share my favorite SQL Server myths and realities with you all.

References are given in all suitable places.

Note for Newbies: Some myths listed here are talking about behaviour change of a feature/command in a specific scenario and may
not be the case for all the scenarios. Read experts books to understand internal implementations in detail.

 Myth 1: Table variables are created and manipulated in memory instead of the Tempdb database

 

Reality: It’s proved that even table variables can consume space in Tempdb.

 

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

Myth 2: Truncate cannot be rolled back because it’s not a fully logged and only page deallocations is recorded.

 

Reality: You can rollback data from TRUNCATE if surrounded by a transaction and session is not closed. 
http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-When-are-pages-from-a-truncated-table-reused.aspx

http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/

 

  

Myth 3: An Update to a row in the table is immediately saved to disk

 

Reality: Updates are affecting pages in memory and T-Log immediately. Dirty pages are updated into disk later (Checkpoint, Lazywriter)


http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

 

Myth 4: Query Optimizer can perform SEEK in a Composite index if any one key column part of composite index is referred in WHERE/JOIN clauses.


Reality:

Index SEEK is possible only if first column of any usable Index is present in WHERE or JOIN clauses.

Update: If a column is used in WHERE or JOIN clauses and if that column is not a first column of any available index, SEEK is not possible.

Myth 4, 6 and 13 are closely related. they talk about the usage of Indexes by query processor.

 

Myth 5: Rebuilding a clustered index always rebuilds all non clustered indexes

 

Reality: As per Paul, this is not the case for SQL 2005 and SQL 2008. Product is improved a lot.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pd

Myth 6: SELECT COUNT (*) will force a table/clustered index scan

Reality: smallest  Non clustered index (non filtered) scan will be performed if available.
Queries having COUNT (*) has possibility of using any non clustered index (non filtered) with less size if the columns in index (Index key(s), included columns) can serve the purpose.
A query can make use of any smaller size Non Clustered Index (NCI) to execute count(*) with no filter.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pd

Myth 7: Column values are stored sequentially in same order columns are present in table structure

Reality: Nope. Fixed length columns and Variable length columns are stored separately

http://www.sqlservercentral.com/blogs/livingforsqlserver/archive/2010/12/21/sql-server-storage-internals-part-4-how-to-read-a-heap-page.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

Myth 8: NULL won’t occupy space in SQL Server

Reality: Yes for Variable length columns and No for Fixed length columns

You can create a table with nullable fixed & variable length columns and check PAGE content.

Myth 9: Rows in Clustered index table are physically sorted based on clustered index key

Reality: Yes. But you can’t expect the same at all the times. SQL Server can make use of
 Row offset array entries to maintain index order.

We can find lots of demonstrations on web.

Myth 10: Data type of a table is not a key criteria while doing query tuning?

Reality: Data types decide how many rows can fit into a page. A wrong data type may consume 1000’s of excess data pages. This will directly affect IO.
A simple char(100) can do this. Be cautious in choosing data types.

Myth 11: All Indexes in my table will be used in query processor

Reality:  Query processor may keep your index untouched for many reasons.
Lots of Non unique values in index keys or duplicate indexes on same columns can make an index unusable.

Myth 12 An index FILLFACTOR is used by regular DML operations

Reality: Thanks to Paul. FILLFACTOR only applies during index build and rebuild operations.

http://www.devconnections.com/updates/LasVegas_Spring10/SQL/Randal-SQL-SDB306-Mythbusters.pdf

Myth 13: I have created some indexes on frequently used columns. my work is done. All my indexes will be used by query processor.

Reality:

1. Query Processor may decide to go for TABLE SCAN/Clustered Index SCAN if record count of a table is very less (its more faster to refer few data pages instead of refering index pages and perform lookups) .

2. Query Processor may decide to go for TABLE SCAN/Clustered Index SCAN if bigger range of rows need to refered in Non Clustered Index (lookup of huge records is a pain)

3. Query processor may simply keep an  Index unused if the Indexed column is having huge number duplicate values (like city, comments or some datetime columns).

Conclusion:

Paul's  complete myths and realities collection is available in http://www.sqlskills.com/BLOGS/PAUL/ (also available in pdf version)

Will catch you in my next blog. till then happy reading.

Comments

Posted by Jason Brimhall on 28 June 2011

Nice list of myths.

Posted by Anonymous on 29 June 2011

Pingback from  Dew Drop – June 29, 2011 | Alvin Ashcraft's Morning Dew

Posted by robert matthew cook on 29 June 2011

thanks for another good post ramkumar, myths are always great to review

not sure about wording in myth 4, based on my interpretation of it here is a script checked on SQL Server 2008

--assuming there exists a multi-column (salesorderid, salesorderdetailid) cluster index (pk_salesorderdetail_salesorderid_salesorderdetailid)

--assuming there exists any other index these queries will use most narrow index even without salesorderdetailid being left most column

use adventureworks;

GO

SELECT TOP 1 salesorderdetailid FROM sales.salesorderdetail;

GO

SELECT COUNT(*) FROM sales.salesorderdetail WHERE salesorderdetailid IS NOT NULL;

GO

SELECT COUNT(*) FROM sales.salesorderdetail WHERE salesorderdetailid > 0;

GO

SELECT COUNT(*) FROM sales.salesorderdetail WHERE CONVERT(varchar(11), salesorderdetailid) >= '0';

GO

Posted by Ramkumar on 29 June 2011

Thanks Jason.

Sqlmashup, very happy to see your valuable comments.

I think, Myth 4 & 6 are closely related and definition  of Myth 4 should be more accurate (let me update that in detail).

Myth 4:

Myth 4 talks about the usage of index to perform SEEK operation. If a column (with less duplicates) is used in WHERE or JOIN clauses and if there this column is not a first column of any available index, SEEK is not possible.

Myth 6:

Queries having COUNT (*) has possibility of using any non clustered index (non filtered) with less size if the columns in index (Index key(s), included columns) can serve the purpose.

again We have mentioned only high level points and we can't explain in detail on how and when optimizer choose an index in this window right.

Sqlmashup, I have learnt few more usage of NCI with your comments. thanks again.

Posted by Ramkumar on 29 June 2011

Myth 4 & 6 are updated with bit more details.

And I have added one more myth 13 in this list.

think, We cant cover how & why query processor chooses an index in this list.

we have to spread awareness on index usage by query optimizer to developer community in all possible knowledge sharing formats.

Posted by sqlmashup on 30 June 2011

thanks for the clarification but please let me know if this is still off.

you are busting the myth that "having an index (columnA, columnB) means columnB can be used for all index operators".  you explain the conditions where an index seek operator would have be performed if for example there is existed a secondary index (columnB, columnA) but with only index (columnA, columnB) then no index seek operator can be used.

sorry if i added confusion to something that was already clear to begin with.

Posted by Ramkumar on 30 June 2011

sqlmashup, thanks for your comments. your comments leads to dig the topic more gives me a kind of good learning experience. thats what I want.

What i try to convey is:

-----------

Myth4 talks about when SEEK possible:

having an index named Index1 with (columnA, columnB) means,

IF

(ColumnA is not present as first column of index named Index1 and if Column B is part of WHERE/JOIN)

THEN,

SEEK on Index1 is not possible.

In other words, SEEK of columnX is possible only if the columnX is present as a first column of any available Index.

ELSE,

We can expect only Table or CI or NCI SCANs. simple.

-------------

Myth6 talks about usage of any available smaller size indexes:

if two Non Clusterd Indexes (NCI) having all required columns to execute a query, then NCI having lesser size can be considered by query processor.

for a simple SELECT COUNT(*) FROM TABLE.

any smallest size NCI with no filter can be used. TABLE/Clustered Index SCAN is not required. I learnt this from Pauls Myths list.

---

again both are high level points.

as you know, Query processor goes thru series of stages to decide,

1. Index to be used or not. (Myth 13).

2. Pick a right index

3. Decide SEEK/SCAN operators.

Please let me know if I have missed any or made a wrong statement.

Posted by dave.clark on 8 July 2011

Thanks Ramkumar - good list of myths.

Posted by MidBar on 26 July 2011

Interesting collection. Thanks.

Posted by Anonymous on 18 August 2011

Pingback from  #sqlservermyth cost threshold for parallelism = seconds | sqlmashup

Leave a Comment

Please register or log in to leave a comment.