Tibor Karaszi : indexes

Another bugfix for sp_indexinfo

TiborKaraszi — Tue, 09 Nov 2010 08:26:00 GMT

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)

Ola Hallengren's maint procedures now supports exclusions

TiborKaraszi — Mon, 09 Aug 2010 05:02:00 GMT

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)

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

TiborKaraszi — Thu, 01 Apr 2010 07:39:00 GMT

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)

Adding a PK online?

TiborKaraszi — Wed, 13 Jan 2010 08:53:00 GMT

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)

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

TiborKaraszi — Fri, 28 Aug 2009 10:15:00 GMT

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)

Key count in sys[.]indexes

TiborKaraszi — Tue, 23 Jun 2009 06:57:00 GMT

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)

Match those types!

TiborKaraszi — Tue, 28 Apr 2009 10:30:00 GMT

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 column side - not the literal side of your query. What does that mean? Consider below:

WHERE Col = Val

Now, say that the types for above don't match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren't of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for "Data Type Precedence".

What we don't want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here's the repro script from that thread (slightly adjusted by me):

USE tempdb GO IF OBJECT_ID('t') IS NOT NULL DROP TABLE t GO CREATE TABLE t ( c1 int IDENTITY(10000001,1) NOT NULL ,c2 char(8) NULL ,c3 datetime NULL ) GO INSERT INTO t(c3) SELECT TOP 3000000 '20080203' FROM master..spt_values t1 CROSS JOIN master..spt_values t2 CROSS JOIN master..spt_values t3 UPDATE t SET c2 = CAST(c1 AS char(8)) CREATE UNIQUE CLUSTERED INDEX x ON t(c2) GO SELECT * FROM t WHERE c2 = N'10000009' SELECT * FROM t WHERE c2 = N'10000008'

Don't bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.

Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find "WHERE:(Convert([t].[c2])=[@1])". See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That's just because we were a bit sloppy... Compare to below:

SELECT * FROM t WHERE c2 = '10000009' 
SELECT * FROM t WHERE c2 = '10000008'

You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.

OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn't see any difference between the two original queries (N'10000009' and N'10000008'). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the "9" query was slower than the "8" query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the "8" version vs. the "9" version. I used Profiler and confirmed. The "9" version had consistently about 90000 microsecond duration where the "8" version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn't give me anything. Perhaps I didn't capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. :-)

My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N'10000009' compared to N'10000008'. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like "Constant Scan" and "GetRangeThroughConvert" for other blog post on this topic.)

The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!

Sp_indexinfo updated

TiborKaraszi — Wed, 18 Feb 2009 17:30:00 GMT

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 some documentation of what the procedure returns. Done. :-)

http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Execution plan re-use, sp_executesql and TSQL variables

TiborKaraszi — Fri, 29 Aug 2008 06:34:00 GMT

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 thread in the open newsgroups today (.tools, posted by Mike), about an advice that the SQL Server 2005 Database Engine Tuning Advisor apparently gave. It advices to replace sp_executesql usage with declaring TSQL variables and using those in the WHERE clause. Translated to the Adventureworks database, it advices that instead of 1) below, we should use 2) below.

1)EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title FROM Person.Person WHERE LastName = @P1', N'@P1 nvarchar(50)', 'Diaz'2) DECLARE @P1 NVARCHAR(50) SET @P1 = 'Diaz' SELECT FirstName, LastName, PersonType, Title FROM Person.Person WHERE LastName = @P1

Now, I could not reproduce this (make DTA give me the same advice, to replace 1) with 2) ). Perhaps I misunderstood the poster in the group, it is because I'm running SQL Server 2008 DTA and engine, I'm not looking in the right place for that advice, my data isn't representative, I'm running the DTA with some other settings, etc.. But say that DTA does indeed give such an advice, would would it do that? To be honest, I don't know. It can hardly have enough information to determine whether 1) or 2) is the best choice.

In short: Say we have an index on the LastName column and the name we look for can either be a very common name, like perhaps "Smith", or a not so common name, like "Karaszi". For the more common name, a table scan might be the best choice, where for the not-so-common name, an index seek it probably best thing. OK, a very simple example, but serves well for this discussion.

Back to the difference between 1) and 2). There are potentially very important differences between the two:

