multiple instances of MSDE

Hello All,
Hoping someone can tell me how the 5-user governor affects multiple
instances of MSDE.
Does each instance allow unrestricted performance for up to 5 users, or
will the 6th user experience slow-down regardless of which instance db
he/she is using?
Also, with a VS.NET license, does this provide license only one instance of
MSDE, or can you install multiple?
Rein
Hi Rein,
Workload Governor in brief :
Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
MSDE 2000 allow 32,767 connections to an instance of the database engine.
There is no limit for the number of connections that can be executing
operations at the same time. The only effect of the workload governor is
that it starts slowing down the database engine when more than eight
operations are actively running at the same time.
The following link has more information on Workload Governor.
http://msdn.microsoft.com/library/?u.../8_ar_sa2_0ciq
.asp
You can install multiple instance of MSDE, regardless of the number of
VS.NET license you have.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Ashish,
One thing I didn't understand from your response was whether the governor (I
thought it was applied after the fifth concurrent user, not after the 8th
operation) affected all instances of MSDE on a particular machine. If not,
crafty as I can be, I'm inclined to install multiple (up to 16) instances
where each instance will support an 5? ungoverned users (16x5=80 ungoverned
concurrent users!!!) Of course, 16 separate and full-fledged instances will
also probably bog the machine down, but I definately have a solution when my
application slows down due to the governor, add another instance and double
the capacity - I can make up for lost performance from running 2 identical
services by distributed partitioning...
Rein

> Workload Governor in brief :
> Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
> MSDE 2000 allow 32,767 connections to an instance of the database engine.
> There is no limit for the number of connections that can be executing
> operations at the same time. The only effect of the workload governor is
> that it starts slowing down the database engine when more than eight
> operations are actively running at the same time.
...
> You can install multiple instance of MSDE, regardless of the number of
> VS.NET license you have.
> Ashish
|||I thought it was after 5 processes running is when it starts to
slow down.
francisco
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:r9ZqpFBLEHA.3396@.cpmsftngxa10.phx.gbl...
> Hi Rein,
>
> Workload Governor in brief :
> Like all versions of SQL Server 2000, SQL Server 2000 Personal Edition and
> MSDE 2000 allow 32,767 connections to an instance of the database engine.
> There is no limit for the number of connections that can be executing
> operations at the same time. The only effect of the workload governor is
> that it starts slowing down the database engine when more than eight
> operations are actively running at the same time.
>
> The following link has more information on Workload Governor.
>
http://msdn.microsoft.com/library/?u.../8_ar_sa2_0ciq
> asp
>
> You can install multiple instance of MSDE, regardless of the number of
> VS.NET license you have.
>
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
|||hi Francisco,
"Francisco" <nomail@.baseball.net> ha scritto nel messaggio
news:uxN27hGLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> I thought it was after 5 processes running is when it starts to
> slow down.
>
you are quite right, but the reported limit (8) includes some system related
workloads in order to grant a minimum of 5 concurrent users workloads..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Rein,
The Workload governor is affects individual instance of MSDE.
Extract from the link that I had sent in the earlier reply.
================================================== ==========================
==================================
The Microsoft SQL Server 2000 workload governor is designed to limit the
performance of an instance of the database engine any time more than eight
operations are active at the same time. An instance of the SQL Server 2000
database engine is one copy of the database software that operates as an
operating system service.
================================================== ==========================
==================================
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||You could also try the method detailed here:
http://groups.google.com/groups?selm... output=gplain
By changing a single bit in one of the MSDE files you can tell the SQL Server
to allow any number of concurrent operations without a penalty. This is
effectively a hidden switch to turn the workload governor off.

Multiple INSERTs into a single table scaling / performance problem

Hello all ...
This one has me a bit confused. I'd really appreciate any and all
suggestions. This may be pilot error on my part...
My requirement is to have multiple programatic database applications, each
on a separate database connection insert data into a single table as fast as
possible. My hope is that I'll see some scaling of rows-per-second inserted
by adding additional processes, connections and simultaneous inserts.
I'm not able to use BCP for this as the data is coming from an application
style feed.
I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on an
AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
tends to get up around 2.9GB at times ... it is the "only" service on the
box.
Here's my table:
CREATE TABLE MyTable
(myID numeric(10,0) not null,
myChar char(8000) null,
myRowversion rowversion)
ON A_TABLE_FILEGROUP
-- No indexes, just a simple heap. I know that each row will require 8K of
-- storage ... about a page each.
My database transaction log is on a different disk/file group on a separate
controller.
I used SQL Query Analyzer for my test. I opened up a single connection and
ran a simple batch along the lines of:
begin tran
while @.i <= 10000
begin
insert into MyTable ...
values ...
select @.i = @.i + 1
end
commit
OK, when I run the above and check the start and finish times I can insert
about 1200 rows per second. I checked the disks with perf mon and I have
very little Average Disk Write Queues on the table and transaction log disks
(on avg less than 1.)
Sooooo.....
When I open up two additional sessions in SQL Query Analyzer ... and kick
off three of the same loops "at the same time" I average ... a sum total of
about 1200 rows per second. No scaling at all. Note that I am running all
three windows from within the same SQL Query Analyzer session, each with a
different connection into the database.
Again, the disks and the whole box are pretty much snoozing.
Any suggestions I might try? I'm sure I'm missing something here.
Thanks so much!!!!
DBwell...
have you try varchar instead of char?
why a numeric and not an integer?
separated disks is not enough.
What is your disk config?
do you use RAID 5 or Raid 0+1?
how many disks on each controller? (do you use SCSI 15krpm disks?)
have you some write cache on your controllers?
does your files are an good initial size? when SQL server need to expand a
file, this takes some ressources, so if your files are larger then your
requirements you'll improve the performance.
have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
and 60MB/s (SQLIO results):
sqlio -kW -b64 -frandom -LS
with this result I achieve 3333 insert/sec using a script near like you (100
000 rows inserted in 30sec; no transactions)
I insert into a table like yours (int, char(8000), rowversion)
doing all the inserts into 1 transaction reduce the time of the process to
16sec.
and finally adding the set nocount on option, I reduce the process to 7sec
my log & database files are on the same disk
Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> Hello all ...
> This one has me a bit confused. I'd really appreciate any and all
> suggestions. This may be pilot error on my part...
> My requirement is to have multiple programatic database applications, each
> on a separate database connection insert data into a single table as fast
> as
> possible. My hope is that I'll see some scaling of rows-per-second
> inserted
> by adding additional processes, connections and simultaneous inserts.
> I'm not able to use BCP for this as the data is coming from an application
> style feed.
> I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> an
> AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> tends to get up around 2.9GB at times ... it is the "only" service on the
> box.
> Here's my table:
> CREATE TABLE MyTable
> (myID numeric(10,0) not null,
> myChar char(8000) null,
> myRowversion rowversion)
> ON A_TABLE_FILEGROUP
> -- No indexes, just a simple heap. I know that each row will require 8K
> of
> -- storage ... about a page each.
> My database transaction log is on a different disk/file group on a
> separate
> controller.
> I used SQL Query Analyzer for my test. I opened up a single connection
> and
> ran a simple batch along the lines of:
> begin tran
> while @.i <= 10000
> begin
> insert into MyTable ...
> values ...
> select @.i = @.i + 1
> end
> commit
> OK, when I run the above and check the start and finish times I can insert
> about 1200 rows per second. I checked the disks with perf mon and I have
> very little Average Disk Write Queues on the table and transaction log
> disks
> (on avg less than 1.)
> Sooooo.....
> When I open up two additional sessions in SQL Query Analyzer ... and kick
> off three of the same loops "at the same time" I average ... a sum total
> of
> about 1200 rows per second. No scaling at all. Note that I am running
> all
> three windows from within the same SQL Query Analyzer session, each with a
> different connection into the database.
> Again, the disks and the whole box are pretty much snoozing.
> Any suggestions I might try? I'm sure I'm missing something here.
> Thanks so much!!!!
> DB
>|||Thank you very much for your comments ... very useful information and ideas!
Char is the requirement I'm stuck with. I'm working on a "worst case
scenario" so a char(8000) is pretty tough. I know this uses a lot more
storage space than Varchar and yes I'm only storing a single row per page.
It is a long story.
I could switch from a numeric to an int, if that would make a tremendous
scaling difference.
I'm not using any RAID or striped disk setup. The two disks I have are on
two separate controllers. One is a SATA controller and the other is a
Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The data
table is on one and the transaction log is on the other. When I look at the
disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
There's virtually no Average Disk Queue on either one, and the number of
writing I/Os per second is pretty low. Perhaps my controllers are not
pushing the disks fast enough. I'm not sure. Would faster/striped disks
make a difference if Perfmon doesn't show a lot of disk activity? Are there
a couple of other Perfmon settings I should look at? Note that I'm not
having any hard page faults either.
The SQLIO is a good suggestion ... I'll try that as well.
There is a lot of pre-allocated space in both the database and transaction
log files. No problems there.
I also used the NOCOUNT ON and put the work into a single transaction ...
this did double my speed to get to the 1200 per second.
Any more ideas out there? I suppose I am making a BIG assumption that
multiple threads writing into a single table on one disk/file would be
faster. Is this a poor assumption, unless the table is set up across
multiple physical disks/files? Would the relative scaling results be similar
if the rowsize was much smaller, resulting in more rows per disk I/O? If the
disk is overworked, wouldn't the Average Write Disk Queue length be really
high?
I don't see any locking/blocking problems ... perhaps I'm missing something
here.
Thanks so much ...
DB
Doug
"Jeje" wrote:
> well...
> have you try varchar instead of char?
> why a numeric and not an integer?
> separated disks is not enough.
> What is your disk config?
> do you use RAID 5 or Raid 0+1?
> how many disks on each controller? (do you use SCSI 15krpm disks?)
> have you some write cache on your controllers?
> does your files are an good initial size? when SQL server need to expand a
> file, this takes some ressources, so if your files are larger then your
> requirements you'll improve the performance.
> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> and 60MB/s (SQLIO results):
> sqlio -kW -b64 -frandom -LS
> with this result I achieve 3333 insert/sec using a script near like you (100
> 000 rows inserted in 30sec; no transactions)
> I insert into a table like yours (int, char(8000), rowversion)
> doing all the inserts into 1 transaction reduce the time of the process to
> 16sec.
> and finally adding the set nocount on option, I reduce the process to 7sec
> my log & database files are on the same disk
> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> > Hello all ...
> >
> > This one has me a bit confused. I'd really appreciate any and all
> > suggestions. This may be pilot error on my part...
> >
> > My requirement is to have multiple programatic database applications, each
> > on a separate database connection insert data into a single table as fast
> > as
> > possible. My hope is that I'll see some scaling of rows-per-second
> > inserted
> > by adding additional processes, connections and simultaneous inserts.
> >
> > I'm not able to use BCP for this as the data is coming from an application
> > style feed.
> >
> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> > an
> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> > tends to get up around 2.9GB at times ... it is the "only" service on the
> > box.
> >
> > Here's my table:
> >
> > CREATE TABLE MyTable
> >
> > (myID numeric(10,0) not null,
> >
> > myChar char(8000) null,
> >
> > myRowversion rowversion)
> >
> > ON A_TABLE_FILEGROUP
> >
> > -- No indexes, just a simple heap. I know that each row will require 8K
> > of
> >
> > -- storage ... about a page each.
> >
> > My database transaction log is on a different disk/file group on a
> > separate
> > controller.
> >
> > I used SQL Query Analyzer for my test. I opened up a single connection
> > and
> > ran a simple batch along the lines of:
> >
> > begin tran
> >
> > while @.i <= 10000
> >
> > begin
> >
> > insert into MyTable ...
> >
> > values ...
> >
> > select @.i = @.i + 1
> >
> > end
> >
> > commit
> >
> > OK, when I run the above and check the start and finish times I can insert
> > about 1200 rows per second. I checked the disks with perf mon and I have
> > very little Average Disk Write Queues on the table and transaction log
> > disks
> > (on avg less than 1.)
> >
> > Sooooo.....
> >
> > When I open up two additional sessions in SQL Query Analyzer ... and kick
> > off three of the same loops "at the same time" I average ... a sum total
> > of
> > about 1200 rows per second. No scaling at all. Note that I am running
> > all
> > three windows from within the same SQL Query Analyzer session, each with a
> > different connection into the database.
> >
> > Again, the disks and the whole box are pretty much snoozing.
> >
> > Any suggestions I might try? I'm sure I'm missing something here.
> >
> > Thanks so much!!!!
> >
> > DB
> >
>
>|||well... what's appends if the you only use the local drive? (log + data)
the latency of an external drive is not good.
and for the price of a drive, add 3 local drives, and, if you don't care
about crash, put the 4 disks in Raid 0 (bad design, but good performance)
or the 3 new drives in raid 0 for data and the current drive for the log.
your bottleneck is at the disk level for sure.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> Thank you very much for your comments ... very useful information and
> ideas!
> Char is the requirement I'm stuck with. I'm working on a "worst case
> scenario" so a char(8000) is pretty tough. I know this uses a lot more
> storage space than Varchar and yes I'm only storing a single row per page.
> It is a long story.
> I could switch from a numeric to an int, if that would make a tremendous
> scaling difference.
> I'm not using any RAID or striped disk setup. The two disks I have are on
> two separate controllers. One is a SATA controller and the other is a
> Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> data
> table is on one and the transaction log is on the other. When I look at
> the
> disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> There's virtually no Average Disk Queue on either one, and the number of
> writing I/Os per second is pretty low. Perhaps my controllers are not
> pushing the disks fast enough. I'm not sure. Would faster/striped disks
> make a difference if Perfmon doesn't show a lot of disk activity? Are
> there
> a couple of other Perfmon settings I should look at? Note that I'm not
> having any hard page faults either.
> The SQLIO is a good suggestion ... I'll try that as well.
> There is a lot of pre-allocated space in both the database and transaction
> log files. No problems there.
> I also used the NOCOUNT ON and put the work into a single transaction ...
> this did double my speed to get to the 1200 per second.
> Any more ideas out there? I suppose I am making a BIG assumption that
> multiple threads writing into a single table on one disk/file would be
> faster. Is this a poor assumption, unless the table is set up across
> multiple physical disks/files? Would the relative scaling results be
> similar
> if the rowsize was much smaller, resulting in more rows per disk I/O? If
> the
> disk is overworked, wouldn't the Average Write Disk Queue length be really
> high?
> I don't see any locking/blocking problems ... perhaps I'm missing
> something
> here.
> Thanks so much ...
> DB
>
> --
> Doug
>
> "Jeje" wrote:
>> well...
>> have you try varchar instead of char?
>> why a numeric and not an integer?
>> separated disks is not enough.
>> What is your disk config?
>> do you use RAID 5 or Raid 0+1?
>> how many disks on each controller? (do you use SCSI 15krpm disks?)
>> have you some write cache on your controllers?
>> does your files are an good initial size? when SQL server need to expand
>> a
>> file, this takes some ressources, so if your files are larger then your
>> requirements you'll improve the performance.
>> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
>> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
>> and 60MB/s (SQLIO results):
>> sqlio -kW -b64 -frandom -LS
>> with this result I achieve 3333 insert/sec using a script near like you
>> (100
>> 000 rows inserted in 30sec; no transactions)
>> I insert into a table like yours (int, char(8000), rowversion)
>> doing all the inserts into 1 transaction reduce the time of the process
>> to
>> 16sec.
>> and finally adding the set nocount on option, I reduce the process to
>> 7sec
>> my log & database files are on the same disk
>> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
>>
>> "Doug" <Doug@.discussions.microsoft.com> wrote in message
>> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
>> > Hello all ...
>> >
>> > This one has me a bit confused. I'd really appreciate any and all
>> > suggestions. This may be pilot error on my part...
>> >
>> > My requirement is to have multiple programatic database applications,
>> > each
>> > on a separate database connection insert data into a single table as
>> > fast
>> > as
>> > possible. My hope is that I'll see some scaling of rows-per-second
>> > inserted
>> > by adding additional processes, connections and simultaneous inserts.
>> >
>> > I'm not able to use BCP for this as the data is coming from an
>> > application
>> > style feed.
>> >
>> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
>> > on
>> > an
>> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
>> > Server
>> > tends to get up around 2.9GB at times ... it is the "only" service on
>> > the
>> > box.
>> >
>> > Here's my table:
>> >
>> > CREATE TABLE MyTable
>> >
>> > (myID numeric(10,0) not null,
>> >
>> > myChar char(8000) null,
>> >
>> > myRowversion rowversion)
>> >
>> > ON A_TABLE_FILEGROUP
>> >
>> > -- No indexes, just a simple heap. I know that each row will require
>> > 8K
>> > of
>> >
>> > -- storage ... about a page each.
>> >
>> > My database transaction log is on a different disk/file group on a
>> > separate
>> > controller.
>> >
>> > I used SQL Query Analyzer for my test. I opened up a single connection
>> > and
>> > ran a simple batch along the lines of:
>> >
>> > begin tran
>> >
>> > while @.i <= 10000
>> >
>> > begin
>> >
>> > insert into MyTable ...
>> >
>> > values ...
>> >
>> > select @.i = @.i + 1
>> >
>> > end
>> >
>> > commit
>> >
>> > OK, when I run the above and check the start and finish times I can
>> > insert
>> > about 1200 rows per second. I checked the disks with perf mon and I
>> > have
>> > very little Average Disk Write Queues on the table and transaction log
>> > disks
>> > (on avg less than 1.)
>> >
>> > Sooooo.....
>> >
>> > When I open up two additional sessions in SQL Query Analyzer ... and
>> > kick
>> > off three of the same loops "at the same time" I average ... a sum
>> > total
>> > of
>> > about 1200 rows per second. No scaling at all. Note that I am running
>> > all
>> > three windows from within the same SQL Query Analyzer session, each
>> > with a
>> > different connection into the database.
>> >
>> > Again, the disks and the whole box are pretty much snoozing.
>> >
>> > Any suggestions I might try? I'm sure I'm missing something here.
>> >
>> > Thanks so much!!!!
>> >
>> > DB
>> >
>>|||Thank you so much again for helping me.
With a small investment, I can set up a configuration with two internal SATA
disk drives, both plugged into the computer motherboard. I can place the
table on one, and the transaction log on the other. For this application I
don't have to worry about a disk crash/recovery via RAID.
With a much larger investment (that I can make it this is the real
bottleneck) I suppose I could pick up some type of RAID array. If I
understand you, you're saying a RAID array with the table and transaction log
striped over several disks would run faster?
Thanks again!
DB
--
Doug
"Jeje" wrote:
> well... what's appends if the you only use the local drive? (log + data)
> the latency of an external drive is not good.
> and for the price of a drive, add 3 local drives, and, if you don't care
> about crash, put the 4 disks in Raid 0 (bad design, but good performance)
> or the 3 new drives in raid 0 for data and the current drive for the log.
> your bottleneck is at the disk level for sure.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> > Thank you very much for your comments ... very useful information and
> > ideas!
> >
> > Char is the requirement I'm stuck with. I'm working on a "worst case
> > scenario" so a char(8000) is pretty tough. I know this uses a lot more
> > storage space than Varchar and yes I'm only storing a single row per page.
> > It is a long story.
> >
> > I could switch from a numeric to an int, if that would make a tremendous
> > scaling difference.
> >
> > I'm not using any RAID or striped disk setup. The two disks I have are on
> > two separate controllers. One is a SATA controller and the other is a
> > Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> > data
> > table is on one and the transaction log is on the other. When I look at
> > the
> > disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> > There's virtually no Average Disk Queue on either one, and the number of
> > writing I/Os per second is pretty low. Perhaps my controllers are not
> > pushing the disks fast enough. I'm not sure. Would faster/striped disks
> > make a difference if Perfmon doesn't show a lot of disk activity? Are
> > there
> > a couple of other Perfmon settings I should look at? Note that I'm not
> > having any hard page faults either.
> >
> > The SQLIO is a good suggestion ... I'll try that as well.
> >
> > There is a lot of pre-allocated space in both the database and transaction
> > log files. No problems there.
> >
> > I also used the NOCOUNT ON and put the work into a single transaction ...
> > this did double my speed to get to the 1200 per second.
> >
> > Any more ideas out there? I suppose I am making a BIG assumption that
> > multiple threads writing into a single table on one disk/file would be
> > faster. Is this a poor assumption, unless the table is set up across
> > multiple physical disks/files? Would the relative scaling results be
> > similar
> > if the rowsize was much smaller, resulting in more rows per disk I/O? If
> > the
> > disk is overworked, wouldn't the Average Write Disk Queue length be really
> > high?
> >
> > I don't see any locking/blocking problems ... perhaps I'm missing
> > something
> > here.
> >
> > Thanks so much ...
> >
> > DB
> >
> >
> >
> > --
> > Doug
> >
> >
> > "Jeje" wrote:
> >
> >> well...
> >>
> >> have you try varchar instead of char?
> >> why a numeric and not an integer?
> >>
> >> separated disks is not enough.
> >> What is your disk config?
> >> do you use RAID 5 or Raid 0+1?
> >> how many disks on each controller? (do you use SCSI 15krpm disks?)
> >> have you some write cache on your controllers?
> >>
> >> does your files are an good initial size? when SQL server need to expand
> >> a
> >> file, this takes some ressources, so if your files are larger then your
> >> requirements you'll improve the performance.
> >>
> >> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> >> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> >> and 60MB/s (SQLIO results):
> >> sqlio -kW -b64 -frandom -LS
> >>
> >> with this result I achieve 3333 insert/sec using a script near like you
> >> (100
> >> 000 rows inserted in 30sec; no transactions)
> >> I insert into a table like yours (int, char(8000), rowversion)
> >> doing all the inserts into 1 transaction reduce the time of the process
> >> to
> >> 16sec.
> >> and finally adding the set nocount on option, I reduce the process to
> >> 7sec
> >> my log & database files are on the same disk
> >> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
> >>
> >>
> >>
> >> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> >> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> >> > Hello all ...
> >> >
> >> > This one has me a bit confused. I'd really appreciate any and all
> >> > suggestions. This may be pilot error on my part...
> >> >
> >> > My requirement is to have multiple programatic database applications,
> >> > each
> >> > on a separate database connection insert data into a single table as
> >> > fast
> >> > as
> >> > possible. My hope is that I'll see some scaling of rows-per-second
> >> > inserted
> >> > by adding additional processes, connections and simultaneous inserts.
> >> >
> >> > I'm not able to use BCP for this as the data is coming from an
> >> > application
> >> > style feed.
> >> >
> >> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
> >> > on
> >> > an
> >> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
> >> > Server
> >> > tends to get up around 2.9GB at times ... it is the "only" service on
> >> > the
> >> > box.
> >> >
> >> > Here's my table:
> >> >
> >> > CREATE TABLE MyTable
> >> >
> >> > (myID numeric(10,0) not null,
> >> >
> >> > myChar char(8000) null,
> >> >
> >> > myRowversion rowversion)
> >> >
> >> > ON A_TABLE_FILEGROUP
> >> >
> >> > -- No indexes, just a simple heap. I know that each row will require
> >> > 8K
> >> > of
> >> >
> >> > -- storage ... about a page each.
> >> >
> >> > My database transaction log is on a different disk/file group on a
> >> > separate
> >> > controller.
> >> >
> >> > I used SQL Query Analyzer for my test. I opened up a single connection
> >> > and
> >> > ran a simple batch along the lines of:
> >> >
> >> > begin tran
> >> >
> >> > while @.i <= 10000
> >> >
> >> > begin
> >> >
> >> > insert into MyTable ...
> >> >
> >> > values ...
> >> >
> >> > select @.i = @.i + 1
> >> >
> >> > end
> >> >
> >> > commit
> >> >
> >> > OK, when I run the above and check the start and finish times I can
> >> > insert
> >> > about 1200 rows per second. I checked the disks with perf mon and I
> >> > have
> >> > very little Average Disk Write Queues on the table and transaction log
> >> > disks
> >> > (on avg less than 1.)
> >> >
> >> > Sooooo.....
> >> >
> >> > When I open up two additional sessions in SQL Query Analyzer ... and
> >> > kick
> >> > off three of the same loops "at the same time" I average ... a sum
> >> > total
> >> > of
> >> > about 1200 rows per second. No scaling at all. Note that I am running
> >> > all
> >> > three windows from within the same SQL Query Analyzer session, each
> >> > with a
> >> > different connection into the database.
> >> >
> >> > Again, the disks and the whole box are pretty much snoozing.
> >> >
> >> > Any suggestions I might try? I'm sure I'm missing something here.
> >> >
> >> > Thanks so much!!!!
> >> >
> >> > DB
> >> >
> >>
> >>
> >>
>
>|||--
Doug
"Jeje" wrote:
> well... what's appends if the you only use the local drive? (log + data)
> the latency of an external drive is not good.
> and for the price of a drive, add 3 local drives, and, if you don't care
> about crash, put the 4 disks in Raid 0 (bad design, but good performance)
> or the 3 new drives in raid 0 for data and the current drive for the log.
> your bottleneck is at the disk level for sure.
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:055220B6-7A4B-4CF9-976A-71FEA412AECD@.microsoft.com...
> > Thank you very much for your comments ... very useful information and
> > ideas!
> >
> > Char is the requirement I'm stuck with. I'm working on a "worst case
> > scenario" so a char(8000) is pretty tough. I know this uses a lot more
> > storage space than Varchar and yes I'm only storing a single row per page.
> > It is a long story.
> >
> > I could switch from a numeric to an int, if that would make a tremendous
> > scaling difference.
> >
> > I'm not using any RAID or striped disk setup. The two disks I have are on
> > two separate controllers. One is a SATA controller and the other is a
> > Firewire 2.0 controller. Both disks have 16 MB of cache turned on. The
> > data
> > table is on one and the transaction log is on the other. When I look at
> > the
> > disk IO stats using Perfmon I don't see a lot of heavy disk IO activity.
> > There's virtually no Average Disk Queue on either one, and the number of
> > writing I/Os per second is pretty low. Perhaps my controllers are not
> > pushing the disks fast enough. I'm not sure. Would faster/striped disks
> > make a difference if Perfmon doesn't show a lot of disk activity? Are
> > there
> > a couple of other Perfmon settings I should look at? Note that I'm not
> > having any hard page faults either.
> >
> > The SQLIO is a good suggestion ... I'll try that as well.
> >
> > There is a lot of pre-allocated space in both the database and transaction
> > log files. No problems there.
> >
> > I also used the NOCOUNT ON and put the work into a single transaction ...
> > this did double my speed to get to the 1200 per second.
> >
> > Any more ideas out there? I suppose I am making a BIG assumption that
> > multiple threads writing into a single table on one disk/file would be
> > faster. Is this a poor assumption, unless the table is set up across
> > multiple physical disks/files? Would the relative scaling results be
> > similar
> > if the rowsize was much smaller, resulting in more rows per disk I/O? If
> > the
> > disk is overworked, wouldn't the Average Write Disk Queue length be really
> > high?
> >
> > I don't see any locking/blocking problems ... perhaps I'm missing
> > something
> > here.
> >
> > Thanks so much ...
> >
> > DB
> >
> >
> >
> > --
> > Doug
> >
> >
> > "Jeje" wrote:
> >
> >> well...
> >>
> >> have you try varchar instead of char?
> >> why a numeric and not an integer?
> >>
> >> separated disks is not enough.
> >> What is your disk config?
> >> do you use RAID 5 or Raid 0+1?
> >> how many disks on each controller? (do you use SCSI 15krpm disks?)
> >> have you some write cache on your controllers?
> >>
> >> does your files are an good initial size? when SQL server need to expand
> >> a
> >> file, this takes some ressources, so if your files are larger then your
> >> requirements you'll improve the performance.
> >>
> >> have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
> >> Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
> >> and 60MB/s (SQLIO results):
> >> sqlio -kW -b64 -frandom -LS
> >>
> >> with this result I achieve 3333 insert/sec using a script near like you
> >> (100
> >> 000 rows inserted in 30sec; no transactions)
> >> I insert into a table like yours (int, char(8000), rowversion)
> >> doing all the inserts into 1 transaction reduce the time of the process
> >> to
> >> 16sec.
> >> and finally adding the set nocount on option, I reduce the process to
> >> 7sec
> >> my log & database files are on the same disk
> >> Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
> >>
> >>
> >>
> >> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> >> news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> >> > Hello all ...
> >> >
> >> > This one has me a bit confused. I'd really appreciate any and all
> >> > suggestions. This may be pilot error on my part...
> >> >
> >> > My requirement is to have multiple programatic database applications,
> >> > each
> >> > on a separate database connection insert data into a single table as
> >> > fast
> >> > as
> >> > possible. My hope is that I'll see some scaling of rows-per-second
> >> > inserted
> >> > by adding additional processes, connections and simultaneous inserts.
> >> >
> >> > I'm not able to use BCP for this as the data is coming from an
> >> > application
> >> > style feed.
> >> >
> >> > I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running
> >> > on
> >> > an
> >> > AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL
> >> > Server
> >> > tends to get up around 2.9GB at times ... it is the "only" service on
> >> > the
> >> > box.
> >> >
> >> > Here's my table:
> >> >
> >> > CREATE TABLE MyTable
> >> >
> >> > (myID numeric(10,0) not null,
> >> >
> >> > myChar char(8000) null,
> >> >
> >> > myRowversion rowversion)
> >> >
> >> > ON A_TABLE_FILEGROUP
> >> >
> >> > -- No indexes, just a simple heap. I know that each row will require
> >> > 8K
> >> > of
> >> >
> >> > -- storage ... about a page each.
> >> >
> >> > My database transaction log is on a different disk/file group on a
> >> > separate
> >> > controller.
> >> >
> >> > I used SQL Query Analyzer for my test. I opened up a single connection
> >> > and
> >> > ran a simple batch along the lines of:
> >> >
> >> > begin tran
> >> >
> >> > while @.i <= 10000
> >> >
> >> > begin
> >> >
> >> > insert into MyTable ...
> >> >
> >> > values ...
> >> >
> >> > select @.i = @.i + 1
> >> >
> >> > end
> >> >
> >> > commit
> >> >
> >> > OK, when I run the above and check the start and finish times I can
> >> > insert
> >> > about 1200 rows per second. I checked the disks with perf mon and I
> >> > have
> >> > very little Average Disk Write Queues on the table and transaction log
> >> > disks
> >> > (on avg less than 1.)
> >> >
> >> > Sooooo.....
> >> >
> >> > When I open up two additional sessions in SQL Query Analyzer ... and
> >> > kick
> >> > off three of the same loops "at the same time" I average ... a sum
> >> > total
> >> > of
> >> > about 1200 rows per second. No scaling at all. Note that I am running
> >> > all
> >> > three windows from within the same SQL Query Analyzer session, each
> >> > with a
> >> > different connection into the database.
> >> >
> >> > Again, the disks and the whole box are pretty much snoozing.
> >> >
> >> > Any suggestions I might try? I'm sure I'm missing something here.
> >> >
> >> > Thanks so much!!!!
> >> >
> >> > DB
> >> >
> >>
> >>
> >>
>
>

Multiple INSERTs into a single table scaling / performance problem

Hello all ...
This one has me a bit confused. I'd really appreciate any and all
suggestions. This may be pilot error on my part...
My requirement is to have multiple programatic database applications, each
on a separate database connection insert data into a single table as fast as
possible. My hope is that I'll see some scaling of rows-per-second inserted
by adding additional processes, connections and simultaneous inserts.
I'm not able to use BCP for this as the data is coming from an application
style feed.
I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on an
AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
tends to get up around 2.9GB at times ... it is the "only" service on the
box.
Here's my table:
CREATE TABLE MyTable
(myID numeric(10,0) not null,
myChar char(8000) null,
myRowversion rowversion)
ON A_TABLE_FILEGROUP
-- No indexes, just a simple heap. I know that each row will require 8K of
-- storage ... about a page each.
My database transaction log is on a different disk/file group on a separate
controller.
I used SQL Query Analyzer for my test. I opened up a single connection and
ran a simple batch along the lines of:
begin tran
while @.i <= 10000
begin
insert into MyTable ...
values ...
select @.i = @.i + 1
end
commit
OK, when I run the above and check the start and finish times I can insert
about 1200 rows per second. I checked the disks with perf mon and I have
very little Average Disk Write Queues on the table and transaction log disks
(on avg less than 1.)
Sooooo.....
When I open up two additional sessions in SQL Query Analyzer ... and kick
off three of the same loops "at the same time" I average ... a sum total of
about 1200 rows per second. No scaling at all. Note that I am running all
three windows from within the same SQL Query Analyzer session, each with a
different connection into the database.
Again, the disks and the whole box are pretty much snoozing.
Any suggestions I might try? I'm sure I'm missing something here.
Thanks so much!!!!
DBwell...
have you try varchar instead of char?
why a numeric and not an integer?
separated disks is not enough.
What is your disk config?
do you use RAID 5 or Raid 0+1?
how many disks on each controller? (do you use SCSI 15krpm disks?)
have you some write cache on your controllers?
does your files are an good initial size? when SQL server need to expand a
file, this takes some ressources, so if your files are larger then your
requirements you'll improve the performance.
have you tested you disks subsystem using the SQLIOStress & SQLIO tools?
Using 4 local IDE drives (SATA; no cache; Raid 0 (strip)) I reach 960io/s
and 60MB/s (SQLIO results):
sqlio -kW -b64 -frandom -LS
with this result I achieve 3333 insert/sec using a script near like you (100
000 rows inserted in 30sec; no transactions)
I insert into a table like yours (int, char(8000), rowversion)
doing all the inserts into 1 transaction reduce the time of the process to
16sec.
and finally adding the set nocount on option, I reduce the process to 7sec
my log & database files are on the same disk
Its an Opteron dual core server with 4Gb and SQL 2005 x64 version.
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:50F8E5B3-2B49-4572-B2AC-89A1E77A55CC@.microsoft.com...
> Hello all ...
> This one has me a bit confused. I'd really appreciate any and all
> suggestions. This may be pilot error on my part...
> My requirement is to have multiple programatic database applications, each
> on a separate database connection insert data into a single table as fast
> as
> possible. My hope is that I'll see some scaling of rows-per-second
> inserted
> by adding additional processes, connections and simultaneous inserts.
> I'm not able to use BCP for this as the data is coming from an application
> style feed.
> I'm using Windows 2003 Server R2 and SQL Server 2000 SP4. I'm running on
> an
> AMD 64 X2 Dual Core Processer (fast box) with 4 GB of memory. SQL Server
> tends to get up around 2.9GB at times ... it is the "only" service on the
> box.
> Here's my table:
> CREATE TABLE MyTable
> (myID numeric(10,0) not null,
> myChar char(8000) null,
> myRowversion rowversion)
> ON A_TABLE_FILEGROUP
> -- No indexes, just a simple heap. I know that each row will require 8K
> of
> -- storage ... about a page each.
> My database transaction log is on a different disk/file group on a
> separate
> controller.
> I used SQL Query Analyzer for my test. I opened up a single connection
> and
> ran a simple batch along the lines of:
> begin tran
> while @.i <= 10000
> begin
> insert into MyTable ...
> values ...
> select @.i = @.i + 1
> end
> commit
> OK, when I run the above and check the start and finish times I can insert
> about 1200 rows per second. I checked the disks with perf mon and I have
> very little Average Disk Write Queues on the table and transaction log
> disks
> (on avg less than 1.)
> Sooooo.....
> When I open up two additional sessions in SQL Query Analyzer ... and kick
> off three of the same loops "at the same time" I average ... a sum total
> of
> about 1200 rows per second. No scaling at all. Note that I am running
> all
> three windows from within the same SQL Query Analyzer session, each with a
> different connection into the database.
> Again, the disks and the whole box are pretty much snoozing.
> Any suggestions I might try? I'm sure I'm missing something here.
> Thanks so much!!!!
> DB
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com

multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jeromeHello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegroups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>

multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jerome
Hello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegr oups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
sql

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
...this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUK
Depands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
..this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroup
s
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups.
.
..this would mean we would have 30+ filegroups per array and I'm worried th
is
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

multiple database performance

I'm planning on creating multiple databases on a client because I'm storing a large amount of binary data (jpg's and pdf's) in tables and there are size limitations for a single database. So, in order to get around the size limitations, I'm just creating one database for my main tables and multiple other databases to hold the binary data.

Any performance considerations I should consider in regards to multiple databases? Any other thoughts?

Hi,

what about not storing the data in the database ? Do a quick one on this document, it has some detailed information for your design:

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||First, what size limit in SQL 2005 are you trying to avoid?

Second, as someone else pointed out, saving files in the database is not a good design. The main problem is, what do you do with them once they are there? How do you get them out? The OS is much better at saving files efficiently than SQL. Creating an "index" database into the files is a much better method.

To answer your question, it depends on your design and situation. What are you going to do with the data, how do you query the databases, etc. There is very little impact in selecting data from tables in different database on the same sever.|||I think I may have posted this in the wrong group (I am actually going to use SQL Server CE), but the 2 responses were extremely helpful. Thanks!

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple database files on the same disk

Hi there

It is obvious that putting multiple database files on different physical disk is better for performance, but what about splitting the data on different files on the same disk?

I have got a database of about 20GB and only a single data file. will I benefit from splitting this file to multiple files on the same disk?

The answer is not particularly!

What you may benefit from is utilising multiple filegroups, rather than multiple files in a single filegroup. This will allow you to physically place different database tables and indexes into different physical files, which will reduce fragmentation. It is usually also a good idea to create a Data filegroup and make that file the DEFAULT filegroup for new object creation. In this way, your data is stored in a physically separate file to the internal sql server database tables which will still exist in the PRIMARY filegroup.

Hope this helps

BigE

|||

Thanks BigE.

I understand that, but what if I do not have an extra disk, will I bebefit from splitting the big file into multilple files or it does not make any difference if I leave it on a single physical file/file group? will it ease SQL Server's IO work to deal with few files (all on the same disk) rather than with one big file ?

|||

Hi Aviel,

If your database is very huge-large and very active (busy), multiple files can be used to increase performance.

If the table is included in a single data file, SQL server would use only 1 thread to achieve a read of the rows in it.

But if the table were separated into 2 physical files, SQL server would use 2 threads to read it, which potentially could be faster.

In Addition, if each file were on its own disk array or physical disk, the performance gain would even be greater.

Regards,

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

|||

If you've only got one physical disk, multiple IO threads will slow things down as you'll be forcing far less efficient random IO instead of a single sequential read which is much faster.

