Entries for the 'Indexes' Category

Never Index a BIT?

Never say never, right? For example, you have an orders table. It has an IsShipped bit column which is what marks an order as done to the business. You might have a more complex version of this example so employees can see the outstanding order count in the app:

CREATE TABLE#temp
    (
                c1INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                c2 VARCHAR(10) DEFAULT  'bob',
                c3 bit
    )


INSERT INTO#temp(c3)
        VALUES
    (1)
GO 5000000


INSERT INTO#temp(c3)
        VALUES
    (0)
GO 500 


--13,000 IO's
SET STATISTICS io ON
SELECTCOUNT(*)
    FROM #temp
    WHEREc3 = 0


CREATE INDEXix ON#temp(c3) include (c2) 


--6 IO's 
SET STATISTICS io ON
SELECTCOUNT(*)
    FROM #temp
    WHEREc3 = 0
           

In SQL 2008, we can get a little better with filtered indexes but really all we are saving is space.

CREATE INDEXix2 ON#temp(c3) include (c2) 
    WHEREc3 = 0

--4 IO's

SET STATISTICS ioON
SELECT COUNT(*)
    FROM #temp
    WHEREc3 = 0

email it! |   |   | 

Permalink     4 Comments

5 Quick Tips for the Query using the “Wrong” Index

I quote “wrong” because with the info the optimizer has, it thinks it has the right index. :)

1. Look for cardinality problems. You can do this by looking at the actual rows versus estimated rows in the query plan. If they are off, chances are you are not following the best practices listed here.

2. Conversions in the WHERE clause can cause issues. Be sure to look at query plan for implicit conversions

3. Complicated logic in the WHERE clause can also cause problems. Consider indexed persisted computed columns or indexed views.

4. Constraints give the optimizer more info when choosing a plan with the “right” index. It can take a PK or unique constraint into consideration but it does not know about similar logic in a trigger.

5. Large datasets are being returned but the index is not covering. An index with just the columns in the WHERE clause is ok if just a few rows are returned but it does not take much for an index scan to be more efficient.

#1 is the most important one because the link gives 5 or 7 more reason why the “wrong” index is chosen.

P.S. If all else fails, update stats and if that does not work, make the index covering.

email it! |   |   | 

Permalink     1 Comments

Indexed Views vs. Filtered Indexes Part 2

In the first part, we looked at the a very simple example with a single table SELECT using covering indexes. This one is a little more complex. We are still covering but we are joining two tables, ordering and grouping. Filtered indexes do not support joins so we have to create two indexes. In this test, the indexed view wins but filtered indexes come a respectable 2nd place compared to normal indexes.

The DDL(2.5M in tblitem and 25M in tblitemdetails)

