Format Alignment: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

filter data from multiple tables crashes app!

i didnt think my sql qeury was that complicated that it would crash my web
app. all im trying to do is filter data between two tables. heres my query

<cfquery name="GetResults" datasource="#datasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.ContentID <> Content_Sites.ContentID
ORDER BY Content.ContentID DESC
</cfquery
equals works, but when i try not equals, it all goes haywire. any ideas?

TIAjonezy (jonezy@.donotmailmejonezy.com) writes:
> i didnt think my sql qeury was that complicated that it would crash my
> web app. all im trying to do is filter data between two tables. heres
> my query
><cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
></cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

Yes and no. Since I don't know your tables, and neither know what you
are trying to achieve, how could I really have any ideas?

But, OK, having seen people using <> in the wrong place before, I can
make a guess. Say that both table have a thousand rows. You are now
asking for all million combinations of these two thousand rows - save
those that have the same ID.

I guess what you are looking for is really something like:

SELECT *
FROM Content c ,
WHERE NOT EXISTS (SELECT *
FROM Content_Sites cs
WHERE c.ContentID = cs.ContentID)
ORDER BY c.ContentID DESC

That is, list all Content that does not have any content site.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <vl786sjaijmr91@.corp.supernews.com>,
jonezy@.donotmailmejonezy.com says...
> i didnt think my sql qeury was that complicated that it would crash my web
> app. all im trying to do is filter data between two tables. heres my query
> <cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
> </cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

How big are the tables? You DO realize you've asked for a
cross product? That means the database is returning the
ENTIRE contents (minus one row) of the Content_Sites table
for each ROW of the Content table.

Assuming 500 rows in Content_Sites and 1000 rows in
Content, you are getting back 500,000 rows in your query.

--
Cam|||thanks.., dunno how i overlookd NOT EXISTS.

i also realized i forgot to include the table connection between content and
content_sites. guess i was in a hurry.

thanks again!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E8B075BFAYazorman@.127.0.0.1...
> jonezy (jonezy@.donotmailmejonezy.com) writes:
> > i didnt think my sql qeury was that complicated that it would crash my
> > web app. all im trying to do is filter data between two tables. heres
> > my query
> ><cfquery name="GetResults" datasource="#datasource#">
> > SELECT *
> > FROM Content, Content_Sites
> > WHERE Content.ContentID <> Content_Sites.ContentID
> > ORDER BY Content.ContentID DESC
> ></cfquery>
> > equals works, but when i try not equals, it all goes haywire. any
ideas?
> Yes and no. Since I don't know your tables, and neither know what you
> are trying to achieve, how could I really have any ideas?
> But, OK, having seen people using <> in the wrong place before, I can
> make a guess. Say that both table have a thousand rows. You are now
> asking for all million combinations of these two thousand rows - save
> those that have the same ID.
> I guess what you are looking for is really something like:
> SELECT *
> FROM Content c ,
> WHERE NOT EXISTS (SELECT *
> FROM Content_Sites cs
> WHERE c.ContentID = cs.ContentID)
> ORDER BY c.ContentID DESC
> That is, list all Content that does not have any content site.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Filter by values from outer list region

Hello,
I am creating a report where I am using a list region to create report pages
with a few tables and graphs on each page. The dataset for the list region
contains categories of the values i want do display in the tables and
charts. The tables and charts have their own datasets, which all should be
filtered based on the category from the list region. Which changes from page
to page.
My problem is that I don't see how the inner tables and charts can be
filtered by the value of the list region?
Many thanks in advance for any help on this.
Best regards,
VemundHi Vemund Haga,
Thanks for your post.
From your descriptions, I understood that you would like to create Report
Pages dynamically. However, I am not very sure about what you want the
pages depend on, is it possible for your to generate some database sample
scripts and send a sample RDL files to me. Would you please share me more
detailed about the whole process? I would love to reproduce it on my side,
which I believe, will make us closer and quicker to the resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Vemund Haga,
I haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to collect the information. If you could get back
to me at your earliest convenience, we will be able to go ahead. If there
was some part of my post that you didn't understand, please feel free to
post here. I look forward to hearing from you.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Monday, March 26, 2012

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Fillfactor

