Encryption Performance

Hi

I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.

Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.

But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?

Also, I can not have a UDF with these following steps

1. Open symmetric key

2. Convert secretdata using DecryptByKey

3. Close Symmetric Key.

4. return decrypted value.

Can some one give some insights on this ?

Can you show the way you call the DecryptByKeyAutoCert and DecryptByKey builtins? Also, how much data are you decrypting - what are the number of rows you select and the size of encrypted data per row?

You cannot create a function to decrypt, but you can create a procedure to decrypt. For example, see the procedure from http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Number of rows that I am decrypting is 10000. The record size is 516 bytes

DecryptByKey code:

OPEN SYMMETRIC KEY [Cert_Account_Data_Key] DECRYPTION by certificate [cert_Account_Data]

-- Account table has 10000 records

select account_id,

convert( nvarchar(100), decryptbykey(account_number)) as 'Decrypted Account Number',

convert( nvarchar(100), decryptbykey(account_ssn)) as 'Decrypted Account SSN'

from account_t

CLOSE SYMMETRIC KEY [Cert_Account_Data_Key]

DecryptByCert code:

-- 1. create udf

CREATE FUNCTION [dbo].[udf_Decrypt_Account_Data] (@.Secret_Data VARBINARY(256)) returns nvarchar(100)

WITH EXECUTE AS 'DBO'

AS

begin

-- This return decrypted value for the input data using Account Data

return convert( nvarchar(100), decryptbykeyautocert( cert_id( 'cert_Account_Data' ), null, @.Secret_Data))

end

-- selects decrypted data using Account decryption function

select ACCOUNT_ID,

dbo.udf_Decrypt_Account_Data (ACCOUNT_NUMBER) as 'Decrypted Account Number',

dbo.udf_Decrypt_Account_Data (ACCOUNT_SSN) as 'Decrypted Account SSN'

from ACCOUNT_T

thanks

satya

|||

Using decryptbykeyautocert like this will give abysmal performance. The reason for this is that decryptbykeyautocert is efficient if you use it in a query - it will decrypt the key once and it will keep it open for the duration of the query. By putting the builtin call within a function and calling the function from a query, you are basically forcing the builtin to reopen the key each time the function is called - twice per row in your case, and this represents significant overhead.

You don't need to give CONTROL on the encryption key to a user, for him to be able to use it. It is sufficient to grant him VIEW DEFINITION and add another encryption to the key so that the user can access the key through the new encryption. You can add, for example, another certificate encryption using one of the user's certificates. Then the user will be able to just call decryptbykeyautocert directly, instead of this function, and the query will execute much faster.

Thanks
Laurentiu

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encryption

Hi,
i was wondering if the whole table can be encrypted using MS SQL Server 2005. This at present can be done using third party softwares.
Performance won't be an issue.

Thanks and regards,
Chandrachurh

There is no built-in feature for this, to my knowledge. Most organizations accomplish data protection through good security measures.

-Ryan / Kardax

|||

First, it would be very unusual to encrypt the entire table. That would make it impossible to create relationships, enforce constraints, and have meaningful indexes.

Most likely, you want to encrypt 'some' of the data in a table.

A great place to start is Laurentiu's blog:

http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx

And then, a Google? search for [ "sql server 2005" AND table AND encrypt ] on the first page will turn up many good resources.

|||Friends,
in my case performance is not an issue.
Let me clarify the situation.
Well, we have column encryption with asymmetric keys and certificates into place. But, for efficient hackers it wont be a big problem in decrypt them with the hidden security flaws. I know that encryption is very powerful in MS SQL Server 2005 using randomness in keys. Still, every good things have a period.
Take the case of a telephone directory on a cd. Whoever gets the possession of the cd has enough time to go on with R&D activities to decrypt the data. If the table itself is encrypted, they won't be able to view the contents, the hash properties and others repititiveness. I understand that this has been taken care of in SQL Server 2005 by using randomness,i.e., similar contents cannot be deciphered. But, if you can see the contents, and also know this feature you might decrypt it with a bit of extra labour.
Am I talking sense? Please advice.

Chandrachurh wrote:

Hi,
i was wondering if the whole table can be encrypted using MS SQL Server 2005. This at present can be done using third party softwares.
Performance won't be an issue.

Thanks and regards,
Chandrachurh

