Percentage of total: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

Performance Hit w/ Filegroups?

Hi all,
We have db that has been configured w/ about 7 different filegroups.
Still trying to fully understand filegroups and their impact if tables
and indices aren't properly separated. I believe I read that you can
create different files for ea. index, but they must belong to the same
filegroup. In this example below they look to be in separate filegroups.
Is this a bad way of setting up filegroups? Am I multiplying the amt of
threads (exponentially) with this type of set up, which is causing my
i/o bottleneck?
Thank You!
C
ROWCT TBL KEY FILEGRP
15517322 TESTSHIFT X1_TESTSHIFT test1
15422280 TESTSHIFT PK_TESTSHIFT test1
15422280 TESTSHIFT X2_TESTSHIFT test3
15422280 TESTSHIFT X3_TESTSHIFT test4
15422280 TESTSHIFT X4_TESTSHIFT test1
13319460 SHIFT PK_SHIFT test4
13228153 SHIFT X1_SHIFT test1
13228153 SHIFT X2_SHIFT test1
13228153 SHIFT X3_SHIFT test1
13228153 SHIFT X4_SHIFT test1
13228153 SHIFT X5_SHIFT test2
13228153 SHIFT X6_SHIFT test1
10829933 TESTSHFTRC TESTSHFTRC test3
10748087 TESTSHFTRC X1_TESTSHFTRC test2
10748087 TESTSHFTRC X2_TESTSHFTRC test1
9105520 TEST_LOOKUP TEST_LOOKUP test4
8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1If all of these files (and hence filegroups) are on the same drive array it
won't much matter how many you have. If your array can not handle the I/O
load then adding more files will not make it any better. The biggest
advantage to splitting data and indexes into two (or more) filegroups is
only when the files in those filegroups live on separate physical drive
arrays.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:eFb6pvuDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We have db that has been configured w/ about 7 different filegroups.
> Still trying to fully understand filegroups and their impact if tables and
> indices aren't properly separated. I believe I read that you can create
> different files for ea. index, but they must belong to the same filegroup.
> In this example below they look to be in separate filegroups. Is this a
> bad way of setting up filegroups? Am I multiplying the amt of threads
> (exponentially) with this type of set up, which is causing my i/o
> bottleneck?
> Thank You!
> C
> ROWCT TBL KEY FILEGRP
> 15517322 TESTSHIFT X1_TESTSHIFT test1
> 15422280 TESTSHIFT PK_TESTSHIFT test1
> 15422280 TESTSHIFT X2_TESTSHIFT test3
> 15422280 TESTSHIFT X3_TESTSHIFT test4
> 15422280 TESTSHIFT X4_TESTSHIFT test1
> 13319460 SHIFT PK_SHIFT test4
> 13228153 SHIFT X1_SHIFT test1
> 13228153 SHIFT X2_SHIFT test1
> 13228153 SHIFT X3_SHIFT test1
> 13228153 SHIFT X4_SHIFT test1
> 13228153 SHIFT X5_SHIFT test2
> 13228153 SHIFT X6_SHIFT test1
> 10829933 TESTSHFTRC TESTSHFTRC test3
> 10748087 TESTSHFTRC X1_TESTSHFTRC test2
> 10748087 TESTSHFTRC X2_TESTSHFTRC test1
> 9105520 TEST_LOOKUP TEST_LOOKUP test4
> 8583208 TESTSHIFTSEG PK_TESTSHIFTSEG test2
> 8502786 TESTSHIFTSEG X1_TESTSHIFTSEG test1|||Ahhhh, ok. Could there ever be an instance where you group data and
indices in a way that hurts performance even though the files have been
separated onto their own raid sets?
Thanks much for the info Andrew! It's been very helpful.|||Separating indexes and tables onto their own drive arrays should only
increase performance (or at worst case the same) over keeping them all
together on one array. That is as long as you don't remove drives from the
original array.
--
Andrew J. Kelly SQL MVP
"Ooroster" <chris@.nyp.org> wrote in message
news:u7UQ$ixDFHA.4032@.TK2MSFTNGP10.phx.gbl...
> Ahhhh, ok. Could there ever be an instance where you group data and
> indices in a way that hurts performance even though the files have been
> separated onto their own raid sets?
> Thanks much for the info Andrew! It's been very helpful.|||One example where you can hurt yourself with RAID volume segregation is when
you simply make Bad decisions on RAID layout.
For example:
you start with a single RAID 5 Volume with 4 disks and on that volume, you
place everything. OS, Logs, Data, Indexes.
You attempt to segregate data by creating seperate RAID Volumes: You create
2 RAID 5 Volumes with 2 disks each: placing OS and logs on One volume and
you place Data and indexes on the other volume.
You went from having a single decent volume (enough disks for striping and
parity) and you shoot yourself in the foot by creating two volumes that cant
really benefit from striping.
this could be an example where you hurt performance....again, you really
need to understand RAID confiuration to make this stuff work right but
generally speaking, segregation via filegroups is not going to add over head
and reduce performance.
Greg Jackson
Portland, Oregon

