Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.
Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>
|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>
|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>

Fillfactor

I have a very simple insert statement into a table containing about a
million rows.
It runs a fraction of a second for about 1000 times and then takes about 5
seconds, and then again fraction of a second for about 1000 inserts. There
are 3 indexes on the table with fillfactor 70.
Am i right by guessing that at the time when insert takes 5 seconds the page
splits? Should i decrese fillfactor to 50? Or could it be something else?
Thanks.Perhaps there are other possible sources, but it would more likely be your
log files or data files growing. I suppose the quick and dirty check is to
simply monitor both files before and after to see if growth occured.
Autogrowth is evil in my mind.
A single page split is taking a 8K allocated space and splitting it in two
locations. While you don't want this to happen a ton, this is not a task
that would take 5 full seconds, or at least the stall would not be after
record 997 was inserted. I suppose if the records you are inserting are all
being inserted into the SAME part(page) of the index, this could be
problematic.
If the index is related to your performance issues, you could remove the
index prior to the insert, insert, and then rebuild the indexes? This is an
expensive operation too though.
Mark
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||1) you can monitor page splits using a perf mon counter. I doubt very much
this is the issue
2) the issue could be log flushes, checkpoints, blocking, something random
hitting the server hard. examine waiting tasks (not sure which version you
are on so can't specify exactly how to do that) when the delay occurs. also
run sp_who2 active to check for blocking.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I agree with the others that it is probably not related to the fill factor.
I would guess blocking first, disk queues next (checkpoints etc.) and maybe
even file auto growths. Make sure there is plenty of free space in the data
and log files.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>I have a very simple insert statement into a table containing about a
>million rows.
> It runs a fraction of a second for about 1000 times and then takes about 5
> seconds, and then again fraction of a second for about 1000 inserts. There
> are 3 indexes on the table with fillfactor 70.
> Am i right by guessing that at the time when insert takes 5 seconds the
> page splits? Should i decrese fillfactor to 50? Or could it be something
> else?
> Thanks.
>|||I see that i have a lot of checkpoints by running a trace.
This might be my problem
How do i minimize checkpoints?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>I agree with the others that it is probably not related to the fill factor.
>I would guess blocking first, disk queues next (checkpoints etc.) and maybe
>even file auto growths. Make sure there is plenty of free space in the data
>and log files.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>|||Checkpoints are there for a reason which is to limit recovery times in the
event of a crash. There is a setting called the recovery interval that can
affect when checkpoints happen but that is not the solution. If checkpoints
hinder the activity that much you do not have proper disk configurations to
handle the load. It sounds like you probably have your tran log files on the
same drive array as the data files. To deal with lots of writes it is
imperative you separate the logs from the data files onto different physical
(not just logical) arrays. Also consider adding more write cache and check
the read / write ratio of the disk controller. If it is not 100% write back
then change it and you will most likely see improvements with checkpoints.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Michael Kansky" <mike@.zazasoftware.com> wrote in message
news:uo901MBnIHA.4712@.TK2MSFTNGP04.phx.gbl...
>I see that i have a lot of checkpoints by running a trace.
> This might be my problem
> How do i minimize checkpoints?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u4zPw5AnIHA.4504@.TK2MSFTNGP06.phx.gbl...
>>I agree with the others that it is probably not related to the fill
>>factor. I would guess blocking first, disk queues next (checkpoints etc.)
>>and maybe even file auto growths. Make sure there is plenty of free space
>>in the data and log files.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Michael Kansky" <mike@.zazasoftware.com> wrote in message
>> news:%23zHWWj$mIHA.5704@.TK2MSFTNGP05.phx.gbl...
>>I have a very simple insert statement into a table containing about a
>>million rows.
>> It runs a fraction of a second for about 1000 times and then takes about
>> 5 seconds, and then again fraction of a second for about 1000 inserts.
>> There are 3 indexes on the table with fillfactor 70.
>> Am i right by guessing that at the time when insert takes 5 seconds the
>> page splits? Should i decrese fillfactor to 50? Or could it be something
>> else?
>> Thanks.
>>
>

Fill data

INSERT INTO Table1(Title) VALUES('Somedata1')
INSERT INTO Table1(Title) VALUES('Somedata2')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(1,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,1,100,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,200,'Somedata
')
INSERT INTO Table2(ParentId, Table1Id, Type, Title) VALUES(0,2,100,'Somedata
')
> For each Table1.Id I want to Select Top 1 Table2.Id
> where Table2.Table1Id = Table.Id And Table2.ParentId = 0
> Order By Table2.Type Desc och Table2.Id Desc.
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)

> With these Top 1 Table2.Id:s I finally want to Select all data
> From Table2 Where each of the Id:s are equal to either Table2.Id Or
> Table2.ParentId
SELECT Id, ParentId, Table1Id, Type, Title
FROM Table2
WHERE Id IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)
OR ParentId IN
(
SELECT t2.Id as Table2ID
FROM Table1 t1
INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
AND t2.Id=(SELECT TOP 1 t3.Id
FROM Table2 t3
WHERE t3.Table1Id=t1.Id AND
t3.ParentId=0
ORDER BY t3.Type DESC,Id Desc)
)|||That works great. You made my day. A big thank you for your time and knowled
ge.
/ Senna
"markc600@.hotmail.com" wrote:

>
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
>
> SELECT Id, ParentId, Table1Id, Type, Title
> FROM Table2
> WHERE Id IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
> OR ParentId IN
> (
> SELECT t2.Id as Table2ID
> FROM Table1 t1
> INNER JOIN Table2 t2 ON t2.Table1Id=t1.Id AND t2.ParentId=0
> AND t2.Id=(SELECT TOP 1 t3.Id
> FROM Table2 t3
> WHERE t3.Table1Id=t1.Id AND
> t3.ParentId=0
> ORDER BY t3.Type DESC,Id Desc)
> )
>

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris

Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg asint

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg =(@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg,'@.jpg.jpg',

bulkcolumn fromOpenrowset(Bulk'D:\Data\Pics\@.jpg.jpg', Single_Blob)as'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROMOPENROWSET('MSDASQL','Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;','SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris