Browse by Tags

• allocation
• allow_page_lock
• data
• deadlock
• disk
• extent
• fragmentation
• Hardware
• i/o
• insert
• IO
• Microsoft
• online
• query
• raid
• security
• service pack
• SQL Server
• table
• Testing
• tool
• unused indexes

• 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 4:34 PM from Tibor Karaszi | (Comments Off) Filed under: performance, indexes

• 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 10:05 PM from Tibor Karaszi | (Comments Off) Filed under: performance, indexes

• 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 Share this post: email it! | bookmark Read More... Posted Wednesday, July 09, 2008 6:03 PM from Tibor Karaszi | (Comments Off) Filed under: performance, indexes

• 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 5:58 PM from Tibor Karaszi | (Comments Off) Filed under: performance, indexes

• 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 10:17 PM from Tibor Karaszi | (Comments Off) Filed under: performance, indexes

• DBCC EXTENTINFO - unused pages in these extents (undocumented)

  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 Read More... Posted Tuesday, September 04, 2007 10:45 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, indexes, fragmentation, table, deadlock, i/o, insert, data, allocation, extent

• Msg: 2522 The index "<name>" (partition 1) on table "TableName" cannot be reorganized because page level locking is disabled.

  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>" Read More... Posted Tuesday, August 07, 2007 11:43 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, indexes, online, allow_page_lock, service pack

• How to gain Disk related performance with few simple steps

  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 Read More... Posted Tuesday, July 31, 2007 12:24 AM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, Hardware, Microsoft, Testing, indexes, disk, IO, raid, tool

• Unused indexes on a database, its good and bad

  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 Read More... Posted Friday, July 27, 2007 11:51 PM from SSQA.net | (Comments Off) Filed under: security, SQL Server, performance, unused indexes, query, indexes