Poor performance on Pentium 4 against AMD machine

HI,
We had a database on an AMD machine. We ran SQL queries
there and performance were fine.
At a certain point, we moved the database to a Pentium
machine (backup and restore).
And now, on the Pentium machine we get performance on the
same SQL queries which are 2-3 time slower.
Using SQL Profiler we noticed that the disk-read takes
the majority of the time.
We tried to switch disks but no change in performance.
Does anyone have any idea of what could be the reason?
Thanks,
Danny.What disk subsystem is in use in both machines? What RAID levels, SCSI,
number of disks?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||In addition to Tony's question, what about memory configs?
"Danny Korach" <danny.korach@.clicksoftware.com> wrote in message
news:0bb301c3609d$c50dbe10$a101280a@.phx.gbl...
> HI,
> We had a database on an AMD machine. We ran SQL queries
> there and performance were fine.
> At a certain point, we moved the database to a Pentium
> machine (backup and restore).
> And now, on the Pentium machine we get performance on the
> same SQL queries which are 2-3 time slower.
> Using SQL Profiler we noticed that the disk-read takes
> the majority of the time.
> We tried to switch disks but no change in performance.
> Does anyone have any idea of what could be the reason?
> Thanks,
> Danny.

Poor Performance on Dual Xeon Machine

Hi,

I've been creating a db application using MS Access and MSDE. Only two
of us are using the application, and the server and the app both run
great on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problem
is when I take my laptop home, my coworker loses access to the server.

We recently purchased a dedicated server to run the db on at the
office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We also
bought SQL Server, but I installed the Personal Edition becuase we are
not using a server OS. It's my understanding that XP can utilize both
processors, and the Personal Edition can use both processors as well.
(On a side note, why is Enterprise Manager showing that I have 4
processors - why?) In addition, I understand PE has a work-load
governor that cripples performance when more than 5 TSQL commands are
being run simultaneously.

I backed up the db on my laptop and restored it on our new server. But
when I run the exact same queries with the exact same number of rows,
my queries on the new server are take 3x longer(!?). Can someone
please offer a few suggestions for why this is happening? What can I
do to improve performance on the server machine?
Please let me know if I need to supply more information.

Thanks,
Alex>>>We recently purchased a dedicated server to run the db on at the
office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro

You really should install a Server OS not XPPro. If you're stuck with
XPPro, you should install Developer Edition not PE, which is meant for
laptops.

>>>On a side note, why is Enterprise Manager showing that I have 4
processors - why?

MSSQL 2000 was written before Pentium 4 hyperthreading came out. The 2
chips appears as 4 to the software.

>>>I backed up the db on my laptop and restored it on our new server.
But when I run the exact same queries with the exact same number of
rows, my queries on the new server are take 3x longer(!?).
Try running sp_updatestats.|||Louis,

We have MSSQL Standard Edition (which includes Personal and MSDE), and
for the time being we have XPPro.

It seems to me that I'm running a very similar configuration on the
server to what I am on my laptop. PE should perform about the same if
not a little better than MSDE; same w/ XPPro over W2kPro.

I tried updating the statistics; that had no effect.

I've also been using the performance monitor a little and haven't found
anything out of the ordinary; the simple select queries I'm using as
benchmarks between the two machines are not accessing the physical disk
on either.

