DB Engine :: Stored Procedures Running Slowly When Encrypted On New Server?
Jul 14, 2015
I have a strange situation when I try to execute the same Stored Procedure on servers with different processors. Both servers are running the SQL SERVER 2008 R2 version with all updates.
All updates bios, disk controller, firmware, were applied on the new server.
New Server: (considerable difference in processing time)
Stored Procedure without encryption, runs at about 02:16hs
Stored Procedure with encryption, runs at about 08:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.60GHz E5-2697 v3
Old Server: (There Are No difference in processing time)
Stored Procedure without encryption, runs at about 01:00hs
Stored Procedure with encryption, runs at about 01:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.7GHz E5-2697 V2
In terms of configuration, the server 2, have lower technology, lower bus, lower number of processors.
I believe that combination of Hardware/SO/Sql Server has a potential performance loss when running encrypted SP.
I have several stored procedures, created in a development environment,that I need to move to a 'QA' environment, and then in turn, to variousproduction environments.When I move these stored procedures, I would like to encrypt them,using the 'WITH ENCRYPTION' clause.My question is, how do I copy these stored procedures from developmentto their target SQL server environment in an encrypted state?Up until now, we have been moving them by generating an SQL script andthen executing that script on the target server. I have tried thisusing a script with 'WITH ENCRYPTION' specified within it, but itdoesn't appear to work when I try and execute that script on the targetserver.Any advice would be greatly appreciated.Nick.
Hi, Can someone help me with this problem. I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd Set cn = CreateObject("ADODB.Connection") cn.Open "SQL" //Data Source Name Set cmd = CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandText = "LoadApplicants" //Stored Procedure Name cmd.CommandType = adCmdStoredProc cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
I've just had a call saying that their SQL Server v7 box on a Compaq 7000 that has been installed for about 6 months is now running slowly, a look on the CPU shows that SQL server is grabbing 50% upwards of the CPUs (dual processor) even when the front end application is not running , is there anything that I should be checking
I have and SQL server that had NamedPipes, Multiprotocol,IPX/SPX and TCP/IP all loaded. It is running on a 10Mbps network. I have run setup and turned all other protocols off except TCP/IP to increase speed. This has increased speed on all the workstations but the server now runs very very very slowly. If anyone out there has any ideas please let me know.
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
We try to run a stored procedure in management studio - and we see the following error - " Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[SQLSTATE HY000] (Error 0) The log for database 'Some-database' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). The step failed.
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
I have two similar stored procedures which I'm running. One runs andone doesn't. I can run both with no problems in SQL Enterprise (7.0standard) and have checked the permissions and am happy with them.Whilst the statements in each sp are different, I'm calling them inexactly the same way (using Delphi 5 Windows 2000). I don't get anytrappable errors.I ran a trace on what was happening and I get two different set ofresults. This is what I'm trying to get my head around.Client Trace (runs)1 09:09:44 Log started for: Swift Client Import Utility2 09:09:48 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftClient;13 09:09:48 SQL Misc: MSSQL - Set stored procedure on or off4 09:09:48 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL5 09:09:48 SQL Misc: MSSQL - Set statement type6 09:09:48 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftClient;17 09:09:48 SQL Stmt: MSSQL - Close8 09:09:53 SQL Connect: MSSQL - Disconnect NEW9 09:09:53 SQL Connect: MSSQL - Disconnect NEW10 09:09:53 SQL Connect: MSSQL - Disconnect PASSTHRUEnquiry Trace (Doesn't run)1 09:08:21 Log started for: Swift Client Import Utility2 09:08:24 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;13 09:08:24 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;14 09:08:50 SQL Prepare: MSSQL - :1 =dbo.CBFAUpdateSwiftEnquiries;15 09:08:50 SQL Misc: MSSQL - Set stored procedure on or off6 09:08:50 SQL Data In: MSSQL - Param = 1, Name = Result, Type= fldINT32, Precision = 0, Scale = 0, Data = NULL7 09:08:50 SQL Misc: MSSQL - Set statement type8 09:08:50 SQL Execute: MSSQL - :Result =dbo.CBFAUpdateSwiftEnquiries;19 09:08:50 SQL Vendor: MSSQL - dbrpcinit10 09:08:50 SQL Vendor: MSSQL - dbrpcexec11 09:08:50 SQL Vendor: MSSQL - dbsqlok12 09:08:50 SQL Vendor: MSSQL - dbresults13 09:08:50 SQL Vendor: MSSQL - dbnumcols14 09:08:50 SQL Vendor: MSSQL - dbcount15 09:08:50 SQL Stmt: MSSQL - Close16 09:08:50 SQL Vendor: MSSQL - dbdead17 09:08:50 SQL Vendor: MSSQL - dbcancel18 09:08:56 SQL Connect: MSSQL - Disconnect NEW19 09:08:56 SQL Connect: MSSQL - Disconnect NEW20 09:08:56 SQL Connect: MSSQL - Disconnect PASSTHRU21 09:08:56 SQL Vendor: MSSQL - dbdead22 09:08:56 SQL Vendor: MSSQL - dbfreelogin23 09:08:56 SQL Vendor: MSSQL - dbcloseI would have thought that these would be nearly identical. They callsp's on the same servers in the same way, so the call to do thisshould (in my mind) be the same.I can post the sp's if anyone thinks they are of relevance.I can of course set up these stored procedures to run at a certaintime, but I'd like to try and understand this a little more.Thanks in advance.Ryan
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.
In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services??? Any ideas of how to speed this process up?
Hi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
Hi allI am looking for the best method to automate a website's database management. Lets say I have a user registration database and the users register. This sends an automated email to the user with a link to activate the users registration. If the user does not register within 24 hours, his registration must be automatically deleted from the database using a stored procedure.I know how to do this using the global.aspx file, however there must be an alternative way of doing this, especially if the database is an SQL database. I do not know how much MSSQL server access is given to a developer by an as ISP who hosts the website.Can anyone tell me what would be the best method to use.ThxWarren
Developers have complained that some of their jobs are taking too long to run. I used Profiler to trace one really bad performer and could see that the code was making one pass through its loop in about 7 seconds. I found one select statement that was using the majority of the time, plugged it into query analyzer and looked at the execution plan. It was using an index but not very efficiently. I then ran it through index analysis and it recommended a new index. This was cool! The new index helped.
But, the job continued to run very, very slowly - over a week before it died! I ran another trace, and it was hanging for 10+ seconds in random locations. Sometimes it would be on a database call, sometimes it would be on a simple SET @variable = value statement. There was no pattern. According to the sys admin, the server itself did not look taxed. It does not appear to be a locking problem because of the random location of the stall. The application and server are only lightly used at this point, so I would hate to see how bad it could be if it was busy.
I am still fairly new to SQL, though I have years of DBA experience. I would appreciate any ideas - especially the obvious things that I am probably not considering. I have logged a call with Microsoft but they haven't gotten back with an answer. I sent them a trace file and a perfmon file, neither of which showed them anything.
i have a sproc that is taking 5 hours+ to run. that was on 40000 rows. even on 25 rows it takes over an hour. the sproc calls a sproc or two with a cursor or two ( sorry for being so vague) . the sproc stil should not take an hour for 25 rows. i have checked what db objects are locked, and there are no other processes going on at the time, as i have killed all them off. would lack of space on a hard disk or something. any ideas of some strange things that could cause a sproc to run slowly?
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large or Unknown token received from SQL Server
or Protocol error in TDS stream or Bad token from SQL Server: Datastream processing out of sync. or Invalid cursor state or TDS Buffer Link Too Large or Function sequence error
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
I have some encrypted stored procedure. I want to use the output of the encrypted stored procedure insert the output into temp table. is it possible to do. If so please let me know how can I proceed. Thanks in advance
I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.
First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.
So how would someone prohibit viewing of a stored proc like this?
Hello, In SQL 2000, I have created a Stored Procedure as follows,
Code Snippet
CREATE PROCEDURE MyTest WITH RECOMPILE, ENCRYPTION AS Select * From Customer Then after this when i run this sp it giving me the perfect results wht i want, BUT when i want to change something in sp then for I am using the below line of code.
Code Snippet
sp_helptext mytest But its displaying me that this sp is encrypted so you can't see the details and when i am trying to see trhe code of this sp from enterprise manager then also its not displaying me the details and giving me the same error, So i want to ask that if there is a functionality of enrypting the sp code then is there any functionality for decrypting the Stored Procedure also, or not, If yes then wht it is and if NO then wht will be the alternative way for this, ?????
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key encryption by password = 'testAppleA3';
create certificate test with subject = 'test certificate', EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000')); insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees --delete from employees select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test drop certificate test drop master key
Then recreate the master key:
create master key encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test with algorithm = triple_des encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Last night we had a problem caused by a stored proc being deployed to only one server rather than two. I want a way to ensure that we can automatically run on a schedule a check to ensure that the 2 procs on 2 servers are the same.
I need a less robust solution that I implement tomorrow that doesn't involve buying third party tools and a more robust long term strategy that could if need make use of third party tools.
We are collecting values in a string format with delimeteres and sending to DB .We would like to insert the data in Bulk insert format rather than splitting the same and then inserting..
In sql 2014 can we archive the same..sample format currently we are getting the client is like this is
Saleid$ salename$month$year$totalsale#Saleid$salename$month$year$totalsale# has a dataset.
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Hi, I have looked and loked for the answer to this question and made no progress. I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas). Is it alright to create another database and have 2 running simultaneous on the same data engine? Is there any type of limitations that I sould know about - such as number of allowed accesses at one time? Any help is greatly appreicated.
Any sql script or powershell script which outputs late running jobs? Currently I am using the below script to find out currently running jobs along with duration. But my requirement is to add few more columns to the query which indicates whether jobs is running fine or running behind expected time.
-currently using query to pull running jobs SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 AS currently_executing_step_id,
know 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.
