Percentage of total: blocking

Showing posts with label blocking. Show all posts

Monday, March 26, 2012

Performance degradation on sql server 2005

Moved from sql server 2000 to 2005 .Applied snapshot isolation to
reduce blocking...
>From most indicators return times are not any faster
In 2000 we had awe enabled and in 2005 64 bit on 64 bit os non of that
is
We have 32G of ram on a xeon
4 cpu hyperthreaded
Config
lock pages in memory granted to sql server account ...
max memory 25G
Min memory 1G
Not sure what I am missing here
Your input will be highly appreciatedMass
After upgrading to SQL Server 2005 , did you update statistics as well as
rebuilding indexes on the databases?
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184263.874445.285230@.i3g2000cwc.googlegroups.com...
> Moved from sql server 2000 to 2005 .Applied snapshot isolation to
> reduce blocking...
>>From most indicators return times are not any faster
> In 2000 we had awe enabled and in 2005 64 bit on 64 bit os non of that
> is
> We have 32G of ram on a xeon
> 4 cpu hyperthreaded
> Config
> lock pages in memory granted to sql server account ...
> max memory 25G
> Min memory 1G
> Not sure what I am missing here
> Your input will be highly appreciated
>|||Thanks for your response Uri
I did not rebuild indexes but for sure update statistics every day or
some analyst will call me on that .Is it necessary to rebuild indexes?
Uri Dimant wrote:
> Mass
> After upgrading to SQL Server 2005 , did you update statistics as well as
> rebuilding indexes on the databases?
>
>
> "Massa Batheli" <mngong@.gmail.com> wrote in message
> news:1159184263.874445.285230@.i3g2000cwc.googlegroups.com...
> > Moved from sql server 2000 to 2005 .Applied snapshot isolation to
> > reduce blocking...
> >>From most indicators return times are not any faster
> >
> > In 2000 we had awe enabled and in 2005 64 bit on 64 bit os non of that
> > is
> >
> > We have 32G of ram on a xeon
> > 4 cpu hyperthreaded
> > Config
> > lock pages in memory granted to sql server account ...
> > max memory 25G
> > Min memory 1G
> >
> > Not sure what I am missing here
> > Your input will be highly appreciated
> >|||Hi
Yes , it is. I'd sugget you rebuild a heavy updated/inserted tables to do
that on weekly period
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159187341.666362.282160@.i3g2000cwc.googlegroups.com...
> Thanks for your response Uri
> I did not rebuild indexes but for sure update statistics every day or
> some analyst will call me on that .Is it necessary to rebuild indexes?
>
> Uri Dimant wrote:
>> Mass
>> After upgrading to SQL Server 2005 , did you update statistics as well
>> as
>> rebuilding indexes on the databases?
>>
>>
>> "Massa Batheli" <mngong@.gmail.com> wrote in message
>> news:1159184263.874445.285230@.i3g2000cwc.googlegroups.com...
>> > Moved from sql server 2000 to 2005 .Applied snapshot isolation to
>> > reduce blocking...
>> >>From most indicators return times are not any faster
>> >
>> > In 2000 we had awe enabled and in 2005 64 bit on 64 bit os non of that
>> > is
>> >
>> > We have 32G of ram on a xeon
>> > 4 cpu hyperthreaded
>> > Config
>> > lock pages in memory granted to sql server account ...
>> > max memory 25G
>> > Min memory 1G
>> >
>> > Not sure what I am missing here
>> > Your input will be highly appreciated
>> >
>|||On 25.09.2006 14:36, Uri Dimant wrote:
> Yes , it is. I'd sugget you rebuild a heavy updated/inserted tables to do
> that on weekly period
Is that actually common practice with SQL Server? I have never read
such a suggestion in Oracle land and it seems pretty impractical for
DB's that are large or frequently used (24x7).
Kind regards
robert|||Robert Klemme wrote:
> On 25.09.2006 14:36, Uri Dimant wrote:
>> Yes , it is. I'd sugget you rebuild a heavy updated/inserted tables
>> to do that on weekly period
> Is that actually common practice with SQL Server? I have never read
> such a suggestion in Oracle land and it seems pretty impractical for
> DB's that are large or frequently used (24x7).
> Kind regards
> robert
Yes, it is, depending on the type of index, keys involved, etc. For
instance, an index build on a non-sequential key will fragment as new
key values are inserted into middle portions of the index.
Here's an article explaining it in more detail:
http://www.sql-server-performance.com/sj_detect_fragmentation.asp
I have a script that will check the fragmentation of each index,
rebuilding those that are badly fragmented:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'd beg to differ. A VLDB is a VLDB. Regardless of what platform it is on,
the challenges are the same or similar. In this case, if it is a very large
table with little 'down time' for maintenance, running index rebuilds
regularly (or even occasionally) is just not practical.
Linchi
"Tracy McKibben" wrote:
> Robert Klemme wrote:
> > On 25.09.2006 14:36, Uri Dimant wrote:
> >> Yes , it is. I'd sugget you rebuild a heavy updated/inserted tables
> >> to do that on weekly period
> >
> > Is that actually common practice with SQL Server? I have never read
> > such a suggestion in Oracle land and it seems pretty impractical for
> > DB's that are large or frequently used (24x7).
> >
> > Kind regards
> >
> > robert
> Yes, it is, depending on the type of index, keys involved, etc. For
> instance, an index build on a non-sequential key will fragment as new
> key values are inserted into middle portions of the index.
> Here's an article explaining it in more detail:
> http://www.sql-server-performance.com/sj_detect_fragmentation.asp
> I have a script that will check the fragmentation of each index,
> rebuilding those that are badly fragmented:
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||here here.. I totally agree with Linchi. Often, the advice to simply rebuild
indexes is given unnecessarily.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:07A7E217-1824-4FE4-9367-11DAF263DB83@.microsoft.com...
> I'd beg to differ. A VLDB is a VLDB. Regardless of what platform it is on,
> the challenges are the same or similar. In this case, if it is a very
> large
> table with little 'down time' for maintenance, running index rebuilds
> regularly (or even occasionally) is just not practical.
> Linchi
> "Tracy McKibben" wrote:
>> Robert Klemme wrote:
>> > On 25.09.2006 14:36, Uri Dimant wrote:
>> >> Yes , it is. I'd sugget you rebuild a heavy updated/inserted tables
>> >> to do that on weekly period
>> >
>> > Is that actually common practice with SQL Server? I have never read
>> > such a suggestion in Oracle land and it seems pretty impractical for
>> > DB's that are large or frequently used (24x7).
>> >
>> > Kind regards
>> >
>> > robert
>> Yes, it is, depending on the type of index, keys involved, etc. For
>> instance, an index build on a non-sequential key will fragment as new
>> key values are inserted into middle portions of the index.
>> Here's an article explaining it in more detail:
>> http://www.sql-server-performance.com/sj_detect_fragmentation.asp
>> I have a script that will check the fragmentation of each index,
>> rebuilding those that are badly fragmented:
>> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

