bigint - Arithmetic overflow

Hi,

I am getting

Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value =
1152921504606847000.000000.

when I do

select power(2,60) in query analyzer

then I tried

declare @.b bigint
select @.b = power(2,60)

same error.

I tried

select convert(bigint,power(2,60)) no luck

Is there any way to tell sql server to use bigint when it
calculate power(2,60)

Ram.This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)|||Originally posted by rdjabarov
This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)

much thanks !!!!!

bigint - Arithmetic overflow

Hi,
I am getting
Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 1152921504606847000.000000.
when I do
select power(2,60) in query analyzer
then I tried
declare @.b bigint
select @.b = power(2,60)
same error.
I tried
select convert(bigint,power(2,60)) no luck
Is there any way to tell sql server to use bigint when it
calculate power(2,60)
Ram.declare @.t bigint
select @.t = 2
select power(@.t,60)
>--Original Message--
>Hi,
>I am getting
>Server: Msg 232, Level 16, State 3, Line 1
>Arithmetic overflow error for type int, value =>1152921504606847000.000000.
>when I do
>select power(2,60) in query analyzer
>then I tried
>declare @.b bigint
>select @.b = power(2,60)
>same error.
>I tried
>select convert(bigint,power(2,60)) no luck
>Is there any way to tell sql server to use bigint when it
>calculate power(2,60)
>Ram.
>
>
>.
>|||power function will return value of same type as input
type, so instead of constant 2 (which is integer) use a
variable defined as bigint, try following:
declare @.a bigint, @.b bigint
select @.a = 2
select @.b = power(@.a,60)
print @.b
hope this helps.
>--Original Message--
>Hi,
>I am getting
>Server: Msg 232, Level 16, State 3, Line 1
>Arithmetic overflow error for type int, value =>1152921504606847000.000000.
>when I do
>select power(2,60) in query analyzer
>then I tried
>declare @.b bigint
>select @.b = power(2,60)
>same error.
>I tried
>select convert(bigint,power(2,60)) no luck
>Is there any way to tell sql server to use bigint when it
>calculate power(2,60)
>Ram.
>
>
>.
>

Big Int to Date Conversion

Hi Guys!!!
i really need your help now. i tried my level best to make some
tricks on this problem but didn't make it.
i've got a Big Int datatype column contains the "Ticks" (Long value)
and i want to convert it into DateTime but i'm not able to do it. the
value is like this 632557193645506250 i want to convert it into
datetime format.
please help me out guys. i'm using ms sql server 2000.
and front end is vs .net 2005
i want to do it into sql Query please dont ask me the reason but
because of some constraints i can not do it in .net
PLEASE HELP ME GUYS.
hi,
Lucky wrote:
> Hi Guys!!!
> i really need your help now. i tried my level best to make some
> tricks on this problem but didn't make it.
> i've got a Big Int datatype column contains the "Ticks" (Long value)
> and i want to convert it into DateTime but i'm not able to do it. the
> value is like this 632557193645506250 i want to convert it into
> datetime format.
> please help me out guys. i'm using ms sql server 2000.
> and front end is vs .net 2005
> i want to do it into sql Query please dont ask me the reason but
> because of some constraints i can not do it in .net
>
> PLEASE HELP ME GUYS.
DATEDIFF() and DATEADD() builtin functions expect a parameter of the "int"
datatype... id you perform
SET NOCOUNT ON;
DECLARE @.d datetime, @.t bigint;
SET @.t = 632557193645506250;
SELECT @.d = DATEADD (ms, @.t, '19000101');
SELECT @.d AS [date as difference in ms from 01/01/1900];
which adds the specified num of ticks to a base data value (you are required
to know in order to perform calculation), you get
Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.
date as difference in ms from 01/01/1900
NULL
as the provided "difference" exceeds the max supported value...
http://msdn.microsoft.com/library/de...da-db_3vtw.asp
if you can "convert" that big value into separated values as "days" for the
date part and "ms" (depending on the actual precision you are required to
provide) for the time part in order not to overflow the int datatype
(32767), you can easely use the DATEADD(..) function..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:

>if you can "convert" that big value into separated values as "days" for the
>date part and "ms" (depending on the actual precision you are required to
>provide) for the time part in order not to overflow the int datatype
>(32767), you can easely use the DATEADD(..) function..
Hi Andrea,
Eh? 32767 ?
I think you meant to write 2,147,483,647 here...
To Lucky:[vbcol=seagreen]
Can you tell me how long a "tick" lasts, and what the starting point for
the meaasurement is? And what is the exact date and time corresponding
to the 632557193645506250 value in your post?
Hugo Kornelis, SQL Server MVP
|||hi Hugo
Hugo Kornelis wrote:
> On Tue, 4 Apr 2006 19:18:30 +0200, Andrea Montanari wrote:
>
> Hi Andrea,
> Eh? 32767 ?
> I think you meant to write 2,147,483,647 here...
oopsss.. just working with vb6 these days and the relative "int" dimension..
LOL.. I apologise ..
thank you for poiting it out..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

best/fastest why to do this - high level

Hello
Here is table
FileID FileType DateCreated Active
1 Revised 1-Jan-06 1
1 Revised 2-Jan-06 1
1 Created 3-Jan-06 1
2 Created 4-Jan-06 1
2 Revised 5-Jan-06 1
3 Created 6-Jan-06 1
3 Revised 7-Jan-06 1
3 Revised 8-Jan-06 1
3 Revised 9-Jan-06 1
and i want to set old records Active column 0. An old record is defined as
sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside the
first one is old. I.e.
FileID FileType DateCreated Active
1 Revised 2-Jan-06 1
1 Revised 1-Jan-06 0
1 Created 3-Jan-06 0
2 Revised 5-Jan-06 1
2 Created 4-Jan-06 0
3 Revised 9-Jan-06 1
3 Revised 8-Jan-06 0
3 Revised 7-Jan-06 0
3 Created 6-Jan-06 0
now this table will keep growing and growing so here is my idea.
1. Select FileID's that have a active count > 1 where active = 1. The reason
i do this is because if i have a fileID that has not been updated in awhile
then i do not want to waste time reupdating these records.
2. get the latest records for the FileID's found in step one. I would have
to use a subquery to get the latest FileID for each found in step 1.
3. update records that to not equal the records found in step 2 where active
= 1.
Wanted to know ppls thoughs on my approch and if there is a better way of
doing it.
ThanksTry,
update t1
set active = 0
where exists (
select *
from t1 as t2
where t2.FileID = t1.FileID and t2.DateCreated > t1.DateCreated
)
go
AMB
"John Smith" wrote:

