rda submitsql: back

Showing posts with label back. Show all posts

Wednesday, March 28, 2012

RAID Configuration for SQL Server ...

Hi,
We are rolling out an ERP application from MS across our
organisation. And we are using MS SQL Server as the back
end. In this regard, I have few queries -
a) What would be the optimum RAID configuration for the
SQL Server database? Can someone point me towards any
guide?
b) Still we are in the initial stage of our roll out. So
we are not too sure about the growth of database. Is
there any tools available for estimating the size of
database?
Thanks in advance,
Harish MohanbabuHi
You may want to check out the book "SQL Server 2000 Performance Tuning
Technical Reference" which talks about this in some depth ISBN 0-7356-1270-6
Also you may want to browse the site:
http://www.sql-server-performance.com/
Such as:
http://www.sql-server-performance.com/ultimate_sql_server.asp
The topic "Estimating the Size of a Database" in books online should help
you decide how big the data may get:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\cre
atedb.chm::/cm_8_des_02_2h45.htm
John
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:067c01c3cdfb$2d441db0$a101280a@.phx.gbl...
> Hi,
> We are rolling out an ERP application from MS across our
> organisation. And we are using MS SQL Server as the back
> end. In this regard, I have few queries -
> a) What would be the optimum RAID configuration for the
> SQL Server database? Can someone point me towards any
> guide?
> b) Still we are in the initial stage of our roll out. So
> we are not too sure about the growth of database. Is
> there any tools available for estimating the size of
> database?
> Thanks in advance,
> Harish Mohanbabu|||Thanks for your kind and quick reply ....
>--Original Message--
>Hi
>You may want to check out the book "SQL Server 2000
Performance Tuning
>Technical Reference" which talks about this in some
depth ISBN 0-7356-1270-6
>Also you may want to browse the site:
>http://www.sql-server-performance.com/
>Such as:
>http://www.sql-server-
performance.com/ultimate_sql_server.asp
>The topic "Estimating the Size of a Database" in books
online should help
>you decide how big the data may get:
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\cre
>atedb.chm::/cm_8_des_02_2h45.htm
>John

RAID 5 Issues