Performance degradation on sql server 2005

performance degradation during backup

Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...Our cluster does a 221GB backup in about 5hr, so your backup performance
doesn't seem unusual. If the users are seeing performance hits, I'm not
sure it is due to the backups - unless you are backing up to the same disk
as where the data files live.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"L Gonzales" <anonymous@.discussions.microsoft.com> wrote in message
news:23fe01c4dd5c$3de60130$a301280a@.phx.gbl...
Whenever I run database backup during normal operating
hours I see a performance degradation and somtimes
blocking. We are running on active/passive config on MS
Cluster. The database size is 40GB and the backup takes
about an hour to complete. I have it set to run at
midnight but there are cases where I need to run the
backup during normal hrs. I have no experience in MS
Cluster and my gut feeling is that the I/O rate max data
thruput is getting maxed out when I run the backup during
normal operating hrs. I can not let the backup run and
run the perfmon to figure out what is going on because
the end users are annoyed and calling my COO ...|||L Gonzales wrote:
> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
Are you backing up to disk on a different channel? If not, you should
back up to a mirrored array, not a RAID 5 array. You would also benefit
from any of the SQL Server backup programs out there that compress
backups as they can backup much, much faster than native (not to mention
restores are also faster).
--
David Gugick
Imceda Software
www.imceda.com|||Hi
What you are seeing is a Disk performance issue. Whaqt is your disk
configuration?
Is your SAN setup with RAID-5 and the Data and dumps are on the same LUN/set
of disks? Reading and writing to and from the same LUN will really hamper
performance. What does performance monitor say about Disk Queue Length?
We dump at 2.5Tb per hour on our Clustered SQL Servers with SANs.
Regards
Mike
"L Gonzales" wrote:
> Whenever I run database backup during normal operating
> hours I see a performance degradation and somtimes
> blocking. We are running on active/passive config on MS
> Cluster. The database size is 40GB and the backup takes
> about an hour to complete. I have it set to run at
> midnight but there are cases where I need to run the
> backup during normal hrs. I have no experience in MS
> Cluster and my gut feeling is that the I/O rate max data
> thruput is getting maxed out when I run the backup during
> normal operating hrs. I can not let the backup run and
> run the perfmon to figure out what is going on because
> the end users are annoyed and calling my COO ...
>
>|||Raid-10. Same LUN/set of disks.
>--Original Message--
>Hi
>What you are seeing is a Disk performance issue. Whaqt
is your disk
>configuration?
>Is your SAN setup with RAID-5 and the Data and dumps are
on the same LUN/set
>of disks? Reading and writing to and from the same LUN
will really hamper
>performance. What does performance monitor say about
Disk Queue Length?
>We dump at 2.5Tb per hour on our Clustered SQL Servers
with SANs.
>Regards
>Mike
>"L Gonzales" wrote:
>> Whenever I run database backup during normal operating
>> hours I see a performance degradation and somtimes
>> blocking. We are running on active/passive config on
MS
>> Cluster. The database size is 40GB and the backup
takes
>> about an hour to complete. I have it set to run at
>> midnight but there are cases where I need to run the
>> backup during normal hrs. I have no experience in MS
>> Cluster and my gut feeling is that the I/O rate max
data
>> thruput is getting maxed out when I run the backup
during
>> normal operating hrs. I can not let the backup run
and
>> run the perfmon to figure out what is going on
because
>> the end users are annoyed and calling my COO ...
>>
>.
>|||<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...
> Raid-10. Same LUN/set of disks.
Backup to a different LUN/set of disks.|||Not only is that a poor choice for performance since you will most likely
max out the bandwidth but you are risking your database as well. If the
disk array died you would loose the database and your most recent backups.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:247101c4dd6c$60b0ea30$a301280a@.phx.gbl...
> Raid-10. Same LUN/set of disks.
>>--Original Message--
>>Hi
>>What you are seeing is a Disk performance issue. Whaqt
> is your disk
>>configuration?
>>Is your SAN setup with RAID-5 and the Data and dumps are
> on the same LUN/set
>>of disks? Reading and writing to and from the same LUN
> will really hamper
>>performance. What does performance monitor say about
> Disk Queue Length?
>>We dump at 2.5Tb per hour on our Clustered SQL Servers
> with SANs.
>>Regards
>>Mike
>>"L Gonzales" wrote:
>> Whenever I run database backup during normal operating
>> hours I see a performance degradation and somtimes
>> blocking. We are running on active/passive config on
> MS
>> Cluster. The database size is 40GB and the backup
> takes
>> about an hour to complete. I have it set to run at
>> midnight but there are cases where I need to run the
>> backup during normal hrs. I have no experience in MS
>> Cluster and my gut feeling is that the I/O rate max
> data
>> thruput is getting maxed out when I run the backup
> during
>> normal operating hrs. I can not let the backup run
> and
>> run the perfmon to figure out what is going on
> because
>> the end users are annoyed and calling my COO ...
>>
>>.sql

Monday, March 26, 2012

Performance degradation on sql server 2005

Performance degradation on sql server 2005

Performance degradation on sql server 2005

performance degradation during backup

Percentage of total

Blog Archive

About Me