we have 2 servers with similar tables and columns and indexes with fillfactor
0. My replication is very slow during daytime and comes to normal at night
times.when i check the reads and writes both are more that 90 % high but when
i check the page splits it shows nothing.what do i need to do to fasten my
replication.
Do i need to add fillfactor?
i didn't see any page splits though
Thanks
Pardhi
Message posted via http://www.droptable.com
If you have no page splits, then I'd start monitoring for blocking issues
during synchronization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul i didn't see any blocking either
Paul Ibison wrote:
>If you have no page splits, then I'd start monitoring for blocking issues
>during synchronization.
>Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
Message posted via http://www.droptable.com
|||Then perhaps it's just one of the the usual processor/memory/disk access
issues. You could use profiler to monitor these counters and see if this is
the issue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Friday, March 23, 2012

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.
Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.
|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.co...ng_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
--
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.com/rebuilding_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.c...ing_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.

Wednesday, March 21, 2012

Files sizes in a OLE bmp field

I have a database which stores word documents into tables as BLOB fields.
I need to work out what size each WORD document is.
Do you know how can I work out the individual size of each word document?
__________
'sp_spaceused worddoc_attached' only gives me the overall size of the data
in the table.
__________
Thank you for any help in advance
JAD
You can use the datalength function to do this. You can find more
information in books online under datalength.
-Sue
On Fri, 15 Oct 2004 16:05:27 +0100, "JAD" <listgrove@.yahoo.co.uk>
wrote:

>I have a database which stores word documents into tables as BLOB fields.
>I need to work out what size each WORD document is.
>Do you know how can I work out the individual size of each word document?
>__________
>'sp_spaceused worddoc_attached' only gives me the overall size of the data
>in the table.
>__________
>
>Thank you for any help in advance
>JAD
>