Alex|||I doubt performance monitor will say much -- as the new server
shouldn't have any hardware bottlenecks. More likely, it has to do
with the way MSSQL is configured/setup on the machine. (And the fact
it's XP professional with MSSQL PE). What does the execution plans say?|||:(

The execution plan on the laptop and the server are identical. Perhaps
you are right about the OS/MSSQL combinations we're using.

On a positive note, I tracked down a copy of Developer in the office;
I'm going to test XPPro w/ that.|||In article <1106840171.084819.266910@.z14g2000cwz.googlegroups. com>,
alex4groups@.gmail.com says...
> Hi,
> I've been creating a db application using MS Access and MSDE. Only two
> of us are using the application, and the server and the app both run
> great on my laptop (1.6 GHz Pentium M, 2GB RAM, W2KPro). Only problem
> is when I take my laptop home, my coworker loses access to the server.
> We recently purchased a dedicated server to run the db on at the
> office. It's a 2.8 GHz Dual Xeon, 2GB RAM, running XPPro. We also
> bought SQL Server, but I installed the Personal Edition becuase we are
> not using a server OS. It's my understanding that XP can utilize both
> processors, and the Personal Edition can use both processors as well.

Here's the issue - XP Pro can only use 2CPU's, so it's going to use the
first CPU on each Chip and the second (the HT part) virtual CPU will not
be used - at least if I understand it correctly.

While XP makes good use of HT, it does not make as good a use of it as
does Server 2003 Standard does. In fact, XP is not optimised for a
server running Dual Xeons.

In many cases, having HT enabled on a motherboard, but not having full
support in the OS for it (as is W2000), you will see a decrease in
performance.

Disable HT in your BIOS until you can get Server 2000 Standard. OEM cost
for server 2003 standard with 5CAL should be under $500.

> (On a side note, why is Enterprise Manager showing that I have 4
> processors - why?) In addition, I understand PE has a work-load
> governor that cripples performance when more than 5 TSQL commands are
> being run simultaneously.

HT based CPU's always show up as 2 CPU's under Windows 2000 and above -
this is the nature of Xeon and HT type CPU's on Windows. In some cases,
when the OS and App are HT aware, you can see a boost in performance of
as much as 30%, but most people never see it in real life.

I have a 4CPU Xeon and never see any difference in 2000 Server (SP5) and
SQL 2000 between HT enabled or disabled on our 200GB databases with
hundreds of users.

> I backed up the db on my laptop and restored it on our new server. But
> when I run the exact same queries with the exact same number of rows,
> my queries on the new server are take 3x longer(!?). Can someone
> please offer a few suggestions for why this is happening? What can I
> do to improve performance on the server machine?
> Please let me know if I need to supply more information.

There are several things - drop XP Prof, get MSDE 2000 Version, or
better yet, get SQL 2000 Standard and Windows 2003 server standard.

Also, you need to setup your drives properly:

Drive 0 + 1: Mirror (C: 12GB, D:40+GB)
Drive 2,3,4,5,6: RAID-5 (E: big GB)
Drive 7: Single drive - (F: Size as DB backup * 5+)

With the OS on Drive C and the SQL transaction logs on D and the
database data files on E and then online backups nightly to E with tape
backing up E.

You also need to leave 128MB for the OS and as much other RAM for the
SQL Server. Also set your swap file to something like a fixed size of
1GB, set SQL memory to a fixed size of MIN/MAX = TOTAL RAM - 128MB

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||louis (louisducnguyen@.gmail.com) writes:
> You really should install a Server OS not XPPro. If you're stuck with
> XPPro, you should install Developer Edition not PE, which is meant for
> laptops.

Wait! That depends on what Alex is using the database for. If he is
developing an application, it's alright. But if he is using an application,
no, that's a license breach. Developer Edition is *not* for running
applications.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns95EC3BF8DED7Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> louis (louisducnguyen@.gmail.com) writes:
> > You really should install a Server OS not XPPro. If you're stuck with
> > XPPro, you should install Developer Edition not PE, which is meant for
> > laptops.
> Wait! That depends on what Alex is using the database for. If he is
> developing an application, it's alright. But if he is using an application,
> no, that's a license breach. Developer Edition is *not* for running
> applications.

Actually, you might want to check - depending on what you used to design
the application/database with you can redistribute MSDE with your
product.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Leythos (void@.nowhere.lan) writes:
> In article <Xns95EC3BF8DED7Yazorman@.127.0.0.1>, esquel@.sommarskog.se
> says...
>> louis (louisducnguyen@.gmail.com) writes:
>> > You really should install a Server OS not XPPro. If you're stuck with
>> > XPPro, you should install Developer Edition not PE, which is meant for
>> > laptops.
>>
>> Wait! That depends on what Alex is using the database for. If he is
>> developing an application, it's alright. But if he is using an
>> application, no, that's a license breach. Developer Edition is *not*
>> for running applications.
> Actually, you might want to check - depending on what you used to design
> the application/database with you can redistribute MSDE with your
> product.

MSDE is another thing. The issue at hand is Developer Edition which costs
50 USD and is exactly the same as Enterprise Edition - except for the
restriction in license.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns95EC62C1AC31AYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Leythos (void@.nowhere.lan) writes:
> > In article <Xns95EC3BF8DED7Yazorman@.127.0.0.1>, esquel@.sommarskog.se
> > says...
> >> louis (louisducnguyen@.gmail.com) writes:
> >> > You really should install a Server OS not XPPro. If you're stuck with
> >> > XPPro, you should install Developer Edition not PE, which is meant for
> >> > laptops.
> >>
> >> Wait! That depends on what Alex is using the database for. If he is
> >> developing an application, it's alright. But if he is using an
> >> application, no, that's a license breach. Developer Edition is *not*
> >> for running applications.
> > Actually, you might want to check - depending on what you used to design
> > the application/database with you can redistribute MSDE with your
> > product.
> MSDE is another thing. The issue at hand is Developer Edition which costs
> 50 USD and is exactly the same as Enterprise Edition - except for the
> restriction in license.

Sorry, I thought that MSDE was mentioned by the OP in another part of
the thread.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Everyone, thanks for the information.

I installed Developer, and lo and behold, it solved my performance
issues (thanks for your persistance Louis). I'm now querying faster on
the server than I was on my laptop. I'm thinking either the workload
governor for PE was kicking in, or PE was having trouble w/ the
processors like Leythos suggested.

This is a very small tool that I'm developing as we go; only two of us
are using it, and we do not have a huge budget. We are not deploying
it or selling it; we are using it for inhouse analysis. In an effort
to save money, we opted for good processors and 2GB RAM, but we have
only 2 SATA 160 GB harddrives and no RAID. It was my error that we
bought the Standard edition expecting to use it with XP Pro (which my
company owns a ton of licenses already). See, if we had bought the
Server OS, I would have had to pare down my hardware configuration.

I will work on getting W2K3 Server. Until then, would anyone like to
comment on how I have my drives set up?

Disk 1: C, OS and Apps; D, MSSQL Backups; E, Sharedrive for our team
Disk 2: Z, 6GB Virtual Mem; F, MSSQL DB; G, OS & Apps, Sharedrive
backups

In addition, we will also be conducting db backups on a network drive
once a week.
Is this a good configuration given that I only have two drives?|||In article <1106928400.227674.185100@.c13g2000cwb.googlegroups. com>,
alex4groups@.gmail.com says...
> Everyone, thanks for the information.
> I installed Developer, and lo and behold, it solved my performance
> issues (thanks for your persistance Louis). I'm now querying faster on
> the server than I was on my laptop. I'm thinking either the workload
> governor for PE was kicking in, or PE was having trouble w/ the
> processors like Leythos suggested.

Glad to hear that it's working now.

[snip]

> I will work on getting W2K3 Server. Until then, would anyone like to
> comment on how I have my drives set up?
> Disk 1: C, OS and Apps; D, MSSQL Backups; E, Sharedrive for our team
> Disk 2: Z, 6GB Virtual Mem; F, MSSQL DB; G, OS & Apps, Sharedrive
> backups
> In addition, we will also be conducting db backups on a network drive
> once a week.
> Is this a good configuration given that I only have two drives?

Not knowing what you have to a motherboard, I would strongly suggest
that you consider at least a RAID controller card and set that up with
the drives in a Mirror. The PromiseTX card, or if the motherboard
supports RAID, either will work fine.

It appears that you use SQL and your apps and a common sharing point for
everyone, a MIRROR would provide redundancy that would help in the case
of a fault.

You should also consider nightly backups - even a tape backup to a cheap
Travan drive would help in the event of a fault.

My guess would be that if you are using 2xSATA on a Dual Xeon, that the
board supports RAID with an onboard controller.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Leythos,

I forgot to mention that I am doing full backups once a week,
differential backups daily, and transaction logs hourly (overkill, I
know). The backups are made to the first disk, while the database
resides on the second disk. We are also doing a network backup once a
week.

So given that we are already doing all these backups, and given that
this is not a highly time critical application, I'm not too worried
about implementing a fault tolerant RAID configuration. We'd rather
have the extra disk space for now.
I did ask for your advice, though, and I thank you for it.

Alex|||(alex4groups@.gmail.com) writes:
> I installed Developer, and lo and behold, it solved my performance
> issues (thanks for your persistance Louis). I'm now querying faster on
> the server than I was on my laptop. I'm thinking either the workload
> governor for PE was kicking in, or PE was having trouble w/ the
> processors like Leythos suggested.
> This is a very small tool that I'm developing as we go; only two of us
> are using it, and we do not have a huge budget. We are not deploying
> it or selling it; we are using it for inhouse analysis.

Since I brought up licensing, I guess I should comment. Although I have
to say that I have no idea what applies in this case. It you were
developing it, and then would sell it to people, who would run it on
anything but Developer, it would be OK. But when development and
usage is on the same machine it's more difficult. My gut feeling says
that if you use the information from the analisys for business decisions
you are violating the license terms.

But if you really want to find out to avoid being caught red-handed, I
can only recommend you to contact your local Microsoft representative.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <1106952467.779676.29880@.f14g2000cwb.googlegroups.c om>,
alex4groups@.gmail.com says...
> Leythos,
> I forgot to mention that I am doing full backups once a week,
> differential backups daily, and transaction logs hourly (overkill, I
> know). The backups are made to the first disk, while the database
> resides on the second disk. We are also doing a network backup once a
> week.
> So given that we are already doing all these backups, and given that
> this is not a highly time critical application, I'm not too worried
> about implementing a fault tolerant RAID configuration. We'd rather
> have the extra disk space for now.
> I did ask for your advice, though, and I thank you for it.

Alex, we're cool - I would have opted for the Dual 250GB SATA drives and
then mirrored them.

What motherboard are you using in the server? ASUS PC-DL Deluxe?

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Cool. Not sure what make/model the motherboard is - the machine is a
Dell SC1420, and the motherboard is RAID capable; there are RAID
options in the bios and it came w/ raid management software.

Erland, thanks for the licensing tips; I'm looking into getting the
server OS and running Standard Ed.

Poor Execution Plan in SQL Server 2005

Hi,
We have a database in SQL Server 2000. I made a copy of this
database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
machine. When I try one particular SP in both of them, SQL Server 2000
performs almost 2.5 times faster than 2005. The execution plan in 2005
contains HASH physical operators while 2000 has much better plan. I'm
wondered that why this can happen while all of indexes are exactly the same
in both databases.
A few days ago, I heard from a colleague that he had experienced bad
performance after upgrading the database of a customer to SQL Server 2005.
They were forced to move the database to 2000 again! I didn't believe it but
now I'm experiencing a real one!
Any help would be greatly appreciated.
LeilaHi Leila
Are you aware that upgraded databases have all of their index statistics
disabled & that you have to either recompute statistics or rebuild the
indexes before SQL Server 2005 will use any indexes? You didn't mention
this, so perhaps youre problem is this simple?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Leila" <Leilas@.hotpop.com> wrote in message
news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have a database in SQL Server 2000. I made a copy of this
> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
> machine. When I try one particular SP in both of them, SQL Server 2000
> performs almost 2.5 times faster than 2005. The execution plan in 2005
> contains HASH physical operators while 2000 has much better plan. I'm
> wondered that why this can happen while all of indexes are exactly the
> same in both databases.
> A few days ago, I heard from a colleague that he had experienced bad
> performance after upgrading the database of a customer to SQL Server 2005.
> They were forced to move the database to 2000 again! I didn't believe it
> but now I'm experiencing a real one!
> Any help would be greatly appreciated.
> Leila
>|||Thanks Greg,
Do you mean if I perform:
UPDATE STATISTICS MyTable WITH FULLSCAN
on every table, will correct the problem? Or other statements must be used
for indexes'
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> Hi Leila
> Are you aware that upgraded databases have all of their index statistics
> disabled & that you have to either recompute statistics or rebuild the
> indexes before SQL Server 2005 will use any indexes? You didn't mention
> this, so perhaps youre problem is this simple?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We have a database in SQL Server 2000. I made a copy of this
>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
>> machine. When I try one particular SP in both of them, SQL Server 2000
>> performs almost 2.5 times faster than 2005. The execution plan in 2005
>> contains HASH physical operators while 2000 has much better plan. I'm
>> wondered that why this can happen while all of indexes are exactly the
>> same in both databases.
>> A few days ago, I heard from a colleague that he had experienced bad
>> performance after upgrading the database of a customer to SQL Server
>> 2005. They were forced to move the database to 2000 again! I didn't
>> believe it but now I'm experiencing a real one!
>> Any help would be greatly appreciated.
>> Leila
>|||That should work ok assuming the DB isn't too big & you're not trying to
squeeze the task into a small window.
Another option is to rebuild the indexes, which might achieve some
defragmentation as well, but I'd try the first option you suggested first.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Leila" <Leilas@.hotpop.com> wrote in message
news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
> Thanks Greg,
> Do you mean if I perform:
> UPDATE STATISTICS MyTable WITH FULLSCAN
> on every table, will correct the problem? Or other statements must be used
> for indexes'
>
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> Hi Leila
>> Are you aware that upgraded databases have all of their index statistics
>> disabled & that you have to either recompute statistics or rebuild the
>> indexes before SQL Server 2005 will use any indexes? You didn't mention
>> this, so perhaps youre problem is this simple?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We have a database in SQL Server 2000. I made a copy of this
>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
>> machine. When I try one particular SP in both of them, SQL Server 2000
>> performs almost 2.5 times faster than 2005. The execution plan in 2005
>> contains HASH physical operators while 2000 has much better plan. I'm
>> wondered that why this can happen while all of indexes are exactly the
>> same in both databases.
>> A few days ago, I heard from a colleague that he had experienced bad
>> performance after upgrading the database of a customer to SQL Server
>> 2005. They were forced to move the database to 2000 again! I didn't
>> believe it but now I'm experiencing a real one!
>> Any help would be greatly appreciated.
>> Leila
>>
>|||Actually once I created a new database in SQL Server 2005 and imported
tables from 2000. Then generated index scripts from 2000 and applied to
2005. This way I think there must not be need to updating statistics. The
performance improved a little but still the execution plan in 2005 is poor
and about 2 or 1.5 times slower than 2000!
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> That should work ok assuming the DB isn't too big & you're not trying to
> squeeze the task into a small window.
> Another option is to rebuild the indexes, which might achieve some
> defragmentation as well, but I'd try the first option you suggested first.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> Thanks Greg,
>> Do you mean if I perform:
>> UPDATE STATISTICS MyTable WITH FULLSCAN
>> on every table, will correct the problem? Or other statements must be
>> used for indexes'
>>
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> Hi Leila
>> Are you aware that upgraded databases have all of their index statistics
>> disabled & that you have to either recompute statistics or rebuild the
>> indexes before SQL Server 2005 will use any indexes? You didn't mention
>> this, so perhaps youre problem is this simple?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We have a database in SQL Server 2000. I made a copy of this
>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
>> machine. When I try one particular SP in both of them, SQL Server 2000
>> performs almost 2.5 times faster than 2005. The execution plan in 2005
>> contains HASH physical operators while 2000 has much better plan. I'm
>> wondered that why this can happen while all of indexes are exactly the
>> same in both databases.
>> A few days ago, I heard from a colleague that he had experienced bad
>> performance after upgrading the database of a customer to SQL Server
>> 2005. They were forced to move the database to 2000 again! I didn't
>> believe it but now I'm experiencing a real one!
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>|||Actually once I created a new database in SQL Server 2005 and imported
tables from 2000. Then generated index scripts from 2000 and applied to
2005. This way I think there must not be need to updating statistics. The
performance improved a little but still the execution plan in 2005 is poor
and about 2 or 1.5 times slower than 2000!
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> That should work ok assuming the DB isn't too big & you're not trying to
> squeeze the task into a small window.
> Another option is to rebuild the indexes, which might achieve some
> defragmentation as well, but I'd try the first option you suggested first.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> Thanks Greg,
>> Do you mean if I perform:
>> UPDATE STATISTICS MyTable WITH FULLSCAN
>> on every table, will correct the problem? Or other statements must be
>> used for indexes'
>>
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> Hi Leila
>> Are you aware that upgraded databases have all of their index statistics
>> disabled & that you have to either recompute statistics or rebuild the
>> indexes before SQL Server 2005 will use any indexes? You didn't mention
>> this, so perhaps youre problem is this simple?
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We have a database in SQL Server 2000. I made a copy of this
>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same
>> machine. When I try one particular SP in both of them, SQL Server 2000
>> performs almost 2.5 times faster than 2005. The execution plan in 2005
>> contains HASH physical operators while 2000 has much better plan. I'm
>> wondered that why this can happen while all of indexes are exactly the
>> same in both databases.
>> A few days ago, I heard from a colleague that he had experienced bad
>> performance after upgrading the database of a customer to SQL Server
>> 2005. They were forced to move the database to 2000 again! I didn't
>> believe it but now I'm experiencing a real one!
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>|||Leila wrote:
> Actually once I created a new database in SQL Server 2005 and imported
> tables from 2000. Then generated index scripts from 2000 and applied to
> 2005. This way I think there must not be need to updating statistics. The
> performance improved a little but still the execution plan in 2005 is poor
> and about 2 or 1.5 times slower than 2000!
>
So, you're not TRULY comparing the performance of identical databases
then. I would suggest comparing the 2005 execution plan for this query
to that from 2000. What's different? Could you possibly have missed an
index in your migration? It might be worth migrating the database
properly, by doing a backup of the 2000 database, restoring it to 2005,
and then updating the statistics.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy! I will try this one too...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45264E4B.9080207@.realsqlguy.com...
> Leila wrote:
>> Actually once I created a new database in SQL Server 2005 and imported
>> tables from 2000. Then generated index scripts from 2000 and applied to
>> 2005. This way I think there must not be need to updating statistics. The
>> performance improved a little but still the execution plan in 2005 is
>> poor and about 2 or 1.5 times slower than 2000!
> So, you're not TRULY comparing the performance of identical databases
> then. I would suggest comparing the 2005 execution plan for this query to
> that from 2000. What's different? Could you possibly have missed an
> index in your migration? It might be worth migrating the database
> properly, by doing a backup of the 2000 database, restoring it to 2005,
> and then updating the statistics.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Can you please post the SET STATISTICS PROFILE ON output for the query from
SQL Server 2000 and SQL Server 2005? Ensure that you have the same data,
indexes and machine configuration for comparison purpose. And to ensure that
stats are updated, please run UPDATE STATISTICS WITH FULLSCAN on the tables
involved in the query only. See Books Online for more details on UPDATE
STATISTICS syntax. The statistics profile output will help narrow down the
problem and maybe rewrite the query differently in SQL Server 2005 to get
better plan.
As a side-note, after upgrade from previous version we do consider
statistics to be out of date. But we will consider statistics that can be
automatically updated and refresh them upon first use. So you don't really
need to do anything special unless you hit some cases where you need higher
sampling rate for example or you have auto-update disabled for statistics
and so on. There are also other benefits for indexes on string columns
because we now maintain summary information so refreshing statistics will
help in this case. Please see the "Index Statistics" topic in Books Online
for more details.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
L> Actually once I created a new database in SQL Server 2005 and
L> imported tables from 2000. Then generated index scripts from 2000
L> and applied to 2005. This way I think there must not be need to
L> updating statistics. The performance improved a little but still the
L> execution plan in 2005 is poor and about 2 or 1.5 times slower than
L> 2000!
L> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
L> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> That should work ok assuming the DB isn't too big & you're not trying
>> to squeeze the task into a small window.
>> Another option is to rebuild the indexes, which might achieve some
>> defragmentation as well, but I'd try the first option you suggested
>> first.
>> Regards,
>> Greg Linwood
>> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> Thanks Greg,
>> Do you mean if I perform:
>> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will correct
>> the problem? Or other statements must be used for indexes'
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> Hi Leila
>> Are you aware that upgraded databases have all of their index
>> statistics disabled & that you have to either recompute statistics
>> or rebuild the indexes before SQL Server 2005 will use any
>> indexes? You didn't mention this, so perhaps youre problem is this
>> simple?
>> Regards,
>> Greg Linwood
>> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We have a database in SQL Server 2000. I made a copy of this
>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
>> same machine. When I try one particular SP in both of them, SQL
>> Server 2000 performs almost 2.5 times faster than 2005. The
>> execution plan in 2005 contains HASH physical operators while
>> 2000 has much better plan. I'm wondered that why this can happen
>> while all of indexes are exactly the same in both databases.
>> A few days ago, I heard from a colleague that he had experienced
>> bad performance after upgrading the database of a customer to SQL
>> Server 2005. They were forced to move the database to 2000 again!
>> I didn't believe it but now I'm experiencing a real one!
>> Any help would be greatly appreciated.
>> Leila|||Thanks indeed!
I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do you
think it can help more if be posted here?
"Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote in
message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
> Can you please post the SET STATISTICS PROFILE ON output for the query
> from SQL Server 2000 and SQL Server 2005? Ensure that you have the same
> data, indexes and machine configuration for comparison purpose. And to
> ensure that stats are updated, please run UPDATE STATISTICS WITH FULLSCAN
> on the tables involved in the query only. See Books Online for more
> details on UPDATE STATISTICS syntax. The statistics profile output will
> help narrow down the problem and maybe rewrite the query differently in
> SQL Server 2005 to get better plan.
> As a side-note, after upgrade from previous version we do consider
> statistics to be out of date. But we will consider statistics that can be
> automatically updated and refresh them upon first use. So you don't really
> need to do anything special unless you hit some cases where you need
> higher sampling rate for example or you have auto-update disabled for
> statistics and so on. There are also other benefits for indexes on string
> columns because we now maintain summary information so refreshing
> statistics will help in this case. Please see the "Index Statistics" topic
> in Books Online for more details.
> --
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
> L> Actually once I created a new database in SQL Server 2005 and
> L> imported tables from 2000. Then generated index scripts from 2000
> L> and applied to 2005. This way I think there must not be need to
> L> updating statistics. The performance improved a little but still the
> L> execution plan in 2005 is poor and about 2 or 1.5 times slower than
> L> 2000!
>
>
> L> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> L> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> That should work ok assuming the DB isn't too big & you're not trying
> >> to squeeze the task into a small window.
> >> Another option is to rebuild the indexes, which might achieve some
> >> defragmentation as well, but I'd try the first option you suggested
> >> first.
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
> >> "Leila" <Leilas@.hotpop.com> wrote in message
> >> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
> >> Thanks Greg,
> >> Do you mean if I perform:
> >> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will correct
> >> the problem? Or other statements must be used for indexes'
>
>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> >> Hi Leila
> >> Are you aware that upgraded databases have all of their index
> >> statistics disabled & that you have to either recompute statistics
> >> or rebuild the indexes before SQL Server 2005 will use any
> >> indexes? You didn't mention this, so perhaps youre problem is this
> >> simple?
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
> >> "Leila" <Leilas@.hotpop.com> wrote in message
> >> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> >> Hi,
> >> We have a database in SQL Server 2000. I made a copy of this
> >> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
> >> same machine. When I try one particular SP in both of them, SQL
> >> Server 2000 performs almost 2.5 times faster than 2005. The
> >> execution plan in 2005 contains HASH physical operators while
> >> 2000 has much better plan. I'm wondered that why this can happen
> >> while all of indexes are exactly the same in both databases.
> >> A few days ago, I heard from a colleague that he had experienced
> >> bad performance after upgrading the database of a customer to SQL
> >> Server 2005. They were forced to move the database to 2000 again!
> >> I didn't believe it but now I'm experiencing a real one!
> >> Any help would be greatly appreciated.
> >> Leila
>
>
>
>
>|||You can only save graphical execution plans from SSMS when connected to SQL
Server 2005. So this will not help for SQL Server 2000. It is best to run
both queries using SET STATISTICS PROFILE ON, capture the output and post it
here.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Leila wrote on Sat, 7 Oct 2006 11:11:33 +0330:
L> Thanks indeed!
L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
L> you think it can help more if be posted here?
L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
L> wrote in message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
>> Can you please post the SET STATISTICS PROFILE ON output for the
>> query from SQL Server 2000 and SQL Server 2005? Ensure that you have
>> the same data, indexes and machine configuration for comparison
>> purpose. And to ensure that stats are updated, please run UPDATE
>> STATISTICS WITH FULLSCAN on the tables involved in the query only.
>> See Books Online for more details on UPDATE STATISTICS syntax. The
>> statistics profile output will help narrow down the problem and
>> maybe rewrite the query differently in
>> SQL Server 2005 to get better plan.
>> As a side-note, after upgrade from previous version we do consider
>> statistics to be out of date. But we will consider statistics that
>> can be automatically updated and refresh them upon first use. So you
>> don't really need to do anything special unless you hit some cases
>> where you need higher sampling rate for example or you have
>> auto-update disabled for statistics and so on. There are also other
>> benefits for indexes on string columns because we now maintain
>> summary information so refreshing statistics will help in this case.
>> Please see the "Index Statistics" topic in Books Online for more
>> details.
>> --
>> Umachandar Jayachandran
>> Microsoft SQL Server Performance Team
>> SQL Server Engine Team Tips Blog at
>> http://blogs.msdn.com/sqltips/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
L>> Actually once I created a new database in SQL Server 2005 and
L>> imported tables from 2000. Then generated index scripts from 2000
L>> and applied to 2005. This way I think there must not be need to
L>> updating statistics. The performance improved a little but still
L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
L>> than 2000!
L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
L>> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> That should work ok assuming the DB isn't too big & you're not
>> trying to squeeze the task into a small window.
>> Another option is to rebuild the indexes, which might achieve some
>> defragmentation as well, but I'd try the first option you suggested
>> first.
>> Regards,
>> Greg Linwood
>> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> Thanks Greg,
>> Do you mean if I perform:
>> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will
>> correct the problem? Or other statements must be used for
>> indexes'
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> Hi Leila
>> Are you aware that upgraded databases have all of their index
>> statistics disabled & that you have to either recompute
>> statistics or rebuild the indexes before SQL Server 2005 will
>> use any indexes? You didn't mention this, so perhaps youre
>> problem is this simple?
>> Regards,
>> Greg Linwood
>> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>> We have a database in SQL Server 2000. I made a copy of this
>>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
>>> same machine. When I try one particular SP in both of them, SQL
>>> Server 2000 performs almost 2.5 times faster than 2005. The
>>> execution plan in 2005 contains HASH physical operators while
>>> 2000 has much better plan. I'm wondered that why this can
>>> happen while all of indexes are exactly the same in both
>>> databases.
>>> A few days ago, I heard from a colleague that he had experienced
>>> bad performance after upgrading the database of a customer to
>>> SQL
>>> Server 2005. They were forced to move the database to 2000
>>> again!
>>> I didn't believe it but now I'm experiencing a real one!
>>> Any help would be greatly appreciated.
>>> Leila|||oops! I didn't know that! I'll post the result of STATISTICS PROFILE soon!
"Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote in
message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
> You can only save graphical execution plans from SSMS when connected to
> SQL Server 2005. So this will not help for SQL Server 2000. It is best to
> run both queries using SET STATISTICS PROFILE ON, capture the output and
> post it here.
> --
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Leila wrote on Sat, 7 Oct 2006 11:11:33 +0330:
> L> Thanks indeed!
> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
> L> you think it can help more if be posted here?
>
> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
> L> wrote in message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
> >> Can you please post the SET STATISTICS PROFILE ON output for the
> >> query from SQL Server 2000 and SQL Server 2005? Ensure that you have
> >> the same data, indexes and machine configuration for comparison
> >> purpose. And to ensure that stats are updated, please run UPDATE
> >> STATISTICS WITH FULLSCAN on the tables involved in the query only.
> >> See Books Online for more details on UPDATE STATISTICS syntax. The
> >> statistics profile output will help narrow down the problem and
> >> maybe rewrite the query differently in
> >> SQL Server 2005 to get better plan.
> >> As a side-note, after upgrade from previous version we do consider
> >> statistics to be out of date. But we will consider statistics that
> >> can be automatically updated and refresh them upon first use. So you
> >> don't really need to do anything special unless you hit some cases
> >> where you need higher sampling rate for example or you have
> >> auto-update disabled for statistics and so on. There are also other
> >> benefits for indexes on string columns because we now maintain
> >> summary information so refreshing statistics will help in this case.
> >> Please see the "Index Statistics" topic in Books Online for more
> >> details.
> >> --
> >> Umachandar Jayachandran
> >> Microsoft SQL Server Performance Team
> >> SQL Server Engine Team Tips Blog at
> >> http://blogs.msdn.com/sqltips/default.aspx
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
> L>> Actually once I created a new database in SQL Server 2005 and
> L>> imported tables from 2000. Then generated index scripts from 2000
> L>> and applied to 2005. This way I think there must not be need to
> L>> updating statistics. The performance improved a little but still
> L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
> L>> than 2000!
>
>
> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> L>> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> That should work ok assuming the DB isn't too big & you're not
> >> trying to squeeze the task into a small window.
> >> Another option is to rebuild the indexes, which might achieve some
> >> defragmentation as well, but I'd try the first option you suggested
> >> first.
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
> >> "Leila" <Leilas@.hotpop.com> wrote in message
> >> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
> >> Thanks Greg,
> >> Do you mean if I perform:
> >> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will
> >> correct the problem? Or other statements must be used for
> >> indexes'
>
>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
> >> Hi Leila
> >> Are you aware that upgraded databases have all of their index
> >> statistics disabled & that you have to either recompute
> >> statistics or rebuild the indexes before SQL Server 2005 will
> >> use any indexes? You didn't mention this, so perhaps youre
> >> problem is this simple?
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
> >> "Leila" <Leilas@.hotpop.com> wrote in message
> >> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> >>> Hi,
> >>> We have a database in SQL Server 2000. I made a copy of this
> >>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
> >>> same machine. When I try one particular SP in both of them, SQL
> >>> Server 2000 performs almost 2.5 times faster than 2005. The
> >>> execution plan in 2005 contains HASH physical operators while
> >>> 2000 has much better plan. I'm wondered that why this can
> >>> happen while all of indexes are exactly the same in both
> >>> databases.
> >>> A few days ago, I heard from a colleague that he had experienced
> >>> bad performance after upgrading the database of a customer to
> >>> SQL
> >>> Server 2005. They were forced to move the database to 2000
> >>> again!
> >>> I didn't believe it but now I'm experiencing a real one!
> >>> Any help would be greatly appreciated.
> >>> Leila
>
>
>
>
>
>|||Your plans are likely different due to cross-table statistical correlation
in your data columns. These are unfortunately very hard to model well, and
they can be tricky in terms of plan selection.
While that's not great news, I can probably help you improve the overall
performance of your application if you are able to add some indexes. You
need to check with any administrators you have, but SQL Server 2005 has a
feature for indexes called "included columns". This lets you add non-key
columns into an index to make it cover the set of columns your query needs.
Your statisics profile contains a couple of these kinds of patterns:
|--Merge Join(Concatenation)
9 13 12 Merge Join
Concatenation
1 1 | | | | | | |
|--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
SEEK:([TDepUser].
0 1 | | | | | | |
|--Index
Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
What's happening is that you are seeking for a particular value in an index.
However, that index does not "cover" the complete set of columns you need (*
in this case, and I'm sure you can get advice from others here about whether
you need this or not ;).
So, this query seeks on a nice btree, gets a value, and then tries to do a
complete scan of another index to find the other columns you need. (the
scan also pushes a predicate to be evaluated on each row before returning
into the QP, which is why the count is so low). The CPU overhead of this is
still pretty high.
One simple way to improve the performance of this query is to just make sure
you have an index that covers the set of columns you need. You can do this
in SQL 2000 by adding extra "unnecessary" columns into the index key. For
SQL 2005, you can just add them into the INCLUDE (column list) clause in
CREATE INDEX.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ ; ]
Once you have done this, you should be able to re-compile the query and see
a plan that has only a seek on a single index.
Your particular query may have multiple such cases. Each of them you fix
should generally improve performance for this query.
It's worth noting that this will make updates slightly more expensive on
this table. However, given that you have this class of issue in both
profiles, I'll go out on a limb and say that fixing this will give you a net
positive performance improvement.
I hope that helps. I have a basic section on this in my blog. I'll try to
get some time to talk more about included columns.
http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
Thanks,
Conor Cunningham
SQL Server Query Processor Development Team
"Leila" <Leilas@.hotpop.com> wrote in message
news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
> Ok! I attached a zip file to this message. It contains:
> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
> (Plain Text Format)
> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
> (CSV Format)
> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
> (Plain Text)
> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
> (CSV Format)
> 5) XML Plan for query execution in SQL Server 2005
> 6) The main query which I execute (It uses a view that is not included in
> this file)
> One important thing in comparison between these two plans appears in first
> 10 lines of CSV files: SQL Server 2000 performs some joins using Merge
> algorithm while SQL Server 2005 uses Hash join. I have written a script
> that declares a cursor from indexes of the database and I do ALTER
> INDEX...REBUILD. Even I wrote a similar script to update all statistics
> with FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
> cache) and 20 seconds in 2005 (with cache).
> I really appreciate any help! This problem has prevented us to migrate to
> SQL Server 2005.
> Thanks in advance!
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com> wrote
>> in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output and
>> post it here.
>> --
>> Umachandar Jayachandran
>> Microsoft SQL Server Performance Team
>> SQL Server Engine Team Tips Blog at
>> http://blogs.msdn.com/sqltips/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Leila wrote on Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> wrote in message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
>> >> Can you please post the SET STATISTICS PROFILE ON output for the
>> >> query from SQL Server 2000 and SQL Server 2005? Ensure that you have
>> >> the same data, indexes and machine configuration for comparison
>> >> purpose. And to ensure that stats are updated, please run UPDATE
>> >> STATISTICS WITH FULLSCAN on the tables involved in the query only.
>> >> See Books Online for more details on UPDATE STATISTICS syntax. The
>> >> statistics profile output will help narrow down the problem and
>> >> maybe rewrite the query differently in
>> >> SQL Server 2005 to get better plan.
>> >> As a side-note, after upgrade from previous version we do consider
>> >> statistics to be out of date. But we will consider statistics that
>> >> can be automatically updated and refresh them upon first use. So you
>> >> don't really need to do anything special unless you hit some cases
>> >> where you need higher sampling rate for example or you have
>> >> auto-update disabled for statistics and so on. There are also other
>> >> benefits for indexes on string columns because we now maintain
>> >> summary information so refreshing statistics will help in this case.
>> >> Please see the "Index Statistics" topic in Books Online for more
>> >> details.
>> >> --
>> >> Umachandar Jayachandran
>> >> Microsoft SQL Server Performance Team
>> >> SQL Server Engine Team Tips Blog at
>> >> http://blogs.msdn.com/sqltips/default.aspx
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
>> L>> Actually once I created a new database in SQL Server 2005 and
>> L>> imported tables from 2000. Then generated index scripts from 2000
>> L>> and applied to 2005. This way I think there must not be need to
>> L>> updating statistics. The performance improved a little but still
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> That should work ok assuming the DB isn't too big & you're not
>> >> trying to squeeze the task into a small window.
>> >> Another option is to rebuild the indexes, which might achieve some
>> >> defragmentation as well, but I'd try the first option you suggested
>> >> first.
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> >> Thanks Greg,
>> >> Do you mean if I perform:
>> >> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will
>> >> correct the problem? Or other statements must be used for
>> >> indexes'
>>
>>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> >> Hi Leila
>> >> Are you aware that upgraded databases have all of their index
>> >> statistics disabled & that you have to either recompute
>> >> statistics or rebuild the indexes before SQL Server 2005 will
>> >> use any indexes? You didn't mention this, so perhaps youre
>> >> problem is this simple?
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> >>> Hi,
>> >>> We have a database in SQL Server 2000. I made a copy of this
>> >>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
>> >>> same machine. When I try one particular SP in both of them, SQL
>> >>> Server 2000 performs almost 2.5 times faster than 2005. The
>> >>> execution plan in 2005 contains HASH physical operators while
>> >>> 2000 has much better plan. I'm wondered that why this can
>> >>> happen while all of indexes are exactly the same in both
>> >>> databases.
>> >>> A few days ago, I heard from a colleague that he had experienced
>> >>> bad performance after upgrading the database of a customer to
>> >>> SQL
>> >>> Server 2005. They were forced to move the database to 2000
>> >>> again!
>> >>> I didn't believe it but now I'm experiencing a real one!
>> >>> Any help would be greatly appreciated.
>> >>> Leila
>>
>>
>>
>>
>>
>>
>
>|||Thanks Conor,
I like this weblog and I had read your post. Previously I created some new
indexes in SQL Server 2005 for this query and its time became 18 seconds (it
was 20). Then I run Tuning Advisor that created one indexed view with few
more indexes. But it could not be faster than 15-16 seconds while SQL Server
2000 runs in 11 seconds without all these new indexes! It's really strange
that new version of this wonderful software is not performing as well as
previous version. I admit that SQL Server 2005 has great advantages but our
customers have large databases and the query performance is the highest
priority for us. We cannot recommend them to upgrade while we are not
certain about its result. I spent hours to optimize performance of this
query by indexes, although it didn't become as good as 2000, it's odd to
spend a lot of time for each query when upgrading because our database has
many queries like that.
As a developer I like to migrate to SQL Server 2005 but I understand what my
boss feels and why he is worried!
"Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
message news:ukevToW7GHA.3396@.TK2MSFTNGP04.phx.gbl...
> Your plans are likely different due to cross-table statistical correlation
> in your data columns. These are unfortunately very hard to model well,
> and they can be tricky in terms of plan selection.
> While that's not great news, I can probably help you improve the overall
> performance of your application if you are able to add some indexes. You
> need to check with any administrators you have, but SQL Server 2005 has a
> feature for indexes called "included columns". This lets you add non-key
> columns into an index to make it cover the set of columns your query
> needs.
> Your statisics profile contains a couple of these kinds of patterns:
> |--Merge Join(Concatenation) 9 13 12 Merge
> Join Concatenation
> 1 1 | | | | | | |
> |--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
> SEEK:([TDepUser].
> 0 1 | | | | | | |
> |--Index
> Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
> What's happening is that you are seeking for a particular value in an
> index. However, that index does not "cover" the complete set of columns
> you need (* in this case, and I'm sure you can get advice from others here
> about whether you need this or not ;).
> So, this query seeks on a nice btree, gets a value, and then tries to do a
> complete scan of another index to find the other columns you need. (the
> scan also pushes a predicate to be evaluated on each row before returning
> into the QP, which is why the count is so low). The CPU overhead of this
> is still pretty high.
> One simple way to improve the performance of this query is to just make
> sure you have an index that covers the set of columns you need. You can
> do this in SQL 2000 by adding extra "unnecessary" columns into the index
> key. For SQL 2005, you can just add them into the INCLUDE (column list)
> clause in CREATE INDEX.
> CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
> ON <object> ( column [ ASC | DESC ] [ ,...n ] )
> [ INCLUDE ( column_name [ ,...n ] ) ]
> [ WITH ( <relational_index_option> [ ,...n ] ) ]
> [ ON { partition_scheme_name ( column_name )
> | filegroup_name
> | default
> }
> ]
> [ ; ]
>
> Once you have done this, you should be able to re-compile the query and
> see a plan that has only a seek on a single index.
> Your particular query may have multiple such cases. Each of them you fix
> should generally improve performance for this query.
> It's worth noting that this will make updates slightly more expensive on
> this table. However, given that you have this class of issue in both
> profiles, I'll go out on a limb and say that fixing this will give you a
> net positive performance improvement.
> I hope that helps. I have a basic section on this in my blog. I'll try
> to get some time to talk more about included columns.
> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
> Thanks,
> Conor Cunningham
> SQL Server Query Processor Development Team
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included in
>> this file)
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do ALTER
>> INDEX...REBUILD. Even I wrote a similar script to update all statistics
>> with FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
>> cache) and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate to
>> SQL Server 2005.
>> Thanks in advance!
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> wrote in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output
>> and
>> post it here.
>> --
>> Umachandar Jayachandran
>> Microsoft SQL Server Performance Team
>> SQL Server Engine Team Tips Blog at
>> http://blogs.msdn.com/sqltips/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Leila wrote on Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> wrote in message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
>> >> Can you please post the SET STATISTICS PROFILE ON output for the
>> >> query from SQL Server 2000 and SQL Server 2005? Ensure that you
>> >> have
>> >> the same data, indexes and machine configuration for comparison
>> >> purpose. And to ensure that stats are updated, please run UPDATE
>> >> STATISTICS WITH FULLSCAN on the tables involved in the query only.
>> >> See Books Online for more details on UPDATE STATISTICS syntax. The
>> >> statistics profile output will help narrow down the problem and
>> >> maybe rewrite the query differently in
>> >> SQL Server 2005 to get better plan.
>> >> As a side-note, after upgrade from previous version we do consider
>> >> statistics to be out of date. But we will consider statistics that
>> >> can be automatically updated and refresh them upon first use. So
>> >> you
>> >> don't really need to do anything special unless you hit some cases
>> >> where you need higher sampling rate for example or you have
>> >> auto-update disabled for statistics and so on. There are also other
>> >> benefits for indexes on string columns because we now maintain
>> >> summary information so refreshing statistics will help in this
>> >> case.
>> >> Please see the "Index Statistics" topic in Books Online for more
>> >> details.
>> >> --
>> >> Umachandar Jayachandran
>> >> Microsoft SQL Server Performance Team
>> >> SQL Server Engine Team Tips Blog at
>> >> http://blogs.msdn.com/sqltips/default.aspx
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
>> L>> Actually once I created a new database in SQL Server 2005 and
>> L>> imported tables from 2000. Then generated index scripts from 2000
>> L>> and applied to 2005. This way I think there must not be need to
>> L>> updating statistics. The performance improved a little but still
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times
>> slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> That should work ok assuming the DB isn't too big & you're not
>> >> trying to squeeze the task into a small window.
>> >> Another option is to rebuild the indexes, which might achieve some
>> >> defragmentation as well, but I'd try the first option you
>> >> suggested
>> >> first.
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> >> Thanks Greg,
>> >> Do you mean if I perform:
>> >> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will
>> >> correct the problem? Or other statements must be used for
>> >> indexes'
>>
>>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> >> Hi Leila
>> >> Are you aware that upgraded databases have all of their index
>> >> statistics disabled & that you have to either recompute
>> >> statistics or rebuild the indexes before SQL Server 2005 will
>> >> use any indexes? You didn't mention this, so perhaps youre
>> >> problem is this simple?
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> >>> Hi,
>> >>> We have a database in SQL Server 2000. I made a copy of this
>> >>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the
>> >>> same machine. When I try one particular SP in both of them,
>> >>> SQL
>> >>> Server 2000 performs almost 2.5 times faster than 2005. The
>> >>> execution plan in 2005 contains HASH physical operators while
>> >>> 2000 has much better plan. I'm wondered that why this can
>> >>> happen while all of indexes are exactly the same in both
>> >>> databases.
>> >>> A few days ago, I heard from a colleague that he had
>> >>> experienced
>> >>> bad performance after upgrading the database of a customer to
>> >>> SQL
>> >>> Server 2005. They were forced to move the database to 2000
>> >>> again!
>> >>> I didn't believe it but now I'm experiencing a real one!
>> >>> Any help would be greatly appreciated.
>> >>> Leila
>>
>>
>>
>>
>>
>>
>>
>>
>|||My only other "simple" advice would be to try to construct the sql 2000 plan
using hints in sql 2005.
So, take the plan from sql 2000's stats profile output you have, then try to
figure out how to build something that has index and join algorithm/order
hints.
select ... from table1 with (index=whateversql2000 picked) inner hash join
...
or whatever is appropriate for each part of the plan. This is obviously
unpleasant since you have views, but there are occasionally cases where
upgrades can be difficult - usually these are these complex statistical
cases I mentioned in my previous post.
If the customer has paid for SQL 2005 and you can't make progress after
trying what I've suggested, then please call Microsoft CSS and get a case
opened. They may be able to find something to help you or it will be
escalated.
Indexed views would be another possible "solution", but I'd call CSS if you
can't get the plan choice to be acceptable after doing these tricks.
It's also possible that I've just missed something (remember, I'm debugging
blind here ;).
I hope things work out.
Sincerely,
Conor
"Leila" <Leilas@.hotpop.com> wrote in message
news:eueLemi7GHA.140@.TK2MSFTNGP05.phx.gbl...
> Thanks Conor,
> I like this weblog and I had read your post. Previously I created some new
> indexes in SQL Server 2005 for this query and its time became 18 seconds
> (it was 20). Then I run Tuning Advisor that created one indexed view with
> few more indexes. But it could not be faster than 15-16 seconds while SQL
> Server 2000 runs in 11 seconds without all these new indexes! It's really
> strange that new version of this wonderful software is not performing as
> well as previous version. I admit that SQL Server 2005 has great
> advantages but our customers have large databases and the query
> performance is the highest priority for us. We cannot recommend them to
> upgrade while we are not certain about its result. I spent hours to
> optimize performance of this query by indexes, although it didn't become
> as good as 2000, it's odd to spend a lot of time for each query when
> upgrading because our database has many queries like that.
> As a developer I like to migrate to SQL Server 2005 but I understand what
> my boss feels and why he is worried!
>
>
>
>
> "Conor Cunningham [MS]" <conorc_removeme@.online.microsoft.com> wrote in
> message news:ukevToW7GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> Your plans are likely different due to cross-table statistical
>> correlation in your data columns. These are unfortunately very hard to
>> model well, and they can be tricky in terms of plan selection.
>> While that's not great news, I can probably help you improve the overall
>> performance of your application if you are able to add some indexes. You
>> need to check with any administrators you have, but SQL Server 2005 has a
>> feature for indexes called "included columns". This lets you add non-key
>> columns into an index to make it cover the set of columns your query
>> needs.
>> Your statisics profile contains a couple of these kinds of patterns:
>> |--Merge Join(Concatenation) 9 13 12 Merge
>> Join Concatenation
>> 1 1 | | | | | | |
>> |--Index Seek(OBJECT:([Kermanshah].[dbo].[TDepUser].[IX_TDepUser_1]),
>> SEEK:([TDepUser].
>> 0 1 | | | | | | |
>> |--Index
>> Scan(OBJECT:([Kermanshah].[dbo].[TDabirDomain].[IX_TDabirDomain_DeptUser]),
>> What's happening is that you are seeking for a particular value in an
>> index. However, that index does not "cover" the complete set of columns
>> you need (* in this case, and I'm sure you can get advice from others
>> here about whether you need this or not ;).
>> So, this query seeks on a nice btree, gets a value, and then tries to do
>> a complete scan of another index to find the other columns you need.
>> (the scan also pushes a predicate to be evaluated on each row before
>> returning into the QP, which is why the count is so low). The CPU
>> overhead of this is still pretty high.
>> One simple way to improve the performance of this query is to just make
>> sure you have an index that covers the set of columns you need. You can
>> do this in SQL 2000 by adding extra "unnecessary" columns into the index
>> key. For SQL 2005, you can just add them into the INCLUDE (column list)
>> clause in CREATE INDEX.
>> CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
>> ON <object> ( column [ ASC | DESC ] [ ,...n ] )
>> [ INCLUDE ( column_name [ ,...n ] ) ]
>> [ WITH ( <relational_index_option> [ ,...n ] ) ]
>> [ ON { partition_scheme_name ( column_name )
>> | filegroup_name
>> | default
>> }
>> ]
>> [ ; ]
>>
>> Once you have done this, you should be able to re-compile the query and
>> see a plan that has only a seek on a single index.
>> Your particular query may have multiple such cases. Each of them you fix
>> should generally improve performance for this query.
>> It's worth noting that this will make updates slightly more expensive on
>> this table. However, given that you have this class of issue in both
>> profiles, I'll go out on a limb and say that fixing this will give you a
>> net positive performance improvement.
>> I hope that helps. I have a basic section on this in my blog. I'll try
>> to get some time to talk more about included columns.
>> http://blogs.msdn.com/queryoptteam/archive/2006/04/06/570176.aspx
>> Thanks,
>> Conor Cunningham
>> SQL Server Query Processor Development Team
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:O83cLyR7GHA.1248@.TK2MSFTNGP03.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included
>> in this file)
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate
>> to SQL Server 2005.
>> Thanks in advance!
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23xczWX76GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> oops! I didn't know that! I'll post the result of STATISTICS PROFILE
>> soon!
>>
>> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> wrote in
>> message news:eA6IW016GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> You can only save graphical execution plans from SSMS when connected
>> to
>> SQL Server 2005. So this will not help for SQL Server 2000. It is best
>> to
>> run both queries using SET STATISTICS PROFILE ON, capture the output
>> and
>> post it here.
>> --
>> Umachandar Jayachandran
>> Microsoft SQL Server Performance Team
>> SQL Server Engine Team Tips Blog at
>> http://blogs.msdn.com/sqltips/default.aspx
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Leila wrote on Sat, 7 Oct 2006 11:11:33 +0330:
>> L> Thanks indeed!
>> L> I can connect to 2000 and 2005 via SSMS and prepare an XML Plan. Do
>> L> you think it can help more if be posted here?
>>
>> L> "Umachandar Jayachandran [MS]" <umajay_nospam@.online.microsoft.com>
>> L> wrote in message news:OCdvVvZ6GHA.3760@.TK2MSFTNGP02.phx.gbl...
>> >> Can you please post the SET STATISTICS PROFILE ON output for the
>> >> query from SQL Server 2000 and SQL Server 2005? Ensure that you
>> >> have
>> >> the same data, indexes and machine configuration for comparison
>> >> purpose. And to ensure that stats are updated, please run UPDATE
>> >> STATISTICS WITH FULLSCAN on the tables involved in the query only.
>> >> See Books Online for more details on UPDATE STATISTICS syntax. The
>> >> statistics profile output will help narrow down the problem and
>> >> maybe rewrite the query differently in
>> >> SQL Server 2005 to get better plan.
>> >> As a side-note, after upgrade from previous version we do consider
>> >> statistics to be out of date. But we will consider statistics that
>> >> can be automatically updated and refresh them upon first use. So
>> >> you
>> >> don't really need to do anything special unless you hit some cases
>> >> where you need higher sampling rate for example or you have
>> >> auto-update disabled for statistics and so on. There are also
>> >> other
>> >> benefits for indexes on string columns because we now maintain
>> >> summary information so refreshing statistics will help in this
>> >> case.
>> >> Please see the "Index Statistics" topic in Books Online for more
>> >> details.
>> >> --
>> >> Umachandar Jayachandran
>> >> Microsoft SQL Server Performance Team
>> >> SQL Server Engine Team Tips Blog at
>> >> http://blogs.msdn.com/sqltips/default.aspx
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >> Leila wrote on Fri, 6 Oct 2006 12:33:54 +0330:
>> L>> Actually once I created a new database in SQL Server 2005 and
>> L>> imported tables from 2000. Then generated index scripts from
>> 2000
>> L>> and applied to 2005. This way I think there must not be need to
>> L>> updating statistics. The performance improved a little but still
>> L>> the execution plan in 2005 is poor and about 2 or 1.5 times
>> slower
>> L>> than 2000!
>>
>>
>> L>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> L>> news:%2304XchP6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> That should work ok assuming the DB isn't too big & you're not
>> >> trying to squeeze the task into a small window.
>> >> Another option is to rebuild the indexes, which might achieve
>> >> some
>> >> defragmentation as well, but I'd try the first option you
>> >> suggested
>> >> first.
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:eS%23qO0O6GHA.3952@.TK2MSFTNGP04.phx.gbl...
>> >> Thanks Greg,
>> >> Do you mean if I perform:
>> >> UPDATE STATISTICS MyTable WITH FULLSCAN on every table, will
>> >> correct the problem? Or other statements must be used for
>> >> indexes'
>>
>>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:epPHSOM6GHA.3508@.TK2MSFTNGP06.phx.gbl...
>> >> Hi Leila
>> >> Are you aware that upgraded databases have all of their index
>> >> statistics disabled & that you have to either recompute
>> >> statistics or rebuild the indexes before SQL Server 2005 will
>> >> use any indexes? You didn't mention this, so perhaps youre
>> >> problem is this simple?
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood
>> >> "Leila" <Leilas@.hotpop.com> wrote in message
>> >> news:O12cNSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
>> >>> Hi,
>> >>> We have a database in SQL Server 2000. I made a copy of this
>> >>> database(mdf,ndf,ldf) and attached it to SQL Server 2005 on
>> >>> the
>> >>> same machine. When I try one particular SP in both of them,
>> >>> SQL
>> >>> Server 2000 performs almost 2.5 times faster than 2005. The
>> >>> execution plan in 2005 contains HASH physical operators while
>> >>> 2000 has much better plan. I'm wondered that why this can
>> >>> happen while all of indexes are exactly the same in both
>> >>> databases.
>> >>> A few days ago, I heard from a colleague that he had
>> >>> experienced
>> >>> bad performance after upgrading the database of a customer to
>> >>> SQL
>> >>> Server 2005. They were forced to move the database to 2000
>> >>> again!
>> >>> I didn't believe it but now I'm experiencing a real one!
>> >>> Any help would be greatly appreciated.
>> >>> Leila
>>
>>
>>
>>
>>
>>
>>
>>
>>
>|||P@. (P@.@.discussions.microsoft.com) writes:
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under
> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided to let the process run till it ends) We've found that many of
> the queries containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
Unfortunately, the only advice I can give is to first use Profiler to
find the slow queries. Then analyse the query plans, and see what
improvements you can make. If you are lucky, there is a common theme
to all of them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Erland,
We exactly know the slow query! We have done everything (from new indexes to
indexed views) but 2005 is much slower than 2000 :(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B42562ACBBYazorman@.127.0.0.1...
> P@. (P@.@.discussions.microsoft.com) writes:
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under
>> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided to let the process run till it ends) We've found that many of
>> the queries containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
> Unfortunately, the only advice I can give is to first use Profiler to
> find the slow queries. Then analyse the query plans, and see what
> improvements you can make. If you are lucky, there is a common theme
> to all of them.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Not yet :(
"P@." <P@.@.discussions.microsoft.com> wrote in message
news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
> Hello Leila,
> Did you find any "friendly" solution to your performance issue?
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under SQL
> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided
> to let the process run till it ends) We've found that many of the queries
> containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
> Any hint is appreciated!
>
> Thank|||does your database is a new one or a copy from the SQL2000 and attached to
your SQL2005 server?
does your query force the order or use any query hint? (like forcing hash
joins, maxdop etc...)
does your hardware is the same?
do you have the x64 version?
I suffer a memory issue which cause the server to "loose" its memory which
cause the server to become near unresponsive.
So a standard process can takes hours instead of minutes.
Track the memory usage and if you see a the free memory to go from 0% to
100% you have the same issue.
ALL the applications see their memory usage to fall. (not only SQL Server)
"P@." <P@.@.discussions.microsoft.com> wrote in message
news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
> Hello Leila,
> Did you find any "friendly" solution to your performance issue?
> We're having the same issue with some of our queries. In fact, 1 of our
> Datawarehouse loading process that usually takes around 3 hours under SQL
> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
> decided
> to let the process run till it ends) We've found that many of the queries
> containing sub-queries are 20x slower than before :(
> First thing we did after upgrading was to DBCC CHECKDB, recreated indexes,
> updates stats with fullscan, update usage...
> I would really like to avoid re-optimizing every queries for SQL 2005.
> Any hint is appreciated!
>
> Thank|||I have restored db in 2005 and rebuilt all indexes. Both SQL Server
instances are on the same machine. I stop one of them while testing the
other instance.
No hint is used in the query. I'm using 32bit edition on 32bit Windows!
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:u4G42pYNHHA.1276@.TK2MSFTNGP04.phx.gbl...
> does your database is a new one or a copy from the SQL2000 and attached to
> your SQL2005 server?
> does your query force the order or use any query hint? (like forcing hash
> joins, maxdop etc...)
> does your hardware is the same?
> do you have the x64 version?
> I suffer a memory issue which cause the server to "loose" its memory which
> cause the server to become near unresponsive.
> So a standard process can takes hours instead of minutes.
> Track the memory usage and if you see a the free memory to go from 0% to
> 100% you have the same issue.
> ALL the applications see their memory usage to fall. (not only SQL Server)
> "P@." <P@.@.discussions.microsoft.com> wrote in message
> news:536109F1-5A16-486E-A34F-5EB74EB78F77@.microsoft.com...
>> Hello Leila,
>> Did you find any "friendly" solution to your performance issue?
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under SQL
>> 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided
>> to let the process run till it ends) We've found that many of the queries
>> containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
>> Any hint is appreciated!
>>
>> Thank
>|||Leila (Leilas@.hotpop.com) writes:
> We exactly know the slow query! We have done everything (from new
> indexes to indexed views) but 2005 is much slower than 2000 :(
Have you compared the query plans between SQL 2000 and SQL 2005?
I am afraid that I cannot say much more about a query that I know
nothing about.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Can you check if you get the same plan for both 2000 and 2005. Since you
already know the query.
If you find that the plans is different you can use hints or plan forcing to
try and reuse SQL 2000's plan.
Hardik
"Leila" <Leilas@.hotpop.com> wrote in message
news:e9tlD9INHHA.3312@.TK2MSFTNGP03.phx.gbl...
> Hi Erland,
> We exactly know the slow query! We have done everything (from new indexes
> to indexed views) but 2005 is much slower than 2000 :(
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98B42562ACBBYazorman@.127.0.0.1...
>> P@. (P@.@.discussions.microsoft.com) writes:
>> We're having the same issue with some of our queries. In fact, 1 of our
>> Datawarehouse loading process that usually takes around 3 hours under
>> SQL 2000 took 5days under 2005! yes, 5days! (ok, we were on holiday and
>> decided to let the process run till it ends) We've found that many of
>> the queries containing sub-queries are 20x slower than before :(
>> First thing we did after upgrading was to DBCC CHECKDB, recreated
>> indexes,
>> updates stats with fullscan, update usage...
>> I would really like to avoid re-optimizing every queries for SQL 2005.
>> Unfortunately, the only advice I can give is to first use Profiler to
>> find the slow queries. Then analyse the query plans, and see what
>> improvements you can make. If you are lucky, there is a common theme
>> to all of them.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||The plans are almost very different! I tried JOIN HINTs but there are much
more items that must be forced I suppose.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B6726A2A65Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
> Have you compared the query plans between SQL 2000 and SQL 2005?
> I am afraid that I cannot say much more about a query that I know
> nothing about.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Could you attach/add the query and the showplan_text for both 2000 and 2005
so that we have a better idea what is happening.
Hardik
"Leila" <Leilas@.hotpop.com> wrote in message
news:Ozbg36lNHHA.724@.TK2MSFTNGP04.phx.gbl...
> The plans are almost very different! I tried JOIN HINTs but there are much
> more items that must be forced I suppose.
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns98B6726A2A65Yazorman@.127.0.0.1...
>> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
>> Have you compared the query plans between SQL 2000 and SQL 2005?
>> I am afraid that I cannot say much more about a query that I know
>> nothing about.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>|||Leila (Leilas@.hotpop.com) writes:
> One important thing in comparison between these two plans appears in
> first 10 lines of CSV files: SQL Server 2000 performs some joins using
> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
> script that declares a cursor from indexes of the database and I do
> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
> statistics with FULLSCAN. This query takes about 11 seconds in SQL
> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
> appreciate any help! This problem has prevented us to migrate to SQL
> Server 2005.
You might have tried this already, but what happens if you rewrite the
query as:
SELECT *
FROM TLetter T
WHERE EXISTS (SELECT *
FROM VLetterPermissionNew V
WHERE V.lettRecordID = T.lettRecordID
AND V. /* ? */ dpusUserID= '00001000003Y')
AND TLetter.lettAccess <= @.UserAccess
I'm not saying that this is going to resolve the overall problem, but at
least you are eliminating the cost of the DISTINCT.
Also, would it be possible for you to change the UNION in the view to
UNION ALL?
Judging from the query plan, it appears that the permissions view is
very complex. This is not that surprising, we have some quite complex
permissions rule in our system as well. We have taken a different tack
on the problem though, and consequently we have the performance problem
elsewhere.
What we have done is to materialize the permissions in a table, so for
each account each user is permitted to see, there is a row in that
table. (With the exception for users that are permitted to see all
accounts. There is a special solution for these.) This makes it very
easy to apply permissions in queries. Just join against the permissions
table. I don't know in which context your query appears, but ours
typically appear in interactive forms, where 11 seconds in response
time would not be acceptable.
As you may guess, we have (had?) some problems with performance of
maintaining this table. There is an attempt to update the data directly
if there is a change that affects permissions, but we also have a nightly
job that makes a complete refresh. Particularly the nightly job has
sometimes been running a bit too long for our customers' taste.
I don't know if you have considered materialising your permissions. This
is not an entirely trivial task, as it reqiure a lot of changes to the
processes that upates the data. And if the updates are frequent, the
cost for maintaing the permissions table may preclude this solution. But
if you take this step, it is very likely that you will be able to reduce
execution times for queries like this to less than one second.
(I have here more or less ruled out indexed view out of hand, since
indexed views comes with so many restrictions. Onf of them is that
the view definition must not include UNION, which yours appear to do.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for great explanations Erland!
Actually this is the scenario of sending mails inside a big organization.
When a new mail is generated and sent by a user, for example all of the
recipients and their bosses in higher level of organization's hierarchy are
allowed to access the mail. I think materialization is impossible because
there are a lot of users! And the hierarchy is the big trouble! Imagine that
when a new mail is sent, hundreds of rows must be generated to indicate that
which users can access this mail. I believe that this mail system is not
using differed processing logic and everything must be immediate. Therefore
we cannot use nightly jobs.
We are satisfy with the performance of SQL Server 2000, we are not trying to
have this query in 1 second! At least we must gain 2000's performance in
2005.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B883D53E81CYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
>> appreciate any help! This problem has prevented us to migrate to SQL
>> Server 2005.
> You might have tried this already, but what happens if you rewrite the
> query as:
> SELECT *
> FROM TLetter T
> WHERE EXISTS (SELECT *
> FROM VLetterPermissionNew V
> WHERE V.lettRecordID = T.lettRecordID
> AND V. /* ? */ dpusUserID= '00001000003Y')
> AND TLetter.lettAccess <= @.UserAccess
> I'm not saying that this is going to resolve the overall problem, but at
> least you are eliminating the cost of the DISTINCT.
> Also, would it be possible for you to change the UNION in the view to
> UNION ALL?
> Judging from the query plan, it appears that the permissions view is
> very complex. This is not that surprising, we have some quite complex
> permissions rule in our system as well. We have taken a different tack
> on the problem though, and consequently we have the performance problem
> elsewhere.
> What we have done is to materialize the permissions in a table, so for
> each account each user is permitted to see, there is a row in that
> table. (With the exception for users that are permitted to see all
> accounts. There is a special solution for these.) This makes it very
> easy to apply permissions in queries. Just join against the permissions
> table. I don't know in which context your query appears, but ours
> typically appear in interactive forms, where 11 seconds in response
> time would not be acceptable.
> As you may guess, we have (had?) some problems with performance of
> maintaining this table. There is an attempt to update the data directly
> if there is a change that affects permissions, but we also have a nightly
> job that makes a complete refresh. Particularly the nightly job has
> sometimes been running a bit too long for our customers' taste.
> I don't know if you have considered materialising your permissions. This
> is not an entirely trivial task, as it reqiure a lot of changes to the
> processes that upates the data. And if the updates are frequent, the
> cost for maintaing the permissions table may preclude this solution. But
> if you take this step, it is very likely that you will be able to reduce
> execution times for queries like this to less than one second.
> (I have here more or less ruled out indexed view out of hand, since
> indexed views comes with so many restrictions. Onf of them is that
> the view definition must not include UNION, which yours appear to do.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks for great explanation Erland!
Actually this is the scenario of sending mails inside a big organization.
When a new mail is generated and sent by a user, for example all of the
recipients and their bosses in higher level of organization's hierarchy are
allowed to access the mail. I think materialization is impossible because
there are a lot of users! And the hierarchy is the big trouble! Imagine that
when a new mail is sent, hundreds of rows must be generated to indicate that
which users can access this mail. I believe that this mail system is not
using differed processing logic and everything must be immediate. Therefore
we cannot use nightly jobs.
We are satisfy with the performance of SQL Server 2000, we are not trying to
have this query in 1 second! At least we must gain 2000's performance in
2005.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B883D53E81CYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> One important thing in comparison between these two plans appears in
>> first 10 lines of CSV files: SQL Server 2000 performs some joins using
>> Merge algorithm while SQL Server 2005 uses Hash join. I have written a
>> script that declares a cursor from indexes of the database and I do
>> ALTER INDEX...REBUILD. Even I wrote a similar script to update all
>> statistics with FULLSCAN. This query takes about 11 seconds in SQL
>> Server 2000 (with cache) and 20 seconds in 2005 (with cache). I really
>> appreciate any help! This problem has prevented us to migrate to SQL
>> Server 2005.
> You might have tried this already, but what happens if you rewrite the
> query as:
> SELECT *
> FROM TLetter T
> WHERE EXISTS (SELECT *
> FROM VLetterPermissionNew V
> WHERE V.lettRecordID = T.lettRecordID
> AND V. /* ? */ dpusUserID= '00001000003Y')
> AND TLetter.lettAccess <= @.UserAccess
> I'm not saying that this is going to resolve the overall problem, but at
> least you are eliminating the cost of the DISTINCT.
> Also, would it be possible for you to change the UNION in the view to
> UNION ALL?
> Judging from the query plan, it appears that the permissions view is
> very complex. This is not that surprising, we have some quite complex
> permissions rule in our system as well. We have taken a different tack
> on the problem though, and consequently we have the performance problem
> elsewhere.
> What we have done is to materialize the permissions in a table, so for
> each account each user is permitted to see, there is a row in that
> table. (With the exception for users that are permitted to see all
> accounts. There is a special solution for these.) This makes it very
> easy to apply permissions in queries. Just join against the permissions
> table. I don't know in which context your query appears, but ours
> typically appear in interactive forms, where 11 seconds in response
> time would not be acceptable.
> As you may guess, we have (had?) some problems with performance of
> maintaining this table. There is an attempt to update the data directly
> if there is a change that affects permissions, but we also have a nightly
> job that makes a complete refresh. Particularly the nightly job has
> sometimes been running a bit too long for our customers' taste.
> I don't know if you have considered materialising your permissions. This
> is not an entirely trivial task, as it reqiure a lot of changes to the
> processes that upates the data. And if the updates are frequent, the
> cost for maintaing the permissions table may preclude this solution. But
> if you take this step, it is very likely that you will be able to reduce
> execution times for queries like this to less than one second.
> (I have here more or less ruled out indexed view out of hand, since
> indexed views comes with so many restrictions. Onf of them is that
> the view definition must not include UNION, which yours appear to do.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> Actually this is the scenario of sending mails inside a big
> organization. When a new mail is generated and sent by a user, for
> example all of the recipients and their bosses in higher level of
> organization's hierarchy are allowed to access the mail. I think
> materialization is impossible because there are a lot of users! And the
> hierarchy is the big trouble! Imagine that when a new mail is sent,
> hundreds of rows must be generated to indicate that which users can
> access this mail.
You are right, that does not sound like a bright idea. A materialized
table would have to build around more static entities. Whether that is
possible in your case, I have no idea.
Did you try the rewrite with EXISTS or consider whether UNION all was an
option?
By the way, if you have a hierachy to weed out, beware that SQL 2005
offers recursive CTE to handle these more easily.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Actually the DISTINCT and UNION are required, but I have not tried EXISTS
yet.
I was thinking of materialization using SSB! For example when a new mail is
sent, a message is sent to queue as well, therefore the user doesn't have to
wait until the materialization to complete. I have convinced my manager that
a latency about 2 or 3 minutes is acceptable so that other users who have
permission can view the new mail. What's your idea?
Another question, why EXISTS has advantage here that you suggest it?
Thanks!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B9729462BBYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Actually this is the scenario of sending mails inside a big
>> organization. When a new mail is generated and sent by a user, for
>> example all of the recipients and their bosses in higher level of
>> organization's hierarchy are allowed to access the mail. I think
>> materialization is impossible because there are a lot of users! And the
>> hierarchy is the big trouble! Imagine that when a new mail is sent,
>> hundreds of rows must be generated to indicate that which users can
>> access this mail.
> You are right, that does not sound like a bright idea. A materialized
> table would have to build around more static entities. Whether that is
> possible in your case, I have no idea.
> Did you try the rewrite with EXISTS or consider whether UNION all was an
> option?
> By the way, if you have a hierachy to weed out, beware that SQL 2005
> offers recursive CTE to handle these more easily.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> Actually the DISTINCT and UNION are required, but I have not tried EXISTS
> yet.
> I was thinking of materialization using SSB! For example when a new mail
> is sent, a message is sent to queue as well, therefore the user doesn't
> have to wait until the materialization to complete. I have convinced my
> manager that a latency about 2 or 3 minutes is acceptable so that other
> users who have permission can view the new mail. What's your idea?
Hah! That was an interesting idea! Service Broker really opens new
possibilities. I would never have though about that one, but in a case
like this, it can make sense, as it solves the problem for the user: having
to wait for the prompt.
> Another question, why EXISTS has advantage here that you suggest it?
When I see a query like:
SELECT DISCINT a.*
FROM a
JOIN b ON a.col = b.col
I get the idea that someone has added DISTINCT since the join to b can
have multiple matches. But since no column from b is included in the result
set, I assume that the join really means "show all rows from a that match a
condition in b", and this is better expressed with EXISTS for two reasons:
1) it speaks in clear terms what we are doing. 2) The DISTINCT is no longer
needed.
It is possible that you need DISTINCT nevertheless, but as I recall your
query had a SELECT *, and if you need DISTINCT in that case, this means
that your letters table does not have a primary key!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> I get the idea that someone has added DISTINCT since the join to b can
> have multiple matches. But since no column from b is included in the
> result
> set, I assume that the join really means "show all rows from a that match
> a
> condition in b", and this is better expressed with EXISTS for two reasons:
> 1) it speaks in clear terms what we are doing. 2) The DISTINCT is no
> longer
> needed.
Cool! I'll try it!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98B9A2A713BCCYazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Actually the DISTINCT and UNION are required, but I have not tried EXISTS
>> yet.
>> I was thinking of materialization using SSB! For example when a new mail
>> is sent, a message is sent to queue as well, therefore the user doesn't
>> have to wait until the materialization to complete. I have convinced my
>> manager that a latency about 2 or 3 minutes is acceptable so that other
>> users who have permission can view the new mail. What's your idea?
> Hah! That was an interesting idea! Service Broker really opens new
> possibilities. I would never have though about that one, but in a case
> like this, it can make sense, as it solves the problem for the user:
> having
> to wait for the prompt.
>> Another question, why EXISTS has advantage here that you suggest it?
> When I see a query like:
> SELECT DISCINT a.*
> FROM a
> JOIN b ON a.col = b.col
> I get the idea that someone has added DISTINCT since the join to b can
> have multiple matches. But since no column from b is included in the
> result
> set, I assume that the join really means "show all rows from a that match
> a
> condition in b", and this is better expressed with EXISTS for two reasons:
> 1) it speaks in clear terms what we are doing. 2) The DISTINCT is no
> longer
> needed.
> It is possible that you need DISTINCT nevertheless, but as I recall your
> query had a SELECT *, and if you need DISTINCT in that case, this means
> that your letters table does not have a primary key!
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks Hardik, I'll try it and post the result!
"Hardik Bati [MS]" <hardikb_removethis_@.microsoft.com> wrote in message
news:uSs%23waaOHHA.400@.TK2MSFTNGP04.phx.gbl...
> Can you try and see if this query works for you. I can't assure you but it
> might. Let me know what you find.
> Thanks,
> Hardik
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OfiaaoyNHHA.4848@.TK2MSFTNGP04.phx.gbl...
>> Ok! I attached a zip file to this message. It contains:
>> 1) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (Plain Text Format)
>> 2) Result of STATISTICS PROFILE for query execution in SQL Server 2000
>> (CSV
>> Format)
>> 3) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (Plain Text)
>> 4) Result of STATISTICS PROFILE for query execution in SQL Server 2005
>> (CSV
>> Format)
>> 5) XML Plan for query execution in SQL Server 2005
>> 6) The main query which I execute (It uses a view that is not included in
>> this file)
>> One important thing in comparison between these two plans appears in
>> first
>> 10 lines of CSV files: SQL Server 2000 performs some joins using Merge
>> algorithm while SQL Server 2005 uses Hash join. I have written a script
>> that
>> declares a cursor from indexes of the database and I do ALTER
>> INDEX...REBUILD. Even I wrote a similar script to update all statistics
>> with
>> FULLSCAN. This query takes about 11 seconds in SQL Server 2000 (with
>> cache)
>> and 20 seconds in 2005 (with cache).
>> I really appreciate any help! This problem has prevented us to migrate to
>> SQL Server 2005.
>>
>> "Hardik Bati [MS]" <hardikb_removethis_@.microsoft.com> wrote in message
>> news:uHw7sjoNHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> Could you attach/add the query and the showplan_text for both 2000 and
>> 2005
>> so that we have a better idea what is happening.
>> Hardik
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:Ozbg36lNHHA.724@.TK2MSFTNGP04.phx.gbl...
>> The plans are almost very different! I tried JOIN HINTs but there are
>> much
>> more items that must be forced I suppose.
>>
>> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>> news:Xns98B6726A2A65Yazorman@.127.0.0.1...
>> Leila (Leilas@.hotpop.com) writes:
>> We exactly know the slow query! We have done everything (from new
>> indexes to indexed views) but 2005 is much slower than 2000 :(
>> Have you compared the query plans between SQL 2000 and SQL 2005?
>> I am afraid that I cannot say much more about a query that I know
>> nothing about.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>>
>>
>
>sql