Obtain The Query Plan Of A Running Process
Sep 21, 2006Hi,
Is there a way to findout the query plan of the executing process using
the SPID/KPID information.
Thanks in advance,
Thyagu.D
ADVERTISEMENT
SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should
Mar 29, 2015I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache
Mar 14, 2015Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
SQL 2012 :: Obtain Two Columns In A Query?
Aug 19, 2014i want to obtain two columns in a query but i don´t know how to.ex.
col1 col2
1 A
1 B
2 A
1 C
I need something like this.
col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y
Need One Query To Obtain Results I Can Only Get With Two Queries
Jul 23, 2005I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
View 3 Replies View RelatedMaintenance Plan Not Running.
Dec 26, 2007hi..
Maintenance plan for backing up the user db not running from the time its created.
Version is SQL Server 2000 standard edition with SP3 running on Windows 2003.
also the recovery model for the db is also FULL.
What elso do I need to check to sort out this issue.
Please help for this.
Regards,
How To Obtain Query Execution Time As A Variable?
Feb 28, 2008Hi guys,
I am looking for system procedure or system variable that will give the execution time of a SQL query.
I need to capture the execution time of a query in a variable and depending on the value do furthur processing.
Any help in this regard will be appreciated.
Obtain The Result Of Dynamic Query With Openrowset
Oct 5, 2006im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
Query To Obtain Users And Privileges From Databases
Apr 24, 2007Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
thank you
Long Running Process
Jan 10, 2001Hi all,
I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.
The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?
NOTE: the sql maintanace job does not include the backup of database and transaction log.
Exact SQL For Running Process
Aug 27, 2007
For a long running stored procedure, how can I determine the SQL statement within the stored procedure that is currently running?
The Activity Monitor only shows the name of the stored procedure and whether it is a SELECT/INSERT/UPDATE, never the complete statement.
Thank you.
Running Process() Within SQLCLR
May 11, 2006By using impersonation I am able to push data to network path / old fashioned DBF's. Of course, I must use unsafe. I am unable, however to successfully run a "cmd.exe" process(), even if I populate the Username, Domain and password (with securestring). It authenticates correctly (checked by giving wrong password or bad username). But when it runs (even just a "cmd.exe"), I get an access is denied error...
StreamWriter sw = File.CreateText("C:\Error.txt");
WindowsIdentity clientId = null;
WindowsImpersonationContext impersonatedUser = null;
clientId = SqlContext.WindowsIdentity;
impersonatedUser = clientId.Impersonate();
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in "secret")
password.AppendChar(c);
Process p = new Process();
ProcessStartInfo si = new ProcessStartInfo("cmd.exe");
si.UserName = "MyName";
si.Password = password;
si.Domain = "MySecretDomain";
si.UseShellExecute = false;
try
{
p.StartInfo = si;
p.Start();
}
catch (Exception ex)
{
//handle the exception here (This exception handler will not handle the exception, but I get a
//Window popup (While executing my CLR!!!)
}
finally
{
sw.Close();
}
The message popup says: The application failed to initialize poperly (0xc0000142). Click on OK to terminate the application. I'm kind of at a loss...
I'm using a Windows 2003 server box with the latest SQL Server 2005, .NET 2.0/2005. Let me know if you need anything else.
Oh, just FYI - I am moving replicated data from SQL2005 server to a legacy app using DBF (FoxPro driver). I really need an external DOS app to process some data for the DOS application (Clipper).
Throttling A Long-running Process
Apr 13, 2007I have a SQL procedure that can take several minutes to complete. I allow users to initiate the process through a web site and view a progress bar. When the process is running, though, the site slows to a crawl or times out completely. That long running process seems to block all other queries on the database. Is there a way to give this process a low priority or somehow throttle its resource use so that the other web processes can get a chance to run in a timely manner?
Thanks for any advice.
Kill Process That's Running Xp_cmdshell
Feb 1, 2001I executed xp_cmdshell command.
More than 24 hours this process still running.
I tried killing this process with enterprise manager and query analyser, both
gave me a message saying its successfully killed. But when i do a sp_who,
the process still their executing.
how can I kill this process that's running xp_cmdshell
thanks
Running C#.exe From Execute Process Task
Mar 13, 2008My problem is that I am trying to set up an Execute Process Task to run a C# executable. The .exe captures information from an ftp site and downloads it into a directory on our server. Here is the command line run:
C:SAXOEodinReleaseSaxoEod
I have set the Execute Process Task up as follows:
Require Full name: True
Executable: C:SaxoEODobjReleaseSaxoEod.exe
FailTaskIfReturnCodeIsNotSuccessValue: False
SuccessValue:0
TimeOut:0
WindowStyle:Normal
I have the FailTaskIfReturnCodeIsNotSuccessValue set to False because when it is set to true I get the following error:
Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:SaxoEODobjReleaseSaxoEod.exe" "" at "", The process exit code was "-532459699" while the expected was "0".
Setting FailTaskIfReturnCodeIsNotSuccessValue to False does not work either because the files are not downloaded from the FTP site.
I am relatively new to the SSIS arena. I have googled this thing to death and have not found an answer. Any help in the right direction is appreciated. Thanks.
Dave Christman
Process Task Hangs Running Bcp
Jun 26, 2006
We are attempting to use Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00 to run a BCP command from a Process task. We are able to do so without a problem when running it interactively, but when run as a SQL Server Agent job the bcp process never completes and the job hangs indefinitely.
We also tried using a Script task for this, with the same result.
Has anyone else run into this problem?
Thanks,
Ron
Want To Block All Other Db Activity While My Archive Process Is Running...
Oct 29, 2007i'd like to block all other processes that interact with my database while my archive process is running on that database. Can this be done?
Error In DTSX At The End Of Long Running ETL Process
Feb 14, 2006Hi,
We have a DTSX in which parallel threads are processing ETL. There is a Final step in the DTS to do cleanup tasks. we are getting the following error after ETL completes. This is not consistently reproduced.
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in prelogin response". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: Timeout error [258].
Connection manager is set as Localhost.
Can somebody help us out here?
thanks
The Process Is Running And Is Waiting For A Response From One Of The Backend CONNECTI
Sep 22, 2006We are useing merge replication with MSSQL Server 2000 for our two location, we have more than 6 database in replication on has issue following error and few time not updated data due to this error -
The process is running and is waiting for a response from one of the backend CONNECTION
With regards
R.MAll
What Task Can I Use To Stop My Control Flow Process From Running
Apr 24, 2007Hi
What SSIS Task or process can i use to stop my Control Flow Process from running?
I created a SQL Task to do a count on a table to see if there is data, if the count is > 0 then the Control Flow task must continue, else it uses a RAISERROR statement which i use with the event handler, but i want to put something in the event handler to stop the process then and not continue?
Any help will be greatly appreciated.
Kind Regards
Carel Greaves
Execute Process Task - Warnings When Running DTExec
Jun 22, 2007I have a manager SSIS package that calls a generic loader SSIS package, via the 'Execute Process Task'. The manger package (amongst other things) creates a config file and and then executes DTExec passing the config specification in the Arguments property, i.e. '
'/FILE "D:AudiencePackagesUKDW_Core_Loader_Persist.dtsx" /CONFIGFILE "D:AudienceDataSCPmystery.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF"
It works as expected, but, I always get the warning message 'File/Process "DTExec" is not in path.', I know that it is because the exe runs and loads the correct package which then performs the correct actions.
Have I dropped the ball here? I cannot see any reason for the warning as DelayValidation==True, I could set RequireFullFileName==True, but I do not know where SQL is installed on the various nodes.
Any input would be warmly received
Thanks
DB Engine :: Running Performance Monitor While ETL Process Is In Progress?
Jul 21, 2015know if running performance counters during ETL process is running will impact performance on the server, I have win 2008 r2 server with sql server 2008 r2. I want to measure performance of server while ETL is in progress.
View 4 Replies View RelatedSQL 2012 :: Execute Process Task Error When Running Under Agent?
Jul 11, 2014I have an execute process task in SSIS that executes a .Net program.When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't work. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
Running EXEs On Prod SQL Box Through Execute Process Task - Best Practices
Apr 8, 2008
Hi All,
We're trying to run an EXE from SSIS through "Execute Process" task.
The EXE folder contains other DLLs as well.
The EXE interacts with the database and reporting services and sends some e-mails(max 500 a day) out to customers.
My question is:
Is it ok to run this kind of EXE on the production SQL box?
If not, why?
(People argue that running EXEs is not advisable on production boxes)
Q: Why did Microsoft introduce "Execute Process" task when we cannot run EXEs on the production box?
If somebody can educate whether it's ok to run such EXEs on prod SQLs.
In either case, some explanation is greatly appreciated.
Thanks,
Siva.
Integration Services :: Error Running A Fuzzy Grouping Process
May 26, 2015I have a table that I need to identify similarities so I'm running a Fuzzy Grouping Process. I'm getting the follow errors and I can't identify the problema since all the fields are varchar, except for the first that is int but not use in the fuzzy.
select
MSSEndCustomerTPID
, orgname
, address1
, cityname
, statename
, countryname
from [sales].[vw_Fact_VolumeSales] a
inner join [GMOFBI].[dbo].[vw_Dim_MSS_Organization] b
on a.EndCustomerOrganizationKey=b.MSSOrganizationKey
[code]...
Maintenance Plan Fails Trying To Delete Files Older Thean One Day. Running SQL Server 2005
Oct 1, 2007I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help would be appreciated!!
Running A Ssis Package In Sql Server Agent With Execute Process Task
Apr 1, 2008Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
How To Kill A Long Running Query Running On A Background Thread.
Sep 1, 2006
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
Master Data Services :: Error - Query Processor Could Not Produce A Query Plan
Jul 19, 2015We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan
Oct 6, 2015SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements
Oct 30, 2015When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
SQL 2005 V9.0.2047 (SP1) - The Query Processor Could Not Produce A Query Plan
May 15, 2006Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
Transact SQL :: Query Plan Shows Table Not Even In Query?
Jul 22, 2015I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.