|||

Chandrachurh wrote:

Hi,
i was wondering if the whole table can be encrypted using MS SQL Server 2005. This at present can be done using third party softwares.
Performance won't be an issue.

Thanks and regards,
Chandrachurh

|||

Encryption will protect you against someone stealing your database. If you use AES encryption, it cannot be deciphered using today's technology without having the encryption key.

But if you're selling an application and you are trying to protect the proprietary data in your application from the users of the application, then encryption will not help you achieve that goal. It's not that attackers will break the AES encryption algorithm, but you're going to give them the encryption key yourself, embedded in your application.

Thanks

Laurentiu

Encrypted Stored Proc Performance

Does anyone know if there executing encrypted stored procedures in SQL
Server 2005 is any slower than regular ones?
Thanks,
KripHi, Krip,
There really should be no difference in performance, since the execution of
the stored procedure is not from the text. Maybe recompiles take slightly
longer, but I would not know.
The gentleman in this thread from 2004 felt that his encrypted stored
procedures ran faster, but the full testing method was questioned.
http://groups.google.com/group/micr...80e7a94e230ea8f
From the same time period Jacco Schalkwijk (SQL Server MVP) noted that the
difference was negligable.
RLF
"Krip" <amk@.kynetix.com> wrote in message
news:DC3CA6E3-A4D6-4B1C-99AB-88660359C9B7@.microsoft.com...
> Does anyone know if there executing encrypted stored procedures in SQL
> Server 2005 is any slower than regular ones?
> Thanks,
> Krip
>|||Thanks Russell. Makes sense. Good to hear from you!
-Krip

enabling/disabling query optimizer

I would like to run some tests to measure the performance gain through the query optimizer. Is it possible to turn off the query optimizer? Is there any command line switch or SQL operator for that?This is not possible. You can however write the SELECT statement or query with hints that force join orders or specific type of joins. See SELECT statement topic for more details.

Enabling SQL performance monitoring objects

How do I enable the SQL performance monitoring objects to be used by
performance monitor? The objects were in the list at one time but now they
have vanished?
Best regards
Mark Baldwin
Software Developer
"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>
I have seen this before in prior versions and it was a matter of rerunning a
particular script from setup disk. I don't remember which one and which
version of SQL Server is applied to.
Here is a KB article however that may be of some use:
http://support.microsoft.com/kb/227662
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Counters may disappear when you don't close performance monitor before
restarting SQL Server. See MSKB 812915
<http://support.microsoft.com/default...&Product=sql2k>.
This is scheduled to be fixed in SP4, which is currently in beta
http://support.microsoft.com/default...;en-us;888799.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>
|||Mark
If you are able to do it, the following may fix your problem.
At the command prompt, type in the following:
unlodctr.exe MSSQLServer
Then type in the following:
lodctr.exe <SQL Server path>\binn\sqlctr.ini
Reboot the server
Hope this helps
John
"Dan Guzman" wrote:

> Counters may disappear when you don't close performance monitor before
> restarting SQL Server. See MSKB 812915
> <http://support.microsoft.com/default...&Product=sql2k>.
> This is scheduled to be fixed in SP4, which is currently in beta
> http://support.microsoft.com/default...;en-us;888799.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mark Baldwin" <swozz_@.hotmail.com> wrote in message
> news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
>
>

Enabling SQL performance monitoring objects

How do I enable the SQL performance monitoring objects to be used by
performance monitor? The objects were in the list at one time but now they
have vanished?
Best regards
Mark Baldwin
Software Developer"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>
I have seen this before in prior versions and it was a matter of rerunning a
particular script from setup disk. I don't remember which one and which
version of SQL Server is applied to.
Here is a KB article however that may be of some use:
http://support.microsoft.com/kb/227662
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Counters may disappear when you don't close performance monitor before
restarting SQL Server. See MSKB 812915
<http://support.microsoft.com/defaul...b;en-us;888799.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>|||Mark
If you are able to do it, the following may fix your problem.
At the command prompt, type in the following:
unlodctr.exe MSSQLServer
Then type in the following:
lodctr.exe <SQL Server path>\binn\sqlctr.ini
Reboot the server
Hope this helps
John
"Dan Guzman" wrote:

