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 Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

(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 ...

When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when it cannot quickly find a page in an existing extent with sufficient space to hold the row being ...

All of sudden the re-org of an index started failing and no error was displayed within that scheduled job we execute. After executing the code from Query Editior I was able to get the message as follows: Msg 2552, Level 16, State 1, Line 3 The index ''<name>'' (partition 1) on table ''TableName'' cannot be reorganized because page level ...

Within your SQL Server environment there are few simple steps you can take up in order to gain performance to a level, for instance when configuring a SQL Server that will only contain a few gigabytes (GB) of data and not sustain heavy read nor write activity, it is not as important to be concerned with the subject of disk I/O and balancing of SQL ...

You may be aware that the indexes on a SQL Server database take up space, so in case of unused indexes its obvious that they waste storage. It is easy to find the indexes using SP_HELPINDEX statement against a table, so to identify the unused indexes what is the way out. In the previous versions of SQL Server Index Tuning wizard is only way out, ...