For 1), SQL Server will determine a plan based on the contents of the parameter when the plan is created. I.e., it can determine selectivity based on that and determine whether it is a good idea to use an index or not. The plan is then cached and can be re-used. But what if we for the first execution pass in something which is very selective, but typically we are not very selective? Or the other way around? I,e, the plan for one case might not be optimal for some other case. This is where we have new optimizer hints in 2005 like OPTIMIZE FOR and the RECOMPILE hints. I will not go into details about these here, since I'm focusing on the differences between 1) and 2). See the white paper I mentioned, and of course Books Online, for more details.

For 2), SQL Server (or rather: the optimizer) has no knowledge of the contents of the variable when the plan is produced. So it cannot use the statistics to determine selectivity. In above case, it instead uses density (stored with the statistics, assuming such exists for the column). Density is basically the 1/ number of unique values for the column(s). This might be a good representation for a typical lastname, but perhaps not too good for a very common or a very uncommon lastname. Now, in 2005, we have hints for these situations as well (RECOMPILE), but again, that is not the point here.

In order for DTA to give the best advice here, it would have to know about the distribution over the data for that column and also have many executions of that query to see if "all" executions are using a typical value (sp_executesql might be better) or if the stored density value is a good representation for "all" queries that are passed from the clients. I very much doubt that DTA has this level of sophistication. Basically, I don't know why it advices this. There might be other aspects, like "avoid dynamic SQL" (which whether that holds for this case we could argue in another post), but DTA is about performance, not best practices.

Bottom line is that these things are not simple and we should be very cautious with "rules of thumbs".

Here's some TSQL code to demonstrate the differences between 1) and 2). As always, only execute after you read and understood the code!

--Create a copy of the person table 
--We will have lots of "Diaz" and very few "Gimmi" 
IF OBJECT_ID('p') IS NOT NULL DROP TABLE p 
GO 
SELECT * INTO p  
FROM Person.Person 

CREATE INDEX x ON dbo.p(LastName) 

--Create lots of Diaz 
INSERT INTO p 
 SELECT  BusinessEntityID + 30000, PersonType, NameStyle, Title, FirstName, MiddleName, N'Diaz', Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate 
 FROM p 
  
--Make sure we have up-to-date statistics 
UPDATE STATISTICS p WITH FULLSCAN 

--Verify execution plan and I/O cost 
--for table scan with low selectivity  
--and index seek with high selectivity 
SET STATISTICS IO ON 

DBCC FREEPROCCACHE 
SELECT * FROM p WHERE LastName = 'Diaz' 
--20183 rows, table scan, 7612 pages 

DBCC FREEPROCCACHE 
SELECT * FROM p WHERE LastName = 'Gimmi' 
--1 row, index seek, 3 pages 



------------------------------------------------------------ 
--sp_execute alternative 
------------------------------------------------------------ 

--Table scan will be used for both because of execution plan re-use 
DBCC FREEPROCCACHE 
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1', 
N'@P1 nvarchar(50)', 'Diaz' 
--20183 rows, table scan, 7612 pages 

EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1', 
N'@P1 nvarchar(50)', 'Gimmi' 
--1 row, table scan, 7612 pages 

--Other way around 
--Index search will be used for both because of execution plan re-use 
DBCC FREEPROCCACHE 
EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1', 
N'@P1 nvarchar(50)', 'Gimmi' 
--1 row, index seek, 3 pages 

EXEC sp_executesql N'SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1', 
N'@P1 nvarchar(50)', 'Diaz' 
--20183 rows, index seek, 20291 pages 


------------------------------------------------------------------ 
--Alternative using variable 
------------------------------------------------------------------ 
DBCC FREEPROCCACHE 
DECLARE @P1 NVARCHAR(50) 
SET @P1 = 'Diaz' 
SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1 
GO 
--20183 rows, index seek, 20291 pages 

DBCC FREEPROCCACHE 
DECLARE @P1 NVARCHAR(50) 
SET @P1 = 'Gimmi' 
SELECT FirstName, LastName, PersonType, Title 
FROM p 
WHERE LastName = @P1 
GO 
--1 rows, index seek, 1 pages 

--Same plan even though very different selectivity 
--and emptying plan cache in between 

--Estimated 33 rows for both above. 
--See if that is drawn from statistics density? 

--Formula for density: 1/#OfUniqueValuesInColumn 
SELECT 1./COUNT(DISTINCT LastName) FROM p 
--0.00082918739 

--Does that match density in index statistics? 
DBCC SHOW_STATISTICS('p', 'x') 
--Yes 

--How many rows in the table? 
SELECT COUNT(*) FROM p 
--39944 

--So how many rows would we estimate based on density? 
SELECT 0.00082918739 * 39944 
--Yep, 33 rows. 

