Let me start by saying that the contents of this post is not very advanced. If you have read the excellent paper ''Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005'', http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx and understood it, you would already know below, and much more... I was reading a ...

Is it quicker and/or lower overhead to insert into a heap vs. a clustered table?I don't know. So I decided to do a test. Some background information first: The test was inspired from a sidebar with Gert-Jan Strik in the open newsgroups. Basically I expressed that a heap doesn't automatically carry lower overhead... just because it is a heap. Now, ...

I got some more feedback (see yesterday's blog post on this) which I now incorporated into sp_indexinfo. See change log at bottom of the article for details. http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

(See my initial blog post for general information about this proc.) I just updated sp_indexinfo a bit: I added the schema name as a new column in the output of the first resultset. I added an optional second resultset with missing index information. This information is obviously drawn from the missing index dynamic management views. ...

I am. I find myself endlessly hunting for index information when working against the various SQL Servers I come in contact with. And, sure, the information is there. You just need to go and get it. This generally means that I start with sp_helpindex. Then some SELECT from sys.indexes. Then some more against sys.partitions and sys.allocation units ...

  My Thursday commentary for the SQL Server Magazine Update e-newsletter discussed quiet changes in SQL Server 2005 and I just realized there's another one I wanted to mention. If you use Indexed Views at all, you're probably aware that there is a set of SET options that must on set appropriately in order for your indexed views to work as ...

I had been thinking of this post all day, and then noticed that Denis wrote a post with almost the same name. I was worried he might have written about something similar, but it turns out not to be the case. A group of colleagues have been having a discussion about this topic recently, which was spurred by the fact that the  Microsoft ...

I recently received a question about the storage of nonclustered index keys. I am assuming you are aware of the fact that if your table has a clustered index, SQL Server uses the clustered index key (all of its columns, if it is a composite index) as a 'bookmark' in your nonclustered indexes,  to allow your nonclustered indexes to uniquely ...

Yet another fragmentation post, to answer a question asked in response to a previous answer.... SQL_Girl is still confused about DETAILED vs LIMITED, and I don't blame her. She reported that the BOL says:  ''The nonleaf levels of indexes are only processed when mode = DETAILED. ''  But I had said: ''The second misunderstanding involves ...

  A couple of questions arrived regarding my earlier post about fragmentation on the system tables, and since the questions really don't have anything to do with system tables, I felt they deserved a new post. 1) Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this ...