Performance hit for cross-database partitioned view (on same serve

What would be the performance difference for a view spanning multiple tables
in separate filegroups but in the same database as opposed to the same view
spanning tables in different databases (all on the same server).
Since the execution plan is the same, I would think that there is no
difference but some of the tests I have done seem to indicuate a performance
hit of about 10% for the cross-database view.If there are updates, then there are cost related to transaction commit
cross databases.
You might want to use set statistic IO to see more details of the query
execution.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C764B4BA-4C3E-41B4-BC41-E85A894255EC@.microsoft.com...
> What would be the performance difference for a view spanning multiple
tables
> in separate filegroups but in the same database as opposed to the same
view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a
performance
> hit of about 10% for the cross-database view.|||If the databases are on the same server and running under the same instance
of SQL Server then the difference should be very small. Running under
different instances (even on the same server) requires cross-process
marshalling and incurrs a performance impact.
"DBA72" wrote:
> What would be the performance difference for a view spanning multiple tables
> in separate filegroups but in the same database as opposed to the same view
> spanning tables in different databases (all on the same server).
> Since the execution plan is the same, I would think that there is no
> difference but some of the tests I have done seem to indicuate a performance
> hit of about 10% for the cross-database view.

Wednesday, March 28, 2012

Performance different

I have 2 databases that are identical in schema and structure, one hold June
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

Dear All,

Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.

Thanks and regards,
Nishant Saini
http://www.simplyjava.comNishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

There is a topic that covers this in Books Online. On the top of my head
I don't recall anything immediate, but I'm off to other things right now,
and don't want to look around.

Could you post one of your queries you've been testing with and the
query plan on Standard and Enterprise?

I presume that you are running the two editions on the same hardware?

Quote:

Originally Posted by

Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>

The Enterprise Edition offers improvements over the Standard Edition,
especially on parallel operations and caching. If you have a
significant amount of data you will defnitely notice a better
performance with this edition.

Regards,
lucm|||Are there any indexed views in the database? From the Books On Line
(BOL):

"Indexed views can be created in any edition of SQL Server 2005. In
SQL Server 2005 Enterprise Edition, the query optimizer automatically
considers the indexed view. To use an indexed view in all other
editions, the NOEXPAND table hint must be used."

So if there were indexed views, but no references to the table hint,
performance could certainly be far better using the Enterprise
edition.

Roy Harvey
Beacon Falls, CT

On 15 Dec 2006 03:01:20 -0800, "Nishant Saini"
<nishant.saini@.gmail.comwrote:

Quote:

Originally Posted by

>Dear All,
>
>We have a database which contains many tables which have millions of
>records. When We attach the database with MS SQL Server 2005 Standard
>Edition Server and run some queries (having joins, filters etc.) then
>they take very long time to execute while when We execute same queries
>on Enterprise Edition then they run 10 times faster than on standard
>edition.
>
>Our database does not use any features which are present in Enterprise
>Edition and not present in Standard Edition. We want to know what are
>the differences between Standard Edition and Enterprise Edition for
>performance. Why should we go for Enterprise Edition when Standard
>Edition has all the features required.
>
>We are presently using evaluation versions of SQL Server 2005 Standard
>and Enterprise Editions.
>
>Thanks and regards,
>Nishant Saini
>http://www.simplyjava.com

|||Although Enterprise Edition is more aggresive when it comes to
read-aheads, and has some features that can improves performance in some
very specific situations (such a parallel index creation, Advanced
Scanning, etc.), if both systems have the same amount of memory, then I
would not expect a 10-fold performance difference.

How much memory does the system have, and have you assigned this memory
to SQL Server?

You could copy the "fast" database to the "slow" configuration with
detach/attach and see if that makes a difference. This way you can rule
out any database differences (such as one database with up-to-date
statistics and another with unusable or no statistics).

HTH,
Gert-Jan

Nishant Saini wrote:

Quote:

Originally Posted by

>
Dear All,
>
We have a database which contains many tables which have millions of
records. When We attach the database with MS SQL Server 2005 Standard
Edition Server and run some queries (having joins, filters etc.) then
they take very long time to execute while when We execute same queries
on Enterprise Edition then they run 10 times faster than on standard
edition.
>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.
>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com

|||"Nishant Saini" <nishant.saini@.gmail.comwrote in message
news:1166180480.672397.223250@.73g2000cwn.googlegro ups.com...

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access. Clear the procedure and memory
cache prior to side by side testing.

Quote:

Originally Posted by

>
Our database does not use any features which are present in Enterprise
Edition and not present in Standard Edition. We want to know what are
the differences between Standard Edition and Enterprise Edition for
performance. Why should we go for Enterprise Edition when Standard
Edition has all the features required.

I only consider EE if the hardware requires the use.

Quote:

Originally Posted by

>
We are presently using evaluation versions of SQL Server 2005 Standard
and Enterprise Editions.
>
Thanks and regards,
Nishant Saini
http://www.simplyjava.com
>

|||On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russrose@.hotmail.com>
wrote:

Quote:

Originally Posted by

>Assuming identical hardware, a 10x speed difference is usually the
>difference between memory and disk access.

My experience, for what it is worth, has been that extreme performance
differences - better or worse - are usually the result of different
execution plans.

Roy Harvey
Beacon Falls, CT|||Thanks for the responses...

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005? Can we control the execution plans?

On Dec 16, 6:39 am, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

On Fri, 15 Dec 2006 15:54:07 -0600, "Russ Rose" <russr...@.hotmail.com>
wrote:
>

Quote:

Originally Posted by

Assuming identical hardware, a 10x speed difference is usually the
difference between memory and disk access.My experience, for what it is worth, has been that extreme performance

differences - better or worse - are usually the result of different
execution plans.
>
Roy Harvey
Beacon Falls, CT

If there are no indexed views in the database ans data is selected from
tables directly, then should there be any difference in performance?

Quote:

Originally Posted by

>"Indexed views can be created in any edition of SQL Server 2005. In
>SQL Server 2005 Enterprise Edition, the query optimizer automatically
>considers the indexed view. To use an indexed view in all other
>editions, the NOEXPAND table hint must be used."

Thanks and regards,
Nishant Saini
http://www.simplyjava.com|||Nishant Saini (nishant.saini@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...
>
Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?

There could be many reasons for that. Roy Harvey mentioned indexed views
for instance.

If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.

Quote:

Originally Posted by

Can we control the execution plans?

Yes. SQL 2005 actually permit you to specify the plan exactly to using
plan guides. This is definitely an advance feature, and nothing you
should use at a whim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.

Right now we need to know

1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Thanks in advance
Nitin Goyal

On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

Yes, The execution plans are different in both the databases.
Why the execution plans are so different in both the versions of SQL
Server 2005?There could be many reasons for that. Roy Harvey mentioned indexed views

for instance.
>
If you want a better answer, please post the query and the two plans,
so that we know what we are talking about.
>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to specify the plan exactly to using

plan guides. This is definitely an advance feature, and nothing you
should use at a whim.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||<nitin.goel@.daffodildb.comwrote in message
news:1166429585.200709.298530@.80g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.
>
Regarding the execution plan, it may be different due to different
sizes of both databases. We are right now trying to check execution
plan for similar databases on both editions. We will post our findings
as soon as we get them.
>
Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?

The query engine is the same.

Quote:

Originally Posted by

2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.

Not specifically.

However, Enterprise Edition supports more physical RAM which can make some
queries faster.

In addition, it can support things like parellelized query builds.

In general EE is more for specific features (like clustering beyond 2 nodes)
than pure size.

If you have a mission critical database that's 1 gig, but you want an N+2
architecture, you'll want EE.

If you have a datawarehouse that 500 gig that can be down for periods of
time during restores, etc, Standard Edition may be fine.

Check out MS's page on feature differences.

Quote:

Originally Posted by

>
Thanks in advance
Nitin Goyal
>
>
On Dec 16, 4:39 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>Nishant Saini (nishant.sa...@.gmail.com) writes:

Quote:

Originally Posted by

Thanks for the responses...

Quote:

Originally Posted by

>for instance.
>>
>If you want a better answer, please post the query and the two plans,
>so that we know what we are talking about.
>>

Quote:

Originally Posted by

Can we control the execution plans?Yes. SQL 2005 actually permit you to
specify the plan exactly to using

>plan guides. This is definitely an advance feature, and nothing you
>should use at a whim.
>>
>--
>Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>>
>Books Online for SQL Server 2005
>athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
>Books Online for SQL Server 2000
>athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

Right now we need to know
>
1. Enterprise Edition has its own Query running mechanism ( which is
different and faster than Standard Edition mechanism) or NOT ?
2. If it is special, what kind of performance differences we will get
while using Std Edition.
3. Is there any specific DB size ( or table sizes ) for which Standard
Edition is fine and after that one should go for Enterprise.
>
Thanks in advance
Nitin Goyal

The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.

As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.

Regards,
lucm|||lucm wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Right now we need to know

Thanks in advance
Nitin Goyal

>
The engine is not the same. Have a look here:
http://www.microsoft.com/sql/prodin...e-features.mspx
especially in "Scalability and Performance" and "High Availability"
sections.
>
As for choosing Standard or Enterprise, you should think about
concurrent users and operations, not database size.
>
Regards,
lucm

Although the storage engine is different (or at least exposes more
features), the query optimizer is the same. So if you are not using
indexed views, you can live with 4 CPU's and you do not need the
advanced OLAP tools, then you can expect the same query performance. The
only documented exception is Advanced Scans which could improve
performance in OLAP type situations.

For a manageability and high availability point of view, there are many
reasons to choose EE, but IMO not from a performance point of view
(given the restrictions above).

If you disagree with me, then please mention a specific feature that
will cause EE to outperform SE (on regular DML statements).

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

If you want to compare Standard and Enterprise, you must of course
use the same database on the same hardware. Else the test is meaningless.

Quote:

Originally Posted by

Rather than asking questions on the net, and hope that people will look
up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Here is the H/W and database configuration in this case.

Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.

FYI, we have searched MS SQL Server books online + seen the differences
of SE and EE given by MS + asked our local vendor but he knows nothing.
And the queries run by us are select queries with joins on around 5 -1
0 tables. So the picture is still murky and Microsoft is not answering.

And Mr Sommarskog, we do not want anyone to search for us. We are
looking for very specific answers and we will be thankful if someone
can help us (we feel others have also faced same problems.)

Thanks
Nitin Goyal

On Dec 19, 3:14 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(nitin.g...@.daffodildb.com) writes:

Quote:

Originally Posted by

As Nishant said we are not using any special features of Enterprise
Edition. Indexed views are not being used and data is taken directly
from tables.

Quote:

Originally Posted by

Regarding the execution plan, it may be different due to different
sizes of both databases.If you want to compare Standard and Enterprise, you must of course

use the same database on the same hardware. Else the test is meaningless.
>

Quote:

Originally Posted by

up the answers for you, why not look in this place in Books Online
instead:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/81f3e917-884a-4cc8-aca2-0a5fea89f355.htm
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||

Quote:

Originally Posted by

System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.
>
RAM: it is an OS issue and not of SQL Server. There is no RAM support
specification in SQL Server (EE / SE). By the way, OS used by us is Win
2003 Server EE.
>

First of all, RAID-0 is a no-go. If this is a production server you
need at least a mirror (RAID-1).

Now, do you *need* EE or not? Without a load average it is difficult to
say. Since you purchased a server with a single CPU and a small amount
of RAM, you probably don't have too many concurrent users.

One of my customers has up to 100 concurrent users, he has a hardware
weaker than yours, a bigger database, and he is very happy with SE.
They make quite an extensive use of the server, and they can afford the
downtime when they restore lost data or rebuild indexes.

Regards,
lucm|||nitin.goel@.daffodildb.com wrote:

Quote:

Originally Posted by

>
Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
[snip]

I am not sure you are aware of this, but the EE is approximately 4 times
as expensive as SE (when using CPU-based licensing). There is no way you
can make a business case that justifies spending an additional $15,000
on EE for performance reasons. If you were to spend another x dollars on
performance it would definitely be in the hardware, in whatever area is
limiting perfomance (in your case most likely in the I/O area). But if
you don't have to spend the money now, then you can postpone such
decision and see how it runs with the current hardware.

As for your maintenance window: it should be more than sufficient to
handle any problems with a database of just 20 GB, so from that point of
view you don't need EE either.

Gert-Jan|||(nitin.goel@.daffodildb.com) writes:

Quote:

Originally Posted by

Here is the H/W and database configuration in this case.
>
System: - HP Proliant ML 370 G4 Server ( Intel Xeon 3.2 GH on Intel
7520 Chipset; Dual CPU capable )
RAM: - 4 GB DDR2 ECC
HDD: - 2 * 73 GB SCSI ( 15000 RPM ) RAID 0
DB size: - 20 GB
Downtime: - 2 hours a day i.e. morning 5 to 7 AM.
>
Parallel queries: Parallel Query processing runs only on multiple
processors. In our case only one processor is available to SQL Server.

Does this mean that there are more CPUs in the box, but there are
other applications that gets to use those? Or is there only one CPU
in the box, and SQL Server is the only app on the machine?

The main reason I ask this, is that if there other apps on the machine,
this makes it even more difficult to assess your questions.

Quote:

Originally Posted by

And since you never post any of the queries, and nor the query plans,
you are not giving us any chances to straighten things out.

But I like to agree with what Gert-Jan said. If you have extrememe
performance requirement, you may want to cough up the extra money for
Enterprise to get whatever small fraction you can win. But if you
had those requirements, you would not let it suffice with a single
CPU box.

So go for Standard. Keep in mind that if your business expands and
you will want Enterprise one day, you can always upgrade at that point.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||<nitin.goel@.daffodildb.comwrote in message
news:1166597810.015478.291810@.a3g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

RED ALERT!!!

Switch to RAID 1 or add at least one more disk and go RAID 5 or kiss your
data (and job?) goodbye.|||Thank you for replies. Some more information from my side:

1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
5. We will post some queries by Monday morning.

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||Thank you for replies. Some more information from my side:

BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Thanks again
Nitin Goyal|||<nitin.goel@.daffodildb.comwrote in message
news:1166681115.492154.131080@.79g2000cws.googlegro ups.com...

Quote:

Originally Posted by

Thank you for replies. Some more information from my side:
>
1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing
mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.

Again, the TYPE of query really matters. If each query takes a 100
milliseconds vs. 10 seconds.

Quote:

Originally Posted by

3. There is only one CPU in the box and only SQL Server runs on it.

Well you may or may not be CPU bound, again, depends a lot on what you're
doing her.e

Quote:

Originally Posted by

4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).

