Best design for edit tracking?

Hey all,
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.
What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:
1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.
My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a web dev question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.OK, the quickest solution to this goest something like this...
Add two rows to your core table, the first is a DateTime with a Default
constraint of GetDate(). The second column is used to identify who made the
change.
Next when update one of the rows, you need to simply include the identifier
of the person who made the change.
Next when you read the table you need to read and compare two rows. The
most recent row, and the second most recent row. This provides the
information about what changed - as a freebie you'll gain access to the old
version of the row.
Another approach would be to have two tables. The first is the core table
as it is now. The second table contains a flag for each column in your core
table, the DateTime column (as above) and the user identifier column (as
above). This time when you edit the row, you also place a new row into this
table settings the flags for the columns which were altered. Don't forget
to include a foreign key back to your core table. When you select the row
from the core table you can also join on your change map, selecting the
max(datetime) and this will tell what columns were changed in the last edit.
But it won't tell you the values that they were before. There is a big
advantage in this method as your core table doesn't need to be altered.
Regards
Colin Dawson
www.cjdawson.com
"roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
> Hey all,
> This is a general question. Here's my scenario: We have a legacy
> database. The core table within contains almost 4 million records in
> SQL Server. Currently we have a web front end which allows uses to
> search through the database for the information they need.
> What the client wants is a web front end which allows some users to
> edit the core table above. No problem. What the client also wants is
> for the non-editable web front end (used for research) to display (via
> colored text) which records have been edited. Example:
> 1.) Jimmy edits records x, y, and z in coretable1 using the editing
> web app.
> 2.) Jane comes along and uses the research web app to hunt down some
> records. She views records s through z. In her view she notices that
> certain cells in rows x, y, and z are colored red. This tells her that
> Jimmy has edited those specific fields in those specific rows.
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
>|||Colin
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
I think you menat "add to columns", and it is worth mentioning that with
this solutuin you will have to write a trigget on that table in order to
track changes
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
> OK, the quickest solution to this goest something like this...
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
> Next when update one of the rows, you need to simply include the
> identifier of the person who made the change.
> Next when you read the table you need to read and compare two rows. The
> most recent row, and the second most recent row. This provides the
> information about what changed - as a freebie you'll gain access to the
> old version of the row.
> Another approach would be to have two tables. The first is the core table
> as it is now. The second table contains a flag for each column in your
> core table, the DateTime column (as above) and the user identifier column
> (as above). This time when you edit the row, you also place a new row
> into this table settings the flags for the columns which were altered.
> Don't forget to include a foreign key back to your core table. When you
> select the row from the core table you can also join on your change map,
> selecting the max(datetime) and this will tell what columns were changed
> in the last edit. But it won't tell you the values that they were before.
> There is a big advantage in this method as your core table doesn't need to
> be altered.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
> news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
>|||oops, I did mean columns yes.
A trigger won't really be able to help as you'll need to supply extra
information than what is stored in the original table. It would be better
to use a Stored procedure and directly enter the data into the new columns.
Of course, the exception to this is that if the application connects to SQL
using seperate usernames, it is possible to use the @.@.User in a trigger to
accomplish the same result. With the applications that my company creates,
this is not possible as they alway connect with the same user (connection
pooling)
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSloXSxdGHA.4108@.TK2MSFTNGP03.phx.gbl...
> Colin
> I think you menat "add to columns", and it is worth mentioning that with
> this solutuin you will have to write a trigget on that table in order to
> track changes
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
>|||On 13 May 2006 08:30:37 -0700, roy.@.nderson@.gm@.il.com wrote:

