Friday, March 30, 2012
Impact of HD (de)fragmentation on SQL Server performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
Tea C."Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.sql
Impact of HD (de)fragmentation on SQL Server performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
--
Tea C."Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.
Impact of HD (de)fragmentation on SQL Server performance
than fragmented ones.
Are there any published metrics that demonstrate this for Transactional
Databases?
(e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
fragmentation vs. no fragmentation -- using the same load in both scenarios
-- in terms of any performance indicators: disk seek time, R/W Queue, etc).
Thanks,
Tea C.
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
> performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
> scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
> etc).
>
The fragmentation percentage is almost meaningless for large databases. A
100g database in 2 fragments would equate to 50% disk fragmentation, but has
0.00000001% affect on performance. On the other hand, a small, hot database
in many fragments could adversly affect performance without causing alarming
disk fragmentation percentage.
David
|||Hi
Having the Logs on a RAID-5 affects performance more than fragmentation at
OS level.
Auto grow and Auto shrink have an effect as it causes your DB to become
fragmented.
Sometime s with RAID-5, having file fragmentation is actually good as it
might then involve a different spindle than if the data was contiguous.
Test the performance differences in your environment, setup may play a big
role.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tea C" <smece1972@.yahoo.com.nospam> wrote in message
news:C0FF7B34-58B8-4BDF-8360-261ADABEEA94@.microsoft.com...
> It seems logical to me, that defragmented HDs would yield better
performance
> than fragmented ones.
> Are there any published metrics that demonstrate this for Transactional
> Databases?
> (e.g., RAID 5, 200GB Drive with 50% disk fragmentation and 65% file
> fragmentation vs. no fragmentation -- using the same load in both
scenarios
> -- in terms of any performance indicators: disk seek time, R/W Queue,
etc).
> Thanks,
> --
> Tea C.
Impact of empty tables on database size and performance
Hello All,
When creating my database I have modeled some of the tables after the Adventureworks sample database.
There are some fields or entire tables in Adventureworks that I do not see an imediate use for, however; I would hate to ommit them to find out later they would have been benificial. (.eg territory table).
In general terms what would the impact be on size and performance of a database which contains tables or fields that do not contain data.
Thanks for your help!
Blair:
There will be little impact from tables that are not used provided these tables are small and do not grow. If the tables are large then they can impact your storage requirements and the size and speed of your backups.
Fields that are not used are a different story -- especially if the fields are populated with large amounts of "spectator" data. These "spectator" fields will then impact the amount of space required for their tables and will have an impact on the amount of time required for a "table scan". This becomes amplified to some extent if these fields participate in any indexes. If your fields are all null the amount of bloating will not be as great.
Another problem that can occur if you leave in fields that have at the moment no use is that in the future it can become tempting for somebody to start "using" these fields in ways that are not planned. Eliminating unused fields can stop this kind of "cobbling" before it takes place.
I would suggest that you are better off with a well designed, well thought out database in which each column has a specific meaning with a specific intended use.
Impact of "Rows per batch" on OLE DB Destination?
OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?
I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:
For each row:
begin transaction
insert row
commit transaction
thanks,
Nitesh
Nitesh Ambastha wrote:
OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:For each row:
begin transaction
insert row
commit transaction
thanks,
Nitesh
Hi Nitesh.
Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.
-Jamie|||Mmmmmhmmmmm, I'd like to see that! :)|||OK I thought I'd have a go at this. Where is this "rows per batch" parameter/property?
I can't see it. Am I being really dumb?|||
Jamie,
It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.
Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).
Thanks
Sutha
Sutha Thiru wrote:
Jamie,
It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.
Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).
Thanks
Sutha
OK, I'm glad I'm not heading for the loony bin. I was looking at an IDW15 instance which doesn't have it. I've checked my IDW16 VM and its there.
I wonder why it (and many other properties) were removed on IWD15?
And here's another question. Why are they (Rows per batch, keep nulls, table lock, check constraints, keep ID, max insert commit size) not available via the preoperties pane?
cheers Sutha
-Jamie|||
Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.
Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?
Thanks
Sutha
Sutha Thiru wrote:
Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.
Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?
Thanks
Sutha
As an aside...I don't think they are new properties. I'm *sure* I remember seeing them way back when. It seems to me like they disappeared for IDW15 and now they've reappeared again. Weird!!
-Jamie|||
I did test way back when on the perf of loading batches per transaction. I generally found that 2000 was a good figure. However I supsect this depends greatly on,
the wdith of the rows being inserted,
the io configuration
memory in the system
Interestingly, why is there no batch rows setting for the SQL destination?
|||I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it?)-Jamie|||
In SQL server 2005, it is our decision to remove properties "RowsPerBatch" and "BatchSize" from SQL server destination adapter.
Reason is SQL server dest adapter is not written to utilize the functionalities the two properties provide - e.g. query plan optimization.
We will consider whether to enable them in the next version.
|||"Rows per batch" is a Fast Load Option. In the OLE DB Destination, it is only visible (along with some other options like Keep Identity, Keep Nulls etc.) if you choose the Data Access Mode "Table or View - Fast Load". If you choose the data access mode "Table or view", they won't show up. They never disappeared from IDW15, my guess is you are looking at it with the non fast load option, and therefore are not finding it there.
They are available from the properties window, but in a different way. You can specify them in the FastLoadOptions. For example,
FastLoadOptions : TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 1000
sqlWednesday, March 21, 2012
Image Data Type
Does any one has any document or link about pros and cons of using Image
data type to store images? Specially performance point of view.
Thanks,
Mukesh
Well, it depends. A quick search on the web reveals this article:
http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
I hope this will get you started.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
> Hello,
> Does any one has any document or link about pros and cons of using Image
> data type to store images? Specially performance point of view.
> Thanks,
> Mukesh
|||Thanks Wei,
This will help a lot...
Thanks,
Mukesh
"Wei Xiao [MSFT]" wrote:
> Well, it depends. A quick search on the web reveals this article:
> http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
> I hope this will get you started.
>
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
> news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
>
>
Image Data Type
Does any one has any document or link about pros and cons of using Image
data type to store images? Specially performance point of view.
Thanks,
MukeshWell, it depends. A quick search on the web reveals this article:
http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
I hope this will get you started.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
> Hello,
> Does any one has any document or link about pros and cons of using Image
> data type to store images? Specially performance point of view.
> Thanks,
> Mukesh|||Thanks Wei,
This will help a lot...
Thanks,
Mukesh
"Wei Xiao [MSFT]" wrote:
> Well, it depends. A quick search on the web reveals this article:
> http://www.extremeexperts.com/sql/FAQ/StoreImages.aspx
> I hope this will get you started.
>
> --
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Mukesh" <Mukesh@.discussions.microsoft.com> wrote in message
> news:DBDBEF9F-4897-4043-8F2C-5E0BA9CCA541@.microsoft.com...
>
>