--I.e, for the variable alternative, SQL Server has no  
--knowledge of contents of those variables so it must use density instead. 

--Clean up: 
IF OBJECT_ID('p') IS NOT NULL DROP TABLE p

Are inserts quicker to heap or clustered tables?

TiborKaraszi — Thu, 14 Aug 2008 12:05:00 GMT

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, heaps vs. clustered tables is a huge topic with many aspects. I will not cover anything else here except inserts into a heap vs. a table which is clustered on an ever increasing key. No other indexes. There will be no fragmentation. I do not cover searches, covering etc. Only the pure insert aspect. OK? Good!

One might think that a heap has lower overhead because it is a ... heap. But hang on for a second and think about what happens when you do an insert:

Heap:
SQL Server need to find where the row should go. For this it uses one or more IAM pages for the heap, and it cross references these to one or more PFS pages for the database file(s). IMO, there should be potential for a noticable overhead here. And even more, with many users hammering the same table I can imagine blocking (waits) against the PFS and possibly also IAM pages.

Clustered table:
Now, this is dead simple. SQL server navigates the clustered index tree and find where the row should go. Since this is an ever increasing index key, each row will go to the end of the table (linked list).

The result:
So what is the conclusion? I did several executions of the code at the end of this post, with some variations. Basically there was no or very little difference whith only one user. I.e., no contention to the GAM or PFS pages. This was pretty consistent for below three scenarios:

Insert with subselect. I.e., this inserts lots of rows in the same statement.
Insert in a loop (one insert and row per iteration), many rows in the same transaction.
Insert in a loop, one row per transaction.

Now the difference between 2 and 3 is important.
With many transactions, we incur an overhead of force-log-write-at-commit *for each row*. I.e., much more overhead against the transaction log. And indeed, the timings between 2 and 3 for one of my executions (10000 rows) showed that 2 took on average 650 ms where the same number for 3 was 5600 ms. This is about 9 times longer!!! Now, this was more or less expected, but another important aspect is when we have several users. With many users, we might run into blocking on the PFS and IAM pages. Also, with several users it is meaningless to do it all in one transaction since we will block and essentially single-thread the code anyhow. I.e., the only revelant measure where we run many users is the loop construction where each row is its own transaction (3).

There was indded a noticeable difference when I executed several inserts in parallell and had each insert in its own transaction (for clustered table vs. heap table).

Some numbers:
I did 4 repeated tests and calculated average execution time for inserting 10000 rows for a thread. With 6 parallel thread I had 22 seconds for a clustered table and 29 seconds for a heap table. With 10 threads I had 31 seconds for a clustered table and 42 seconds for a heap table.

I didn't find performance difference more than a couple of percents for batch inserts, when I single threaded (only one thread pumping inserts), or when I had all inserts in the loop as one transaction.

Now, I would need lots of more time to run exchaustive tests, but my interpretation is that with many users doing inserts, there is an noticable overhead for the heap vs clustering on a increasing key.

The code:
Note that for parallell executions, I recommend starting the DoTheInserts procedure using SQLCMD, a BAT file and START. As always, read the code carefully (so you understand it) and execute at your own risk.

--------------------------------------------
--Create the database etc.
--------------------------------------------
USE master SET NOCOUNT ON
GO
IF DB_ID('TestDb') IS NOT NULL DROP DATABASE TestDb
GO
--Makes files large enough so that inserts don't causes autogrow
CREATE DATABASE TestDb
ON PRIMARY
(NAME = 'TestDb', FILENAME = 'C:\TestDb.mdf', SIZE = 300MB, FILEGROWTH = 50MB)
LOG ON
(NAME = 'TestDb_log', FILENAME = 'C:\TestDb_log.ldf', SIZE = 200MB, FILEGROWTH = 100MB)
GO
--Full recovery to avoid effect of system caused log truncation
ALTER DATABASE TestDb SET RECOVERY FULL
BACKUP DATABASE TestDb TO DISK = 'nul'
USE TestDb

--Execution time log table
IF OBJECT_ID('TimeLogger') IS NOT NULL DROP TABLE TimeLogger
GO
CREATE TABLE TimeLogger
(
SomeId int identity
,spid int
,TableStructure varchar(10) CHECK (TableStructure IN ('heap', 'clustered'))
,InsertType varchar(20) CHECK (InsertType IN('one statement', 'loop'))
,ExecutionTimeMs int
)
GO

