Entries for the 'SQL performance tuning' 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

Why do I have to use a MERGE\HASH JOIN Hint?

I have run into this several times lately so I thought I would whip out a quick post.

The scenario

A query runs much faster with a hash join hint than a nested loop but that is what the optimizer is choosing.

High level background

Merge and hash are good when you are working with lots of rows because it can leverage sequential IO. If you do a nested loop join with lookups on 100-1000+ rows, the random IOs can be 10-1000% more expensive than a sequential scan.

Why?

So why does the optimizer choose a nested loop? I have seen it is because the optimizer had to make a best guess at how make rows were being returned. It most cases, the optimizer guesses 1 row will be returned so a nested loop is the best choice as far as the optimizer can tell.

Identifying the problem 

Identifying the problem is pretty easy. All you need to do is look at the query plan. It can be text, xml or graphical. Look actual and estimated rows returned. If the actual rows are 11teen,000,000 but the estimated rows is 1 then this may be the problem

Causes

I most often see this when large table variables, local variables or modified stored procedure parameters are involved. There can be other causes. See my favorite white paper.

Fix?

Easy. Don’t use table variables and local variables. If you have to modify sp parameters, pass them to a nested stored proc. Make sure stats are up to date. If the WHERE cause is complex, try putting the logic in a persisted computed column or indexed view. Another option is to use a covering index. Of course, the last resort is the JOIN hint.

Need more?

Want to get real deep on stuff like this? Check out Craig Freedman’s blog or his Inside SQL Server 2005 chapters.

email it! |   |   | 

Permalink     4 Comments

This Post Needs More Stored Procedures

New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.

I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)

In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.

--So what happens when you turn on "Optimize for adhoc" and forced parameterization 
exec sp_configure  'show advanced options', 1
reconfigure with override
go
exec sp_configure 'optimize', 1
reconfigure with override 
alter database master set parameterization forced
dbcc freeproccache 

--Let's run a query will not generate a trivial plan.
select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go

--The CacheObjType is a compiled plan and 57344 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

--Now, let’s turn off forced parameterization

alter database master set parameterization simple
dbcc freeproccache

select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go 

--The CacheObjType is a Compiled Plan Stub and 320 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

email it! |   |   | 

Permalink     4 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

Breaking: Quest-SolidQ Partnership

The podcast from Brian Moran and Bill Bosworth provides good insight into the the deal. Click here.

Here is an excerpt for the forthcoming press release:

Quest Software is announcing its new partnership with Solid Quality Mentors, a global provider of education and solutions for Microsoft data and development platforms.

This is not a sales reseller relationship, but a true joining of forces to provide new real value to the SQL Server community. Specifically, our plans are to work together in three specific areas: community outreach, product development and professional services.

Community outreach: The companies will work together on joint programs including speaking at Quest and Solid Quality Mentors events, authoring technical briefs and white papers and providing joint webcasts and podcasts for the SQL Server community.

Product development: Quest will collaborate with Solid Quality Mentors on product road maps and feature sets, drawing on their combined experience of real-world SQL Server challenges to provide the best solutions possible for SQL Server DBAs and developers.

Professional services: Quest customers will benefit from consulting services offered by Solid Quality Mentors to help implement tools and manage complex environments.

Peep http://quest.com Monday for more details.

email it! |   |   | 

Permalink     88 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

CXPACKET, MAXDOP and your OLTP system