Sladescross's Blog

Blogging about Sharepoint related stuff

Filter Statistics May 17, 2012

Filed under: Filter Statistics,SQL Tip,Statistics — sladescross @ 11:35 am

http://blogs.msdn.com/b/psssql/archive/2010/09/28/case-of-using-filtered-statistics.aspx

Here is the statement “create statistics Region_stats_id on Region (id) where name = ‘Dallas’”.

Now if you execute the same select statement (select detail from Region join Sales on Region.id = Sales.id where name=’Dallas’), the cardinality estimate is correct as shown below for the nested loop join.

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/12/17/lies-damned-lies-and-statistics-part-iii-sql-server-2008.aspx

To start with, I need to know my domain of values:

SELECT MIN(c2) AS minVal, AVG(c2) avgVal, MAX(c2) maxVal FROM test1

Fig4_Ranges

I have only some 1 200 000 rows in this table, so I am going to go ahead and try with 3 ranges (ideally you should have an idea how many rows are in each range):

CREATE STATISTICS testStatLow     ON test1 (c2) WHERE c2 < 200 GO

CREATE STATISTICS testStatMed     ON test1 (c2) WHERE c2 > 200 AND c2 < 400 GO

CREATE STATISTICS testStatHi     ON test1 (c2) WHERE c2 > 400 GO

About these ads
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 63 other followers