Performance gain on specifying SqlDbType on parameter

How much if any of a perf gain is there when you specify the SqlDbType on a parameter?There is a perf gain, since it is listed as a performance tip inChapter 12 - Improving ADO.NET Performance of the Improving .NET Application Performance and Scalability guide.

Here is what the guide says:

Specify Parameter Types
When you create a new parameter, use the relevant enumerated type to specify the data type of the parameter. Use an enumerated type such as SqlDbType or OleDbType. This prevents unnecessary type conversions that are otherwise performed by the data provider.

Performance gain by switching from Win2000 to Win2003?

Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>
|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas

"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>

Performance gain by switching from Win2000 to Win2003?

Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
--
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>

Performance gain by switching from Win2000 to Win2003?

Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
Spec for Win2003 mentions better use of hyperthreading for SQL Server and IO
performance.
Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
Server from using it by unchecking the last few set of the processors in EM?
Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
Win2003?
Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
hit by the /PAE addressing?
The cache stat on Prepared is at 60%.
Thanks.It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>|||Unfortunately, there is no Win2K EE, only AS.
Also, AWE will help with offloading lower 2 GB memory for data pages, but
not much else. This could releave some pressure you are experiencing on
your PROC CACHE, which is limited to the 4 GB region regardless of AWE or
PAE.
Are you currently using the /3GB boot.ini switch. I would try this first to
see if your PROC CACHE situation improves first before going through the
time and effort of an upgrade and/or adding additional memory. AWE adds
functionality but it also adds new headaches.
If this doesn't help, I doubt going to AWE will either. The best long term
solution, then, would be to start checking out the 64-bit platforms, IA64,
not that x64, EMT64 garbage.
Run DBCC PROCCACHE to see if you are being starved or not, both before and
after setting the /3GB switch.
I'm not so sure why there is so much negativity surrounding the use of HTT.
I've never had a problem with it even though I know there is a lot of
articles out there. I wonder if the same sentiment exists over the new
multi-core chips?
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OO3aO$CaFHA.4040@.TK2MSFTNGP14.phx.gbl...
It is impossible to say how much improvement you will get as it depends on
so many things. But in general if you are using any of the resources
moderate to heavily you should see some improvements. Basically the Memory
utilization is much better, the I/O and Networking has greatly improved and
scheduling is much better as well. Win2003 is HT aware and will better
support it over Win2K but you should start by setting the MAXDOP to the
number of physical processors and see how that goes. PAE does not add that
much overhead and if you actually need more memory it is the way to go. To
let SQL Server use more than 4GB you will also need to enable AWE.
Andrew J. Kelly SQL MVP
"Peter Yao" <peteryao@.NoSPAMhotmail.com> wrote in message
news:O4Sovm9ZFHA.1940@.TK2MSFTNGP10.phx.gbl...
> Currently have Win2000 Enterprise, 4 Xeon CPU, 4 GB memory.
> Spec for Win2003 mentions better use of hyperthreading for SQL Server and
> IO
> performance.
> Do you leave hyperthreading on at BIOS level for OS to use but prevent SQL
> Server from using it by unchecking the last few set of the processors in
> EM?
> Can I expect a 5% to 10% perf. increase by upgrading from Win2000 to
> Win2003?
> Also thinking about upgrading 4GB to 8GB using /PAE is there a heavy perf.
> hit by the /PAE addressing?
> The cache stat on Prepared is at 60%.
> Thanks.
>sql

Performance gain

How much of a performance gain I can get on 1.2 million rows insert via DTS
package If I,
- Drop 1 clustered and 2 non-clustered index on the table I am loading
- 2-3 foreign keys referencing other tables
- Replace the delete table with truncate table
- Do a bulk insert
- Re-create the indexes
- Re-create the foreign keys
Thanks for any input.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
>
Well best way will be to try it. ;-)
BUT, in my experience what you're doing will help.

> - Drop 1 clustered and 2 non-clustered index on the table I am loading
This helps, but mostly if the imported data is already sorted on the
clustered index. Otherwise, rebuilding the clustered index may eat up any
savings.

> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
HUGE help

> - Do a bulk insert
Definitely faster w/o indices.

> - Re-create the indexes
> - Re-create the foreign keys
>
In general you should see a big improvement, especially if the clustered
index doesn't have to do much data movement.

> Thanks for any input.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You have given us too little data to assist you properly. The biggest thing
missing is how many rows are ALREADY in the table and how many rows are in
the referencing tables?
In general if you are inserting an amount that is a reasonably large
fraction of the total rows already in the table, then it is better to
drop/rebuild. But in this case if your table already has a billion rows,
adding 1.2 million would be much faster keeping things as they are.
TheSQLGuru
President
Indicium Resources, Inc.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
> - Drop 1 clustered and 2 non-clustered index on the table I am loading
> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
> - Do a bulk insert
> - Re-create the indexes
> - Re-create the foreign keys
> Thanks for any input.
|||On Thu, 10 May 2007 09:10:01 -0700, DXC
<DXC@.discussions.microsoft.com> wrote:

