Browse by Tags

• Backup
• Maintenance
• Performance
• SQL Server 2008
• SQL Server Agent
• Tools
• Trace
• Troubleshooting

• Another bugfix for sp_indexinfo

  JackMcC reported correctly at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/02/18/sp-indexinfo-updated.aspx that sp_indexinfo will count rows double (or even tripple) if there are lob or row overflow allocations. Now fixed. Thanks Jack. Read More... Posted Tuesday, November 09, 2010 10:26 AM by TiborKaraszi | 0 Comments Filed under: indexes, Tools

• Ola Hallengren's maint procedures now supports exclusions

  Ola has a set of stored procedures to do maint operations, see http://ola.hallengren.com/ . Ola has now updated them to support exclusions or inclusions of tables, indexes or even whole schemas from index rebuild/reorg. Check out http://ola.hallengren.com/Documentation.html#SelectingIndexes Read More... Posted Monday, August 09, 2010 8:02 AM by TiborKaraszi | 0 Comments Filed under: Backup, indexes, Maintenance, Performance, SQL Server Agent

• How selective do we need to be for an index to be used?

  You know the answer already: It depends. But I often see some percentage value quoted and the point of this post is to show that there is no such percentage value. To get the most out of this blog post, you should understand the basic structure for an Read More... Posted Thursday, April 01, 2010 10:39 AM by TiborKaraszi | 15 Comments Filed under: indexes, Performance

• Adding a PK online?

  I just read in a forum about a user who want to replikate a table, but the table doesn't have a PK. The table is pretty large, and having the table not available while adding the PK is undesireable. The table has a clustered index already, and there are Read More... Posted Wednesday, January 13, 2010 10:53 AM by TiborKaraszi | 2 Comments Filed under: indexes, Performance

• Heaps, forwarding pointers, ALTER TABLE REBUILD and non-clustered indexes

  Let's start with some background on forwarding pointers: Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new Read More... Posted Friday, August 28, 2009 1:15 PM by TiborKaraszi | 5 Comments Filed under: indexes, Performance, SQL Server 2008

• Key count in sys[.]indexes

  The old sysindexes table (as of 2005 implemented as a compatibility view) has a useful column named keycnt. This is supposed to give us the number of columns (keys) in the index. However, to make heads and tails out of the numbers, we need to understand Read More... Posted Tuesday, June 23, 2009 9:57 AM by TiborKaraszi | 1 Comments Filed under: indexes

• Match those types!

  This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the Read More... Posted Tuesday, April 28, 2009 1:30 PM by TiborKaraszi | 17 Comments Filed under: indexes, Performance, Tools, Trace, Troubleshooting

• Sp_indexinfo updated

  For a long while, I've had a few things I wanted to add for sp_indexinfo (my procedure which returns bunch of information for indexes). Dejan Sarka suggested adding XML indexes as well as making the column list look nicer. I've also had a request to add Read More... Posted Wednesday, February 18, 2009 7:30 PM by TiborKaraszi | 7 Comments Filed under: indexes, Performance

• Execution plan re-use, sp_executesql and TSQL variables

  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 Read More... Posted Friday, August 29, 2008 9:34 AM by TiborKaraszi | 7 Comments Filed under: indexes, Performance

• Are inserts quicker to heap or clustered tables?

  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 Read More... Posted Thursday, August 14, 2008 3:05 PM by TiborKaraszi | 11 Comments Filed under: indexes, Performance

• Yet some more fixes for sp_indexinfo...

  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 Read More... Posted Wednesday, July 09, 2008 11:03 AM by TiborKaraszi | 4 Comments Filed under: indexes, Performance

• New version of sp_indexinfo

  (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 Read More... Posted Tuesday, July 08, 2008 10:58 AM by TiborKaraszi | 5 Comments Filed under: indexes, Performance

• Fed up with hunting physical index details?

  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. Read More... Posted Monday, July 07, 2008 3:17 PM by TiborKaraszi | 7 Comments Filed under: indexes, Performance

• Find table and index name for fragmented indexes

  Got this question from a newsgroup today. The answer is pretty simple, just use the dynamic management view sys.dm_db_index_physical_stats. I'm posting this here mostly so I have somewhere to refer to when asked this question... I prefer to have a helper Read More... Posted Sunday, November 11, 2007 9:53 PM by TiborKaraszi | 14 Comments Filed under: indexes