> Counters may disappear when you don't close performance monitor before
> restarting SQL Server. See MSKB 812915
> <http://support.microsoft.com/defaul...b;en-us;888799.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mark Baldwin" <swozz_@.hotmail.com> wrote in message
> news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
>
>

Enabling SQL performance monitoring objects

How do I enable the SQL performance monitoring objects to be used by
performance monitor? The objects were in the list at one time but now they
have vanished?
--
Best regards
Mark Baldwin
Software Developer"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>
I have seen this before in prior versions and it was a matter of rerunning a
particular script from setup disk. I don't remember which one and which
version of SQL Server is applied to.
Here is a KB article however that may be of some use:
http://support.microsoft.com/kb/227662
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Counters may disappear when you don't close performance monitor before
restarting SQL Server. See MSKB 812915
<http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&Product=sql2k>.
This is scheduled to be fixed in SP4, which is currently in beta
http://support.microsoft.com/default.aspx?scid=kb;en-us;888799.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Baldwin" <swozz_@.hotmail.com> wrote in message
news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> How do I enable the SQL performance monitoring objects to be used by
> performance monitor? The objects were in the list at one time but now they
> have vanished?
> --
> Best regards
> Mark Baldwin
> Software Developer
>
>|||Mark
If you are able to do it, the following may fix your problem.
At the command prompt, type in the following:
unlodctr.exe MSSQLServer
Then type in the following:
lodctr.exe <SQL Server path>\binn\sqlctr.ini
Reboot the server
Hope this helps
John
"Dan Guzman" wrote:
> Counters may disappear when you don't close performance monitor before
> restarting SQL Server. See MSKB 812915
> <http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&Product=sql2k>.
> This is scheduled to be fixed in SP4, which is currently in beta
> http://support.microsoft.com/default.aspx?scid=kb;en-us;888799.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mark Baldwin" <swozz_@.hotmail.com> wrote in message
> news:XMSdnaHx_r0qLnLcRVn-hQ@.giganews.com...
> > How do I enable the SQL performance monitoring objects to be used by
> > performance monitor? The objects were in the list at one time but now they
> > have vanished?
> >
> > --
> > Best regards
> > Mark Baldwin
> > Software Developer
> >
> >
> >
>
>

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peter
hi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
current location: alicante (es)
"Peter Hartlén" wrote:

> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be in
> trouble if there is a power outage. But how serious is this? The machine is
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it when
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>
|||"Peter Hartln" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter
|||"Peter Hartln" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.

> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.

> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David
|||Hi David, thanks for your reply!

> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter
|||"Peter Hartln" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.

> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartln" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David
|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peterhi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
current location: alicante (es)
"Peter Hartlén" wrote:

> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
in
> trouble if there is a power outage. But how serious is this? The machine i
s
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it whe
n
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>|||"Peter Hartln" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter|||"Peter Hartln" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.

> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.

> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David|||Hi David, thanks for your reply!

> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter|||"Peter Hartln" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.

> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartln" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peterhi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
--
current location: alicante (es)
"Peter Hartlén" wrote:
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be in
> trouble if there is a power outage. But how serious is this? The machine is
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it when
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>|||"Peter Hartlén" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
>> Realistically, the worst case is that you will need to recover your
>> databsaes from backup.
>> However, you should NEVER do this either in production or development.
>> The reasons for doing it in production are obvious.
>> In development turning on write cashing on disks badly distorts the
>> performance characteristics of the databsae by eliminating the cost of
>> log flushing. This can leave perforance problems to arise in production
>> when you wonder why trying to do 2000 single-row inserts without a
>> transaction is slow.
>> David
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.
> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.
> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David|||Hi David, thanks for your reply!
> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.
> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartlén" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any nested
>> transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware itself,
>> only the write cache, and I got a 10x improvment when enabling write
>> cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>> "Peter Hartlén" <peter@.data.se> wrote in message
>> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any
>> nested transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware
>> itself, only the write cache, and I got a 10x improvment when enabling
>> write cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
>> No. One single transaction is just right. The mistake most people make
>> is omiting the transaction, and letting each statement commit by itself.
>>
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
>> Yes. The only time write caching should makes a huge difference is when
>> the application is flushing the log (commiting transactions) too often.
>> What sort of performance numbers are you seeing? How are you doing the
>> import.
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>