>How much of a performance gain I can get on 1.2 million rows insert via DTS
>package If I,
>- Drop 1 clustered and 2 non-clustered index on the table I am loading
>- 2-3 foreign keys referencing other tables
>- Replace the delete table with truncate table
>- Do a bulk insert
>- Re-create the indexes
>- Re-create the foreign keys
>Thanks for any input.
Besides two good answers already, how long does it take now?
J.

Performance gain

How much of a performance gain I can get on 1.2 million rows insert via DTS
package If I,
- Drop 1 clustered and 2 non-clustered index on the table I am loading
- 2-3 foreign keys referencing other tables
- Replace the delete table with truncate table
- Do a bulk insert
- Re-create the indexes
- Re-create the foreign keys
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
>
Well best way will be to try it. ;-)
BUT, in my experience what you're doing will help.

> - Drop 1 clustered and 2 non-clustered index on the table I am loading
This helps, but mostly if the imported data is already sorted on the
clustered index. Otherwise, rebuilding the clustered index may eat up any
savings.

> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
HUGE help

> - Do a bulk insert
Definitely faster w/o indices.

> - Re-create the indexes
> - Re-create the foreign keys
>
In general you should see a big improvement, especially if the clustered
index doesn't have to do much data movement.

> Thanks for any input.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You have given us too little data to assist you properly. The biggest thing
missing is how many rows are ALREADY in the table and how many rows are in
the referencing tables'
In general if you are inserting an amount that is a reasonably large
fraction of the total rows already in the table, then it is better to
drop/rebuild. But in this case if your table already has a billion rows,
adding 1.2 million would be much faster keeping things as they are.
TheSQLGuru
President
Indicium Resources, Inc.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
> - Drop 1 clustered and 2 non-clustered index on the table I am loading
> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
> - Do a bulk insert
> - Re-create the indexes
> - Re-create the foreign keys
> Thanks for any input.|||On Thu, 10 May 2007 09:10:01 -0700, DXC
<DXC@.discussions.microsoft.com> wrote:

>How much of a performance gain I can get on 1.2 million rows insert via DTS
>package If I,
>- Drop 1 clustered and 2 non-clustered index on the table I am loading
>- 2-3 foreign keys referencing other tables
>- Replace the delete table with truncate table
>- Do a bulk insert
>- Re-create the indexes
>- Re-create the foreign keys
>Thanks for any input.
Besides two good answers already, how long does it take now?
J.

Performance gain

How much of a performance gain I can get on 1.2 million rows insert via DTS
package If I,
- Drop 1 clustered and 2 non-clustered index on the table I am loading
- 2-3 foreign keys referencing other tables
- Replace the delete table with truncate table
- Do a bulk insert
- Re-create the indexes
- Re-create the foreign keys
Thanks for any input."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
>
Well best way will be to try it. ;-)
BUT, in my experience what you're doing will help.
> - Drop 1 clustered and 2 non-clustered index on the table I am loading
This helps, but mostly if the imported data is already sorted on the
clustered index. Otherwise, rebuilding the clustered index may eat up any
savings.
> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
HUGE help
> - Do a bulk insert
Definitely faster w/o indices.
> - Re-create the indexes
> - Re-create the foreign keys
>
In general you should see a big improvement, especially if the clustered
index doesn't have to do much data movement.
> Thanks for any input.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You have given us too little data to assist you properly. The biggest thing
missing is how many rows are ALREADY in the table and how many rows are in
the referencing tables'
In general if you are inserting an amount that is a reasonably large
fraction of the total rows already in the table, then it is better to
drop/rebuild. But in this case if your table already has a billion rows,
adding 1.2 million would be much faster keeping things as they are.
--
TheSQLGuru
President
Indicium Resources, Inc.
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:72D6D8E4-6F94-4DB9-A93E-F67F07F3D277@.microsoft.com...
> How much of a performance gain I can get on 1.2 million rows insert via
> DTS
> package If I,
> - Drop 1 clustered and 2 non-clustered index on the table I am loading
> - 2-3 foreign keys referencing other tables
> - Replace the delete table with truncate table
> - Do a bulk insert
> - Re-create the indexes
> - Re-create the foreign keys
> Thanks for any input.|||On Thu, 10 May 2007 09:10:01 -0700, DXC
<DXC@.discussions.microsoft.com> wrote:
>How much of a performance gain I can get on 1.2 million rows insert via DTS
>package If I,
>- Drop 1 clustered and 2 non-clustered index on the table I am loading
>- 2-3 foreign keys referencing other tables
>- Replace the delete table with truncate table
>- Do a bulk insert
>- Re-create the indexes
>- Re-create the foreign keys
>Thanks for any input.
Besides two good answers already, how long does it take now?
J.