Thursday, June 6, 2013

[SQL 2012] Why the difference in query execution times?

[SQL 2012] Why the difference in query execution times?

Why the difference in query execution times?

Posted: 06 Jun 2013 02:50 AM PDT

I have this query: UPDATE invoice SET actual_membernumber = members.mbrnbr, vin = members.vin FROM Network_Transaction invoice (nolock) CROSS APPLY ( SELECT clientindex, lname, fname, mbrnbr, vin FROM vw_ActiveMembers members (nolock) WHERE members.clientindex = invoice.client_id ) AS members INNER JOIN erm_claimsdata c (nolock) ON c.clientid = members.clientindex WHERE invoice.transaction_type = 'I' AND (@filename is null OR invoice.filename = @filename) AND (@client_id is NULL OR client_id = @client_id) AND invoice.deletedflag = 0 AND invoice.exportedflag = 0 AND invoice.last_eight_vin is not null AND invoice.actual_membernumber is null AND members.vin LIKE '%' + invoice.last_eight_vinif I run it in SSMS it takes about 8 minutes, but when the same query is executed in a stored proc it takes 5 hours...sometimes longer before I kill the session. What could possibly cause this? This is on SQL Server 2005. Thanks

Calling SSIS package remotely fails on Proxy works on Local

Posted: 05 Jun 2013 08:17 PM PDT

Hi all, New to SSIS and trying to set up a new set of ETLs. We have a Database server and a ETL server.I use a proxy account on the DB server to call the ETLs. It does this via powershell. Link below:http://blogs.msdn.com/b/dbrowne/archive/2010/10/11/remote-ssis-package-execution-with-powershell-2-0.aspxIf i run the package locally via the proxy account it runs with no errors. If i run it from the DB server Agent job it fails with the error:MessageExecuted as user: DOMAIN\SSIS_Proxy. The job script encountered the following errors. These errors did not stop the script: Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.Res.GetString(String key, Object arg0) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code -532459699. The step failed.If i run the agent job via a proxy with my account it works! so it must be security related?The proxy account has the following sec which i thought was enough, as i said it works when i run it locally on the same account.. Could anyone help? clean it up for me?ETL Server: DCOM permissions based on this article:http://msdn.microsoft.com/en-us/library/aa337083.aspxLaunch and Activation Permissions - Local and Remote (I tried with just remote permissions and i couldnt get packages to run)ETL DB engine:Public LoginUser to MSDB with db_ssisoperatorThanks for any help!

moving tempdb - recovery plan

Posted: 05 Jun 2013 11:28 PM PDT

So this is one of those areas where I feel least comfortable... messing around with system files. Our new production SQL server was set up with all of the system databases on the same VMDK - including tempdb - and it turns out that is bad for replication to our DR environment. So I have been tasked with moving tempdb to it's own separate drive. Now, moving tempdb itself isn't the issue - I've done it before, it's a relatively simple process - ALTER DATABASE with the new location for each file, restart SQL, and you're done.The problem is, I need to develop a plan for if things don't go right. That is, we get to the "restart SQL" phase and it won't start. So I was hoping to put my plan here and see what people thought.1. Back up master - we already have backups of master nightly but back it up anyway.2. Stop SQL3. Make copies of master.mdf and master.ldf to another folder4. Start SQL5. Run the script to move tempdb6. Restart SQL7. If SQL won't start, move the copies I made of master.mdf and master.ldf back to the original folder; start SQL.8. If SQL won't start (any reason it wouldn't?) then start in single-user mode, restore masterThe reason I am looking at steps 2, 3, and 7 is because, in my view, it's a lot simpler. With step 8, I need to make sure that no services try to connect to SQL when I'm starting it in single-user mode; which means either mucking with the firewall to block them, or turning off all of the services I can think of on other servers. I figure that if I just put the original master files back, SQL should start okay (assuming no corruption during the copy, but that's unlikely).Does this make sense? Anything I'm missing?

Problems to remove Oracle ODBC aliases from ODBC GUI

Posted: 05 Jun 2013 05:12 PM PDT

Hi,(I know this not SQL 12 matter but I'm in a hurry and did not spot a better spot for this)The new SqlServ2012 64bit server was installed with Oracle 11g client but later it was found out that they still had a source of version 8.x ==> NO GO and another source that did not function.They uninstalled 11g and installed 10g - both 64/32 bit clients because I need 32 with SSIS.BUT after the 10g, I was not able to remove the 11g ODBC entries from the 32 bit ODBC. It was calling after 11g drivers and a NO GO. Did not help even though I deleted the entries from ODBC.INI and from the register and rebooted.So I tried and created another alias with 10g 32 bit home but it gave error messages, connection was established and DB metadata was readable but when trying to run with SSIS (32 bit)"[i]The specified DSN contains an architecture mismatch between the Driver and Application[/i]"...that is related in configuration situation in the topic below[i]http://msdn.microsoft.com/en-us/library/windows/desktop/ms712362%28v=vs.85%29.aspx[/i]What should I try? I think I should reinstall 10g 32 client (64 bit ODBC seemed to work with Sql Agent) but how can I force tge 32 ODBC settings clean??Ville(The reason I'm using this source over ODBC is because it just does not function over OleDB like another 10g source functions OK...I dont' understand these Oracle features)

Grant Control On Schema verses granting individual permissions on schema?

Posted: 05 Jun 2013 07:39 AM PDT

Would granting control at the schema level take the place of granting individual permissions on the schema? For example, GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]Verses--apply permissions to schemasGRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]GOGRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]GOGRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]GOGRANT INSERT ON SCHEMA::[Developer_Schema] TO [Developer_Role]GOGRANT DELETE ON SCHEMA::[Developer_Schema] TO [Developer_Role]GOGRANT UPDATE ON SCHEMA::[Developer_Schema] TO [Developer_Role]GO--ensure role membership is correctEXEC sp_addrolemember N'Developer_Role ', N'User1'GO--allow users to create tables in Developer_SchemaGRANT CREATE TABLE TO [Developer_Role]GO--Allow user to connect to databaseGRANT CONNECT TO [User1]Thanks, Kevin

SQL 2012 Cluster failure during Add node

Posted: 05 Jun 2013 06:48 AM PDT

I installed a new SQL 2012 failover cluster Enterprise on a 4 node cluster. When I am trying to run Add node on any of the other 3 nodes, the installation fails with the following error:While running 'Update HADRResource'gives error:Status: Failed: see logs for details Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail. Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: 0x86D80018 Error description: There was an error to lookup cluster resource types. Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .Please help in resolving this error.

2012 SQL Server 2012 64 Bit Enterprise running on VMWare virtual server with 256 Gig of RAM

Posted: 05 Jun 2013 04:33 AM PDT

Not sure if this is the correct forum, but it appears that the single instance 2012 SQL Server 2012 64 Bit Enterprise with 225 Gig allocated to SQL running on VMWare is not allocating the correct memory. We have Minimum memory setup for 200 Gig and the Maximum for 225 Gig and the SQL server only is using roughly 20 Gig as viewed through SQL Sentry. The Page Life Expectancy is only round 5 to 8 and should be up around 300, so I'm not sure if anyone else has had this problem?Any assistance would be appreciated or I can provide additional information as needed.Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog