.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

CPU killer when showing execution plan when having a missing index

Posted By:      Posted Date: August 30, 2010    Points: 0   Category :Sql Server
 
Hi you all! Do you have problems with flickering and CPU getting high when you show the execution plan for a SQL Statement AND there is a missing index suggestion? See related connect id: https://connect.microsoft.com/SQLServer/feedback/details/360457/hovering-mouse-over-execution-plans-query-line-is-cpu-killer I really hope someone from Microsoft can take a look at this a give us a status update, over TWO years since I reported it and still an issue. Have you experienced the same? Please vote the bug up to "raise" the flag.Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)


View Complete Post


More Related Resource Links

Maintenance plan to rebuild Full-text index ? (SQL 2005)

  
Is it possible, through the GUI Wizard, to create a Maintenance plan that rebuilds (rather than reorganise/optimise) a Full text index in 2005 ? The full text index is based on one table only - so would using the "Rebuild index task" in the Wizard do the trick ? I wouldn't mind if the other indexes on the table were rebuilt too, but main concern in the Full text Index. If not - then I guess it would be a scheduled job which does a ALTER FULLTEXT CATALOG catalog_name REBUILD, but I thought it would be nice to keep it within the - Management - Maintenance plans - section of SQL Management Studio. Also - should this be preceeded with a transaction log backup ? Had some issues rebuilding them last night - seemed a Log backup was needed.

Improper execution plan with scalar function

  
Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!

Execution Plan in Activity Monitor (Recent Expensive queries)

  
If we see the execution plan of a query in Activity Monitor > Recent Expensive Queries is it Estimated Execution Plan or Actual Execution Plan ?

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Query execution plan problem

  
Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

Execution plan help

  
When doing a query execution plan, I see the following: Clustered Index Scan Predicate [FinanceDW].[dbo].[fact_temp] [segment_key =2] which is Cost at 55% This is what im doing: where segment_key = 2 Any ideas what this means? an how I can improve it?

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

Save Execution plan option disabled

  
You cannot vote on your own post
0

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya

Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?

  

As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)

TIA


Table Spool takes up too much processing in execution plan

  

I have a query that is taking over 2 hours to run. When I look at the execution plan for a major segment of it, 75% of the cost is on three instances of "Table Spool" (25% each). What is "Table Spool" and how do you address it? The execution plan lists this as

 Table Spool
(Eager Spool)

There are actually 2 queries involved. One loops through a series of dates and calls the second one. All joining fields have indexes in their corresponding tables:

Calling Query:

    -- Process all quarters
        declare @lastQtrStart datetime
        declare @currentQtrStart datetime
        declare @currentQtrEnd datetime

        set @currentQtrStart = '1/1/2008'
        set @lastQtrStart = '1/1/2020'

        while @currentQtrStart < @LastQtrStart
            begin
                set @currentQtrEnd = dateadd(d , -1 , dateadd(MM , 3 , @currentQtrStart))
              

How to estimate "Minimum query plan threshold for considering queries for parallel execution(cost es

  

Hi,

We have 4 QUAD core processors and we have set max degree of parellelism to 4(as suggested by Microsoft). As of now we are using the default value for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)".

Please suggest how to estimate the best value for for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)"?

Thanks in advance.

 

 


How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4

How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend