OK.
lets say i have a 100gb db. I really just want to replicate 1 table
within that db that is 65gb at like 100,000,000 rows(but who cares, we
will use the entire db for this example). This is a production db that
is constantly being updated. How would I be able to do something like a
no sync or something of that nature on a db that is constantly being
updated?
Wouldn't I need to shut off access of anything entering that db from the
point I started the backup to the point the subscriber does the restore,
and I can then setup the publication with "nosync"?? I guess I could
setup no sync and then manually dts the missing 1 day of information
over some other way and then we can manually sync items from there?
The other option i was thinking about was starting the 100gb snapshot,
disabling the distribution agent from starting to sync. Copy those
..bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
the mean time let the log file for the db grow like crazy since their
are articles marked for replication that cannot be delivered because the
agent is disabled. Then once they do a restore enable the distribution
agent and tell it to skip looking for the snapshot that was once there
and just deliver the transactions that are waiting in the log? Does
that make sense.
The short of this, Is that I am trying to figure out how to replicate a
100gb db without shutting off access to it. This db is updated 24/7.
tia, and sorry to be so long winded
-comb
Ideally you will kick off all of your users and backup and restore, bcp the
data out, or use DTS.
What I do is create a no-sync subscription at lets say midnight. Then I get
my data over, usually using bcp (if you can use snapshot isolation within
SQL 2005), and the deploy it on my subscriber. Then I start up my
distribution agent with the allow data consistency errors and I try to fix
the consistency issues.
Its very difficult but it seems to be the best option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1e3843bad612d2a99896e4@.news.newsreader.co m...
> OK.
> lets say i have a 100gb db. I really just want to replicate 1 table
> within that db that is 65gb at like 100,000,000 rows(but who cares, we
> will use the entire db for this example). This is a production db that
> is constantly being updated. How would I be able to do something like a
> no sync or something of that nature on a db that is constantly being
> updated?
> Wouldn't I need to shut off access of anything entering that db from the
> point I started the backup to the point the subscriber does the restore,
> and I can then setup the publication with "nosync"?? I guess I could
> setup no sync and then manually dts the missing 1 day of information
> over some other way and then we can manually sync items from there?
> The other option i was thinking about was starting the 100gb snapshot,
> disabling the distribution agent from starting to sync. Copy those
> .bcp,.sch,.idx files to tape. Over night the tape to the subscriber, in
> the mean time let the log file for the db grow like crazy since their
> are articles marked for replication that cannot be delivered because the
> agent is disabled. Then once they do a restore enable the distribution
> agent and tell it to skip looking for the snapshot that was once there
> and just deliver the transactions that are waiting in the log? Does
> that make sense.
> The short of this, Is that I am trying to figure out how to replicate a
> 100gb db without shutting off access to it. This db is updated 24/7.
> tia, and sorry to be so long winded
> -comb
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Question about Primary key
Hi there
Ok here is my problem.
Lets say i have 20 rows in my table and i delete 19 and add 1.
The primary key's will be 1 and 21, my question is how do i get it to say 1 and 2 ?
looks like your table primary key is an identity column.
Why do you want to do this ? What if you delete a column and add another again ? Do you want to keep repeating this ?
DBCC
CHECKIDENT(tableName, RESEED, new_identity_value)Make sure to set the new identity value to the max of the current identity fields. i.e. run something with logic like:
var maxIdentity = SELECT MAX(ID) FROM tableName
DBCC CHECKIDENT(tableName, RESEED, maxIdentity)
If your last identity was 1 after the delete, the next row entered will be 2.
question about primary key
how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison
question about primary key
how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibisonsql
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibisonsql
question about primary key
how can I set my primary key to star again from 1? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible?
thanks!
Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible?
thanks!
Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison
question about performance of partition tables
1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the Windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
> news:119d201c3f56c$fba82640$a001280a@.phx
.gbl...
time
speedup
covering
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx
.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the Windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
>
> be overwhelmed byt
> simultaneously the less
> them selves. At
> factor. You also
> together and have more
> message
> time
> speedup
> covering
> answer.
question about performance of partition tables
1. When I partition a table ( 90 million rows ) into
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql
five partitions. (create view as select * from table1
union select * from table 2 union ...)
I get a 2 x times query time increase, but by the time
#partitions = 10, the query time is same as the large
table. when #partitions = 15 query time > large table.
Ideally I would want to partition it into 50 states.
Why is it that parallel query execution does not speedup
the process.
The indexes are virtually the same ( most cases covering
non-clustered indexes.)
Is it because of merging the results and sorting them
( because of the group by/order by clauses )? however
the query plan says those are 0%.
You can ask me any question about the query statement,
table format and query plan execution.
I thought about this for a week and don't have an answer.There will always come a point where the resources will be overwhelmed byt
he requests. The more queries you attempt to do simultaneously the less
chances are they will each perform as well as running by them selves. At
some point the cpu or disk queues will start playing a factor. You also
have more overhead when trying to put all the results together and have more
chance of tempdb being a factor.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh" <anonymous@.discussions.microsoft.com> wrote in message
news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> 1. When I partition a table ( 90 million rows ) into
> five partitions. (create view as select * from table1
> union select * from table 2 union ...)
> I get a 2 x times query time increase, but by the time
> #partitions = 10, the query time is same as the large
> table. when #partitions = 15 query time > large table.
> Ideally I would want to partition it into 50 states.
> Why is it that parallel query execution does not speedup
> the process.
> The indexes are virtually the same ( most cases covering
> non-clustered indexes.)
> Is it because of merging the results and sorting them
> ( because of the group by/order by clauses )? however
> the query plan says those are 0%.
> You can ask me any question about the query statement,
> table format and query plan execution.
> I thought about this for a week and don't have an answer.|||cpu utilization seems low : < 40 %
disk I/O queues < 15 on RAID-5 containing tables
(6 disks) and < 3 on RAID-10 containing indexes
(6 disks)
tempdb and log on seperate RAID-10 ( disk queues < 2)
(4 disks)
It is true that queries which only use indexes (covering)
work well upto 10 partitions
queries which would need to use leaf keys and then use
clustering keys work well upto 5 partitions.
Is there something I should know on creating the
clustered/non-clustered indexes different on the
partitioned tables?
Is there some material I can read on?
Is the windows 2000 server be part of the bottleneck?
Is there any specific parameters I should target and
resolve. ( I am aiming for 60 partitions : each with
1-2 million rows each to reduce skewness). I thought this
would speed up the queries 2 orders of magnitude. Sadly
this does not work the way I calculated.
>--Original Message--
>There will always come a point where the resources will
be overwhelmed byt
>he requests. The more queries you attempt to do
simultaneously the less
>chances are they will each perform as well as running by
them selves. At
>some point the cpu or disk queues will start playing a
factor. You also
>have more overhead when trying to put all the results
together and have more
>chance of tempdb being a factor.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
message
>news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
>> 1. When I partition a table ( 90 million rows ) into
>> five partitions. (create view as select * from table1
>> union select * from table 2 union ...)
>> I get a 2 x times query time increase, but by the
time
>> #partitions = 10, the query time is same as the large
>> table. when #partitions = 15 query time > large table.
>> Ideally I would want to partition it into 50 states.
>> Why is it that parallel query execution does not
speedup
>> the process.
>> The indexes are virtually the same ( most cases
covering
>> non-clustered indexes.)
>> Is it because of merging the results and sorting them
>> ( because of the group by/order by clauses )? however
>> the query plan says those are 0%.
>> You can ask me any question about the query statement,
>> table format and query plan execution.
>> I thought about this for a week and don't have an
answer.
>
>.
>|||Disk queues of 15 or so on a 6 disk Raid 5 are still high enough to warrant
paying attention to them. That means you are in fact waiting on disk I/O.
You might get better results from combining all 12 disks from the indexes
and data into one Raid 10. As for the partitioning that is tough to say.
Most partitioning requires a lot of testing under your specific conditions
to determine which is best for you. Are you sure you need to partition that
table at all? 50 partitions is a lot and you only have 90 million rows.
What are the typical queries like against this table? Maybe just adjusting
the clustered index will do.
--
Andrew J. Kelly
SQL Server MVP
"Ramesh Krishnan" <anonymous@.discussions.microsoft.com> wrote in message
news:11d6201c3f62b$ea334100$a301280a@.phx.gbl...
> cpu utilization seems low : < 40 %
> disk I/O queues < 15 on RAID-5 containing tables
> (6 disks) and < 3 on RAID-10 containing indexes
> (6 disks)
> tempdb and log on seperate RAID-10 ( disk queues < 2)
> (4 disks)
> It is true that queries which only use indexes (covering)
> work well upto 10 partitions
> queries which would need to use leaf keys and then use
> clustering keys work well upto 5 partitions.
> Is there something I should know on creating the
> clustered/non-clustered indexes different on the
> partitioned tables?
> Is there some material I can read on?
> Is the windows 2000 server be part of the bottleneck?
> Is there any specific parameters I should target and
> resolve. ( I am aiming for 60 partitions : each with
> 1-2 million rows each to reduce skewness). I thought this
> would speed up the queries 2 orders of magnitude. Sadly
> this does not work the way I calculated.
> >--Original Message--
> >There will always come a point where the resources will
> be overwhelmed byt
> >he requests. The more queries you attempt to do
> simultaneously the less
> >chances are they will each perform as well as running by
> them selves. At
> >some point the cpu or disk queues will start playing a
> factor. You also
> >have more overhead when trying to put all the results
> together and have more
> >chance of tempdb being a factor.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ramesh" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:119d201c3f56c$fba82640$a001280a@.phx.gbl...
> >> 1. When I partition a table ( 90 million rows ) into
> >> five partitions. (create view as select * from table1
> >> union select * from table 2 union ...)
> >> I get a 2 x times query time increase, but by the
> time
> >> #partitions = 10, the query time is same as the large
> >> table. when #partitions = 15 query time > large table.
> >>
> >> Ideally I would want to partition it into 50 states.
> >> Why is it that parallel query execution does not
> speedup
> >> the process.
> >>
> >> The indexes are virtually the same ( most cases
> covering
> >> non-clustered indexes.)
> >> Is it because of merging the results and sorting them
> >> ( because of the group by/order by clauses )? however
> >> the query plan says those are 0%.
> >>
> >> You can ask me any question about the query statement,
> >> table format and query plan execution.
> >>
> >> I thought about this for a week and don't have an
> answer.
> >
> >
> >.
> >sql
Question about performance
I'm using a tableadapter for my update operations.
The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.
This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?
Thanks in advance.
How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||
The cpu isn't fully being utilzed on the sql server machine.
Here is the exact process:
There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.
Example schema:
Table1 ( ID1 (identity, pk), 3 misc rows)
Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)
Table 3 ( ID2 (ForigneKey) )
So, Table1 is parent to Table2 and Table3 is parent to table 2.
-
The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).
So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?
Please let me know if you need anymore detail!
|||
I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.
http://www.condoresorts.com/sample.jpg
Ok, so here is the process:
1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.
2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.
3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.
* RoomInfo and Room_Days primary key is an identity.
The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.
A six month time span creates the following data:
1 entry in RoomInfo
180 entries in Room_Days ( on average)
540 entries in RoomDay_Customer ( for 3 people for example)
The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?
Question about performance
I'm using a tableadapter for my update operations.
The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.
This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?
Thanks in advance.
How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||
The cpu isn't fully being utilzed on the sql server machine.
Here is the exact process:
There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.
Example schema:
Table1 ( ID1 (identity, pk), 3 misc rows)
Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)
Table 3 ( ID2 (ForigneKey) )
So, Table1 is parent to Table2 and Table3 is parent to table 2.
-
The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).
So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?
Please let me know if you need anymore detail!
|||
I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.
http://www.condoresorts.com/sample.jpg
Ok, so here is the process:
1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.
2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.
3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.
* RoomInfo and Room_Days primary key is an identity.
The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.
A six month time span creates the following data:
1 entry in RoomInfo
180 entries in Room_Days ( on average)
540 entries in RoomDay_Customer ( for 3 people for example)
The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?
Question about Pages
I am looking into a locking issue and I want to find out the specific
rows that live on a page.
Sp_lock returns the following
page id - 1:3873267
But, I don't know of any way to view the data there or correlate it
with specific rows in a table. Is there a way to do this?1:3873267 means - datafile number 1, page number 3873267. You can try
undocummented DBCC PAGE statement to find the records on this page.
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <jbergmanster@.gmail.com> napisal w wiadomosci
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Have a look here:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Andrew J. Kelly SQL MVP
<jbergmanster@.gmail.com> wrote in message
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Thank you, Andrew for that Blog article. That helped me find the
records I need. I have blogged about my testing of the locking issue
at http://jeffbergman.com/cs/blogs/csjeff/archive/2007/08/13/12.aspx
which describes a locking issue I was having with ADO.Net and the
SqlDataReader.
On Aug 13, 11:30 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Have a look here:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/62565...
> --
> Andrew J. Kelly SQL MVP
> <jbergmans...@.gmail.com> wrote in message
> news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
> >I am looking into a locking issue and I want to find out the specific
> > rows that live on a page.
> > Sp_lock returns the following
> > page id - 1:3873267
> > But, I don't know of any way to view the data there or correlate it
> > with specific rows in a table. Is there a way to do this?
rows that live on a page.
Sp_lock returns the following
page id - 1:3873267
But, I don't know of any way to view the data there or correlate it
with specific rows in a table. Is there a way to do this?1:3873267 means - datafile number 1, page number 3873267. You can try
undocummented DBCC PAGE statement to find the records on this page.
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <jbergmanster@.gmail.com> napisal w wiadomosci
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Have a look here:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
Andrew J. Kelly SQL MVP
<jbergmanster@.gmail.com> wrote in message
news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
>I am looking into a locking issue and I want to find out the specific
> rows that live on a page.
> Sp_lock returns the following
> page id - 1:3873267
> But, I don't know of any way to view the data there or correlate it
> with specific rows in a table. Is there a way to do this?
>|||Thank you, Andrew for that Blog article. That helped me find the
records I need. I have blogged about my testing of the locking issue
at http://jeffbergman.com/cs/blogs/csjeff/archive/2007/08/13/12.aspx
which describes a locking issue I was having with ADO.Net and the
SqlDataReader.
On Aug 13, 11:30 am, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> Have a look here:
> http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/62565...
> --
> Andrew J. Kelly SQL MVP
> <jbergmans...@.gmail.com> wrote in message
> news:1187028314.018378.50630@.m37g2000prh.googlegroups.com...
> >I am looking into a locking issue and I want to find out the specific
> > rows that live on a page.
> > Sp_lock returns the following
> > page id - 1:3873267
> > But, I don't know of any way to view the data there or correlate it
> > with specific rows in a table. Is there a way to do this?
Tuesday, March 20, 2012
Question about IIF() function?
I tried to replace the value on rows using the IIF() function and it did not work. The following is the mdx code.
WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified"))) ON ROWS FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS FROM [AP Statistics by Patient Type])
What is wrong with the IIF() function....
IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))
Thanks
The problem is that you are returning strings and the row axis needs a set of members. You would need to put your IIF in a calculated measure on the columns and also define a set of members on the rows.
At a guess it would probably need to look something like the following (my changes in red).
WITH MEMBER [Measures].[ParameterCaption]
AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL'
MEMBER [Measures].[PatientCalc] AS IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))
SELECT {[Measures].[ParameterCaption]
, [Measures].[ParameterValue]
, [Measures].[ParameterLevel]
, [Measures].[PatientCalc]}
ON COLUMNS ,
[DIM_Patient].[In Out Patient].Members ON ROWS
FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS
FROM [AP Statistics by Patient Type])
Question about Grouping rows in Select statements with joins
Hi
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:
> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:
> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>
Question about FullText Search
Hi eveyone,
I have a fullText catalog for a very simple table with 3 rows which contains text like below:
Row 1==> First term, Second term 4-3/16" third term 14.75 forth term 998 3-3/8" 8911 8
Row 2==> First term, Second term 5-3/16" third term 14.95 forth term 11
Row 3==> First term, Second terms 2/18" third term 14.07 forth term 3-3/8" 3
When I try to run query:
1. Select * from myFullText where contains(Text, '3/16') return row 1 and 2 ( ok )
2. Select * from myFullText where contains(Text, '11-3/8') ==> return row 2 (matching 11 only)
3. Select * from myFullText where contains(Text, '3-3/8') return no record
4. Select * from myFullText where contains(Text, '3/8') return no record
If someone do understand about Microsoft FullText search, please help me by explain to me why the output for sql #2, 3 and 4 are like that.
Regards,
John Dang
FYI,
Currently I am using SqlServer 2005, english language and Window XP Pro OS.
JDang
|||Workaround:
Add more keywords:
3-3/8 + 3/8 + 3-3 + 3 + 338 ...
Consider to eliminate slashes and dashes
Make sure that noisewords do not disdurb. ( I dont know enough about when they are used )
Thomas Boffo
Question about FullText Search
Hi eveyone,
I have a fullText catalog for a very simple table with 3 rows which contains text like below:
Row 1==> First term, Second term 4-3/16" third term 14.75 forth term 998 3-3/8" 8911 8
Row 2==> First term, Second term 5-3/16" third term 14.95 forth term 11
Row 3==> First term, Second terms 2/18" third term 14.07 forth term 3-3/8" 3
When I try to run query:
1. Select * from myFullText where contains(Text, '3/16') return row 1 and 2 ( ok )
2. Select * from myFullText where contains(Text, '11-3/8') ==> return row 2 (matching 11 only)
3. Select * from myFullText where contains(Text, '3-3/8') return no record
4. Select * from myFullText where contains(Text, '3/8') return no record
If someone do understand about Microsoft FullText search, please help me by explain to me why the output for sql #2, 3 and 4 are like that.
Regards,
John Dang
FYI,
Currently I am using SqlServer 2005, english language and Window XP Pro OS.
JDang
|||Workaround:
Add more keywords:
3-3/8 + 3/8 + 3-3 + 3 + 338 ...
Consider to eliminate slashes and dashes
Make sure that noisewords do not disdurb. ( I dont know enough about when they are used )
Thomas Boffo
Monday, March 12, 2012
Question about database size and performance...
I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
Question about database size and performance...
I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
RogRoger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
Question about database size and performance...
I have a project where I will be loading ~ 242 Million Rows of data to a
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
table and then adding to this on a monthly basis. Maybe 3 million a month
added. This will be used mainly for reporting purposes.
Is there a limit as to how many rows a table in SQL can have? SQL Server
2000 is what I am using?
As for performance what recommendations would you have? Is there a website
out there that discussses this in length?
Thanks,
Rog
Roger wrote:
> I have a project where I will be loading ~ 242 Million Rows of data
> to a table and then adding to this on a monthly basis. Maybe 3
> million a month added. This will be used mainly for reporting
> purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL
> Server 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
> website out there that discussses this in length?
> Thanks,
> Rog
What will you be doing with the data once it's loaded in the table?
In general, proper index design so queries run as efficiently as
possible is very important on large tables. Just as table design is
important. For example, if you have large varchar or char columns that
are NULLable and many rows might contain NULL, it may be better to
create another table with a 1:1 relationship to store the large data.
Keeping row size as small as possible is better.
SQL Server has no trouble handling tables of this size. Or dishing out
data from a query. It will give you correct results whether the queries
are tuned or not or whether the table is well-designed. The question is,
how long are your users willing to wait for a response.
If you have the DDL for the table, consider posting it here for
comments. And then tune your queries.
David G.
|||As David said, attention to table and index design details are important
with large tables. The maximum rows per table is limited only by available
storage in SQL 2000. We have tables with billions of rows that perform
quite well on appropriately sized hardware.
You also need to consider administration, maintenance and your availability
requirements. For example, partitioning financial data into separate tables
by quarter can reduce the time it takes to load data, purge data, rebuild
indexes, etc. Multiple filegroups can provide more granular backup/recovery
options. The downside is additional administration and schema complexity.
This may or may not affect performance, depending on how data are accessed.
You might check out the chapter RDBMS Performance Tuning Guide for Data
Warehousing in the SQL 2000 Resource Kit:
http://www.microsoft.com/resources/d...rt5/c2061.mspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
|||Thanks for the insite from all of your responses..... I will post my
schema here in a short while... Days to see what suggestions you might
have.
Thanks,
Rog
"Roger" <rogoflap@.gmail.com> wrote in message
news:Oex4dY7iEHA.536@.TK2MSFTNGP11.phx.gbl...
> I have a project where I will be loading ~ 242 Million Rows of data to a
> table and then adding to this on a monthly basis. Maybe 3 million a month
> added. This will be used mainly for reporting purposes.
> Is there a limit as to how many rows a table in SQL can have? SQL Server
> 2000 is what I am using?
> As for performance what recommendations would you have? Is there a
website
> out there that discussses this in length?
> Thanks,
> Rog
>
Wednesday, March 7, 2012
Question about ad hoc Compiled Plans in syscacheobjects
When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks
<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegr oups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/de...qlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks
<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegr oups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/de...qlquerproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
Question about ad hoc Compiled Plans in syscacheobjects
When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
Question about ad hoc Compiled Plans in syscacheobjects
When I query syscacheobjects I see multiple rows for the same query
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
where only the WHERE condition changes:
cacheobjtype objtype usecounts sql
-- -- -- --
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
My understanding is that SQL Server should be able to reuse the
execution plan when only the WHERE condition changes. Or am I not
looking at the above results correctly?
Thanks<pshroads@.gmail.com> wrote in message
news:1116265537.540499.114880@.g43g2000cwa.googlegroups.com...
> When I query syscacheobjects I see multiple rows for the same query
> where only the WHERE condition changes:
> cacheobjtype objtype usecounts sql
> -- -- -- --
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2847876
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2897760
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949594
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949601
> Compiled Plan Adhoc 1 DECLARE @.INPAR1 int SELECT
> org_id FROM orgs WHERE org_id <> 0 AND orgrow = 2949713
> My understanding is that SQL Server should be able to reuse the
> execution plan when only the WHERE condition changes. Or am I not
> looking at the above results correctly?
>
When a programmer hard-codes a parameter value instead of using a variable,
the assumption is that the programmer wants a different plan for each
different parameter value. SQL Server will ignore the presumed intent of
the programmer and "autoparameterize" the query only in limited
circumstances.
From
Microsoft SQL Server Query Processor Internals and Architecture
Hal Berenson and Kalen Delaney
http://msdn.microsoft.com/library/d... />
erproc.asp
The other case when processing a statement from a client is when the query
comes in as a SQL language event. The flow is not that different, with one
exception. In this case, SQL Server tries to use a technique called
autoparameterization. The SQL text is matched up against an
autoparameterization template. Autoparameterization is a difficult issue, so
other database management products, which have been able to take advantage
of shared SQL in the past, have generally not provided this option. The
problem with it is if SQL Server were to automatically autoparameterize
every query, some (or even most) of those queries would get very bad plans
for some of the specific values that are subsequently submitted. In the case
where the programmer puts a parameter marker in the code, the assumption is
that the programmer knows the range of values to expect, and is willing to
accept the plan that SQL Server comes up with. But when the programmer
actually supplies a specific value, and SQL Server decides to treat that
value as a changeable parameter, there is the possibility that any plan
generated that works for one value may not work for subsequent values. With
stored procedures, the programmer can force new plans to be generated by
putting the WITH RECOMPILE option in the procedure. With
autoparameterization, there is no way for the programmer to indicate that a
new plan must be developed for each new value.
SQL Server is then very conservative when it comes to autoparameterization.
There is a template of queries that are safe to be autoparameterized, and
only queries that match the template will have autoparameterization applied.
For example, suppose we have a query containing a WHERE clause with an
equality operator and no joins, with a unique index on the column in the
WHERE clause. SQL Server knows there will be never be more than one row
returned and the plan should always use that unique index. SQL Server will
never consider scanning, and the actual value will never change the plan in
any way. This kind of query is safe for autoparameterization.
If the query matches the autoparameterization template, SQL Server actually
replaces the literals with parameter markers, (for example, @.p1, @.p2) and
that's what we send into the server, just as if it were an sp_executesql
call. If the query was something SQL Server decided was not safe to
autoparameterize, the client will send SQL Server the literal SQL text as ad
hoc SQL.
David
Subscribe to:
Posts (Atom)