help emergency: performance

Showing posts with label performance. Show all posts

Wednesday, March 28, 2012

IA64 bit Performance vs X64 - Update statistics

Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server is
4-way SQL EE x64, 9 GB RAM.
At the end of the day (just prior to midnight) I backup the prod database,
then run update statistics fullscan on 13 tables, 30% scan on 9 other tables.
Elapsed time is 4.5 hrs.
I can restore the backup just taken on prod over to Dev, run the same update
statistics process from Prod on Dev and the elapsed time on Dev is 3.25 hours.
Any ideas that adequately explain the difference in elapsed time?
--
jl"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
> is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod
> database,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> tables.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same
> update
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> hours.
> Any ideas that adequately explain the difference in elapsed time?
Perhaps your dev server is just faster than your production server.
David|||I forgot to mention that the SQL 2000 version for both is SP4.
--
jl
"John L" wrote:
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod database,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other tables.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same update
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25 hours.
> Any ideas that adequately explain the difference in elapsed time?
> --
> jl|||Trying to have that make sense to mgt would be a challenge even though I
inherited these servers. The dev box does have faster CPUs than the prod
server. Regrettably mgt listened to someone's notion that the IA64 servers
were "high performers" Maybe that pitch was referring Oracle on Linux :-)
--
jl
"David Browne" wrote:
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> > Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
> > is
> > 4-way SQL EE x64, 9 GB RAM.
> >
> > At the end of the day (just prior to midnight) I backup the prod
> > database,
> > then run update statistics fullscan on 13 tables, 30% scan on 9 other
> > tables.
> > Elapsed time is 4.5 hrs.
> >
> > I can restore the backup just taken on prod over to Dev, run the same
> > update
> > statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> > hours.
> >
> > Any ideas that adequately explain the difference in elapsed time?
> Perhaps your dev server is just faster than your production server.
> David
>
>|||Hi John,
x64 is definitely better than the IA64 processor. But, you cannot really
compare your production numbers to the development numbers because
Production usually has a lot more going on VS your Dev environment. Plus,
you should check your waitstats counters, filestats, and various other
perfmon counters to get a better picture on the difference in performance.
-Shiva
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
>I forgot to mention that the SQL 2000 version for both is SP4.
> --
> jl
>
> "John L" wrote:
>> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
>> server is
>> 4-way SQL EE x64, 9 GB RAM.
>> At the end of the day (just prior to midnight) I backup the prod
>> database,
>> then run update statistics fullscan on 13 tables, 30% scan on 9 other
>> tables.
>> Elapsed time is 4.5 hrs.
>> I can restore the backup just taken on prod over to Dev, run the same
>> update
>> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
>> hours.
>> Any ideas that adequately explain the difference in elapsed time?
>> --
>> jl|||Shiva:
I might be guilty of some gross oversimplification here. I agree that
production servers theoretically have more going on with them but this an 8-5
organization meaning that there is no user activity during the time frames in
which these maintenance activities occur (on either server). Ruling out
application related traffic generated by end-users, I am left with elapsed
job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace the
single core CPUs with dual core IA 64s and the run time goes up to 6.25
hours), the EMT64T single core box (dev) runs in 3.25 hours.
The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the IA
64 dual cores are 1200 Mhz.
My hope with this post was that perhaps there was someone else out there who
had seen something the same or similar in an IA 64 environment. I am getting
the feeling that there were not many who chose to go with SQL 2000 64-bit on
the IA 64 which may be why SQL 2005 is the only MS product certified for IA
64. I fully expect them to drop support for this architecture especially if
the marketplace doesn't ask for it.
There might be a very simple explanation for the observed phenonmenon and
that is simply CPU speed as David Browne has already hinted at.
--
jl
"Shiva" wrote:
> Hi John,
> x64 is definitely better than the IA64 processor. But, you cannot really
> compare your production numbers to the development numbers because
> Production usually has a lot more going on VS your Dev environment. Plus,
> you should check your waitstats counters, filestats, and various other
> perfmon counters to get a better picture on the difference in performance.
> -Shiva
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
> >I forgot to mention that the SQL 2000 version for both is SP4.
> > --
> > jl
> >
> >
> > "John L" wrote:
> >
> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
> >> server is
> >> 4-way SQL EE x64, 9 GB RAM.
> >>
> >> At the end of the day (just prior to midnight) I backup the prod
> >> database,
> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> >> tables.
> >> Elapsed time is 4.5 hrs.
> >>
> >> I can restore the backup just taken on prod over to Dev, run the same
> >> update
> >> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> >> hours.
> >>
> >> Any ideas that adequately explain the difference in elapsed time?
> >> --
> >> jl
>
>|||Hi,
I don't think that updating statistics is the right load to test Itanium2
processor. Based on my experiences when you update statistics, only one
processor is used so it doesn't matter how many cores you have. There is
usually a lot of disk activity involved (depending on amount of RAM) so disk
subsistem has important role here.
I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
running simple querries (basic selects...where...) Itanium2 is almost never
faster, usualy is 10-20% slower than Xeon, BUT when running more complex
querries like large complex joins, sorts on large amounts of data, Itanium
is always faster. More complex querry it is and more data is executing on,
faster it is comparing to Xeon. I think that Itanium is best suited for
running large reports, OLAP, complex calculations and so on, when X64 on
Xeons is faster for simple busines applications.
I've also noticed that processor parallelism is much better implemented on
IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itaniums
relly made execution times for a lot of procedures almost twice as fast than
using one proccesor. When running the same load on Xeons, parallelism made
it actually slower.
I'm begining to test it on SQL2005. So far, a lot of procedures are faster
then on SQL2000.....
Those are my experiences so far...
Tom
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...
> Shiva:
> I might be guilty of some gross oversimplification here. I agree that
> production servers theoretically have more going on with them but this an
> 8-5
> organization meaning that there is no user activity during the time frames
> in
> which these maintenance activities occur (on either server). Ruling out
> application related traffic generated by end-users, I am left with elapsed
> job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace
> the
> single core CPUs with dual core IA 64s and the run time goes up to 6.25
> hours), the EMT64T single core box (dev) runs in 3.25 hours.
> The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the
> IA
> 64 dual cores are 1200 Mhz.
> My hope with this post was that perhaps there was someone else out there
> who
> had seen something the same or similar in an IA 64 environment. I am
> getting
> the feeling that there were not many who chose to go with SQL 2000 64-bit
> on
> the IA 64 which may be why SQL 2005 is the only MS product certified for
> IA
> 64. I fully expect them to drop support for this architecture especially
> if
> the marketplace doesn't ask for it.
> There might be a very simple explanation for the observed phenonmenon and
> that is simply CPU speed as David Browne has already hinted at.
> --
> jl
>
> "Shiva" wrote:
>> Hi John,
>> x64 is definitely better than the IA64 processor. But, you cannot really
>> compare your production numbers to the development numbers because
>> Production usually has a lot more going on VS your Dev environment. Plus,
>> you should check your waitstats counters, filestats, and various other
>> perfmon counters to get a better picture on the difference in
>> performance.
>> -Shiva
>> "John L" <JohnL@.discussions.microsoft.com> wrote in message
>> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
>> >I forgot to mention that the SQL 2000 version for both is SP4.
>> > --
>> > jl
>> >
>> >
>> > "John L" wrote:
>> >
>> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
>> >> server is
>> >> 4-way SQL EE x64, 9 GB RAM.
>> >>
>> >> At the end of the day (just prior to midnight) I backup the prod
>> >> database,
>> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
>> >> tables.
>> >> Elapsed time is 4.5 hrs.
>> >>
>> >> I can restore the backup just taken on prod over to Dev, run the same
>> >> update
>> >> statistics process from Prod on Dev and the elapsed time on Dev is
>> >> 3.25
>> >> hours.
>> >>
>> >> Any ideas that adequately explain the difference in elapsed time?
>> >> --
>> >> jl
>>|||Tom:
A funny thing about our IA64 experience is that our h/w mgr jumped out and
bought dual core CPUs for the server. After installation the UPDATE STATS
took 1.25 hrs longer than with the single core IA64 CPUs.
Thanks for sharing your experience, this is exactly the sort of feedback
that I have been seeking.
--
jl
"Tom" wrote:
> Hi,
> I don't think that updating statistics is the right load to test Itanium2
> processor. Based on my experiences when you update statistics, only one
> processor is used so it doesn't matter how many cores you have. There is
> usually a lot of disk activity involved (depending on amount of RAM) so disk
> subsistem has important role here.
> I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
> running simple querries (basic selects...where...) Itanium2 is almost never
> faster, usualy is 10-20% slower than Xeon, BUT when running more complex
> querries like large complex joins, sorts on large amounts of data, Itanium
> is always faster. More complex querry it is and more data is executing on,
> faster it is comparing to Xeon. I think that Itanium is best suited for
> running large reports, OLAP, complex calculations and so on, when X64 on
> Xeons is faster for simple busines applications.
> I've also noticed that processor parallelism is much better implemented on
> IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itaniums
> relly made execution times for a lot of procedures almost twice as fast than
> using one proccesor. When running the same load on Xeons, parallelism made
> it actually slower.
> I'm begining to test it on SQL2005. So far, a lot of procedures are faster
> then on SQL2000.....
> Those are my experiences so far...
> Tom
>
>
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...
> > Shiva:
> >
> > I might be guilty of some gross oversimplification here. I agree that
> > production servers theoretically have more going on with them but this an
> > 8-5
> > organization meaning that there is no user activity during the time frames
> > in
> > which these maintenance activities occur (on either server). Ruling out
> > application related traffic generated by end-users, I am left with elapsed
> > job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace
> > the
> > single core CPUs with dual core IA 64s and the run time goes up to 6.25
> > hours), the EMT64T single core box (dev) runs in 3.25 hours.
> >
> > The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the
> > IA
> > 64 dual cores are 1200 Mhz.
> >
> > My hope with this post was that perhaps there was someone else out there
> > who
> > had seen something the same or similar in an IA 64 environment. I am
> > getting
> > the feeling that there were not many who chose to go with SQL 2000 64-bit
> > on
> > the IA 64 which may be why SQL 2005 is the only MS product certified for
> > IA
> > 64. I fully expect them to drop support for this architecture especially
> > if
> > the marketplace doesn't ask for it.
> >
> > There might be a very simple explanation for the observed phenonmenon and
> > that is simply CPU speed as David Browne has already hinted at.
> >
> > --
> > jl
> >
> >
> > "Shiva" wrote:
> >
> >> Hi John,
> >>
> >> x64 is definitely better than the IA64 processor. But, you cannot really
> >> compare your production numbers to the development numbers because
> >> Production usually has a lot more going on VS your Dev environment. Plus,
> >> you should check your waitstats counters, filestats, and various other
> >> perfmon counters to get a better picture on the difference in
> >> performance.
> >>
> >> -Shiva
> >>
> >> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> >> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
> >> >I forgot to mention that the SQL 2000 version for both is SP4.
> >> > --
> >> > jl
> >> >
> >> >
> >> > "John L" wrote:
> >> >
> >> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
> >> >> server is
> >> >> 4-way SQL EE x64, 9 GB RAM.
> >> >>
> >> >> At the end of the day (just prior to midnight) I backup the prod
> >> >> database,
> >> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> >> >> tables.
> >> >> Elapsed time is 4.5 hrs.
> >> >>
> >> >> I can restore the backup just taken on prod over to Dev, run the same
> >> >> update
> >> >> statistics process from Prod on Dev and the elapsed time on Dev is
> >> >> 3.25
> >> >> hours.
> >> >>
> >> >> Any ideas that adequately explain the difference in elapsed time?
> >> >> --
> >> >> jl
> >>
> >>
> >>
>
>sql

