Changing number of data files

Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J

quote:

---

>--Original Message--
>I have a 225Gb database on a test server. It is currently

---

in one file and I

quote:

---

>would like to split it into several files smaller files.

---

What is the best

quote:

---

>way of doing this?
>thanks
>Gav
>
>.
>

---

If I create another data file is it possible to move data from one file to
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...[QUOTE]
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
>
> in one file and I
> What is the best|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J

quote:

---

>--Original Message--
>If I create another data file is it possible to move data

---

from one file to

quote:

---

>the other? I currently have a 220Gb and a 50Gb file I

---

would like to fill up

quote:

---

>the 50Gb file so I can resize the 220Gb file.

---

Unfortunately I don't have

quote:

---

>much spare storage space so I'm not sure how else I would

---

do it. Only other

quote:

---

>thing I can think of doing is dropping the entire

---

database and recreating it

quote:

---

>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
to.[QUOTE]
currently[QUOTE]
files.[QUOTE]
>
>.
>

---

|||Gav
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John

quote:

---

>--Original Message--
>Yes, but that sort of depends where your data file is.

---

for

quote:

---

>instance you can create a data file on a different

---

drive /

quote:

---

>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do

---

with

quote:

---

>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary

---

keys

quote:

---

>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
data[QUOTE]
>from one file to
>would like to fill up
>Unfortunately I don't have
would[QUOTE]
>do it. Only other
>database and recreating it
>message
data[QUOTE]
>to.
>currently
>files.
>.
>

---

Changing join order based on parameters

Hi,
I keep coming across a performance problem in SQL that I feel must have
an elegant solution, I just can't find it.
The problem is quite obvious:
I have a query that performs a search based on some search parameters
which are given default values in the query of null.
I have a heirachical structure of tables which might be used in the
filtering
e.g.
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd
I filter the result by adding to these join filters something like
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
= @.filterCol1 )
etc...
The problem comes when there are a few hundred thousand rows in each
table, and my search wants to filter on a parameter which affects
TableD. It appears from the execution plan (and indeed only seems
logical) that SQL has to join the entirity of tables A,B and C on their
foreign keys, before joining to D in order to filter. This of course
makes the query take ages.
I've got ideas of how I could get around this, but they are at best
"clunky".
for example:
1) I could have several different "selects" each joining in a
different order based on the parameters that are set.
2) I could build up a "filter" temporary table based on the parameters
passed, then only do the select once, starting with this filter table
first
3) I could come up with a nice elegant solution which currently eludes
me.
Any suggestions?
WillWill

> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb OR TableB.col1 =
COALESCE(@.filterCol1,TableB.col1 )
WHERE @.filterCol1 IS NULL
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143715517.502232.295550@.z34g2000cwc.googlegroups.com...
> Hi,
> I keep coming across a performance problem in SQL that I feel must have
> an elegant solution, I just can't find it.
> The problem is quite obvious:
> I have a query that performs a search based on some search parameters
> which are given default values in the query of null.
> I have a heirachical structure of tables which might be used in the
> filtering
> e.g.
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd
> I filter the result by adding to these join filters something like
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
> etc...
> The problem comes when there are a few hundred thousand rows in each
> table, and my search wants to filter on a parameter which affects
> TableD. It appears from the execution plan (and indeed only seems
> logical) that SQL has to join the entirity of tables A,B and C on their
> foreign keys, before joining to D in order to filter. This of course
> makes the query take ages.
> I've got ideas of how I could get around this, but they are at best
> "clunky".
> for example:
> 1) I could have several different "selects" each joining in a
> different order based on the parameters that are set.
> 2) I could build up a "filter" temporary table based on the parameters
> passed, then only do the select once, starting with this filter table
> first
> 3) I could come up with a nice elegant solution which currently eludes
> me.
> Any suggestions?
> Will
>|||Uri,
Thanks for replying.
I must admit I'm having trouble fully understanding what the aim of the
change is. As I see it if I pass something not null as @.filterCol1 then
I won't get any results back. This isn't what I wanted. Essentially the
point of the @.filtercol1 was to indicate that I'm using optional
parameters to filter it, so sometimes I want to filter on @.filterCol1,
sometimes the user might not select any filter for that, in which case
I'll want to filter on another variable @.filterCol2 (sorry I should
have put a fuller example in, I was abreviating it and only using one
variable). This behaviour in itself isn't causing me any problems. My
issue is that if the user only selects a parameter which filters on
tableD, I need this to be used to optimise my query. e.g.:
let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
= TableB.Col1)
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
= TableD.Col2)
I need my sql query to realise that TableD is going to be the bounding
result set, evaluate this one first, then use that to restrict the
scope of the joins on the other 3 tables. However if the user were to
put a value into @.filterCol1 and null for @.filterCol2 then I would need
it to evaluate tableB first as this will be the smallest result set.
I don't think there's going to be a perfect answer, but this problem
must be encountered a lot.
Will|||Will
Well if I understood you cannot dictate to optimizer what order of joins to
be chosen. Yes, there are some JOIN hints but not in this case I think. Have
you considered ( I don't know your business requirements) to separe this
SELECT statement i mean JOIN two tables/three tables based on
parameteters that you've got?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143720216.439397.65770@.t31g2000cwb.googlegroups.com...
> Uri,
> Thanks for replying.
> I must admit I'm having trouble fully understanding what the aim of the
> change is. As I see it if I pass something not null as @.filterCol1 then
> I won't get any results back. This isn't what I wanted. Essentially the
> point of the @.filtercol1 was to indicate that I'm using optional
> parameters to filter it, so sometimes I want to filter on @.filterCol1,
> sometimes the user might not select any filter for that, in which case
> I'll want to filter on another variable @.filterCol2 (sorry I should
> have put a fuller example in, I was abreviating it and only using one
> variable). This behaviour in itself isn't causing me any problems. My
> issue is that if the user only selects a parameter which filters on
> tableD, I need this to be used to optimise my query. e.g.:
> let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
> = TableB.Col1)
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
> = TableD.Col2)
> I need my sql query to realise that TableD is going to be the bounding
> result set, evaluate this one first, then use that to restrict the
> scope of the joins on the other 3 tables. However if the user were to
> put a value into @.filterCol1 and null for @.filterCol2 then I would need
> it to evaluate tableB first as this will be the smallest result set.
> I don't think there's going to be a perfect answer, but this problem
> must be encountered a lot.
> Will
>|||Uri,
Yes, splitting the select out into different ones dependent on
parameters seems to be the only way to go (but unfortunately not an
option as I won't be able to justify a potentially breaking change such
as that for performance increases). I was hoping that there would be
some nice sql trick that allows you to always hit the smaller tables
first.
Thanks for your help
Will|||If not specified otherwise (through the use of join hints) the Query
Optimizer will always choose the best order according to indexes, statistics
,
etc.
ML
http://milambda.blogspot.com/|||I know that the optimiser will try, and that I can override with join
hints, but in this case the optimiser can't help because the logic of
the join forces it to be evaluated in that order. What I was hoping for
was some kind of new way of laying out the query such that I could
"enter" my data structure from different directions depending on which
one has parameters defined to filter it. I suspect it's not possible,
but doesn't anyone else find this a problem? is there some better way
to either lay out the tables or to have filter procs with multiple
optional parameters?
Cheers
Will|||One way to filter the rows before the join is to use inline table functions,
that accept filtering values as parameters, I'm not sure, however, if this
will actually improve the performance. It's an option to consider and test.
ML
http://milambda.blogspot.com/|||"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143723585.520821.232940@.e56g2000cwe.googlegroups.com...
>I know that the optimiser will try, and that I can override with join
> hints, but in this case the optimiser can't help because the logic of
> the join forces it to be evaluated in that order. What I was hoping for
> was some kind of new way of laying out the query such that I could
> "enter" my data structure from different directions depending on which
> one has parameters defined to filter it. I suspect it's not possible,
> but doesn't anyone else find this a problem? is there some better way
> to either lay out the tables or to have filter procs with multiple
> optional parameters?
>
The problem is that SQL Server will pick one query plan and reuse it for all
parameter sets. If this is in a stored procedure, you could try to mark it
WITH RECOMPILE, or use dynamic SQL.
David|||You have hit one of the problems with SQL Server. It was built for a
"departmetn level" database on a small machine. It keeps one exectuion
plan for each stored procedure and uses that plan when the proc is
invoked. They added parameter sniffing, but that can actually hurt.
Better products, meant for enterprise level applications and VLDB hold
multiple plans (Is DB2 keeping 16 or 32 now? I do not remember). Thus
they know that when the query looks like this (I will use the @. for
parameters even tho that is not Standard SQL):
SELECT x.y.x
FROM Foobar AS F1
WHERE F1.a = COALESCE (@.p1, a)
AND F1.b = COALESCE (@.p2, b)
AND WHERE F1.c = COALESCE (@.p3, c)
SQL needs to generate 8 execution plans, one for each combination of
NULLs. The best one will be picked at run time when we know (@.p1, @.p2,
@.p3) and twher the NULLs are that will turn a search condition into a
constant TRUE, FALSE or UNKNOWN.
This is your solution #1 -- fake a good optimizer and RDBMS engine in
T-SQL by hand. You can do it with IF-THEN-ELSE control flow in T-SQL.
It is a XXXXX to maintain, but easy to write with a cut and paste in a
text editor. It can run much faster than what you have now, however.

Changing datatype: smallint to int in a large table..

Hi,

I need to change the datatype of a very large table from smallint to int...
What would be an ideal solution to get this done in least amount of time. May be I can try with ALTER but , I am not sure about the time it would take ...and the page splits etc..