> Hello
> Here is table
> FileID FileType DateCreated Active
> 1 Revised 1-Jan-06 1
> 1 Revised 2-Jan-06 1
> 1 Created 3-Jan-06 1
> 2 Created 4-Jan-06 1
> 2 Revised 5-Jan-06 1
> 3 Created 6-Jan-06 1
> 3 Revised 7-Jan-06 1
> 3 Revised 8-Jan-06 1
> 3 Revised 9-Jan-06 1
>
> and i want to set old records Active column 0. An old record is defined as
> sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside th
e
> first one is old. I.e.
> FileID FileType DateCreated Active
> 1 Revised 2-Jan-06 1
> 1 Revised 1-Jan-06 0
> 1 Created 3-Jan-06 0
> 2 Revised 5-Jan-06 1
> 2 Created 4-Jan-06 0
> 3 Revised 9-Jan-06 1
> 3 Revised 8-Jan-06 0
> 3 Revised 7-Jan-06 0
> 3 Created 6-Jan-06 0
>
> now this table will keep growing and growing so here is my idea.
> 1. Select FileID's that have a active count > 1 where active = 1. The reas
on
> i do this is because if i have a fileID that has not been updated in awhil
e
> then i do not want to waste time reupdating these records.
> 2. get the latest records for the FileID's found in step one. I would have
> to use a subquery to get the latest FileID for each found in step 1.
> 3. update records that to not equal the records found in step 2 where acti
ve
> = 1.
>
> Wanted to know ppls thoughs on my approch and if there is a better way of
> doing it.
> Thanks
>
>|||On Wed, 24 May 2006 17:57:01 -0700, Alejandro Mesa
<AlejandroMesa@.discussions.microsoft.com> wrote:

>Try,
>update t1
>set active = 0
>where exists (
>select *
>from t1 as t2
>where t2.FileID = t1.FileID and t2.DateCreated > t1.DateCreated
> )
>go
That answer does not address the requirement to sequence on FileType
DESC before DateCreated.
Roy Harvey
Beacon Falls, CT|||How can a file be revised before it is created? Is this valid data, or a
typo? Are you concerned with the latest date for each FileID being the
active one, or do you really want to sort alphabetically descending on
FileType?
"John Smith" <zzaro@.excite.com> wrote in message
news:uo$j5G4fGHA.4784@.TK2MSFTNGP03.phx.gbl...
> Hello
> Here is table
> FileID FileType DateCreated Active
> 1 Revised 1-Jan-06 1
> 1 Revised 2-Jan-06 1
> 1 Created 3-Jan-06 1
> 2 Created 4-Jan-06 1
> 2 Revised 5-Jan-06 1
> 3 Created 6-Jan-06 1
> 3 Revised 7-Jan-06 1
> 3 Revised 8-Jan-06 1
> 3 Revised 9-Jan-06 1
>
> and i want to set old records Active column 0. An old record is defined as
> sorting by FileID ASC, FileType DSC, DateCreated DSC. Any record beside
the
> first one is old. I.e.
> FileID FileType DateCreated Active
> 1 Revised 2-Jan-06 1
> 1 Revised 1-Jan-06 0
> 1 Created 3-Jan-06 0
> 2 Revised 5-Jan-06 1
> 2 Created 4-Jan-06 0
> 3 Revised 9-Jan-06 1
> 3 Revised 8-Jan-06 0
> 3 Revised 7-Jan-06 0
> 3 Created 6-Jan-06 0
>
> now this table will keep growing and growing so here is my idea.
> 1. Select FileID's that have a active count > 1 where active = 1. The
reason
> i do this is because if i have a fileID that has not been updated in
awhile
> then i do not want to waste time reupdating these records.
> 2. get the latest records for the FileID's found in step one. I would have
> to use a subquery to get the latest FileID for each found in step 1.
> 3. update records that to not equal the records found in step 2 where
active
> = 1.
>
> Wanted to know ppls thoughs on my approch and if there is a better way of
> doing it.
> Thanks
>|||update t1
set active = 0
where active = 1 --just to avoid rows that do not need updating
and exists
(
select *
from t1 as t2
where t2.FileID = t1.FileID
and
(
(
t2.DateCreated > t1.DateCreated
and t2.FileType = t1.FileType
)
or
t2.FileType < t1.FileType
)
)
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:n31a7259n1ndr6ounml4jbmqs5hkgtbadj@.
4ax.com...
> On Wed, 24 May 2006 17:57:01 -0700, Alejandro Mesa
> <AlejandroMesa@.discussions.microsoft.com> wrote:
>
> That answer does not address the requirement to sequence on FileType
> DESC before DateCreated.
>
as
the
> Roy Harvey
> Beacon Falls, CT|||Well there are two ppl that run a program to create a "revised" file or a
"created" file.
the person that creates a "created" file does not work on the wend and
cannot run the "created" file progarm. but the person that runs the revised
can run it 24/7.
this is why i sort by file type. because if i have a "revised" file i dont
care for the "created" file
so yes i need to sort by file type.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eYlpsOAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> How can a file be revised before it is created? Is this valid data, or a
> typo? Are you concerned with the latest date for each FileID being the
> active one, or do you really want to sort alphabetically descending on
> FileType?
> "John Smith" <zzaro@.excite.com> wrote in message
> news:uo$j5G4fGHA.4784@.TK2MSFTNGP03.phx.gbl...
> the
> reason
> awhile
> active
>|||I want to only deal with fileID that have count(active) > 1 where active = 1
is there a way to do this without a having?
the query you gave me will look at every fileID where its = 1. Every fileID
will always have one record set to one. I onley want to deal with fileID
that have more then one record set to active.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ODjrAVAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
> update t1
> set active = 0
> where active = 1 --just to avoid rows that do not need updating
> and exists
> (
> select *
> from t1 as t2
> where t2.FileID = t1.FileID
> and
> (
> (
> t2.DateCreated > t1.DateCreated
> and t2.FileType = t1.FileType
> )
> or
> t2.FileType < t1.FileType
> )
> )
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:n31a7259n1ndr6ounml4jbmqs5hkgtbadj@.
4ax.com...
> as
> the
>|||You have to look at every fileId to determine if it has a count > 1, my
approach attempts identifies the "Active Row" and update all the other
records by exclusion. In any case, you have to look at the rows with
count=1 in order to exclude them. You can't know how many rows you have
without looking at those rows first.
That said, adding another criteria to the main select may affect
performance. You would have to try it both ways to see which is faster. I
think it is simply redundant, and expect it to hurt performance, but it is
worth a try.
and fileID in (select t3.fileID
from t1 as t3
group by t3.fileID
having count(t3.fileID) >1)
or:
and exists (select t3.fileID from t1 as t3
where t3.fileID = t1.fileID
group by t3.fileID
having count(t3.fileID) >1)
or:
and not exists (select t3.fileID from t1 as t3
where t3.fileID = t1.fileID
group by t3.fileID
having count(t3.fileID) =1)
"John Smith" <zzaro@.excite.com> wrote in message
news:unuhs0AgGHA.1456@.TK2MSFTNGP04.phx.gbl...
> I want to only deal with fileID that have count(active) > 1 where active =
1
> is there a way to do this without a having?
> the query you gave me will look at every fileID where its = 1. Every
fileID
> will always have one record set to one. I onley want to deal with fileID
> that have more then one record set to active.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:ODjrAVAgGHA.4940@.TK2MSFTNGP05.phx.gbl...
>