Files not filing with data

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high
contention on this file. We added 3 more files to match the processor
number. The problem is that the 3 additional files are not filling with
data. Does anyone know why this happens or can reccommend a fix?
--
willHi Will
Did you add the new files to the same file group or did you create a new
group?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will
>|||Hi
Since all your tables are in the 1st file group, SQL Server will continue to
use the 1st file group until it is full.
It does not balance over the files. You need to specifically move a table or
Index onto a file group for it to be used immediately.
Unless each file group is on a separate disk system (or LUN on a SAN), it
will not help adding file groups as the same IO contention continues to
exists.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> We have a quad sql server that runs OLTP transactions at the rate of
> 100's per second (read & Write).
> We used to have all the tables on 1 file but started to notice high
> contention on this file. We added 3 more files to match the processor
> number. The problem is that the 3 additional files are not filling with
> data. Does anyone know why this happens or can reccommend a fix?
> --
> will|||Let me correct myself:
We have a file group called 'Avail'.
In That file group we had all the tables running on 1 file.
We added 3 additional files to that filegroup and noticed they were not
filling with data. Has anyone seen this before?
--
will
"Kalen Delaney" wrote:
> Hi Will
> Did you add the new files to the same file group or did you create a new
> group?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> > We have a quad sql server that runs OLTP transactions at the rate of
> > 100's per second (read & Write).
> >
> > We used to have all the tables on 1 file but started to notice high
> > contention on this file. We added 3 more files to match the processor
> > number. The problem is that the 3 additional files are not filling with
> > data. Does anyone know why this happens or can reccommend a fix?
> > --
> > will
> >
>
>|||If a table is created on a filegroup, and that filegroup has multiple files,
all the files should be used as more data is inserted into the table.
Are you seeing that existing tables are not seeming to use the new files?
How are you determining that?
Can you try creating a new table on the filegroup and see if its data is
spread around?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"we7313" <we7313@.discussions.microsoft.com> wrote in message
news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> Let me correct myself:
> We have a file group called 'Avail'.
> In That file group we had all the tables running on 1 file.
> We added 3 additional files to that filegroup and noticed they were not
> filling with data. Has anyone seen this before?
> --
> will
>
> "Kalen Delaney" wrote:
>> Hi Will
>> Did you add the new files to the same file group or did you create a new
>> group?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "we7313" <we7313@.discussions.microsoft.com> wrote in message
>> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
>> > We have a quad sql server that runs OLTP transactions at the rate of
>> > 100's per second (read & Write).
>> >
>> > We used to have all the tables on 1 file but started to notice high
>> > contention on this file. We added 3 more files to match the processor
>> > number. The problem is that the 3 additional files are not filling
>> > with
>> > data. Does anyone know why this happens or can reccommend a fix?
>> > --
>> > will
>> >
>>
>>
>|||Yes the existing table is not using the new files in the file group.
If I go into enterprisemanager/view/taskpad I can see how big the data files
are and much data is actually in them. What I'm seeing is that 99% of the
data continues to go into the original file. I do see about 1% of data going
to the other 3 files combined.
--
will
"Kalen Delaney" wrote:
> If a table is created on a filegroup, and that filegroup has multiple files,
> all the files should be used as more data is inserted into the table.
> Are you seeing that existing tables are not seeming to use the new files?
> How are you determining that?
> Can you try creating a new table on the filegroup and see if its data is
> spread around?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> news:286B7811-24A5-4323-8A4F-52384C44C3E3@.microsoft.com...
> > Let me correct myself:
> > We have a file group called 'Avail'.
> > In That file group we had all the tables running on 1 file.
> > We added 3 additional files to that filegroup and noticed they were not
> > filling with data. Has anyone seen this before?
> > --
> > will
> >
> >
> > "Kalen Delaney" wrote:
> >
> >>
> >> Hi Will
> >>
> >> Did you add the new files to the same file group or did you create a new
> >> group?
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "we7313" <we7313@.discussions.microsoft.com> wrote in message
> >> news:AD68081F-08DD-43E6-A3C7-D5F186C1D561@.microsoft.com...
> >> > We have a quad sql server that runs OLTP transactions at the rate of
> >> > 100's per second (read & Write).
> >> >
> >> > We used to have all the tables on 1 file but started to notice high
> >> > contention on this file. We added 3 more files to match the processor
> >> > number. The problem is that the 3 additional files are not filling
> >> > with
> >> > data. Does anyone know why this happens or can reccommend a fix?
> >> > --
> >> > will
> >> >
> >>
> >>
> >>
> >>
> >
>
>

Files & FileGroups

Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.

1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go

2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
....
TO FILEGROUP filename_table
go

If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?In order to move the table to another filegroup, you need to first move the data, then move any indexes that you also want on that filegroup or another filegroup. To move the data, you need to create or alter the clustered index. To move the indicies, you need to drop and recreate the indicies.

-PatP|||thanks Pat for the heads up I will work towards that...

filegroups table

Hi!!!
I would like to know what filegroup is in the table or what tables are in the filegroup, using SELECT.
Excume my english.
Thank you.May check DBCC CHECKFILEGROUP which returns part of information required.|||It is found in sysindexes.
The data pages will be id = 0 if no clustered index or 1 if there is a clustered index.

groupid will give the filegroup id.

You can find the filegroup corresponding to the groupid in sysfiles.

Monday, March 19, 2012