IF OBJECT_ID('RowsToInsert') IS NOT NULL DROP TABLE RowsToInsert
CREATE TABLE RowsToInsert(#rows int)
GO

--Support procedures
IF OBJECT_ID('CreateTables') IS NOT NULL DROP PROC CreateTables
GO
CREATE PROC CreateTables AS
IF OBJECT_ID('HeapLoop') IS NOT NULL DROP TABLE HeapLoop
CREATE TABLE HeapLoop(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
IF OBJECT_ID('ClusteredLoop') IS NOT NULL DROP TABLE ClusteredLoop
CREATE TABLE ClusteredLoop(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
CREATE CLUSTERED INDEX x ON ClusteredLoop(c1)
IF OBJECT_ID('HeapOneStatement') IS NOT NULL DROP TABLE HeapOneStatement
CREATE TABLE HeapOneStatement(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
IF OBJECT_ID('ClusteredOneStatement') IS NOT NULL DROP TABLE ClusteredOneStatement
CREATE TABLE ClusteredOneStatement(c1 int identity, c2 int DEFAULT 2, c3 datetime DEFAULT GETDATE(), c4 char(200) DEFAULT 'g')
CREATE CLUSTERED INDEX x ON ClusteredOneStatement(c1)
GO

IF OBJECT_ID('TruncateTables') IS NOT NULL DROP PROC TruncateTables
GO
CREATE PROC TruncateTables AS
TRUNCATE TABLE HeapLoop
TRUNCATE TABLE ClusteredLoop
TRUNCATE TABLE HeapOneStatement
TRUNCATE TABLE ClusteredOneStatement
GO

IF OBJECT_ID('DoBefore') IS NOT NULL DROP PROC DoBefore
GO
CREATE PROC DoBefore AS
BACKUP LOG TestDb TO DISK = 'nul'
CHECKPOINT
GO

IF OBJECT_ID('iHeapLoop') IS NOT NULL DROP PROC iHeapLoop
GO
CREATE PROC iHeapLoop @rows int AS
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
INSERT INTO HeapLoop (c2) VALUES(2)
SET @i = @i + 1
END
GO

IF OBJECT_ID('iClusteredLoop') IS NOT NULL DROP PROC iClusteredLoop
GO
CREATE PROC iClusteredLoop @rows int AS
DECLARE @i int = 1
WHILE @i <= @rows
BEGIN
INSERT INTO ClusteredLoop (c2) VALUES(2)
SET @i = @i + 1
END
GO

IF OBJECT_ID('iHeapOneStatement') IS NOT NULL DROP PROC iHeapOneStatement
GO
CREATE PROC iHeapOneStatement @rows int AS
INSERT INTO HeapOneStatement (c2)
SELECT TOP(@rows) 2 FROM syscolumns a CROSS JOIN syscolumns b
GO

IF OBJECT_ID('iClusteredOneStatement') IS NOT NULL DROP PROC iClusteredOneStatement
GO
CREATE PROC iClusteredOneStatement @rows int AS
INSERT INTO ClusteredOneStatement (c2)
SELECT TOP(@rows) 2 FROM syscolumns a CROSS JOIN syscolumns b
GO

--Proc to do the inserts
IF OBJECT_ID('DoTheInserts') IS NOT NULL DROP PROC DoTheInserts
GO
CREATE PROC DoTheInserts
AS
DECLARE @dt datetime, @NumberOfRowsToInsert int
SET @NumberOfRowsToInsert = (SELECT #rows FROM RowsToInsert)
EXEC DoBefore --Batch allocation, heap:
SET @dt = GETDATE()
EXEC iHeapOneStatement @rows = @NumberOfRowsToInsert
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'heap', 'one statement', DATEDIFF(ms, @dt, GETDATE()))

EXEC DoBefore --Batch allocation, clustered:
SET @dt = GETDATE()
EXEC iClusteredOneStatement @rows = @NumberOfRowsToInsert
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'clustered', 'one statement', DATEDIFF(ms, @dt, GETDATE()))

EXEC DoBefore --Single allocations, heap:
SET @dt = GETDATE()
--BEGIN TRAN
EXEC iHeapLoop @rows = @NumberOfRowsToInsert
--COMMIT
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'heap', 'loop', DATEDIFF(ms, @dt, GETDATE()))

EXEC DoBefore --Single allocations, clustered
SET @dt = GETDATE()
--BEGIN TRAN
EXEC iClusteredLoop @rows = @NumberOfRowsToInsert
--COMMIT
INSERT INTO TimeLogger (spid, TableStructure, InsertType, ExecutionTimeMs)
VALUES(@@SPID, 'clustered', 'loop', DATEDIFF(ms, @dt, GETDATE()))
GO

--Run the tests
EXEC CreateTables
TRUNCATE TABLE TimeLogger
TRUNCATE TABLE RowsToInsert INSERT INTO RowsToInsert VALUES(10000)

--<Below can be executed over several connections>
EXEC DoTheInserts
EXEC DoTheInserts
EXEC DoTheInserts
EXEC DoTheInserts
--</Below can be executed over several connections>

--How did we do?
SELECT COUNT(*) AS NumberOfExecutions, TableStructure, InsertType, AVG(ExecutionTimeMs) AS AvgMs
FROM TimeLogger WITH(NOLOCK)
GROUP BY TableStructure, InsertType
ORDER BY InsertType, TableStructure

--Verify that no fragmentation
SELECT
OBJECT_NAME(OBJECT_ID) AS objName
,index_type_desc
,avg_fragmentation_in_percent AS frag
,page_count AS #pages
,record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME(OBJECT_ID) <> 'TimeLogger' AND index_level = 0

Yet some more fixes for sp_indexinfo...

TiborKaraszi — Wed, 09 Jul 2008 08:03:00 GMT

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

New version of sp_indexinfo

TiborKaraszi — Tue, 08 Jul 2008 07:58:00 GMT

(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'm not sure I generated the CREATE INDEX statment properly (the equality and inequality columns) since I didn't have much missing index entries to play with at the moment. All tests and replies are much welcome ("work fine" - "doesn't work" - "doesn't work because of..." - "change aaa to bbb" etc.).

I also updated the web site with some tips on creating a view in the databases where you want to work a lot so you can select from this view, aggregate etc. I will try to as much as possible stick with less procedural code and more set-based code. My aim is to have perhaps only two SELECT statements in the proc, so we can just take a SELECT statement, create a view or function inside your database and work with it from there. So, for instance, I will probably not add support for specify 'schemaname.tablename' for the first parameter as that will probably require some procedural parsing code (as seen in sp_helpindex). There's a trade-off between all the nice things you can complement using procedural code and having all in one or a few SELECT statments and being able to simply creating a view or table function from those.

One possible enhancement is to add fragmentation information. For this I will need to perform some type of join or subquery against sys.dm_db_index_physical_stats. My concern here is the cases where you don't want this information (because of cost for large tables/databases). Ideally I don't want two versions of the query (see above paragraph) but I also don't want to pay the penalty in cases where we don't want this info. Possibly this is doable using some correlated subquery as a column together with CASE, but this is only in my mind yet. Suggestions and thoughts are welcome.

You find the proc at: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Fed up with hunting physical index details?

TiborKaraszi — Mon, 07 Jul 2008 12:17:00 GMT

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 (we want space usage stats as well). And perhaps general usage stats (sys.dm_index_usage_stats). (I sometimes might even use the GUI (SSMS) reports and index dialog - but you might already know that I'm not much of a GUI person.)

The good news with all this is that I learn to use these catalog and dynamic management views. Bad news is that it is kind of ... boring to do the same thing again and again.

This is why wrote sp_indexinfo. You might have your own index information procedures (which you wrote yourself or found on the Internet). If not, you are welcome to use this one. I aim to improve it over time, so suggestions are welcome. Possible improvements include:

Make it a function. Functions are nice since we can order the results, aggregate, and basically do whatever we want to when we SELECT from the function. But for this I need to find out how we install a user-defined global system function - there's no supported way to do this. I'm not sure I want to go there...
Reurn missing index information as well. For this we probably want two resultsets, and only return missing index information when we targeted *one* table (no wildcards). If we do this, then function is out since a function can only return *one* result set.

If you care to give it a spin, please let me know. I just wrote the procedure, so I haven't tested it much yet. If you do find bugs, please leave a comment and I will incorporate into the source (let me know if you want to be acknowledged). Any comments are welcome.

You find the proc at: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp

Find table and index name for fragmented indexes

TiborKaraszi — Sun, 11 Nov 2007 19:53:00 GMT

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 function to get the index name:

CREATE FUNCTION dbo.index_name (@object_id int, @index_id int) RETURNS sysname AS BEGIN RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id) END; GO

And then a simple query:

SELECT  
 OBJECT_NAME(object_id) AS tblName 
,dbo.index_name(object_id, index_id) AS ixName 
,avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) 
WHERE avg_fragmentation_in_percent > 20 
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

Then you just adjust the search clause to your liking. One hint is to exclude nindexes with few pages (the page_count column).