Best way to handle Index Rebuilding/Reindexing.

Hi,
Currently we defrage our indexes on a regular basis and defrag then when
statics are at a certain level. What I want to find out is:
Is there a way to add speed control during rebuilding and defragging
process? What I mean by this is that typically I run this at slow times but
if something comes up and database gets busy, I want to make sure most of
the recourses (including i/o) are taken away for the defrag/reindexing
processes and given back to data processing. Perhsp if server stays busy for
a certain amount of time, simply cancel the defrag and/or reindex job. In my
environment, I simply cannot afford to not have i/o available for realtime
transactions. I need to do this for both SQL server 2000 and for SQL server
2005. Any ideas?
Thank you.Not in current versions of SQL Server. You can affect how many CPU's are
used by rebuilding indexes with the MAXDOP. So if you don't want an index
rebuild to monopolize all the processors you can set the MAXDOP to less than
the max. But in terms of I/O there is no way to govern that. Bottom line is
that if your requirements are such that you need to reindex and do other I/O
you need to ensure your hardware can handle the max load you need to
sustain.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thank you Andrew.
Is there a way to quantify non-index-rebuilt / non-index-defrag activity? I
am thinking while performing index maintenance, I can run another job that
keeps track of other traffic. If it exceeds a pre-set threshold, it will
kill index jobs.
Unfortunately changing hardware to support all my load is currently not
possible as I have over 150 SQL servers, each at a different site and
replacing them will mean $$$$$$.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
> Not in current versions of SQL Server. You can affect how many CPU's are
> used by rebuilding indexes with the MAXDOP. So if you don't want an index
> rebuild to monopolize all the processors you can set the MAXDOP to less
> than the max. But in terms of I/O there is no way to govern that. Bottom
> line is that if your requirements are such that you need to reindex and do
> other I/O you need to ensure your hardware can handle the max load you
> need to sustain.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||first, SQL 2005 Enterprise provides some option for online index operation,
these options are optimized to reduce the impact on the server.
second, when a server is "IDLE" starting an index defrag will change the
status from idle to heavy IO, so this impact your other processes. if you
want to stop the index action when there is activity, you will be stopped
because you generate activities with the defrag himself. so its hard to
detect if the activity is user based or job based.
(you can use sys.dm_exec_connections to know what is the IO generated by the
defrag process, but you have to do a lot of calculations and you have to
know what is the connectionid of the job... start to become complicated)
Maybe you can test the number of users connected to the database, if there
is too many users, wait a minute and test again...
so you have a first job which is the defrag job himself.
then you create another job, a monitoring activity job, this one will stop
the first job if there is too many users connected to the database; also you
can add a schedule to start again the first job 1hour later for example
using the monitoring job task.
you can also test the wait duration of the user sessions, if the wait
duration is too high and you defrag job is in progress, then you'll stop the
job. (sys.dm_os_waiting_tasks dynamic view)
hope this will give you some starting point...
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thank you Jeje for your suggestions. I like the wait duration concept. I
will have to give it a try.
Unfortunately, I will not b e able to use the "number of users" method. None
of my users connect directly to my databases. We have application servers
that sit in the middle. All my users connect to application server which
in-turn talk to databases. So, my number of connections are pretty much
same.
Thanks.
"Jeje" <willgart@.hotmail.com> wrote in message
news:AECDD977-BA04-46A6-8BA6-6871E2DB3786@.microsoft.com...
> first, SQL 2005 Enterprise provides some option for online index
> operation, these options are optimized to reduce the impact on the server.
> second, when a server is "IDLE" starting an index defrag will change the
> status from idle to heavy IO, so this impact your other processes. if you
> want to stop the index action when there is activity, you will be stopped
> because you generate activities with the defrag himself. so its hard to
> detect if the activity is user based or job based.
> (you can use sys.dm_exec_connections to know what is the IO generated by
> the defrag process, but you have to do a lot of calculations and you have
> to know what is the connectionid of the job... start to become
> complicated)
>
> Maybe you can test the number of users connected to the database, if there
> is too many users, wait a minute and test again...
> so you have a first job which is the defrag job himself.
> then you create another job, a monitoring activity job, this one will stop
> the first job if there is too many users connected to the database; also
> you can add a schedule to start again the first job 1hour later for
> example using the monitoring job task.
> you can also test the wait duration of the user sessions, if the wait
> duration is too high and you defrag job is in progress, then you'll stop
> the job. (sys.dm_os_waiting_tasks dynamic view)
> hope this will give you some starting point...
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||Killing a DBREINDEX job will roll back all the changes so it would probably
be better to let it finish. If you go the DEFRAG route you can stop that at
any time with no ill effect. But be aware that this command is always fully
logged and depending on how fragmented the table is to begin with it may log
much more than the index size itself when running. The Defrag operation is
always single threaded so you don't have CPU contention on servers with
multiple CPU's (not counting HT) but you still have lots of I/O. The best
defense for this kind of issue is to make sure the log file are on their own
Raid 1 or 10 and the data is on another Raid 10. If tempdb is on yet another
raid you can use the sort in Tempdb option for the rebuilds. But again the
bottom line is the hardware must be up to the task, you can't squeeze blood
from a stone. Another key to minimizing these types of issues are to only
rebuild or defrag the indexes that absolutely need it. Don't rebuild them
all each time. Look first to see which are fragmented beyond a certain point
and only do those. There are examples in BOL under DBCC SHOWCONTIG and the
2005 replacement for how to do this.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
> Thank you Andrew.
> Is there a way to quantify non-index-rebuilt / non-index-defrag activity?
> I am thinking while performing index maintenance, I can run another job
> that keeps track of other traffic. If it exceeds a pre-set threshold, it
> will kill index jobs.
> Unfortunately changing hardware to support all my load is currently not
> possible as I have over 150 SQL servers, each at a different site and
> replacing them will mean $$$$$$.
> Thanks.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's are
>> used by rebuilding indexes with the MAXDOP. So if you don't want an index
>> rebuild to monopolize all the processors you can set the MAXDOP to less
>> than the max. But in terms of I/O there is no way to govern that. Bottom
>> line is that if your requirements are such that you need to reindex and
>> do other I/O you need to ensure your hardware can handle the max load you
>> need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Thank you Andrew.
Currently we have a scheduled job that determines whether or not an index
needs to be defraged. If it does, it performs the defrag. Rebuilding is done
manually only. I would like to add some sort of intelligence into my defrag
script.
Say for example, I have 100 indexes on between all my databases and scripts
see that 5 of them need to be defraged. This might not be an issue but if
one day 20 of them need to be defraged, this might be an issue. Is there any
other way to control I/O a little better? I am not CPU bound by any mean on
any of my sites. Disk bound? Yes for sure. But short of replacing all
existing systems and adding clusters ($ in millions), I need to come up with
some method better than what I have.
Thanks for any help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
> Killing a DBREINDEX job will roll back all the changes so it would
> probably be better to let it finish. If you go the DEFRAG route you can
> stop that at any time with no ill effect. But be aware that this command
> is always fully logged and depending on how fragmented the table is to
> begin with it may log much more than the index size itself when running.
> The Defrag operation is always single threaded so you don't have CPU
> contention on servers with multiple CPU's (not counting HT) but you still
> have lots of I/O. The best defense for this kind of issue is to make sure
> the log file are on their own Raid 1 or 10 and the data is on another Raid
> 10. If tempdb is on yet another raid you can use the sort in Tempdb option
> for the rebuilds. But again the bottom line is the hardware must be up to
> the task, you can't squeeze blood from a stone. Another key to minimizing
> these types of issues are to only rebuild or defrag the indexes that
> absolutely need it. Don't rebuild them all each time. Look first to see
> which are fragmented beyond a certain point and only do those. There are
> examples in BOL under DBCC SHOWCONTIG and the 2005 replacement for how to
> do this.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag activity?
>> I am thinking while performing index maintenance, I can run another job
>> that keeps track of other traffic. If it exceeds a pre-set threshold, it
>> will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's are
>> used by rebuilding indexes with the MAXDOP. So if you don't want an
>> index rebuild to monopolize all the processors you can set the MAXDOP to
>> less than the max. But in terms of I/O there is no way to govern that.
>> Bottom line is that if your requirements are such that you need to
>> reindex and do other I/O you need to ensure your hardware can handle the
>> max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||As has already been noted, once you're in a rebuild, not much. However, I've
been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY & @.@.IO_BUSY
to determin server load before starting something running. Haven't tried it
yet.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Currently we defrage our indexes on a regular basis and defrag then when
> statics are at a certain level. What I want to find out is:
> Is there a way to add speed control during rebuilding and defragging
> process? What I mean by this is that typically I run this at slow times
> but if something comes up and database gets busy, I want to make sure most
> of the recourses (including i/o) are taken away for the defrag/reindexing
> processes and given back to data processing. Perhsp if server stays busy
> for a certain amount of time, simply cancel the defrag and/or reindex job.
> In my environment, I simply cannot afford to not have i/o available for
> realtime transactions. I need to do this for both SQL server 2000 and for
> SQL server 2005. Any ideas?
> Thank you.
>|||Thanks Jay.
Do you know of a way to pause defrag? Perhaps I can put it through a loop
and let it run for a few seconds and then pause it for a second so that
other transactions can get through... Just an idea.
"Jay" <spam@.nospam.org> wrote in message
news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
> As has already been noted, once you're in a rebuild, not much. However,
> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
> @.@.IO_BUSY to determin server load before starting something running.
> Haven't tried it yet.
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not have
>> i/o available for realtime transactions. I need to do this for both SQL
>> server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>|||not possible to pause a defrag.
use the reorganize option:
alter index reorganize
which is an online operation and don't lock the end user.
or use
alter index rebuild with (online = on)
I don't remember which one use a transaction and which one don't use a
transaction.
so without a transaction, stopping the defrag process don't loose the
current defragmented pages, while in a transaction... its lost.
online operation are designed to reduce the impact to the end user, there is
no lock.
so if you want to pause the defrag process for just 1 second, its not
relevant to try to stop the process.
do you suffer locking issues today when you do your defrag?
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
> Thanks Jay.
> Do you know of a way to pause defrag? Perhaps I can put it through a loop
> and let it run for a few seconds and then pause it for a second so that
> other transactions can get through... Just an idea.
> "Jay" <spam@.nospam.org> wrote in message
> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Well a Cluster will do absolutely nothing for performance, it is a hardware
fail over option only. Not being CPU bound is usually another indication
that the disks are a problem. If the data can't read or write fast enough
then the CPU's can't work as hard. You can't reduce the I/O that is required
for a Defrag operation when it is doing work. As I stated you can stop the
defrag process at any time. You may want to run it for a period of time and
wait x many seconds or minutes in between to give others a chance to catch
up.
What is the current disk situation like? How many drives, what type of
arrays, where are the files etc?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Thank you Andrew.
> Currently we have a scheduled job that determines whether or not an index
> needs to be defraged. If it does, it performs the defrag. Rebuilding is
> done manually only. I would like to add some sort of intelligence into my
> defrag script.
> Say for example, I have 100 indexes on between all my databases and
> scripts see that 5 of them need to be defraged. This might not be an issue
> but if one day 20 of them need to be defraged, this might be an issue. Is
> there any other way to control I/O a little better? I am not CPU bound by
> any mean on any of my sites. Disk bound? Yes for sure. But short of
> replacing all existing systems and adding clusters ($ in millions), I need
> to come up with some method better than what I have.
> Thanks for any help.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this command
>> is always fully logged and depending on how fragmented the table is to
>> begin with it may log much more than the index size itself when running.
>> The Defrag operation is always single threaded so you don't have CPU
>> contention on servers with multiple CPU's (not counting HT) but you still
>> have lots of I/O. The best defense for this kind of issue is to make sure
>> the log file are on their own Raid 1 or 10 and the data is on another
>> Raid 10. If tempdb is on yet another raid you can use the sort in Tempdb
>> option for the rebuilds. But again the bottom line is the hardware must
>> be up to the task, you can't squeeze blood from a stone. Another key to
>> minimizing these types of issues are to only rebuild or defrag the
>> indexes that absolutely need it. Don't rebuild them all each time. Look
>> first to see which are fragmented beyond a certain point and only do
>> those. There are examples in BOL under DBCC SHOWCONTIG and the 2005
>> replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want an
>> index rebuild to monopolize all the processors you can set the MAXDOP
>> to less than the max. But in terms of I/O there is no way to govern
>> that. Bottom line is that if your requirements are such that you need
>> to reindex and do other I/O you need to ensure your hardware can handle
>> the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to make
>> sure most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford
>> to not have i/o available for realtime transactions. I need to do this
>> for both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||Since I have so many SQL servers at various different sites installed over
the years, each site is different when it comes to disk situcation. In old
days, we used Legato based clustering which is a disk hog on its own. In
that case we may have only 6 disks in a flat raid 5. I know this is probably
the worst setup you can do, but this is how it is on those sites and short
of adding an external array with more disks, there is only so much I can do.
Newer sites use MS clustering and typically have 14-24 disks in various
config.
My concerns is not with sites that are on MS Clustering, but instead with
older sites (with Legato Clustering). In most cases each server is dual CPU
single core or dual core in newer sites. So I am sure none of the sites are
CPU bound. Disk bound is a different issue.All my Legato based sites are
disk bound. In Legato clusters, there are no shared disks but instead one of
the logical drives is mirrored accross servers to create a cluster. This
mirroring create additional load on the i/o subsystem. This is why I said
that short of installing new clusters, I can't do much. All my sql servers
are some type of cluster for redundency. Most are still on SQL 2000.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Well a Cluster will do absolutely nothing for performance, it is a
> hardware fail over option only. Not being CPU bound is usually another
> indication that the disks are a problem. If the data can't read or write
> fast enough then the CPU's can't work as hard. You can't reduce the I/O
> that is required for a Defrag operation when it is doing work. As I stated
> you can stop the defrag process at any time. You may want to run it for a
> period of time and wait x many seconds or minutes in between to give
> others a chance to catch up.
> What is the current disk situation like? How many drives, what type of
> arrays, where are the files etc?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an index
>> needs to be defraged. If it does, it performs the defrag. Rebuilding is
>> done manually only. I would like to add some sort of intelligence into my
>> defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this command
>> is always fully logged and depending on how fragmented the table is to
>> begin with it may log much more than the index size itself when running.
>> The Defrag operation is always single threaded so you don't have CPU
>> contention on servers with multiple CPU's (not counting HT) but you
>> still have lots of I/O. The best defense for this kind of issue is to
>> make sure the log file are on their own Raid 1 or 10 and the data is on
>> another Raid 10. If tempdb is on yet another raid you can use the sort
>> in Tempdb option for the rebuilds. But again the bottom line is the
>> hardware must be up to the task, you can't squeeze blood from a stone.
>> Another key to minimizing these types of issues are to only rebuild or
>> defrag the indexes that absolutely need it. Don't rebuild them all each
>> time. Look first to see which are fragmented beyond a certain point and
>> only do those. There are examples in BOL under DBCC SHOWCONTIG and the
>> 2005 replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently not
>> possible as I have over 150 SQL servers, each at a different site and
>> replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>> an index rebuild to monopolize all the processors you can set the
>> MAXDOP to less than the max. But in terms of I/O there is no way to
>> govern that. Bottom line is that if your requirements are such that
>> you need to reindex and do other I/O you need to ensure your hardware
>> can handle the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to
>> make sure most of the recourses (including i/o) are taken away for
>> the defrag/reindexing processes and given back to data processing.
>> Perhsp if server stays busy for a certain amount of time, simply
>> cancel the defrag and/or reindex job. In my environment, I simply
>> cannot afford to not have i/o available for realtime transactions. I
>> need to do this for both SQL server 2000 and for SQL server 2005. Any
>> ideas?
>> Thank you.
>>
>>
>>
>|||Thank you jeje. Unfortunately online reindexing is only available in SQL
2005 Ent. Most of my servers are still on SQL 2000 Std.
I experience a lot of delays in response when defrag is active. At that time
I am disk bound. I know I need faster/better disk system but until that is
done (over 100 servers), I need to find some type of software solution.
"Jeje" <willgart@.hotmail.com> wrote in message
news:36C4CB13-2DEE-4E42-8FAC-55C41AA78B23@.microsoft.com...
> not possible to pause a defrag.
> use the reorganize option:
> alter index reorganize
> which is an online operation and don't lock the end user.
> or use
> alter index rebuild with (online = on)
> I don't remember which one use a transaction and which one don't use a
> transaction.
> so without a transaction, stopping the defrag process don't loose the
> current defragmented pages, while in a transaction... its lost.
> online operation are designed to reduce the impact to the end user, there
> is no lock.
> so if you want to pause the defrag process for just 1 second, its not
> relevant to try to stop the process.
> do you suffer locking issues today when you do your defrag?
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a loop
>> and let it run for a few seconds and then pause it for a second so that
>> other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||alter index reorganize is the option which not loose any job when you stop
it.
and the BOL says it's always done online. so the online option cannot be
changed and this method will works on both std and ent. editions.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:ulAY6rY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thank you jeje. Unfortunately online reindexing is only available in SQL
> 2005 Ent. Most of my servers are still on SQL 2000 Std.
> I experience a lot of delays in response when defrag is active. At that
> time I am disk bound. I know I need faster/better disk system but until
> that is done (over 100 servers), I need to find some type of software
> solution.
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:36C4CB13-2DEE-4E42-8FAC-55C41AA78B23@.microsoft.com...
>> not possible to pause a defrag.
>> use the reorganize option:
>> alter index reorganize
>> which is an online operation and don't lock the end user.
>> or use
>> alter index rebuild with (online = on)
>> I don't remember which one use a transaction and which one don't use a
>> transaction.
>> so without a transaction, stopping the defrag process don't loose the
>> current defragmented pages, while in a transaction... its lost.
>> online operation are designed to reduce the impact to the end user, there
>> is no lock.
>> so if you want to pause the defrag process for just 1 second, its not
>> relevant to try to stop the process.
>> do you suffer locking issues today when you do your defrag?
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a
>> loop and let it run for a few seconds and then pause it for a second so
>> that other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow
>> times but if something comes up and database gets busy, I want to make
>> sure most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford
>> to not have i/o available for realtime transactions. I need to do this
>> for both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>>
>|||Well you are right in that a single Raid 5 with all the files on it is the
worst situation in terms of performance short of a single drive with no
array. Raid 5's are notoriously bad for high write type operations. How
large are these tables? If they are small enough you can try changing the
recovery mode to SIMPLE or Bulk Logged and use DBCC DBREINDEX. This will
generate minimal activity in the log unlike a Defrag which can have a
tremendous amount of activity. The operation will be cleaner and potentially
faster depending ont he table size, number of indexes and how badly
fragmented they are over a defrag operation. The down side is this is an off
line operation in 2000. But again if the tables are small enough they may
only be inaccessible for brief periods of time and cause minor blocking.
This will reduce some of the I/O due to the decreased Log activity.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:uG1ZZmY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Since I have so many SQL servers at various different sites installed over
> the years, each site is different when it comes to disk situcation. In old
> days, we used Legato based clustering which is a disk hog on its own. In
> that case we may have only 6 disks in a flat raid 5. I know this is
> probably the worst setup you can do, but this is how it is on those sites
> and short of adding an external array with more disks, there is only so
> much I can do. Newer sites use MS clustering and typically have 14-24
> disks in various config.
> My concerns is not with sites that are on MS Clustering, but instead with
> older sites (with Legato Clustering). In most cases each server is dual
> CPU single core or dual core in newer sites. So I am sure none of the
> sites are CPU bound. Disk bound is a different issue.All my Legato based
> sites are disk bound. In Legato clusters, there are no shared disks but
> instead one of the logical drives is mirrored accross servers to create a
> cluster. This mirroring create additional load on the i/o subsystem. This
> is why I said that short of installing new clusters, I can't do much. All
> my sql servers are some type of cluster for redundency. Most are still on
> SQL 2000.
> Thanks.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well a Cluster will do absolutely nothing for performance, it is a
>> hardware fail over option only. Not being CPU bound is usually another
>> indication that the disks are a problem. If the data can't read or write
>> fast enough then the CPU's can't work as hard. You can't reduce the I/O
>> that is required for a Defrag operation when it is doing work. As I
>> stated you can stop the defrag process at any time. You may want to run
>> it for a period of time and wait x many seconds or minutes in between to
>> give others a chance to catch up.
>> What is the current disk situation like? How many drives, what type of
>> arrays, where are the files etc?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an
>> index needs to be defraged. If it does, it performs the defrag.
>> Rebuilding is done manually only. I would like to add some sort of
>> intelligence into my defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you can
>> stop that at any time with no ill effect. But be aware that this
>> command is always fully logged and depending on how fragmented the
>> table is to begin with it may log much more than the index size itself
>> when running. The Defrag operation is always single threaded so you
>> don't have CPU contention on servers with multiple CPU's (not counting
>> HT) but you still have lots of I/O. The best defense for this kind of
>> issue is to make sure the log file are on their own Raid 1 or 10 and
>> the data is on another Raid 10. If tempdb is on yet another raid you
>> can use the sort in Tempdb option for the rebuilds. But again the
>> bottom line is the hardware must be up to the task, you can't squeeze
>> blood from a stone. Another key to minimizing these types of issues are
>> to only rebuild or defrag the indexes that absolutely need it. Don't
>> rebuild them all each time. Look first to see which are fragmented
>> beyond a certain point and only do those. There are examples in BOL
>> under DBCC SHOWCONTIG and the 2005 replacement for how to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a pre-set
>> threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently
>> not possible as I have over 150 SQL servers, each at a different site
>> and replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> Not in current versions of SQL Server. You can affect how many CPU's
>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>> an index rebuild to monopolize all the processors you can set the
>> MAXDOP to less than the max. But in terms of I/O there is no way to
>> govern that. Bottom line is that if your requirements are such that
>> you need to reindex and do other I/O you need to ensure your hardware
>> can handle the max load you need to sustain.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Currently we defrage our indexes on a regular basis and defrag then
>>> when statics are at a certain level. What I want to find out is:
>>>
>>> Is there a way to add speed control during rebuilding and defragging
>>> process? What I mean by this is that typically I run this at slow
>>> times but if something comes up and database gets busy, I want to
>>> make sure most of the recourses (including i/o) are taken away for
>>> the defrag/reindexing processes and given back to data processing.
>>> Perhsp if server stays busy for a certain amount of time, simply
>>> cancel the defrag and/or reindex job. In my environment, I simply
>>> cannot afford to not have i/o available for realtime transactions. I
>>> need to do this for both SQL server 2000 and for SQL server 2005.
>>> Any ideas?
>>>
>>> Thank you.
>>>
>>
>>
>>
>|||All my servers are in Simple mode. There is no way I have have it full and
have enough storage. Table sizes differ greatlely. It is a high transaction
environment so data gets added and changes a lot. Most of the data is
removed from the system after 3 months via (daily) automated jobs. This
keeps the system a bit lean. Total disk usage for all my databases is never
more than 50GB (per server, per site). This is 24/7/365 operation so most of
the offline stuff can't be done. I have only "slow" times but no close
times. I can probably reindex small tables but for larger ones I have to
think twice. This is why we try to defrag indexes as much as possible (only
the ones that need it) and reindex only when defrag is useless.
I know it seems like I am trying to make it hard for anyone to propose any
solution, but unfortunatley this is the environment I have to deal with.
Thanks for all your help.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eZVRqha8HHA.5404@.TK2MSFTNGP02.phx.gbl...
> Well you are right in that a single Raid 5 with all the files on it is the
> worst situation in terms of performance short of a single drive with no
> array. Raid 5's are notoriously bad for high write type operations. How
> large are these tables? If they are small enough you can try changing the
> recovery mode to SIMPLE or Bulk Logged and use DBCC DBREINDEX. This will
> generate minimal activity in the log unlike a Defrag which can have a
> tremendous amount of activity. The operation will be cleaner and
> potentially faster depending ont he table size, number of indexes and how
> badly fragmented they are over a defrag operation. The down side is this
> is an off line operation in 2000. But again if the tables are small enough
> they may only be inaccessible for brief periods of time and cause minor
> blocking. This will reduce some of the I/O due to the decreased Log
> activity.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:uG1ZZmY8HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Since I have so many SQL servers at various different sites installed
>> over the years, each site is different when it comes to disk situcation.
>> In old days, we used Legato based clustering which is a disk hog on its
>> own. In that case we may have only 6 disks in a flat raid 5. I know this
>> is probably the worst setup you can do, but this is how it is on those
>> sites and short of adding an external array with more disks, there is
>> only so much I can do. Newer sites use MS clustering and typically have
>> 14-24 disks in various config.
>> My concerns is not with sites that are on MS Clustering, but instead with
>> older sites (with Legato Clustering). In most cases each server is dual
>> CPU single core or dual core in newer sites. So I am sure none of the
>> sites are CPU bound. Disk bound is a different issue.All my Legato based
>> sites are disk bound. In Legato clusters, there are no shared disks but
>> instead one of the logical drives is mirrored accross servers to create a
>> cluster. This mirroring create additional load on the i/o subsystem. This
>> is why I said that short of installing new clusters, I can't do much. All
>> my sql servers are some type of cluster for redundency. Most are still on
>> SQL 2000.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23gQVAgV8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Well a Cluster will do absolutely nothing for performance, it is a
>> hardware fail over option only. Not being CPU bound is usually another
>> indication that the disks are a problem. If the data can't read or write
>> fast enough then the CPU's can't work as hard. You can't reduce the I/O
>> that is required for a Defrag operation when it is doing work. As I
>> stated you can stop the defrag process at any time. You may want to run
>> it for a period of time and wait x many seconds or minutes in between to
>> give others a chance to catch up.
>> What is the current disk situation like? How many drives, what type of
>> arrays, where are the files etc?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:%2373ozBQ8HHA.2752@.TK2MSFTNGP06.phx.gbl...
>> Thank you Andrew.
>> Currently we have a scheduled job that determines whether or not an
>> index needs to be defraged. If it does, it performs the defrag.
>> Rebuilding is done manually only. I would like to add some sort of
>> intelligence into my defrag script.
>> Say for example, I have 100 indexes on between all my databases and
>> scripts see that 5 of them need to be defraged. This might not be an
>> issue but if one day 20 of them need to be defraged, this might be an
>> issue. Is there any other way to control I/O a little better? I am not
>> CPU bound by any mean on any of my sites. Disk bound? Yes for sure. But
>> short of replacing all existing systems and adding clusters ($ in
>> millions), I need to come up with some method better than what I have.
>> Thanks for any help.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OE5X03I8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Killing a DBREINDEX job will roll back all the changes so it would
>> probably be better to let it finish. If you go the DEFRAG route you
>> can stop that at any time with no ill effect. But be aware that this
>> command is always fully logged and depending on how fragmented the
>> table is to begin with it may log much more than the index size itself
>> when running. The Defrag operation is always single threaded so you
>> don't have CPU contention on servers with multiple CPU's (not counting
>> HT) but you still have lots of I/O. The best defense for this kind of
>> issue is to make sure the log file are on their own Raid 1 or 10 and
>> the data is on another Raid 10. If tempdb is on yet another raid you
>> can use the sort in Tempdb option for the rebuilds. But again the
>> bottom line is the hardware must be up to the task, you can't squeeze
>> blood from a stone. Another key to minimizing these types of issues
>> are to only rebuild or defrag the indexes that absolutely need it.
>> Don't rebuild them all each time. Look first to see which are
>> fragmented beyond a certain point and only do those. There are
>> examples in BOL under DBCC SHOWCONTIG and the 2005 replacement for how
>> to do this.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:O6r5gmD8HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> Thank you Andrew.
>> Is there a way to quantify non-index-rebuilt / non-index-defrag
>> activity? I am thinking while performing index maintenance, I can run
>> another job that keeps track of other traffic. If it exceeds a
>> pre-set threshold, it will kill index jobs.
>> Unfortunately changing hardware to support all my load is currently
>> not possible as I have over 150 SQL servers, each at a different site
>> and replacing them will mean $$$$$$.
>> Thanks.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uShzc9C8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>>> Not in current versions of SQL Server. You can affect how many CPU's
>>> are used by rebuilding indexes with the MAXDOP. So if you don't want
>>> an index rebuild to monopolize all the processors you can set the
>>> MAXDOP to less than the max. But in terms of I/O there is no way to
>>> govern that. Bottom line is that if your requirements are such that
>>> you need to reindex and do other I/O you need to ensure your
>>> hardware can handle the max load you need to sustain.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Currently we defrage our indexes on a regular basis and defrag then
>>> when statics are at a certain level. What I want to find out is:
>>>
>>> Is there a way to add speed control during rebuilding and
>>> defragging process? What I mean by this is that typically I run
>>> this at slow times but if something comes up and database gets
>>> busy, I want to make sure most of the recourses (including i/o) are
>>> taken away for the defrag/reindexing processes and given back to
>>> data processing. Perhsp if server stays busy for a certain amount
>>> of time, simply cancel the defrag and/or reindex job. In my
>>> environment, I simply cannot afford to not have i/o available for
>>> realtime transactions. I need to do this for both SQL server 2000
>>> and for SQL server 2005. Any ideas?
>>>
>>> Thank you.
>>>
>>>
>>
>>
>>
>>
>|||Dragon (noSpam_Badill@.hotmail.com) writes:
> All my servers are in Simple mode. There is no way I have have it full
> and have enough storage. Table sizes differ greatlely. It is a high
> transaction environment so data gets added and changes a lot. Most of
> the data is removed from the system after 3 months via (daily) automated
> jobs. This keeps the system a bit lean. Total disk usage for all my
> databases is never more than 50GB (per server, per site). This is
> 24/7/365 operation so most of the offline stuff can't be done. I have
> only "slow" times but no close times. I can probably reindex small
> tables but for larger ones I have to think twice. This is why we try to
> defrag indexes as much as possible (only the ones that need it) and
> reindex only when defrag is useless.
A 24/7/365 high-transaction environment with simple recovery? Interesting.
Very interesting. "Yes, we have a lot of transactions, but if a disk
crashes, we can easily recreate them once we restored a backup. Or we
don't care that we lose them."
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||For what ever reasons you have let the environment drive the other factors
such as recovery mode, maintenance etc. As we have been saying over and over
again you simply can't achieve peak performance and concurrency without the
proper hardware to begin with no matter how much you want to. The fact that
you have all of the files on a single Raid 5 will limit the I/O you can do
with SQL Server. At the very least you need to separate the Logs from the
Data onto their own separate physical Raid 1 or Raid 10 array. Adding just
two disks to a system like this and moving all the log files will likely
yield a tremendous gain in throughput. If these systems have caching
controllers then make sure the cache is set to 100% write back and 0% read.
Add as much cache as possible. But other than that there is little you can
do other than what has already been suggested. Erland highlights an
excellent point about the logs. Running in Simple mode can not be driven by
lack of disk space. It is the business requirements that must drive these
decisions. Can you really afford to loose a days worth of transactions?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99A56ECB396FDYazorman@.127.0.0.1...
> Dragon (noSpam_Badill@.hotmail.com) writes:
>> All my servers are in Simple mode. There is no way I have have it full
>> and have enough storage. Table sizes differ greatlely. It is a high
>> transaction environment so data gets added and changes a lot. Most of
>> the data is removed from the system after 3 months via (daily) automated
>> jobs. This keeps the system a bit lean. Total disk usage for all my
>> databases is never more than 50GB (per server, per site). This is
>> 24/7/365 operation so most of the offline stuff can't be done. I have
>> only "slow" times but no close times. I can probably reindex small
>> tables but for larger ones I have to think twice. This is why we try to
>> defrag indexes as much as possible (only the ones that need it) and
>> reindex only when defrag is useless.
> A 24/7/365 high-transaction environment with simple recovery? Interesting.
> Very interesting. "Yes, we have a lot of transactions, but if a disk
> crashes, we can easily recreate them once we restored a backup. Or we
> don't care that we lose them."
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Pause a defrag, no. Defrag one index at a time and spread the load over n
days, sure.
But then, so do you.
"Dragon" <noSpam_Badill@.hotmail.com> wrote in message
news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
> Thanks Jay.
> Do you know of a way to pause defrag? Perhaps I can put it through a loop
> and let it run for a few seconds and then pause it for a second so that
> other transactions can get through... Just an idea.
> "Jay" <spam@.nospam.org> wrote in message
> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then when
>> statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp if
>> server stays busy for a certain amount of time, simply cancel the defrag
>> and/or reindex job. In my environment, I simply cannot afford to not
>> have i/o available for realtime transactions. I need to do this for both
>> SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>|||Thank you Jay.
"Jay" <spam@.nospam.org> wrote in message
news:ezYWDxn8HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Pause a defrag, no. Defrag one index at a time and spread the load over n
> days, sure.
> But then, so do you.
> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
> news:eJ1bBKV8HHA.5504@.TK2MSFTNGP02.phx.gbl...
>> Thanks Jay.
>> Do you know of a way to pause defrag? Perhaps I can put it through a loop
>> and let it run for a few seconds and then pause it for a second so that
>> other transactions can get through... Just an idea.
>> "Jay" <spam@.nospam.org> wrote in message
>> news:OdIzD4U8HHA.1208@.TK2MSFTNGP05.phx.gbl...
>> As has already been noted, once you're in a rebuild, not much. However,
>> I've been thinking about using a combination of: WAITFOR, @.@.CPU_BUSY &
>> @.@.IO_BUSY to determin server load before starting something running.
>> Haven't tried it yet.
>>
>> "Dragon" <noSpam_Badill@.hotmail.com> wrote in message
>> news:OQJIPvC8HHA.2208@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Currently we defrage our indexes on a regular basis and defrag then
>> when statics are at a certain level. What I want to find out is:
>> Is there a way to add speed control during rebuilding and defragging
>> process? What I mean by this is that typically I run this at slow times
>> but if something comes up and database gets busy, I want to make sure
>> most of the recourses (including i/o) are taken away for the
>> defrag/reindexing processes and given back to data processing. Perhsp
>> if server stays busy for a certain amount of time, simply cancel the
>> defrag and/or reindex job. In my environment, I simply cannot afford to
>> not have i/o available for realtime transactions. I need to do this for
>> both SQL server 2000 and for SQL server 2005. Any ideas?
>> Thank you.
>>
>>
>|||I think you have to understand the the hardware environment to decide what
can be added. Currently if I have a Legato cluster (which I do in most of my
systems) it means I do not have an external shared storage device. Legato
mirror's a server's internal drives across servers. When this was
implemented this was the best/cost saving/redundency providing solution out
there. All our new system do not have this configuration. new systems have
MS clustering with some type of shared array or SAN and have proper disk
configuration for data, logs, indexes, temp dB etc.
In older Legato system, we do have raid controllers with cache (256-512MB)
and they are currently set to 100% write. There is no possiblity of adding
additional disks as these server support a maximum of 6 disks and that's
what they got. If I want to add any additional hardware performance, I have
to go change the OS to enterprise as we as SQL to Enterprise (in SQL 2000),
add a shared disk array and create a cluster. This is a no brainer for newer
sites but older ones (and lots of them) will take time.
I know if there is no 'soft' solution or patching-up for the performance
then their isn't one. This is why I want to make sure I ask all the experts
here if there is anything I can do.
As for simple recovery vs full recover, full recovery will require me to
perform multiple backups all day long. In most cases I do not have enough
storage to make these backups. Even if I do, I am addinging additional I/O
on the disks (for backups) which in my case can be a killer. Currently I
perform one full SQL DB backups everyday which then get backed up to tape
drives. Unfortunately, I am stuck with this environment until replacement
hardware is installed at all these sites. This could be 2-3 years before all
are changed.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23JQaYgi8HHA.600@.TK2MSFTNGP05.phx.gbl...
> For what ever reasons you have let the environment drive the other factors
> such as recovery mode, maintenance etc. As we have been saying over and
> over again you simply can't achieve peak performance and concurrency
> without the proper hardware to begin with no matter how much you want to.
> The fact that you have all of the files on a single Raid 5 will limit the
> I/O you can do with SQL Server. At the very least you need to separate the
> Logs from the Data onto their own separate physical Raid 1 or Raid 10
> array. Adding just two disks to a system like this and moving all the log
> files will likely yield a tremendous gain in throughput. If these systems
> have caching controllers then make sure the cache is set to 100% write
> back and 0% read. Add as much cache as possible. But other than that there
> is little you can do other than what has already been suggested. Erland
> highlights an excellent point about the logs. Running in Simple mode can
> not be driven by lack of disk space. It is the business requirements that
> must drive these decisions. Can you really afford to loose a days worth of
> transactions?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns99A56ECB396FDYazorman@.127.0.0.1...
>> Dragon (noSpam_Badill@.hotmail.com) writes:
>> All my servers are in Simple mode. There is no way I have have it full
>> and have enough storage. Table sizes differ greatlely. It is a high
>> transaction environment so data gets added and changes a lot. Most of
>> the data is removed from the system after 3 months via (daily) automated
>> jobs. This keeps the system a bit lean. Total disk usage for all my
>> databases is never more than 50GB (per server, per site). This is
>> 24/7/365 operation so most of the offline stuff can't be done. I have
>> only "slow" times but no close times. I can probably reindex small
>> tables but for larger ones I have to think twice. This is why we try to
>> defrag indexes as much as possible (only the ones that need it) and
>> reindex only when defrag is useless.
>> A 24/7/365 high-transaction environment with simple recovery?
>> Interesting.
>> Very interesting. "Yes, we have a lot of transactions, but if a disk
>> crashes, we can easily recreate them once we restored a backup. Or we
>> don't care that we lose them."
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Dragon (noSpam_Badill@.hotmail.com) writes:
> As for simple recovery vs full recover, full recovery will require me to
> perform multiple backups all day long. In most cases I do not have enough
> storage to make these backups. Even if I do, I am addinging additional I/O
> on the disks (for backups) which in my case can be a killer.
That would be log backups. Which would not take as much resources as
full backups, unless your databases are being reloaded all the time
with new data.
The real killer question is: what if a database goes belly up? What
would it cost your organisation to lose one day of data? What will
your manager say when you tell him "sorry, that data is gone forever"?
Of course, it's apparent from your description that your systems are
suffering from a severly underdimensioned budget. And I'm afraid that
the best advice I can give is that you take the political fight in
your organisation to change this - before disaster strikes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Best way to force a varchar column to have no whitespace

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrencemetaperl wrote:

Quote:

Originally Posted by

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Use an update trigger to rewrite it with any whitespace stripped out.|||One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
']%')
WITH CHECK
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"metaperl" <metaperl@.gmail.comwrote in message
news:1159777716.594197.268970@.e3g2000cwe.googlegro ups.com...

Quote:

Originally Posted by

>I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.
>
Regards,
Terrence
>

|||On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:

Quote:

Originally Posted by

>One method is with a CHECK constraint that checks for the list of your
>prohibited characters. You may need to scrub data before adding the
>constraint.
>
>ALTER TABLE MyTable WITH CHECK
>ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
>']%')
WITH CHECK
>GO

Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP|||I think you wanted to include NOT there:

Yes, thanks for the catch, Hugo. I also had an extraneous NO CHECK and '^'
in the expression. The corrected version:

ALTER TABLE Table1 WITH CHECK
ADD CONSTRAINT CK_Table1_Col1
CHECK (Col1 NOT LIKE '%[' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) + ']%')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:hh33i2p3mo572838m7c7hiiqdj2q8ckber@.4ax.com...

Quote:

Originally Posted by

On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:
>

Quote:

Originally Posted by

>>One method is with a CHECK constraint that checks for the list of your
>>prohibited characters. You may need to scrub data before adding the
>>constraint.
>>
>>ALTER TABLE MyTable WITH CHECK
>>ADD CONSTRAINT CK_MyTable_MyColumn
> CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9)
>+
>>']%')
> WITH CHECK
>>GO

>
Hi Dan,
>
I think you wanted to include NOT there:
>
ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO
>
BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.
>
--
Hugo Kornelis, SQL Server MVP

|||CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.