Filegroups and Joins

Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
LeilaThat's correct. You cannot direct a table or index to a particular file -
only to a filegroup. What the author probably meant to say was to place the
tables into separate filegroups on different drives.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
Leila|||Leila
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I read this tip in a article:
> "For very large joins, consider placing the tables to be joined in
> separate physical files in the same filegroup"
> How can I force SQL Server to create a table in filegroup on a particular
> file? As far as I know, I can tell SQL Server to create table on desired
> filegroup, but if that filegroup contains more than one file, is it
possible
> to put table on a particular one?
> Thanks in advance.
> Leila
>|||Thanks Uri,
But you have placed two tables on two different filegroups. The author
mentioned that the tables can be placed in two different files, on the SAME
filegroup!
Leila
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Leila
> CREATE DATABASE test
> GO
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
>
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I read this tip in a article:
> > "For very large joins, consider placing the tables to be joined in
> > separate physical files in the same filegroup"
> >
> > How can I force SQL Server to create a table in filegroup on a
particular
> > file? As far as I know, I can tell SQL Server to create table on desired
> > filegroup, but if that filegroup contains more than one file, is it
> possible
> > to put table on a particular one?
> >
> > Thanks in advance.
> > Leila
> >
> >
>|||The author was wrong. You cannot place an object on a particular file, only
a particular filegroup.
The space for the object is then allocated more or less evenly across all
files in that filegroup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> Thanks Uri,
> But you have placed two tables on two different filegroups. The author
> mentioned that the tables can be placed in two different files, on the
> SAME
> filegroup!
> Leila
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> Leila
>> CREATE DATABASE test
>> GO
>> ALTER DATABASE test
>> ADD FILEGROUP ww_Group
>> GO
>> ALTER DATABASE test
>> ADD FILE
>> ( NAME = ww,
>> FILENAME = 'D:\wwdat1.ndf',
>> SIZE = 5MB,
>> MAXSIZE = 100MB,
>> FILEGROWTH = 5MB)
>> TO FILEGROUP ww_Group
>> create table test..test(id int identity) on [primary]
>> create table test..test_GR(id int identity) on ww_Group
>>
>>
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I read this tip in a article:
>> > "For very large joins, consider placing the tables to be joined in
>> > separate physical files in the same filegroup"
>> >
>> > How can I force SQL Server to create a table in filegroup on a
> particular
>> > file? As far as I know, I can tell SQL Server to create table on
>> > desired
>> > filegroup, but if that filegroup contains more than one file, is it
>> possible
>> > to put table on a particular one?
>> >
>> > Thanks in advance.
>> > Leila
>> >
>> >
>>
>|||Thanks Kalen,
What about log files? If I have more than one log file for my DB, are the
logged information written in multiple files or the log files are filled one
by one?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> The author was wrong. You cannot place an object on a particular file,
only
> a particular filegroup.
> The space for the object is then allocated more or less evenly across all
> files in that filegroup.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > Thanks Uri,
> > But you have placed two tables on two different filegroups. The author
> > mentioned that the tables can be placed in two different files, on the
> > SAME
> > filegroup!
> >
> > Leila
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> Leila
> >> CREATE DATABASE test
> >> GO
> >> ALTER DATABASE test
> >> ADD FILEGROUP ww_Group
> >> GO
> >> ALTER DATABASE test
> >> ADD FILE
> >> ( NAME = ww,
> >> FILENAME = 'D:\wwdat1.ndf',
> >> SIZE = 5MB,
> >> MAXSIZE = 100MB,
> >> FILEGROWTH = 5MB)
> >> TO FILEGROUP ww_Group
> >>
> >> create table test..test(id int identity) on [primary]
> >> create table test..test_GR(id int identity) on ww_Group
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> > Hi,
> >> > I read this tip in a article:
> >> > "For very large joins, consider placing the tables to be joined in
> >> > separate physical files in the same filegroup"
> >> >
> >> > How can I force SQL Server to create a table in filegroup on a
> > particular
> >> > file? As far as I know, I can tell SQL Server to create table on
> >> > desired
> >> > filegroup, but if that filegroup contains more than one file, is it
> >> possible
> >> > to put table on a particular one?
> >> >
> >> > Thanks in advance.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||One by one. SQL Server "rattles through the log files like a snake", where the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Love the analogy, Tibor! ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23vZOx3tEHA.1356@.TK2MSFTNGP11.phx.gbl...
One by one. SQL Server "rattles through the log files like a snake", where
the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message
news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled
one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across
all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Filegroups and indexes