CREATE TABLE [dbo].[tblItem](

    [Itemid] [int] IDENTITY(1,1) NOT NULL,

    [ItemName] [varchar](100) NULL,

    [Datecreated] [datetime] NULL,

    [Closed] [bit] NULL,

PRIMARY KEY CLUSTERED 

(

    [Itemid] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblItemDetails](

    [ItemDetailsID] [int] IDENTITY(1,1) NOT NULL,

    [ItemID] [int] NULL,

    [Color] [varchar](20) NULL,

    [Size] [varchar](20) NULL,

    [Flavor] [varchar](20) NULL,

PRIMARY KEY CLUSTERED 

(

    [ItemDetailsID] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[tblItemDetails]  WITH CHECK ADDCONSTRAINT [fk01] FOREIGN KEY([ItemID])

REFERENCES [dbo].[tblItem] ([Itemid])

GO

ALTER TABLE [dbo].[tblItemDetails] CHECK CONSTRAINT [fk01]

GO

--Create normal and filtered indexes

create index ix01 on tblitem(Datecreated, ItemName)  

create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22' and itemid > 0 and itemid < 50 

create index ix01 on tblitemdetails(itemid, color)  

create index ix02 on tblitemdetails(itemid, color) where itemid > 0 and itemid < 50

--Create indexed views

alter view ivw01

with schemabinding

as 

select  a.ItemID, b.Color, COUNT_big(*) as cnt

from dbo.tblItem a join dbo.tblItemDetails b on a.Itemid= b.ItemID 

where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50

group by a.ItemID, b.Color

create unique clustered index ix01 on ivw01(itemid)

create index ix02 on ivw01(itemid, color) 

Test Queries

set statistics io on

--Using a normal covering index

select  a.ItemID, b.Color, COUNT_big(*) as cnt

from dbo.tblItem a with (index=ix01) join dbo.tblItemDetails b with (index=ix01) on a.Itemid= b.ItemID 

where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50

group by a.ItemID, b.Color

order by cnt

--using a filtered index

select  a.ItemID, b.Color, COUNT_big(*) as cnt

from dbo.tblItem a with (index=ix02) join dbo.tblItemDetails b with (index=ix02) on a.Itemid= b.ItemID 

where Datecreated > convert(datetime, '2007-08-22', 101) and a.itemid > 0 and a.itemid < 50

group by a.ItemID, b.Color

order by cnt

--using an indexed view

select * from ivw01

order by cnt

IO numbers:

Normal covering indexes

(8 row(s) affected)
Table 'tblItemDetails'. Scan count 8, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Filtered covering indexes

(8 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 99, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblItemDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Indexed View

(8 row(s) affected)
Table 'ivw01'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note: By increasing the result set 100 fold(itemid > 0 and itemid < 5000), we see very similar results except with merge joins.

Query Plans:

The query plans are different. Most notably being the scans for the filtered index and indexed view vs. the normal index. This is fine though. They as just a fraction of the size of the normal index.

Conclusion

The mere fact that the indexed view runs with 50 times less IO overhead does not make it the automatic choice. What if you are on standard edition? Filtered indexes work on standard edition. Also we filtered on a date column. What if you need a real time rolling total? You have to use a normal index. Know you tools and then test, test and test some more.

email it! |   |   | 

Permalink     1 Comments

Filtered Indexes VS. Indexed Views

Note: This is a simple example and YMMV. I may test queries with lookups, hashes, aggregations etc later. Let me know if you have an interesting test case.

I decided to run a quick test comparing covering filtered indexes vs. covering indexed views vs. a normal covering index. Read performance-wise, it is a statistical draw.

The DDL and queries

USE [demodb]

GO

CREATE TABLE [dbo].[tblItem](

    [Itemid] [int] IDENTITY(1,1) NOT NULL,

    [ItemName] [varchar](100) NULL,

    [Datecreated] [datetime] NULL,

    [Closed] [bit] NULL,

PRIMARY KEY CLUSTERED 

(

    [Itemid] ASC

) ON [PRIMARY]

) ON [PRIMARY]

GO

create index ix01 on tblitem(Datecreated, ItemName)  

create index ix02 on tblitem(Datecreated, ItemName) where Datecreated > '2007-08-22'

go

create view ivw01

with schemabinding

as 

select ItemName, Itemid from dbo.tblItem

where Datecreated > convert(datetime, '2007-08-22', 101)

create unique clustered index ix01 on ivw01(itemid)

create index ix02 on ivw01(ItemName) 

set statistics io on

--Using a normal covering index

select ItemName, Itemid from tblItem with (index=ix01)

where Datecreated > convert(datetime, '2007-08-22', 101)

--using a filtered index

select ItemName, Itemid from tblItem with (index=ix02)

where Datecreated > convert(datetime, '2007-08-22', 101)

--using an indexed view

select * from ivw01

�

The graphical query plans

There are no surprises in the query plan. It does a scan on the filtered index but that doesn't matter since it is ...err... filtered.

Statistics IO Output

Normal covering index:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Filtered Index:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Indexed view:

(346129 row(s) affected)
Table 'tblItem'. Scan count 1, logical reads 991, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

�

Other Considerations

Some other things to consider is the fact that the filtered index is a fraction of the size of the normal index. The indexed views are an enterprise feature but filtered indexes are not. The optimizer may have a hard time choosing a filtered index but these problems are not present with indexed views. So the right choice may vary with different apps even if the schema is the same.

email it! |   |   | 

Permalink     0 Comments

NTSSUG June Presentation

Here are the slides and scripts from my June presentation at the North Texas SQL Server Users Group. The presentation was on the Data Collector in SQL Server 2008 but it was short so I also touched on filtered indexes, query hashes and troubleshooting. Enjoy and let me know if you have any questions.

Also Peter Debetta is presenting TONIGHT at 6:30 on the following topics:

"We'll be discussing the various ways you can secure your SQL Server data via encryption, including the new Transparent Data Encryption feature of SQL Server 2008."

 Stop by.

email it! |   |   | 

Permalink     0 Comments

Stupid XML SSMS Trick

This might be HAWTALTA for some of you. I just thought of doing this today even though I have been using xml showplan since I was knee high to a junebug. You can make query results clickable just by casting them as XML. For example:

select cast('select 1' as xml)

By clicking the resulting "select 1", a new query editor window opens with "select 1" ready to execute. This would be useful for scripts that generate scripts. Here is a practical example. (Forgive the formatting. I cannot get too wide in the post.)

SELECT  sys.objects.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact

,  cast('CREATE NONCLUSTERED INDEX ~NewNameHere~ ON ' 

+ sys.objects.name + ' ( ' + mid.equality_columns + 

CASE WHEN mid.inequality_columns IS NULL THEN '' 

ELSE CASE WHEN mid.equality_columns IS NULL 

THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' 

+ CASE WHEN mid.included_columns IS NULL 

THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' 

END + ';' as xml) AS CreateIndexStatement

, mid.equality_columns

, mid.inequality_columns

, mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs 

INNER JOIN sys.dm_db_missing_index_groups AS mig 

ON migs.group_handle = mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details AS mid 

ON mig.index_handle = mid.index_handle 

INNER JOIN sys.objects WITH (nolock) 

ON mid.object_id = sys.objects.object_id

WHERE     (migs.group_handle IN

(