I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?
Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
http://support.microsoft.com/default...b;EN-US;224453
http://support.microsoft.com/default...;en-us;Q224587
http://support.microsoft.com/default...;en-us;Q243589
sql

I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224587
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243589

I/O Queue Length

Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?

Possibly. How many disks are part of the array which your distribution database is on?

You have to multiply the queue by the number of spindles which form your array. So if you have 5 disks in your raid 5 array anything beyond a disk queue length of 10 is a danger area.

Currently on one of my main distributors my queue length is pushing 80 - raid 5 (5 disks).|||

Thanks Hillary. ha ha i just found something out (just inherited this by the way) ... straight mirrored sets. no raid config. only thing on server is system dbs, but still...wow. at least logs are on seperate physical set.

I/O performance tuning

Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?|||Quad-core and SQL 2000'
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?

I/O performance tuning

Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?
You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
|||Quad-core and SQL 2000?
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?

Friday, March 23, 2012

I want to test break of mirror db when syncronization no longer possible

I am testing high performance mirror on my local PC

mirroring set on db Northwind between default and named instance

on default (principal) server have few jobs

1. insert into table A,run every 1 minute

2. backup log of Northwind ,run every 10 minute

3. stop named instance ,run every 15 minutes

4. start named instance ,run every 20 minutes

After each stop/ start mirrored db very quickly synchronized .

