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


Post New Web Links

Execution Plan in Activity Monitor (Recent Expensive queries)

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
 
If we see the execution plan of a query in Activity Monitor > Recent Expensive Queries is it Estimated Execution Plan or Actual Execution Plan ?


View Complete Post


More Related Resource Links

SSMS 2008 Activity Monitor Activity Monitor is unable to execute queries against server (SQL Server

  
I get the following error when running SQL Server SSMS 2008 (32-bit) on my (Windows 7 64-bit) workstation against our SQL Server 2005 (32-bit) development server running Windows Server 2003 (32-bit): TITLE: Microsoft SQL Server Management Studio ------------------------------ The Activity Monitor is unable to execute queries against server <my server\instance name>. Activity Monitor for this instance will be placed into a paused state. Use the context menu in the overview pane to resume the Activity Monitor. ------------------------------ ADDITIONAL INFORMATION: Unable to find SQL Server process ID 2256 on server VTX-DEV-01 (Microsoft.SqlServer.Management.ResourceMonitoring)   I can expand the panes and all display data except Overview and Data File I/O. This is true for all instances on that server. The SSMS 2005 Activity Monitor (also on my workstation) works fine when connected to that server. I checked for the registry setting to disable the performance counters on both my machine and the server, and it was not there. I started the Performance Counter DLL Host on my machine, but that made no difference. I can see all Processor counters on the server from my local Performance Monitor. I ran "lodctr /R" on both systems, but that didn't help. The only SQL counters I see on the server (from my workstation) are SQLServer:SSIS Pipeline and SQLServer:SSIS

minimum permissions to run sp_who2, queries against dm views and activity monitor

  
Hi.  I'm trying to explain some suspended tasks that could be tied to blocks on a remote server source.  I want just enough permissions to run sp_who2, activity ssms monitor and the data mgt view query you see below.  What permission should I request minimally to do this?  We're on STD 2008. SELECT *,   SUBSTRING(st.text, (r.statement_start_offset/2)+1,   ((CASE r.statement_end_offset   WHEN -1 THEN DATALENGTH(st.text)   ELSE r.statement_end_offset   END - r.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st     

sql 2008: the activity monitor is unable to execute queries against server

  
Installed sql server 2008 entrprise edition on VM server windows 2008 (64bit). When launch Activity Monitor, get:

The Acityvity Monitor is unable to execute queries against server <server name>.
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.

Additional information:
   Unable to find SQL Server process ID xxxx on server <server name>
   (Microsoft.SqlServer.Management.ResourceMonitoring)



Tried to "use he context menu in the overview pane to resume the Activity Monitor" and got the same error again.

Installed sql 2008 in my XP machine, I can use Activity Monitor (AM) for local instance but cannot launch it for the instance on the server (same error).

Used windows authentication with sysadmin role for connection from my machine. On the server used domain\administrator that the account created the instance.

Please help.

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.

 

 


A Simple XML-driven Tool: Monitor Your Web Site's Activity with COM and Active Scripting

  

This article describes a simple Web site monitoring tool built with XML, JScript, Windows Script Host, and COM objects. Although it is not intended to replace complete Web site monitoring software products, it has many useful features that help to keep Web servers up and running. An XML configuration file specifies which Web sites to monitor and the actions to be taken if the site isn't functioning properly. In addition, the tool can be scheduled to run at any specified interval using the Windows Task Scheduler. Functions that probe the sites, log events, and send e-mail notifications are written in JScript.

Panos Kougiouris

MSDN Magazine July 2000


SQL Server 2008 - Job Activity Monitor Failing

  
I recently began migrating to Windows 2008. One of the first servers is a server which we install our tools on. This is a new server which currently is only running the SQL Server management tools. On this server I installed the SQL Server 2008 R2 management tools, prior to this install there were no other SQL Server tools installed on this server. The majority of the functionality works and I can connect and manage multiple databases servers from SQL Server version 2000 through 2008. But when I try to bring up the Job Activity monitor I get an error ==> Can not show requested dialog. From Windows XP Windows Server 2003 SP2 this functionality works. I get this error if I connect to a SQL Server 2005 or 2008 database instance. I have tried re-installing the client tools but I still get this error..... any ideas?

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!

CPU killer when showing execution plan when having a missing index

  
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)

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

Forcing parallel plan for CPU limited queries

  
I have a simple query: INSERT tableB SELECT SomethingSlowFunction(value) FROM tableA SomethingSlowFunction is a bunch of trigonometric function calls on many columns of the table, all pure T-SQL, no CLR involved. The query is CPU limited (100% on one of the 8 CPUs). If I change the query a little bit (i.e. add a SORT BY) that will cause parallel execution but the sort will take a lot of resources (tempdb munching). Any tips?

SSMS 2008 & Activity Monitor's Processor Time graph is disabled

  
Hi,I just upgraded one of my SQL 2005 clusters to SQL 2008 with CU9.  Now, when I connect to it with SSMS, either from my workstation or directly on a cluster node, the % Processor Time graph in the Activity Monitor screen is disabled.  The other three graphs are fine.  If I connect to a different SQL 2008 server, the % Processor Graph is fine.  I don't see any errors in any app event log.  Has anyone else seen this behavior?Thanks!

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

sql Server 2008: duplicated rows in Activity monitor

  
There are identical notes (Session id, Login etc.) in a Processes tab of Activity Monitor. Usially it is two, sometimes three same rows. Does anyone have a similar situation? Is this a bug or it is just s/th wrong in my comp? (SQL Server 2008 R2 CUP3)

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.
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