I am creating jobs using SQL Server Agent, the job has multiple steps.
Example
Step1 creates tables
Step2 creates group of reports to be executed only when step 1 is successful
l
Step3 creates group of reports to be executed only when step 1 is successful
l
I would like to execute step2 and step3 at the same time, step 3 is not depe
nding on step2.
How I do that? I am thinking of creating another job to execute step3 after
evaluating the status of step1,
somebody has more efficient ideas?
Thanks,One way would be to use DTS and it's workflow engine. You can run sql tasks
conditionally or at the same time.
Ray Higdon MCSE, MCDBA, CCNA
--
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:CCB3CD33-DDFC-4E8B-9324-774A90F7A5EB@.microsoft.com...
> I am creating jobs using SQL Server Agent, the job has multiple steps.
> Example
> Step1 creates tables
> Step2 creates group of reports to be executed only when step 1 is
successfull
> Step3 creates group of reports to be executed only when step 1 is
successfull
> I would like to execute step2 and step3 at the same time, step 3 is not
depending on step2.
> How I do that? I am thinking of creating another job to execute step3
after evaluating the status of step1,
> somebody has more efficient ideas?
> Thanks,
>|||Thanks Ray...I will try that.
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
multiple Index files
1. Is there any benifit to creating a distinct index file for each column to be indexe
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql
Multiple Foreign Keys
I am having trouble creating multiple foreign keys on a table so that I can set up cascading update and cascading delete from two different primary tables. I am using the diagram to do this but when I try to save the diagram I get the following error.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.
What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?
Thanks!Is it possible that I have records in the foreign table that do not transfer back to the primary table?Not just possible, nearly guaranteed.
The best answer is to create a query that will show you the offending rows, then go and fix them. You can often pick a value that makes a reasonable default (sometimes NULL works well, meaning there isn't any relationship). Sometimes you've got to figure out what FK value you need, which can be a lot of work.
-PatP|||Thanks! That was exactly what it was and it is fixed!
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_IndResults_RaceData'. The conflict occurred in database 'VIRA', table 'RaceData', column 'RaceID'.
What would cause this to happen? Is it possible that I have records in the foreign table that do not transfer back to the primary table?
Thanks!Is it possible that I have records in the foreign table that do not transfer back to the primary table?Not just possible, nearly guaranteed.
The best answer is to create a query that will show you the offending rows, then go and fix them. You can often pick a value that makes a reasonable default (sometimes NULL works well, meaning there isn't any relationship). Sometimes you've got to figure out what FK value you need, which can be a lot of work.
-PatP|||Thanks! That was exactly what it was and it is fixed!
Monday, March 19, 2012
Multiple datasets embedded within each other..
I have a dilemma. I am creating an invoice report with a fairly
complicated hierarchy.
I have it working so far. All the invoice elements are doing fine
except the taxes. Because of business requirements, it is not possible
for me to get everything in a single dataset. My invoice may have 40
lines. Some of those lines are taxable and some are not. Taxes might
include 1 type of tax of 5 types of tax.
So i need to get the tax in a separate dataset. What I need to know is
if it is possible to embed one dataset inside another based on a
filter (the ID field used to link the taxes to a line for example).
I know I can reference certain fields in other datasets, but I have an
unknown number of tax lines I have to embed into my existing dataset
in the report. I couldn't find any information on this so if osmeone
can point me in the right direction or flat out tell me its not
possible I would appreciate it.
Thanks.What you want is to embed a subreport in a cell of the report table (or onto
your list control). This subreport can have just a single field if you want.
Have the subreport parameters (remember a subreport is just a regular
report) be whatever values you need to calculate the tax. Test the report
standalone and then drag and drop the report into the cell of the report
control (if that is what you are using). Then do a right mouse click on the
subreport and map the parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ" <resonantblue@.gmail.com> wrote in message
news:1172171435.536157.39690@.v45g2000cwv.googlegroups.com...
>I have a dilemma. I am creating an invoice report with a fairly
> complicated hierarchy.
> I have it working so far. All the invoice elements are doing fine
> except the taxes. Because of business requirements, it is not possible
> for me to get everything in a single dataset. My invoice may have 40
> lines. Some of those lines are taxable and some are not. Taxes might
> include 1 type of tax of 5 types of tax.
> So i need to get the tax in a separate dataset. What I need to know is
> if it is possible to embed one dataset inside another based on a
> filter (the ID field used to link the taxes to a line for example).
> I know I can reference certain fields in other datasets, but I have an
> unknown number of tax lines I have to embed into my existing dataset
> in the report. I couldn't find any information on this so if osmeone
> can point me in the right direction or flat out tell me its not
> possible I would appreciate it.
> Thanks.
>
complicated hierarchy.
I have it working so far. All the invoice elements are doing fine
except the taxes. Because of business requirements, it is not possible
for me to get everything in a single dataset. My invoice may have 40
lines. Some of those lines are taxable and some are not. Taxes might
include 1 type of tax of 5 types of tax.
So i need to get the tax in a separate dataset. What I need to know is
if it is possible to embed one dataset inside another based on a
filter (the ID field used to link the taxes to a line for example).
I know I can reference certain fields in other datasets, but I have an
unknown number of tax lines I have to embed into my existing dataset
in the report. I couldn't find any information on this so if osmeone
can point me in the right direction or flat out tell me its not
possible I would appreciate it.
Thanks.What you want is to embed a subreport in a cell of the report table (or onto
your list control). This subreport can have just a single field if you want.
Have the subreport parameters (remember a subreport is just a regular
report) be whatever values you need to calculate the tax. Test the report
standalone and then drag and drop the report into the cell of the report
control (if that is what you are using). Then do a right mouse click on the
subreport and map the parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ" <resonantblue@.gmail.com> wrote in message
news:1172171435.536157.39690@.v45g2000cwv.googlegroups.com...
>I have a dilemma. I am creating an invoice report with a fairly
> complicated hierarchy.
> I have it working so far. All the invoice elements are doing fine
> except the taxes. Because of business requirements, it is not possible
> for me to get everything in a single dataset. My invoice may have 40
> lines. Some of those lines are taxable and some are not. Taxes might
> include 1 type of tax of 5 types of tax.
> So i need to get the tax in a separate dataset. What I need to know is
> if it is possible to embed one dataset inside another based on a
> filter (the ID field used to link the taxes to a line for example).
> I know I can reference certain fields in other datasets, but I have an
> unknown number of tax lines I have to embed into my existing dataset
> in the report. I couldn't find any information on this so if osmeone
> can point me in the right direction or flat out tell me its not
> possible I would appreciate it.
> Thanks.
>
multiple datafiles- on multiple drives on One raid set
Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
multiple datafiles- on multiple drives on One raid set
Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
multiple datafiles- on multiple drives on One raid set
Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
Monday, March 12, 2012
multiple database performance
I'm planning on creating multiple databases on a client because I'm storing a large amount of binary data (jpg's and pdf's) in tables and there are size limitations for a single database. So, in order to get around the size limitations, I'm just creating one database for my main tables and multiple other databases to hold the binary data.
Any performance considerations I should consider in regards to multiple databases? Any other thoughts?
Hi,
what about not storing the data in the database ? Do a quick one on this document, it has some detailed information for your design:
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Second, as someone else pointed out, saving files in the database is not a good design. The main problem is, what do you do with them once they are there? How do you get them out? The OS is much better at saving files efficiently than SQL. Creating an "index" database into the files is a much better method.
To answer your question, it depends on your design and situation. What are you going to do with the data, how do you query the databases, etc. There is very little impact in selecting data from tables in different database on the same sever.|||I think I may have posted this in the wrong group (I am actually going to use SQL Server CE), but the 2 responses were extremely helpful. Thanks!
Friday, March 9, 2012
Multiple data sources
If I write an SQL query I can select data from various database from within
the same query. Any idea how I can do that when creating a SRS report.?
ThanksThe best way is by creating a stored procedure on the SQL Server, which
picks up the data from the different databases. Then you use this stored
procedure in your report.
Kaisa M. Lindahl Lervik
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
> If I write an SQL query I can select data from various database from
> within the same query. Any idea how I can do that when creating a SRS
> report.?
> Thanks
>|||Great.
thanks very much.
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:es0rtfoAHHA.3604@.TK2MSFTNGP04.phx.gbl...
> The best way is by creating a stored procedure on the SQL Server, which
> picks up the data from the different databases. Then you use this stored
> procedure in your report.
>
> Kaisa M. Lindahl Lervik
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
> news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> If I write an SQL query I can select data from various database from
>> within the same query. Any idea how I can do that when creating a SRS
>> report.?
>> Thanks
>
the same query. Any idea how I can do that when creating a SRS report.?
ThanksThe best way is by creating a stored procedure on the SQL Server, which
picks up the data from the different databases. Then you use this stored
procedure in your report.
Kaisa M. Lindahl Lervik
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
> If I write an SQL query I can select data from various database from
> within the same query. Any idea how I can do that when creating a SRS
> report.?
> Thanks
>|||Great.
thanks very much.
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:es0rtfoAHHA.3604@.TK2MSFTNGP04.phx.gbl...
> The best way is by creating a stored procedure on the SQL Server, which
> picks up the data from the different databases. Then you use this stored
> procedure in your report.
>
> Kaisa M. Lindahl Lervik
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
> news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> If I write an SQL query I can select data from various database from
>> within the same query. Any idea how I can do that when creating a SRS
>> report.?
>> Thanks
>
Multiple data files in a filegroup
I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.
I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.
My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.
Hopefully this is not too confusing.
Thanks.
BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.
I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.
My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.
Hopefully this is not too confusing.
Thanks.
BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.
Wednesday, March 7, 2012
Multiple Columns Index/Key (does it free me from creating a single column indexes?)
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3?
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index?
can anyone explain the main diference between Keys and Indices?
thanks,
Ran Kizi
1. Yes, the multi-column index will also function as an index for the first column. In many situations, it will also be an quasi-efficient index for the second, and perhaps other columns. I would get some response measurements before assuming that indexes were required on the lesser columns.
2. A 'unique key' is in fact, a 'Unique CONSTRAINT.
A Primary Key can be created for one or more columns, requires indexing to be efficient, and an index is automatically generated when the Primary Key is created. A Primary Key requires a value, and that the value is unique.
There is no 'Unique Key', but there is a Unique CONSTRAINT. (Perhaps in your question, you were referring to a Primary Key, and if so, the above applies.) A Unique CONSTRAINT can be created for one or more columns, and automatically creates a matching index. A Unique CONSTRAINT does not require a value, but if a value is provided, it must be unique.
A Foreign Key 'should have' an index to be efficient -but the index is NOT automatically generated when the Foreign Key is created. It is recommended that a matching index be created when the Foreign Key is created. A Foreign Key does NOT require a value, BUT if a value is provided, that value must also exist in the table with the Primary Key relationship.
|||1. Indexes on multiple columns can also be used as index on first column. and in my experience it can not be used as index on second and following columns. you would need to create seprate indexes on those column if your queries will only use second or third column without using first column in queries.
There is a workaround even if you dont have to use first column in your query you can still use COLUMN1 = COLUMN1 only to make optimizer use index as in best of my knowledge if column1 is not present in your query index will be used for second or third column.
2. when you create UNIQUE constraint on multiple columns SQL Server will ultimately create compound unique index on those colunms to make sure uniquenes. for selectivity of index above will apply.
Primary Key does create index automaticaly (if index type is not mentioned and table is heap then will create CLUSTERED INDEX). FORIEGN KEY does not create index automatically and it is highly recomemded to create index on foriegn key column. The one unique reason is when deleting record in primary key table sql server perform a look up in all tables having foriegn key on that table and if no index presented then it will perform clustered index scan or table scan that will ultimately slow down DELETE operation for primary key table.
Multiple Columns Index/Key (does it free me from creating a single column indexes?)
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3?
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index?
can anyone explain the main diference between Keys and Indices?
thanks,
Ran Kizi
1. Yes, the multi-column index will also function as an index for the first column. In many situations, it will also be an quasi-efficient index for the second, and perhaps other columns. I would get some response measurements before assuming that indexes were required on the lesser columns.
2. A 'unique key' is in fact, a 'Unique CONSTRAINT.
A Primary Key can be created for one or more columns, requires indexing to be efficient, and an index is automatically generated when the Primary Key is created. A Primary Key requires a value, and that the value is unique.
There is no 'Unique Key', but there is a Unique CONSTRAINT. (Perhaps in your question, you were referring to a Primary Key, and if so, the above applies.) A Unique CONSTRAINT can be created for one or more columns, and automatically creates a matching index. A Unique CONSTRAINT does not require a value, but if a value is provided, it must be unique.
A Foreign Key 'should have' an index to be efficient -but the index is NOT automatically generated when the Foreign Key is created. It is recommended that a matching index be created when the Foreign Key is created. A Foreign Key does NOT require a value, BUT if a value is provided, that value must also exist in the table with the Primary Key relationship.
|||1. Indexes on multiple columns can also be used as index on first column. and in my experience it can not be used as index on second and following columns. you would need to create seprate indexes on those column if your queries will only use second or third column without using first column in queries.
There is a workaround even if you dont have to use first column in your query you can still use COLUMN1 = COLUMN1 only to make optimizer use index as in best of my knowledge if column1 is not present in your query index will be used for second or third column.
2. when you create UNIQUE constraint on multiple columns SQL Server will ultimately create compound unique index on those colunms to make sure uniquenes. for selectivity of index above will apply.
Primary Key does create index automaticaly (if index type is not mentioned and table is heap then will create CLUSTERED INDEX). FORIEGN KEY does not create index automatically and it is highly recomemded to create index on foriegn key column. The one unique reason is when deleting record in primary key table sql server perform a look up in all tables having foriegn key on that table and if no index presented then it will perform clustered index scan or table scan that will ultimately slow down DELETE operation for primary key table.
Multiple Columns Index/Key (does it free me from creating a single column indexes?)
I hope i'm in the right place, but thanks anyway....
Actually i have 2 questions (regarding sql-server Indices/Keys):
1) I have an index, which is consisted of 4 columns.
I've read elsewhere that this index functions (as well) as an index (single column
index) on the first column of this multi-column index.
Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns
seperately i need to define only 3?
2) I have a unique key consisted of multiple columns.
I'd like to save an index to this combination of columns as well (to speed up
things in DB...).
Does the definition of a multiple-columns key free me from defining the multiple-
columns index?
can anyone explain the main diference between Keys and Indices?
1. I'm sure you are in the wrong place, try the SQL Server forums.
2. A Key implies unique data in the column(s) (Unique Key / Primary Key) where as an index doesn't necessarily have to have unique data in the column(s).
3. An index and a key that use the same definitions (same columns etc.) would be redundant, cost space (and possibly performance) and not desired. The optimizer can use either.
4. The first column of an index is always the most important and determines whether the index will be used or not. If you have no better indexes of the first column then it will be used.
BTW - Don't over do it with indexes!
Please ask such question in the SQL Server Forum, this forum is only for Team Edition for Database Professionals.
Alle
Subscribe to:
Posts (Atom)