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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Explain Plan for sp_executesql

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server


please bear with me as I'm new to Sql Server.

I have extracted a SQL statement from a trace file using the sql profiler tool. This was for a SharePoint application. The SQL statement includes the stored procedure sp_executesql with a number of scalar variables and I was wondering how do I get the explain plan that was actually used when executing the sql.

Is there a system view I can query that gives this sort of information?

Many thanks in advance for any help.


View Complete Post

More Related Resource Links

Estimated Explain Plan does not return



I have a query which takes a very long time to complete appx 1 hour. If I run an estimated execution plan on the query, this can run for over an hour and doesn't return the plan. Any ideas why the plan should take so long. Is the only way around this to clear the cache?



Creating your SharePoint Governance Plan

One of the most common questions that I get from prospects as I'm talking to them about the creation of a governance plan and process is what does it look like. In other words, there are materials available which describe what should be in a governance plan but there isn't a ton of guidance on what the process of creating a governance plan and process are.

Controls: Plan Your Migration to the Visual Studio 2005 Navigation Controls


Navigation is central to a well functioning Web application and it can be implemented numerous ways using today's technologies. This article highlights some of the key details of the new navigation controls that are part of the upcoming ASP.NET 2.0, and talks about how to design and implement one of these navigation controls today. This article contains a simple Web site that demonstrates the use of these navigation controls, pointing out some of the key items such as a breadcrumb control and things to watch for along the way. The article then describes how to implement a breadcrumb control today.

Dave Donaldson and Steven DeWalt

MSDN Magazine June 2004

Downgrade MOSS 2007 to WSS 3.0: What is the best project plan?


Dear All,

I have a customer whom don't want to pay anymore the extra money for the extra MOSS features and has asked me to Downgrade to WSS 3.0...

Is there any plan/procedure to follow? can someone suggest the best practice approach? 

Thanks All


DTA doesn't find referenced table form stored procedure with SP_executesql

Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

Export Maintenance Plan in SSMS

Where are the subplan schedules when I exported /imported a Maintenance Plan in SSMS ? How can I ensure that they are exported with ?

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.

Maintenance Plan Works, But Not Through SQL Job

If I am in the wrong sub-forum, please let me know where I belong. I submitted this issue to this sub, b/c I believe I have an accessibility issue. I have a maintenance place that executes a stored proc. The stored proc is as followings: CREATE PROCEDURE spFirst     @Err AS INT = -1 AS     EXEC @Err = xp_cmdshell 'dir C:\temp\*.exe'     EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail_Proj',                                        @recipients = 'me@xyz.com',                                              @body = 'Complete',                                           @subject = 'Dir Listing' The proc runs successfully and when done, I always get an email message in my Inbox, as expected. When the proc is executed directly from the maintenance plan, it still runs successfully, and the email message is successfully received in my Inbox. A SQL Job was setup to run the maintenance plan. The history of the job indicates that it is completing successfully, but this cannot be true, as my inbox never receives the expected email

sp_executesql vs. sp_sqlexec

what is the difference between these two stored procedures?

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!

Can someone please explain what this line of code is actually doing

I took this line of control from this post http://forums.asp.net/t/1201593.aspx I'm actually trying to do the same dynamic accordion.   MyAccordion.ItemDataBound += new EventHandler<AccordionItemEventArgs>(MyAccordion_ItemDataBound);    I converted to vb.net and this is the equivalent. MyAccordion.ItemDataBound += New EventHandler(Of AccordionItemEventArgs)(MyAccordion_ItemDataBound) when I type  that line in my code I get an error.. Error 3 'Public Event ItemDataBound(sender As Object, e As AjaxControlToolkit.AccordionItemEventArgs)' is an event, and cannot be called directly. Use a 'RaiseEvent' statement to raise an event.    and   Error 4 Delegate 'System.EventHandler(Of AjaxControlToolkit.AccordionItemEventArgs)' requires an 'AddressOf' expression or lambda  

Cannot create maintenance plan in SQL 2008 SP1

I receive the following error trying to create a maintenance plan in SQL 2008 SP1 on a Windows 2008/R2 server: The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install Microsoft SQL Server Management Tools on the remote computer, and then try again. The components that installed are Database Engine Services, Reporting Services, Client Tools Connectivity, Management Tools Basic/Complete, and SQL Client Connectivity SDK.  I have another independent SQL 2008 SP1 server running on the same platform with the same features installed and I have no problem creating maintenance plans there.  However, it does have IIS as it was upgraded from a SQL 2005 server which required IIS for reporting services while SQL 2008 does not. I'm familiar with http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/06c80a78-a877-4a48-a032-b0aef78a598c but that doesn't seem to apply as this was a new SQL 2008 installation on a freshly installed OS.  I've seen several other postings of this on the web but they all appear specific to SQL 2005. What else could be missing? 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)

Maintenance Update Statistics plan

Hi, I've created a maintenance plan for many databases with these tasks (SQL 2005) 1- shrink Database 2- Update Statistics 3- Cleanup History 4- Backup Database (Full) 5- Maintenance Cleanup task The maintenance plan failed at task Update Statics.  Here the error message : Error number : -1073548784 Error message : Executing the query "UPDATE STATISTICS [dbo].[LOG_DETAILS] WITH FULLSCAN " failed with the following error: "Could not continue scan with NOLOCK due to data movement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. My question :How do I know which database is causing the problem and how can I fix the problem ? I don't know a dbo database? thanks,   Jasmin  

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 ?

Can someone explain this inheritance?

I am having trouble understanding the inheritance listed in http://msdn.microsoft.com/en-us/library/cc406686(office.12).aspx <%@ Page Debug="true" Language="C#" MasterPageFile="~/_admin/admin.master" Inherits="MSDN.SharePoint.Samples.WarmupJobManager, MSDN.SharePoint.Samples.SharePointWarmupJob, Version=, Culture=neutral, PublicKeyToken=7fd1d26c5854f031" %> It almost looks to me like two classes are being inherited - WarmUpJobManager AND SharepointWarmupJob. Is this correct?...or even possible?? Neither class is a partial class. Sorry I am new to this and was trying to deploy this example but the inheritance displayed here has me confused. Thanks in advance, Jake.

Can someone explain this assembly in WSP deployment behavior?

I was building a SP timer Job feature. Upon successive feature deployment and retractions using the Visual Studio environment the code changes were not being deployed. I dug around and found this in the AssemblyInfo.cs // You can specify all the values or you can default the Build and Revision Numbers // by using the '*' as shown below: // [assembly: AssemblyVersion("1.0.*")] So I changed what was the following line from [assembly: AssemblyVersion("")] to the stated [assembly: AssemblyVersion("1.0.*")] and all is well (yippee). This seems to make good sense to me. But why is it not the default build/deploy setting? Is there an IDE interface for this setting. Is it related to the the Solution Configuration or build target or an IIS reset timing or GAC cache.....anything like that?
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