>Hey all,
>This is a general question. Here's my scenario: We have a legacy
>database. The core table within contains almost 4 million records in
>SQL Server. Currently we have a web front end which allows uses to
>search through the database for the information they need.
>What the client wants is a web front end which allows some users to
>edit the core table above. No problem. What the client also wants is
>for the non-editable web front end (used for research) to display (via
>colored text) which records have been edited. Example:
>1.) Jimmy edits records x, y, and z in coretable1 using the editing
>web app.
>2.) Jane comes along and uses the research web app to hunt down some
>records. She views records s through z. In her view she notices that
>certain cells in rows x, y, and z are colored red. This tells her that
>Jimmy has edited those specific fields in those specific rows.
>My question is: what is the most efficient way to track these column
>specific edits so that my web app can display them? This may seem like
>a web dev question, but the reality is that my web apps have to
>interact with SQL Server so if anyone has any input, I'd love to hear
>it! Thanks.
Hi Roy,
This is impossible to answer, because the requirements are incomplete.
For instance, what happens if Jimmy edits rows x, y, and z (as in your
example), then Joan edits rows w and x, then Jimmy edits z another time
and then Jane views rows s through z. What columns in what rows have to
be marked as "changed"?
Another question - what if Jimmy edits rows x, y, and z; then nothin
happens for a long time. After a year, Jane looks at rows s through z.
Should Jimmy's changes still be marked?
Hugo Kornelis, SQL Server MVP|||>
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
There are several ways to accomplish that. Do you want your system to
be optimized for retrieval of the current version but support
occasional drill down into editing history. Or do you want to optimize
retrieving history of edits and are ready to pay the price of slowing
down retrieval of current version?

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx --Per
formance
2005
http://www.microsoft.com/technet/pr...ce/default.mspx -
--Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You ca
n
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
>> 2) Put tempdb database on different phsyical disk
>> 3) Add much more RAM as you can and as it allowed by OS
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
>> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
>> Practices 2005
>> <lorenzdomin...@.hotmail.com> wrote in message
>> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>>
>> > Hi
>> > I am having a little trouble finding what is the ideal configuration
>> > for running SQL Server 2005.
>> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
>> > Operating System will be Windows 2003 Standard Server R2
>> > I can have any number of hard drives for either a Raid 1 or Raid 5
>> > configuration or whatever, any comments appreciated?
>> > How should the Hard Disk Drives be set up for a 10Gb Database?
>> > Thanks
>> > Dominic- Hide quoted text -
>> - Show quoted text -
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
--
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
Dominic
Hi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic
|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
..MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegro ups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>
|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

being able to stop parallelism in a C++ Program calling SQL Server

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.

One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.

However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.

Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.

Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?

Thanks in Advance.

Peter Nolan

www.peternolan.com

select * from tablename OPTION (MAXDOP 1)

That should do it. MAXDOP stands for Max Degree of Parallelism, and allows you to limit the number of parallel execution threads for a query. Setting it to 1 essentially disables parallelism for that query.
|||

Hi David,

thanks very much for that....I did not know the syntax of how to do this at query level....

We turned off parallelism at the server level and it turns out the optimiser will still perform a table scan....we have many logical tables in on physical table and even when we query the underlying table with a select * from with a where clause it is scanning the table and taking about 40 seconds to do so....even when we say 'top 1' to make it get the first row and put an index on the field that logically partitions the table.

It looks like we will have to introduce partitioning so that the optimiser can know to scan just the partition in question. We also had a feature we planned to introduce which was to turn off table validation....if an etl subsystem is in production there is little need to validate tables....we never put it in...and now there seems like a need to do so.

Again, thanks for the tip. Much appreciated.

Peter

www.peternolan.com

|||

Hi All,

one of our guys here came up with a better idea....

What the tool is doing is validating the existence of the taget table/views passed to the program....so in the case of a typo a specific message saying the table/view not found is issued.

However the optimiser is thinking the program really wants all the rows.

One of the guys here suggested if the constraint where 1 = 0 is added to the select * then all optimisers will be smart enough to know no rows will be returned yet the database will still check that the table/view exists.

So we will also implement this test and that will make sure all programs test for existence of a table quickly.

If we feel industrious one day we can actually ask ODBC if the table exists.

One reason why I like the select * from tablename as a test for existence of a table over just asking ODBC is that it forces the preparation of the plan and if the view has been invalidated then it will also be caught at this stage of processing where ODBC will return a positive answer to does the view exist...

Again, thanks for your responses...it is much appreciated...

Just by the way....we installed the new performance dashboards just released and it was these that managed to show us the statement that was in a wait state and how we detected the problem...so if you have not installed the new performance dashboards yet....you might want to do so...

Best Regards

Peter