pls help on the same!!

Thanks
Cheriyan.Hi,
If the table has thousands of records,definitely it will take some time to alter the datatype and in the mean time make sure that no application is running which is interacting with that table.

Madhivanan|||Thanks Madhivanan

Is there any way to get around this...any alternate solution with would reduce the total time required..|||Not really. You need to do the ALTER TABLE. Dropping any indexes that hit this column would speed it up.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.|||How can I alter my settings in dbForums so that I can be notified when an answer that I would have come up with and is known to everybody can be sent to me as if I posted it, and at the same time increase MY postings counter?|||Go to the dbforums/forumstore page and select a 1 point item and I will get it and have it sent to you. They have some new stuff in, so check it out.|||I checked. All they have left is gag balls and gimp masks in gift wrappers. Give me your address I'll send you a set ;)|||Oh, that's nasty.|||rdjabarov would suggest that you BCP the data out, create a new table, and then BCP the data back in. It's not something I've done a lot, but its worth a shot.

Are you kidding...that's the only way to FLY!

bcp out...need to do it in CHAR format,drop table, recreate with new types, and bcp back in...|||Out of curiosity, how big is the table (i.e. number of rows)? I am not sure where the turning point is for the performance of BCP vs. just issuing the command. Naturally for a table of 10 rows, you would just issue the alter table command. For a table of 10,000,000 rows, you would probably bcp. Has anyone experimented with the two methods to see where the turnover point is?|||I agree...however...the more paractice the better, especially with less data...the screw ups won't take as long to undo....

But yes, 10 would be silly|||Yes, I found the exact number of rows where BCP becomes more efficient, but I have to let rdjabarov tell you. I will tell you, however, that it is neither a prime number, palindromic, or a perfect square. ;)|||Who cares what the number is, it's how long your mgmt will allow you to keep users from production, that's the determinant. Besides, it depends on what field you ALTER (change, add, or remove), so you (blindman) don't really KNOW the exact number, alright?!|||Oh, brother....|||What? He started it again! Why do I have to come up with the number? It's Brett's job to write code!|||There is no number in these parts...get the scrubs to practice bcp all day long...|||No, no, no. There is no SPOON. There are plenty of numbers. Maybe I should have asked for an order of ten, instead?|||Yes. It's true. I don't really know the number.

Mea culpa.

But I'd still bet its not a prime...

Speaking of no spoons; I've got a stint at a bank in a division called NEO, and they just issued me a generic ID badge that just has NEO where the name should be. I'm half expecing Dr. Smith to leap out at me every time I round a corner.|||Folks, plz clear me here. Will changing the recovery model from full to BULK or Simple have effect on the performance of the alter command? SIMPLE would log all the trans through but clear the log and what about BULK-LOGGED? For i am not sure if each record for that column is written into tran-log or not. sorry for askin stupid questions. :o|||I don't think it is a stupid question. The recovery model setting would help the BCP method, but I do not think it would help the alter table method, as alter table is fully logged, anyway. The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into. Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.|||The only "non"-logged operations that I know of (off the top of my head first thing in the morning) are BCP/bulkinsert, truncate, and select into.

Ahhh...be careful...that's a common misconception that even M$ doesn't make too clear...it's logged at the page level...

Each of these, of course has some logging to them, and the indexes on the involved table would also have changes logged, so make sure you have the drop and create index scripts handy.

Very good...

It's not about the recovery model...it's about the logging

BUT..the recovery model dictates how you should be performing maintenance|||Yes. I should have made that a bit clearer, rather than just putting in the quotes around "non". My lawyer said I could get away with it, though ;-).|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.|||OK, let's just close this "logged/non-logged/page-level-logged" subject once and for all, shall we? NON-LOGGED means that it does not get written to the log file, and that's per Microsoft and all MVP's that know what they are talking about (don't remember names, but have read enough of their stuff and experimented with it to believe it to be true). The fact that you can ROLLBACK any non-logged operation does not mean that it's logged, partially logged, logged at the page level (who came up with this?), etc. Besides, size allocation for a table is not measured in pages, so when a table is truncated for example, it's the EXTENTs that get released back into the unused portion of the database. And again, going back to ROLLBACK, - you can rollback a truncate, but not because it's "partially logged or logged at the page level", but because of the ACID qualities of a transaction.

Ok then...but extents for a truncate?

What about bulk insert (or bcp)? Are you saying that it doesn't try to place the data on the appropriate page for a cluster?

So after those processes, the need to be rebuilt?

Thanks fo the info...still clear as mud...

ever find an article that discusses this...

Damn, another reason why I need to buy Kalens book...|||It's an extent, then "an appropriate page", both for BCP and BULK INSERT. If it was on a page level, - can you imagin the amount of defragmentation even in pubs?