I want to test break of mirror db when synchronization no longer possible

any suggestions ?

We want to place 70 GB db in mirror 300 km away and I want test everything

All possible break scenario

I didn't understand your question completely "I want to test break of mirror db when synchronization no longer possible". However, the following document describes quite a few test scenarios for database mirroring, and may be helpful.

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

I want to test break of mirror db when syncronization no longer possible

I am testing high performance mirror on my local PC

mirroring set on db Northwind between default and named instance

on default (principal) server have few jobs

1. insert into table A,run every 1 minute

2. backup log of Northwind ,run every 10 minute

3. stop named instance ,run every 15 minutes

4. start named instance ,run every 20 minutes

After each stop/ start mirrored db very quickly synchronized .

I want to test break of mirror db when synchronization no longer possible

any suggestions ?

We want to place 70 GB db in mirror 300 km away and I want test everything

All possible break scenario

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

I want to study sizing.

Hello.
Recently, I need to study sizing.
I read the book 'MS SQL Server Performance Tuning'. The book deals with
sizing. But I couldn't understand some contents of the book.
I sent a e-mail about the content to the Micro Press Input, but no effective
answer :-(
Is there any book or site which deals with sizing?"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote:
> Hello.
> Recently, I need to study sizing.
> I read the book 'MS SQL Server Performance Tuning'. The book deals with
> sizing. But I couldn't understand some contents of the book.
> I sent a e-mail about the content to the Micro Press Input, but no
effective
> answer :-(
> Is there any book or site which deals with sizing?
--
I have a copy of the book and I've read sizing or capacity planning. Which
specific area is unclear to you?
--
Eric Cárdenas
SQL Server support

Monday, March 12, 2012

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.
Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.co...erver_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.co...erver_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.
|||Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
>
|||Hi
You may want to look at
http://www.sql-server-performance.co...recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>
|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson
|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of w

s I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.c...server_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of w

s I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.c...server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||
Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
>|||Hi
You may want to look at
http://www.sql-server-performance.c..._recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run
it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to ru
n
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of w

s I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of way
s
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (i
n
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the quer
y
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus doe
s
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

Wednesday, March 28, 2012

IA64 bit Performance vs X64 - Update statistics

IA64 bit Performance vs X64 - Update statistics

I/O Read/Write Performance Problem

I/O Read/Write Performance Problem

I/O Queue Length

I/O performance tuning

I/O performance tuning

Friday, March 23, 2012

I want to test break of mirror db when syncronization no longer possible

I want to test break of mirror db when syncronization no longer possible

I want to study sizing.

Monday, March 12, 2012

I smell a big, fat compiler bug

I smell a big, fat compiler bug

help emergency

Blog Archive

About Me