If IO's your bottleneck, upgrade the IO subsystem!

|||

Thanks Tarek and BigE.

Now I am in kind of a dilemma. Do multiple IO threads for one physical file is better or worse. Any way, I think that IO is not my biggest problem, but I try to make things better and elegant, and having one hugr file of about 20GB seem a bit inelegant.

|||Don't worry about SQL Server finding the positions that it wants within a large file: Infact, the structure of the SQL Server files is much more suited to finding data quickly than the structure of the NTFS file system!|||

Hi Tarek,

Is it possible to have one table data in 2 different files? I did know that.

|||

Hi Roman,

Table partitioning is a powerful new feature in SQL Server 2005, You can implement a horizontal data partition where you can split your data (records) in a specific table in different filegroups - datafiles, for more details check the following URL:
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

Regards

Tarek Ghazali

SQL Server MVP

Web Site: http://www.sqlmvp.com

Multiple Data Files and paralell IO - yes or no

I've looked at many of the threads in this group that discuss whether
or not multiple data files in a filegroup will improve IO performance.
Some say that the BOL is wrong and that you don't need additional
files to have SQL server use parallel IO (starting with SQL 2K).
Others say that it still helps even in SQL 2K. I'm planning on doing
some benchmarking, but thought I'd throw it out there one more time.
What is Microsoft's stance on this? Should we or should we not use
multiple files in a filegroup. If we should, do we use the rule of
thumb of roughly 1 file per spindle in the RAID?
Thanks!
-Peter"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
>
Multiple files per filegroup and RAID can each be used to achieve parallel
IO.
If you have say, 10 disks in 5 2-disk raid mirrors, you might let a
filegroup span 2 of the mirror sets to to improve IO. But just as easily,
you could join the 2 mirror sets into a single stripe set and a single
logical volume. Then you can just use a single file to get parallel IO.
Makes your head hurt.
Since you can usually achieve the same thing with RAID alone that you could
achieve with RAID+Multiple files per filegroup, I would use RAID to alter
the IO performance profile, and always use a single file per filegroup.
And for OLTP applications just get as many disks as you can and SAME (Stripe
and Mirror Everything.
If you have
1 no raid
2 mirror
4 2 mirror sets
6 1 striped mirror set, one mirror set
8 2 striped mirror sets
...
David
David|||Peter,
Microsoft's stance on this is that there are too many variables to give a st
ock answer that will work for all hardware configurations. In my experience
using differeing hardware, I have got the best performance and ease of admin
istration by having one fil
e per database. However, you will need to do your own benchmarking on your o
wn hardware to be confident that the decision you make is right for your env
ironment.
Unless your databases are hundreds of gigabytes, I would suspect that one fi
le per database will be all you need.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||The last answer I got, from someone working for Microsoft Consulting
Services, was that there was no benefit to multiple files on the same disk
volume. However if you have multiple volumes you MAY see improvements.
It depends on the IO characteristics of your disk volumes. Example, if you
had 20 spindles would it be better to create one large RAID volume or >1
smaller volumes if they're all on the same controller? Additional testing
and/or input from the hardware vendor would be needed. If you have multiple
controllers/storage systems, then spreading multiple files across the
controllers should give a benefit. Whether you need to do this would depend
on your app.
HTH,
Mike Kruchten
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
> Thanks!
> -Peter|||Sounds like the answer is no - mutiple data files in a single file
group on one RAID array will not give an IO perf benefit. I'm still
going to do some benchmarking, but thanks very much for your input.
I'd still love to here MS's official statement on this.
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:<OePgmzmFEHA.2768@.tk2msf
tngp13.phx.gbl>...
> The last answer I got, from someone working for Microsoft Consulting
> Services, was that there was no benefit to multiple files on the same disk
> volume. However if you have multiple volumes you MAY see improvements.
> It depends on the IO characteristics of your disk volumes. Example, if you
> had 20 spindles would it be better to create one large RAID volume or >1
> smaller volumes if they're all on the same controller? Additional testing
> and/or input from the hardware vendor would be needed. If you have multipl
e
> controllers/storage systems, then spreading multiple files across the
> controllers should give a benefit. Whether you need to do this would depen
d
> on your app.
> HTH,
> Mike Kruchten
>
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0403291246.190893b6@.posting.google.com...|||This has been debated extensively in the private MVP group, with MS develope
rs as participants in the
discussion. I don't think you will find an official answer from MS, and if y
ou do, it will probably be very
close to what Mark posted.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403301510.5e0602a5@.posting.google.com...
> Sounds like the answer is no - mutiple data files in a single file
> group on one RAID array will not give an IO perf benefit. I'm still
> going to do some benchmarking, but thanks very much for your input.
> I'd still love to here MS's official statement on this.
>

multiple calls to SP

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

Call it one time instead of 180 times =;o)

But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.

Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?

/Kenneth

|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)

This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)

SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType


IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1

CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)

SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval


This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS

SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)

--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END

SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval

|||

SDerix wrote:

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)

On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.

It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.

Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?

Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?

spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)

In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....

So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?

/Kenneth

|||

I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.

Did you try creating indexes on the global temp tables?