Add disks. Lots of disks. Fast disks.

You're better off with 10 40 gig disk tha 1 400 gig disk for example.

(of course that's ignoring RAID and doing stuff like putting your logs on
their own physical disk group.)

Just as a point of reference we had a system handling 14 million selects and
inserts a day running on a 6 yo 4-way Xeon machine up until recently. Just
recently moved this to a 2-way modern box AND added a bunch of other
queries... and if anything it's faster than before.

Quote:

Originally Posted by

5. We will post some queries by Monday morning.
>
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.
>
>
Thanks again
Nitin Goyal
>

|||1. Sorry I have made a mistake, our DB size is 50 GB not 20 GB (typing

Quote:

Originally Posted by

mistake).
2. There are around 100 concurrent users accessing this db from 30
locations.
3. There is only one CPU in the box and only SQL Server runs on it.
4. Regarding I/O performance, what more can be done in I/O area (
that's a very novice Q but I have never worked in this field ).
BTW I know price of both SE and EE but we will spend only if we see any
gain EE. And we are also ready to spend on I/O if it is good for us.

Basic rules for the I/O on a database server: a RAID-10 array using as
many disks as possible (even number) is the best scenario for the
databases, while a 2-disk RAID-1 array is great for transaction logs.

If you use temporary tables, having a distinct 2-disk RAID-1 array for
tempbd is excellent (even better: a 4-disk RAID-10). As for the disks
themselves, the smaller and faster is the better; your 73 GB 15k are
excellent.

If you can afford it, having a RAID controller the databases (RAID-10)
and another one for the transaction logs and tempdb (RAID-1 or RAID-10)
is optimal.

Also don't be shy to add some RAM, as much as you can.

Regards,
lucm

Monday, March 26, 2012

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
Henry
In my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL
|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
.. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:

> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:

> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
>
>
|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:

> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
>
|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:

> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>
|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan
sql

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
HenryIn my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
--
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:
> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
> >
>
>|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:
> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
> >
> > Dear Sir,
> > I have been tried to Reindex all tables' indexs as I expected this can
> > improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> > '',0). But after that, I found that the overall performance is actually
> > degraded and the disk I/O seems to be heavier also. The performance is
> > degraded for three days already, can any one think of any reason that can
> > explain this issue? I have another question for statistic, do I needed to
> > update statistics after the reindex?
> > Pls. help, thanks a lot!
> > Yours sincerely,
> > Henry
>|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:
> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan

Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
HenryIn my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:

> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:

> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
>
>|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:

> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
>|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:

> In my opinion it's very important to update sql stats (proc sp_updatestats
)
> because like all RDBMS those stats are used by MSSQL to optimize the way i
t
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0
),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill facto
r
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan

Wednesday, March 21, 2012

Performance cost of joins

We are in the stage of designing a database for our web application. We have two design options, one is to use normalized tables and using joins to get data from more than one table, the other is to duplicate fields in tables and avoid using joins.
The main purpose is to get high performance from the system. the system is supposed to produce reports that contain information from about 100,000 records from 5 or 6 related tables.
which of these approaches is better from the performance point of view? What is the performance cost of using joins over getting data from one table?Generally, one should not blindly assume that it's always 'normalized and slow' vs. 'de-normalized and fast’. Normalized databases when reading data typically perform better too provided that the optimizer is able to benefit from useful indexes for the frequent joins. Having said that, obviously there are corner cases where de-normalization does improve things for certain data access patterns especially when de-normalization allows you to get rid of some indexes and hence reduce the update costs. But, again, it all depends on your particular logical database schema and the prevalent data access/update patterns.|||

It is generally better to normalize your tables for better data integrity and to reduce duplicate data. After you have a good normalized foundation, then you can think about denormalizing with rollup tables for exceptional cases. For an OLTP workload, you may see some performance issues if you have frequently executed queries that have joins to more than four or five tables, since the query optimizer has to use heuristics to come up with a plan.

