Thanks Guys,
I understand that if the database is at a point that it's forced to autogrow
that will degrade performance while the database is growing. I want to know
what the impact of a say 90-95% full database is. This is with out an
autogrow situation. There will be updates, inserts, and reads but we never
reach full capicity. Does a database say that is 60-75% full perfom better
than a database that is 90-95% full or is there no known impact.
"TheSQLGuru" wrote:
> Best practice is to size your database proactively and only let autogrowth
> act in 'emergency' cases. Size the database to allow for 1-2 years of
> expected growth, and revisit at least every 6 months. This will allow for
> maintenance operations like index rebuilds/reorgs to be able to lay data
> down sequentially on disk for optimal performance and also avoid autogrowth
> slowdowns during peak periods.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:6D4CD763-FC56-4660-9097-8AC4E188D9AF@.microsoft.com...
>
>
Just to add more to what Kevin was getting at. Chances are (not 100%
guaranteed) that the closer you get to a full database the more the data
will be non-contiguous in the data files. When you reindex an index with
DBCC DBREINDEX or ALTER INDEX REBUILD the engine will create an entirely new
copy of the index in the file and then drop the old one when done. So first
off you need about 1.2 times the size of the index in free space just to
rebuild it. But if you only have that exact amount or anything close you
will most likely get the new index built in small fragments or pockets
within the data file(s) where ever there happened to be room. Where as with
plenty of free space the chances are much greater that you will have the
indexes built in a contiguous fashion. And if you do any range or table
scans, read - ahead's etc. this can make a big difference in performance.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:F5291FF9-8516-437D-9053-3814FB5835B1@.microsoft.com...[vbcol=seagreen]
> Thanks Guys,
> I understand that if the database is at a point that it's forced to
> autogrow
> that will degrade performance while the database is growing. I want to
> know
> what the impact of a say 90-95% full database is. This is with out an
> autogrow situation. There will be updates, inserts, and reads but we
> never
> reach full capicity. Does a database say that is 60-75% full perfom
> better
> than a database that is 90-95% full or is there no known impact.
> "TheSQLGuru" wrote:
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
Data Conversion Problem
I have tried the following in all kinds of combinations but cannot get it to
work. At this point I am not seeing the problem straight and need new eyes
to guide me.
CREATE TABLE EmpEvals
(
last_name varchar(25),
first_name varchar(25),
begin_dt datetime,
adj_beg_dt datetime,
termination_date datetime,
eval_months int
)
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
I am getting an error going from datetime to int with the CONVERT/CAST in
the SELECT statement. I've tried both CONVERT and CAST and just cannot get
the CONVERT/CAST the way SQL Server wants it. I even tried changing
eval_months to datetime and it complained. Someone PLEASE help before I have
no hair left to pull out!
TIA
MikeOn Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
wrote:
>INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
>termination_date, eval_months )
>SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
>last_name, first_name, begin_dt, adj_beg_dt, termination_date
>FROM employee
The order of the column list of the INSERT must match the order of the
column list of the SELECT. In the code above, last_name is getting
the data for eval_months, first_name is getting last_name, etc.
Roy Harvey
Beacon Falls, CT|||Hi Mike
DATEDIFF returns an INT so there should be no problem with conversion. In
fact, you shouldn't even need the CAST at all.
I think the problem is that you are returning the value for eval_ months
first in your select, but it is the last column in the table. So all your
select values are trying to go into the wrong columns, and there are
conversion errors.
Using eval_months in your SELECT only gives a column header to the result,
it does not map it to a column of the table you are inserting into. You cold
give it any column name you want and it would be ignored, since you are
inserting into a table, and not returning the SELECT result to the client.
Try this:
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT last_name, first_name, begin_dt, adj_beg_dt, termination_date,
DATEDIFF(m, begin_dt, GETDATE() )
FROM employee
HTH
Kalen Delaney, SQL Server MVP
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||Mike
How about to move an eval_months column at the beginning of the columns
list?
INSERT INTO EmpEvals (eval_months ,last_name, first_name, begin_dt,
adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||BLESS YOU!! That worked nicely. I need a vacation. I'm getting
by
facts.
Thanks.
Mike
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:urs4a21tmcnc7kj680mk4vjbp4ip1d7bfb@.
4ax.com...
> On Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
> wrote:
>
> The order of the column list of the INSERT must match the order of the
> column list of the SELECT. In the code above, last_name is getting
> the data for eval_months, first_name is getting last_name, etc.
> Roy Harvey
> Beacon Falls, CT
work. At this point I am not seeing the problem straight and need new eyes
to guide me.
CREATE TABLE EmpEvals
(
last_name varchar(25),
first_name varchar(25),
begin_dt datetime,
adj_beg_dt datetime,
termination_date datetime,
eval_months int
)
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
I am getting an error going from datetime to int with the CONVERT/CAST in
the SELECT statement. I've tried both CONVERT and CAST and just cannot get
the CONVERT/CAST the way SQL Server wants it. I even tried changing
eval_months to datetime and it complained. Someone PLEASE help before I have
no hair left to pull out!
TIA
MikeOn Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
wrote:
>INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
>termination_date, eval_months )
>SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
>last_name, first_name, begin_dt, adj_beg_dt, termination_date
>FROM employee
The order of the column list of the INSERT must match the order of the
column list of the SELECT. In the code above, last_name is getting
the data for eval_months, first_name is getting last_name, etc.
Roy Harvey
Beacon Falls, CT|||Hi Mike
DATEDIFF returns an INT so there should be no problem with conversion. In
fact, you shouldn't even need the CAST at all.
I think the problem is that you are returning the value for eval_ months
first in your select, but it is the last column in the table. So all your
select values are trying to go into the wrong columns, and there are
conversion errors.
Using eval_months in your SELECT only gives a column header to the result,
it does not map it to a column of the table you are inserting into. You cold
give it any column name you want and it would be ignored, since you are
inserting into a table, and not returning the SELECT result to the client.
Try this:
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT last_name, first_name, begin_dt, adj_beg_dt, termination_date,
DATEDIFF(m, begin_dt, GETDATE() )
FROM employee
HTH
Kalen Delaney, SQL Server MVP
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||Mike
How about to move an eval_months column at the beginning of the columns
list?
INSERT INTO EmpEvals (eval_months ,last_name, first_name, begin_dt,
adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||BLESS YOU!! That worked nicely. I need a vacation. I'm getting
byfacts.
Thanks.
Mike
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:urs4a21tmcnc7kj680mk4vjbp4ip1d7bfb@.
4ax.com...
> On Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
> wrote:
>
> The order of the column list of the INSERT must match the order of the
> column list of the SELECT. In the code above, last_name is getting
> the data for eval_months, first_name is getting last_name, etc.
> Roy Harvey
> Beacon Falls, CT
Thursday, March 8, 2012
Data Center
Can anyone point me to documentation on when you would choose Windows Data Center edition rather than other editions of Windows.
Compare the Editions of Windows Server 2003
http://www.microsoft.com/windowsserv...eeditions.mspx
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.
|||Many thanks. That's one hard page to find.
"Geoff N. Hiten" wrote:
> Compare the Editions of Windows Server 2003
> http://www.microsoft.com/windowsserv...eeditions.mspx
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Center edition rather than other editions of Windows.
>
>
|||Try using Google to search Microsoft.com. It works lots better than the
native search.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...[vbcol=seagreen]
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
http://www.microsoft.com/windowsserv...eeditions.mspx[vbcol=seagreen]
Data[vbcol=seagreen]
Compare the Editions of Windows Server 2003
http://www.microsoft.com/windowsserv...eeditions.mspx
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.
|||Many thanks. That's one hard page to find.
"Geoff N. Hiten" wrote:
> Compare the Editions of Windows Server 2003
> http://www.microsoft.com/windowsserv...eeditions.mspx
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Center edition rather than other editions of Windows.
>
>
|||Try using Google to search Microsoft.com. It works lots better than the
native search.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...[vbcol=seagreen]
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
http://www.microsoft.com/windowsserv...eeditions.mspx[vbcol=seagreen]
Data[vbcol=seagreen]
Data Center
Can anyone point me to documentation on when you would choose Windows Data Center edition rather than other editions of Windows.Compare the Editions of Windows Server 2003
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.|||Try using Google to search Microsoft.com. It works lots better than the
native search.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
> > Compare the Editions of Windows Server 2003
> >
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx
> >
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> > news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> > > Can anyone point me to documentation on when you would choose Windows
Data
> > Center edition rather than other editions of Windows.
> >
> >
> >
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.|||Try using Google to search Microsoft.com. It works lots better than the
native search.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
> > Compare the Editions of Windows Server 2003
> >
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx
> >
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> > news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> > > Can anyone point me to documentation on when you would choose Windows
Data
> > Center edition rather than other editions of Windows.
> >
> >
> >
Data Center
Can anyone point me to documentation on when you would choose Windows Data C
enter edition rather than other editions of Windows.Compare the Editions of Windows Server 2003
x" target="_blank">http://www.microsoft.com/windowsser...ns.msp
x
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.|||Many thanks. That's one hard page to find.
"Geoff N. Hiten" wrote:
> Compare the Editions of Windows Server 2003
> spx" target="_blank">http://www.microsoft.com/windowsser...ns.m
spx
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Center edition rather than other editions of Windows.
>
>|||Try using Google to search Microsoft.com. It works lots better than the
native search.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...[vbcol=seagreen]
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
>
http://www.microsoft.com/windowsser...reeditions.mspx[vbcol=se
agreen]
Data[vbcol=seagreen]
enter edition rather than other editions of Windows.Compare the Editions of Windows Server 2003
x" target="_blank">http://www.microsoft.com/windowsser...ns.msp
x
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Can anyone point me to documentation on when you would choose Windows Data
Center edition rather than other editions of Windows.|||Many thanks. That's one hard page to find.
"Geoff N. Hiten" wrote:
> Compare the Editions of Windows Server 2003
> spx" target="_blank">http://www.microsoft.com/windowsser...ns.m
spx
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Cathy S." <Cathy S.@.discussions.microsoft.com> wrote in message
> news:DF73FC99-5F5D-4457-884B-FF437BCBCFF1@.microsoft.com...
> Center edition rather than other editions of Windows.
>
>|||Try using Google to search Microsoft.com. It works lots better than the
native search.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Cathy Soloway" <Cathy Soloway@.discussions.microsoft.com> wrote in message
news:A525956A-95DA-4F97-A898-7A64EBC00CE2@.microsoft.com...[vbcol=seagreen]
> Many thanks. That's one hard page to find.
> "Geoff N. Hiten" wrote:
>
http://www.microsoft.com/windowsser...reeditions.mspx[vbcol=se
agreen]
Data[vbcol=seagreen]
Wednesday, March 7, 2012
data are not allocated in the properly NDF. Why?
Hi everyone,
The followings actions works fine. But only the first NDF (PRIMERO) is growing up.
I don't get the point at all, I though that SEGUNDO would store February, TERCERO March and
CUARTO April to December (according RANGE LEFT)
I feel that there is something very important that I did not in all of this...
I'm stuck.
--Definition table
CREATE TABLE [dbo].[DatosMensuales](
[Id] [int] NOT NULL,
[Concepto] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[FechaAlta] [datetime] NULL
) ON rangoMesFE(FechaAlta)
CREATE PARTITION FUNCTION rangoMesFP(datetime)
AS RANGE LEFT FOR VALUES('200601','200602','200603');
CREATE PARTITION SCHEME rangoMesFE
AS PARTITION rangoMesFP
TO (PRIMERO,SEGUNDO,TERCERO,CUARTO);
--loading data properly
EXEC CARGARMESES '2006-01-01 00:01:01', 'PRUEBAS ENERO', 1, 10000
EXEC CARGARMESES '2006-02-01 00:01:01', 'PRUEBAS FEBRERO', 10001,20000
EXEC CARGARMESES '2006-03-01 00:01:01', 'PRUEBAS MARZO', 20001,30000
EXEC CARGARMESES '2006-04-01 00:01:01', 'PRUEBAS ABRIL', 30001,40000
SELECT $partition.rangoMesFP('200606') --> DATA IN THE FOURTH PARTITION, OK
Does anyone have any idea about this?
When I launch sp_helpdb only NDF which belong to PRIMERO partition are altered:
pruebas 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\pruebas.mdf PRIMARY 7168 KB Unlimited 1024 KB data only
pruebas_log 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\pruebas_log.ldf NULL 1024 KB 2147483648 KB 10% log only
fich_1 3 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_1.ndf PRIMERO 4096 KB Unlimited 1024 KB data only
fich_2 4 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_2.ndf PRIMERO 4096 KB Unlimited 1024 KB data only
fich_5 5 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_5.ndf CUARTO 3072 KB Unlimited 1024 KB data only
fich_6 6 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_6.ndf QUINTO 3072 KB Unlimited 1024 KB data only
fich_3 7 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_3.ndf SEGUNDO 3072 KB Unlimited 1024 KB data only
fich_4 8 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_4.ndf TERCERO 3072 KB Unlimited 1024 KB data only
data are not allocated in the properly NDF. Why?
Hi everyone,
The followings actions works fine. But only the first NDF (PRIMERO) is growing up.
I don't get the point at all, I though that SEGUNDO would store February, TERCERO March and
CUARTO April to December (according RANGE LEFT)
I feel that there is something very important that I did not in all of this...
I'm stuck.
--Definition table
CREATE TABLE [dbo].[DatosMensuales](
[Id] [int] NOT NULL,
[Concepto] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
[FechaAlta] [datetime] NULL
) ON rangoMesFE(FechaAlta)
CREATE PARTITION FUNCTION rangoMesFP(datetime)
AS RANGE LEFT FOR VALUES('200601','200602','200603');
CREATE PARTITION SCHEME rangoMesFE
AS PARTITION rangoMesFP
TO (PRIMERO,SEGUNDO,TERCERO,CUARTO);
--loading data properly
EXEC CARGARMESES '2006-01-01 00:01:01', 'PRUEBAS ENERO', 1, 10000
EXEC CARGARMESES '2006-02-01 00:01:01', 'PRUEBAS FEBRERO', 10001,20000
EXEC CARGARMESES '2006-03-01 00:01:01', 'PRUEBAS MARZO', 20001,30000
EXEC CARGARMESES '2006-04-01 00:01:01', 'PRUEBAS ABRIL', 30001,40000
SELECT $partition.rangoMesFP('200606') --> DATA IN THE FOURTH PARTITION, OK
Does anyone have any idea about this?
When I launch sp_helpdb only NDF which belong to PRIMERO partition are altered:
pruebas 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\pruebas.mdf PRIMARY 7168 KB Unlimited 1024 KB data only
pruebas_log 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\pruebas_log.ldf NULL 1024 KB 2147483648 KB 10% log only
fich_1 3 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_1.ndf PRIMERO 4096 KB Unlimited 1024 KB data only
fich_2 4 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_2.ndf PRIMERO 4096 KB Unlimited 1024 KB data only
fich_5 5 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_5.ndf CUARTO 3072 KB Unlimited 1024 KB data only
fich_6 6 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_6.ndf QUINTO 3072 KB Unlimited 1024 KB data only
fich_3 7 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_3.ndf SEGUNDO 3072 KB Unlimited 1024 KB data only
fich_4 8 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\fich_4.ndf TERCERO 3072 KB Unlimited 1024 KB data only
Friday, February 17, 2012
CXPACKET error related to MOM process
While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.
Sysprocesses shows this it opened up 4 threads and program name is Microsoft? Reliability Analysis Service.
Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @.i_Return_Code = sp_getapplock @.Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @.LockMode = N'Exclusive', @.LockOwner = N'Session', @.LockTimeout =
Can you please help us, what could be the problem. It has been running fine till couple of days back.
--Prabhu
are you running sql2k or sql2k5? Try lowering the "degree of parallelism" (sp_configure 'max degree of parallelism', #) or using Maxdop query hint (maxdop=1) to relief the problem.
Consider updating to latest service pack if you haven't done so.
http://support.microsoft.com/kb/293232
|||
It's SQL 2K, we are running on latest service pack, version 8.00.2187.
I decreased the degree of parallelism to 3 processors, instead of 4 (MAX, in this case), now all these 3 are pegged. I couldn't use the qry hint, as I mentioned it is running DTS executable.
--Prabhu
|||If max degree parallelism is set to either zero (0) or greater than one (1), you'd still encounter parallelism issue. Try setting it to one (1) to see if it helps.
|||In that case, not more than one processor would be used for parallelism, meaning no parallelism for qry execution. Right?
We would like to make use of all existing processors for qry parallelism, too.
--Prabhu
|||That's correct. If you set 'max degree parallelism' you set it for the entire server. Thus, every query will be affected by this.
The only other option is to use query hint maxdop which affects only that query, but you've already said you can't change that.
Subscribe to:
Posts (Atom)