12/07/13
Estimated execution plan taking too long to be displayedProblem 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
No feedback yet |
|