Your indexing strategy will have more effect on performance than anything else. You have to analyze how volatile your tables are and what kind of workload you have in order to determine what indexes to create. There are good DMV queries that will let you easily see which queries are being executed the most and which indexes are being used. Here is an example:

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/DATEDIFF(SECOND, qs.creation_time, GetDate())DESC

|||

its a question of tradeoff..... for faster dmls u need a normalized structures...for faster (generaly) reports de normalized is better..... so u have to decide where actually u want to optimize the performance...

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

|||

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Performance cost of joins

|||

for me normalized database does the trick... as it may prove to be more helpful in longer run ... ie u always have a greater integrity of data....

for reports u may use views to avoid repeated use of joins...

so optimal for me = normalized + views

Monday, March 12, 2012

Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!
Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the 5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
[vbcol=seagreen]
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
Anith
|||> On complex queries, esp. ones that involve larger underlying datasets, the performance could be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would be determined by file
configuration. I.e., one could use filegroups so that the file placement over the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has the same information
and options whether or not we go across database boundary - assuming in the same instance of
course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>
> Unless he tests out his queries and compare them, we cannot be sure. On complex queries, esp. ones
> that involve larger underlying datasets, the performance could be very different due to
> significant changes in disk I/O.
> --
> Anith
>
|||>> But that wouldn't be specific to inter-database traffic, right? That[vbcol=seagreen]
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
[vbcol=seagreen]
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
Anith
|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)

> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the same information
regardless of whether the tables involved are in the same database or are from several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the optimizer does not
factor disk layout or characteristica when creating an execution plan. Perhaps I should have said:
You can define a database using file groups so you get the same structure as if you had that set of
tables spread over several databases. (Assuming you don't introduce any table partitioning when
spreading over several databases.) If you do end up with a similar file placement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if, for
instance, a table is partitioned over different filegroups compared to the same filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tables in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
> Can we have two databases placed on the same filegroup? Otherwise, it would have to be distinct
> physical file access. You are right in that one could have the underlying files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is why he'll have to test
> out his queries and compare them.
>
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>
|||>> Can we have two databases placed on the same filegroup?[vbcol=seagreen]
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
[vbcol=seagreen]
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
[vbcol=seagreen]
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
[vbcol=seagreen]
... which is all the more reason for the OP to test out his queries and see
it for himself.
[vbcol=seagreen]
Indeed
Anith
|||Thanks for the comments, Anith. Seems we are in agreement here, even if it took a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:

> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of 7.0, share the same
file over several databases. One file is owned by a database (a true subset of the database).
The old architecture was different, where you first created a database device (the file) and then
allocated storage ("segment", similar to a file group) from that file for the database. Thus, you
could end up with two databases using storage from the same file. This model doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW devices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architecture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
>
> I was just emphasising on the fact that physical I/O could be a contributing factor to performance
> differences. If the databases are on distinct files ( distributed or otherwise ) then it can
> contribute to the overall performance of queries when the underlying implementation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>
> Agreed. On the same token if the underlying file placement of the files are different, the
> performance could be different as well.
>
> .. which is all the more reason for the OP to test out his queries and see it for himself.
>
> Indeed
> --
> Anith
>

Friday, March 30, 2012

Performance Hit w/ Filegroups?

Performance hit for cross-database partitioned view (on same serve

Wednesday, March 28, 2012

Performance different

Performance Difference between SQL Server 2005 Standard Edition and Enterprise Edition

Monday, March 26, 2012

Performance degraded after DBCC DBReindex

Performance degraded after DBCC DBReindex

Performance degraded after DBCC DBReindex

Wednesday, March 21, 2012

Performance cost of joins

Performance cost of joins

Performance cost of joins

Monday, March 12, 2012

Performance and inter-database joins

Percentage of total

Blog Archive

About Me