Problem

Displaying the Estimated Execution Plan of a query takes a long time to complete.

Solution

Although, the query is pretty complex, displaying the execution plan should not take more than few seconds. In my case it took over 20 minutes to have the estimated execution plan displayed.

After trying different things like updating the statistics or dropping the automatically generated system statistics, the only thing that worked for me was to use a custom query that generates the drop and create statements for the _WA_Sys statistics:

-- delete and create system statistics for several tables in the database

USE DATABASE_NAME --database name

SELECT
N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N']
CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN'
FROM sys.stats s (NOLOCK)
INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id
INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id
INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id
WHERE s.name like '%_WA_Sys%'
		and t.name	IN ('TABLE_NAME_1',  -- specify here the name of the table one
						'TABLE_NAME_2',  -- specify here the name of the table two
						'TABLE_NAME_3')  -- specify here the name of the table tree
ORDER BY t.name


-- delete and create system statistics for all tables in the database

USE DATABASE_NAME --database name

SELECT
N'DROP STATISTICS [' + schema_name(t.schema_id)+N'].['+ t.name + N'].[' + s.name + N']
CREATE STATISTICS [' + c.name + N'] ON [' + schema_name(t.schema_id)+N'].['+t.name + N'] ([' + c.name + N']) WITH FULLSCAN'
FROM sys.stats s (NOLOCK)
INNER JOIN sys.stats_columns (NOLOCK)sic ON sic.stats_id=s.stats_id AND sic.object_id=s.object_id
INNER JOIN sys.tables t (NOLOCK) on t.object_id = s.object_id
INNER JOIN sys.columns c (NOLOCK) on c.column_id = sic.column_id and c.object_id = sic.object_id
WHERE s.name like '%_WA_Sys%'
ORDER BY t.name

...

The script has two sections. The first one deletes and creates system statistics for several tables and the second deletes and creates system statistics for all tables in the database.

The _WA_Sys statistics are automatically created for non-indexed columns that are used in queries, if the database setting Auto Create Statistics is set to true, which it is by default.

The script practically uses the catalog view sys.stats to generate the drop and create statements for the existing system statistics.

After executing the generated drop and create statements, I tried again to display the Estimated Execution Plan. This time it only took few seconds.

The original script can be found at the bellow address:

http://blogs.msdn.com/b/sqlpfe/archive/2010/06/18/creating-statistics-from-wa-sys-stats.aspx