Someone posted a link a while back to a website that discussed potential
problems with RAID 5. I think it dealt mainly with spare sectors on IDE
drives, but I can't seem to find the link.
Does anyone have anything about why you should not use RAID 5 with a
database?
Thanks,
Rick SawtellRAID 5 is slower for write intensive operations. If your database requires
a lot of updates/inserts/deletes (maybe more than 15% write operations), I
would look into RAID 1 or RAID 1+0. Also, Transaction Logs shouldn't be put
on RAID 5 since they are write intensive by definition.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uL922T5XFHA.712@.TK2MSFTNGP14.phx.gbl...
> Someone posted a link a while back to a website that discussed potential
> problems with RAID 5. I think it dealt mainly with spare sectors on IDE
> drives, but I can't seem to find the link.
> Does anyone have anything about why you should not use RAID 5 with a
> database?
>
> Thanks,
>
> Rick Sawtell
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:OC5asj5XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> RAID 5 is slower for write intensive operations. If your database
requires
> a lot of updates/inserts/deletes (maybe more than 15% write operations), I
> would look into RAID 1 or RAID 1+0. Also, Transaction Logs shouldn't be
put
> on RAID 5 since they are write intensive by definition.
>
All good points Michael, but I am still looking for the link.
There is a "movement" to get rid of RAID 5 for database usage. The article
is a bit inflammatory, however it was good food for thought.
Rick|||Rick Sawtell wrote:
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:OC5asj5XFHA.2124@.TK2MSFTNGP14.phx.gbl...
>> RAID 5 is slower for write intensive operations. If your database
>> requires a lot of updates/inserts/deletes (maybe more than 15% write
>> operations), I would look into RAID 1 or RAID 1+0. Also,
>> Transaction Logs shouldn't be put on RAID 5 since they are write
>> intensive by definition.
>>
> All good points Michael, but I am still looking for the link.
> There is a "movement" to get rid of RAID 5 for database usage. The
> article is a bit inflammatory, however it was good food for thought.
>
> Rick
No sure any of these are what you wanted. I remember an article from a
few months ago (can't find the link). The drive away from RAID 5 has a
lot to do with reduced performance if a drive fails and also drive
prices falling in recent years. It's not the right choice for log files
or tempdb, but for many databases is can be adequate if you can deal
with the possible performance issues during a drive rebuild.
http://www.talkaboutdatabases.com/group/comp.databases.informix/messages/179342.html
http://www.dba-oracle.com/oracle_tips_raid5_bad.htm
David Gugick
Imceda Software
www.imceda.com|||In article <OD9OuX6XFHA.2796@.TK2MSFTNGP09.phx.gbl>, davidg-
nospam@.imceda.com says...
> Rick Sawtell wrote:
> > "Michael C#" <howsa@.boutdat.com> wrote in message
> > news:OC5asj5XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> >> RAID 5 is slower for write intensive operations. If your database
> >> requires a lot of updates/inserts/deletes (maybe more than 15% write
> >> operations), I would look into RAID 1 or RAID 1+0. Also,
> >> Transaction Logs shouldn't be put on RAID 5 since they are write
> >> intensive by definition.
> >>
> >>
> >
> > All good points Michael, but I am still looking for the link.
> >
> > There is a "movement" to get rid of RAID 5 for database usage. The
> > article is a bit inflammatory, however it was good food for thought.
> >
> >
> > Rick
> No sure any of these are what you wanted. I remember an article from a
> few months ago (can't find the link). The drive away from RAID 5 has a
> lot to do with reduced performance if a drive fails and also drive
> prices falling in recent years. It's not the right choice for log files
> or tempdb, but for many databases is can be adequate if you can deal
> with the possible performance issues during a drive rebuild.
> http://www.talkaboutdatabases.com/group/comp.databases.informix/messages/179342.html
> http://www.dba-oracle.com/oracle_tips_raid5_bad.htm
I think, from my experience, the time when you see the most impact is
when the array consists of less than 5 drives. I've seen simple 3 x
drive R5 arrays kill a systems performance when on drive fails, but on 8
drive systems, when a drive fails, it's hardly noticed.
In these days when designers are less technical/experienced, we see a
lot of crappy solutions using something that someone has got from a
friend and failed to properly research.
I've seen soooooo many 3 x drive R5 solutions in the last 2 years that
I'm starting to wonder if I should put a question about R5 performance
on our employment test to weed out those that know from those that think
they know.
--
spam999free@.rrohio.com
remove 999 in order to email me|||> > > "Michael C#" <howsa@.boutdat.com> wrote in message
> > > news:OC5asj5XFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > >> RAID 5 is slower for write intensive operations. If your database
> > >> requires a lot of updates/inserts/deletes (maybe more than 15% write
> > >> operations), I would look into RAID 1 or RAID 1+0. Also,
> > >> Transaction Logs shouldn't be put on RAID 5 since they are write
> > >> intensive by definition.
> >
> >
http://www.talkaboutdatabases.com/group/comp.databases.informix/messages/179342.html
> > http://www.dba-oracle.com/oracle_tips_raid5_bad.htm
Thanks Michael, it was on a link from a link of yours.
http://www.baarf.com/
This is what I was looking for.
Rick|||Holy Cow...."AMEN"
We have been feverishly interviewing Sr. DBA Candidates to fill multiple
positions here.
We are getting some very impressive looking resumes.
However, I am blown away by the fact that whenever I ask even the most
simple quesiton about RAID nearly every single Candidate just stairs at me
with an open mouth.....
(Remember we're interviewing for "Senior" positions)...
The same thing is happening when we ask questions about Clustered Indexes,
PerfMon, Locking, etc.
Safe to say (At least in our market), you'll weed out a large percentage of
your candidates almost immediately by discussing RAID.
Greg Jackson
PDX, Oregon|||Hi, Greg
> We have been feverishly interviewing Sr. DBA Candidates to fill multiple
> positions here.
I'm glad to see that American economy is recovering. Does your company
support H1B ?:-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23PYvRy6XFHA.3840@.tk2msftngp13.phx.gbl...
> Holy Cow...."AMEN"
>
> We have been feverishly interviewing Sr. DBA Candidates to fill multiple
> positions here.
> We are getting some very impressive looking resumes.
> However, I am blown away by the fact that whenever I ask even the most
> simple quesiton about RAID nearly every single Candidate just stairs at me
> with an open mouth.....
> (Remember we're interviewing for "Senior" positions)...
> The same thing is happening when we ask questions about Clustered Indexes,
> PerfMon, Locking, etc.
> Safe to say (At least in our market), you'll weed out a large percentage
of
> your candidates almost immediately by discussing RAID.
>
> Greg Jackson
> PDX, Oregon
>|||In article <#PYvRy6XFHA.3840@.tk2msftngp13.phx.gbl>,
GregoryAJackson@.Hotmail.com says...
> Holy Cow...."AMEN"
>
> We have been feverishly interviewing Sr. DBA Candidates to fill multiple
> positions here.
> We are getting some very impressive looking resumes.
> However, I am blown away by the fact that whenever I ask even the most
> simple quesiton about RAID nearly every single Candidate just stairs at me
> with an open mouth.....
> (Remember we're interviewing for "Senior" positions)...
> The same thing is happening when we ask questions about Clustered Indexes,
> PerfMon, Locking, etc.
> Safe to say (At least in our market), you'll weed out a large percentage of
> your candidates almost immediately by discussing RAID.
Yea, I always ask about clustered indexes, locking, query plans, and
perfmon, then I give them a couple diagrams and see if they understand.
I had one that I always ask people about. The customer that you built an
application for about 1 year ago calls and tells you that all of their
queries from the web server fails for reports, but it works fine for
other queries, they say that some times the smaller ones fail too. Now,
you ask them run run the query in the QA and it works fine, but it takes
forever - what's the problem? If they don't mention reindexing the
database and maintenance I discount them and show them the door...
It's funny how senior production DBA's don't know about rebuilding
indexes to return query performance, or how the number of spindles in an
array makes a difference.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Last company I worked at Had the EXACT problem in production.
It took me 3 months (LITERALLY) to convice the Executives to over ride the
production DBAs recommendation and that we rebuild indexes.
Turns out we did not have a SINGLE clustered index in production (only a
handful of nonclustered), and there was NO index maintenance.
The Prod DBAs response was that "Index Maintenance is too expensive in a
24x7 operation so we just have to live with it"
After losing our largest customer, the execs Finally agreed to try Ol'
Jacksons trick and BAM no more problems...
GAJ|||In article <#UqCE58XFHA.2520@.TK2MSFTNGP09.phx.gbl>,
GregoryAJackson@.Hotmail.com says...
> Last company I worked at Had the EXACT problem in production.
> It took me 3 months (LITERALLY) to convice the Executives to over ride the
> production DBAs recommendation and that we rebuild indexes.
> Turns out we did not have a SINGLE clustered index in production (only a
> handful of nonclustered), and there was NO index maintenance.
> The Prod DBAs response was that "Index Maintenance is too expensive in a
> 24x7 operation so we just have to live with it"
> After losing our largest customer, the execs Finally agreed to try Ol'
> Jacksons trick and BAM no more problems...
We get calls all the time from government groups with SQL problems, it's
almost always a case of a DBA that was really a programmer that could
spell DBA that got moved into the role as a production DBA. In every
case, there is no maintenance plan, other than backups, on the
databases.
I've even interviewed MCSD and MCDBA's that didn't have a clue about
reindexing as part of a maintenance plan, and they didn't understand
that the number of spindles impacts performance too :)
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Glad to help, but David posted those links :)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYaUOi6XFHA.3488@.tk2msftngp13.phx.gbl...
>> > > "Michael C#" <howsa@.boutdat.com> wrote in message
>> > > news:OC5asj5XFHA.2124@.TK2MSFTNGP14.phx.gbl...
>> > >> RAID 5 is slower for write intensive operations. If your database
>> > >> requires a lot of updates/inserts/deletes (maybe more than 15% write
>> > >> operations), I would look into RAID 1 or RAID 1+0. Also,
>> > >> Transaction Logs shouldn't be put on RAID 5 since they are write
>> > >> intensive by definition.
>> >
>> >
> http://www.talkaboutdatabases.com/group/comp.databases.informix/messages/179342.html
>> > http://www.dba-oracle.com/oracle_tips_raid5_bad.htm
>
> Thanks Michael, it was on a link from a link of yours.
> http://www.baarf.com/
> This is what I was looking for.
>
> Rick
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u1d5%2346XFHA.2540@.tk2msftngp13.phx.gbl...
> Hi, Greg
>> We have been feverishly interviewing Sr. DBA Candidates to fill multiple
>> positions here.
> I'm glad to see that American economy is recovering. Does your company
> support H1B ?:-)
They all do, directly or indirectly.|||Hi, Mike
> They all do, directly or indirectly.
How do you know? :-)
I'm on my way to buy one way ticket :-)
"Michael C#" <howsa@.boutdat.com> wrote in message
news:uPOTdpGYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u1d5%2346XFHA.2540@.tk2msftngp13.phx.gbl...
> > Hi, Greg
> >> We have been feverishly interviewing Sr. DBA Candidates to fill
multiple
> >> positions here.
> >
> > I'm glad to see that American economy is recovering. Does your company
> > support H1B ?:-)
> They all do, directly or indirectly.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4Yfy5GYFHA.3164@.TK2MSFTNGP09.phx.gbl...
> Hi, Mike
>> They all do, directly or indirectly.
> How do you know? :-)
> I'm on my way to buy one way ticket :-)
H1B's are approved each year by Congress. We all pay taxes :)|||Uri,
I believe we have employees working here that are H1B.
Dont quote me on that as I generally mind my own business and I'm not an
H.R. Representative.
Greg Jackson
PDX, Oregon

Monday, February 20, 2012

Queue not disabling

HI There

My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?

Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.

It only stops when i disable activation on the queue.

WHy is the queue not disabling ?

Thanx

Did you look at sys.service_queues to see if the queue is really disabled? Does profiler show the queue_disabled event?|||

Thanx Rushi

Thats what i thought just wanted to check Thanx

|||

Sorry that comment was on the wrong post, yes i check sys.service_queues to make sure it is enabled. ANd i check the sql log and can see the activated sp activating and rolling abck thousands of times until i disable actiavtion. I find it very strange it always used to disable becuase of the poison message, but no longer not sure why, i would not even know how to disable poison messages?

Thanx

Wednesday, March 28, 2012

RAID Configuration for SQL Server ...

RAID 5 Issues

RAID 5 Issues

RAID 5 Issues

Monday, February 20, 2012

Queue not disabling

rda submitsql

Blog Archive

About Me