Hi,
I have two physical disks that each have and NDF file(two different
filegroups: FG1 on Disk1, FG2 on disk2).
Also I have two tables that my queries always join them.
Which decision will lead to better performance:
1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes on
disk2(FG2)
2) Placing Table1 and its indexes on FG1 and placing Table2 and its indexes
on FG2.
Many thanks in advance.
LeilaThe only way to know is to test it in your exact situation. It could be
that neither will give you any better performance.
--
Andrew J. Kelly SQL MVP
"Leila" <lelas@.hotpop.com> wrote in message
news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have two physical disks that each have and NDF file(two different
> filegroups: FG1 on Disk1, FG2 on disk2).
> Also I have two tables that my queries always join them.
> Which decision will lead to better performance:
> 1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes
on
> disk2(FG2)
> 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
indexes
> on FG2.
> Many thanks in advance.
> Leila
>|||Andrew,
What conditions should be met so that it helps to increase performance?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> The only way to know is to test it in your exact situation. It could be
> that neither will give you any better performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have two physical disks that each have and NDF file(two different
> > filegroups: FG1 on Disk1, FG2 on disk2).
> > Also I have two tables that my queries always join them.
> > Which decision will lead to better performance:
> > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
indexes
> on
> > disk2(FG2)
> > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> indexes
> > on FG2.
> >
> > Many thanks in advance.
> > Leila
> >
> >
>|||That's a big subject but when it comes to disks your best bet is to see if
you have disk queue issues first. You can do this by monitoring the Avg &
current disk queues in Perfmon. Here are some links that may help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:uuRgqnApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Andrew,
> What conditions should be met so that it helps to increase performance?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > The only way to know is to test it in your exact situation. It could be
> > that neither will give you any better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Leila" <lelas@.hotpop.com> wrote in message
> > news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > > I have two physical disks that each have and NDF file(two different
> > > filegroups: FG1 on Disk1, FG2 on disk2).
> > > Also I have two tables that my queries always join them.
> > > Which decision will lead to better performance:
> > > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
> indexes
> > on
> > > disk2(FG2)
> > > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> > indexes
> > > on FG2.
> > >
> > > Many thanks in advance.
> > > Leila
> > >
> > >
> >
> >
>

filegroups and index

If i create indexes on say filegroupA that spans the X drive and create
tables on say filegroupB that spans the Y drive and if i have a clustered
index, what gets stored on FGA and what on FGB.
Does the leaf level of the Clustered index which are the data pages get
stored on FGB and does the non leaf level get stored on FGA ? and if they
are on 2 different drives or physical arrays, whats the performance impact ?
Please help me understand this
Thank youHassan
You can not seperate your table and a clustered index, you
can put non-clustered indexes in a seperate file group. If
you put a table in one filegroup, the clustered index (all
levels) will be in the same filegroup.
Regards
John
>--Original Message--
>If i create indexes on say filegroupA that spans the X
drive and create
>tables on say filegroupB that spans the Y drive and if i
have a clustered
>index, what gets stored on FGA and what on FGB.
>Does the leaf level of the Clustered index which are the
data pages get
>stored on FGB and does the non leaf level get stored on
FGA ? and if they
>are on 2 different drives or physical arrays, whats the
performance impact ?
>Please help me understand this
>Thank you
>
>.
>

Thursday, March 29, 2012

filter data from multiple tables crashes app!

Filter by values from outer list region

Monday, March 26, 2012

FILLFACTOR 100 on Empty Table

FILLFACTOR 100 on Empty Table

Fillfactor

Friday, March 23, 2012

Fill Factor

Fill Factor

Fill Factor

Wednesday, March 21, 2012

Files sizes in a OLE bmp field

Files not filing with data

Files not filing with data

Files not filing with data

Files & FileGroups

filegroups table

Monday, March 19, 2012

Filegroups and Joins

Filegroups and Joins

Filegroups and Joins

Filegroups and indexes

Filegroups and indexes

filegroups and index

Format Alignment

Blog Archive

About Me