Sql Update command: performance

Showing posts with label performance. Show all posts

Friday, March 23, 2012

Problems Defragging Indices (SQL2005 64-bit)

Hi all,
I have just migrated a database from SQL 2000 32 bit to SQL 2005 64 bit. I
also upgraded the compatability level to 9. Performance was immediately
horrible in terms of query execution.
Trying to solve the issue, I ran a query to retrieve indices with avgfrag >
10%. I then tried rebuilding the indices with ALTER INDEX, and ran my query
again. The avgfrag had NOT CHANGED.
What am I doing wrong?
While we're at it, I got several timeout errors from clients connecting with
ADO.NET from with the network. Their app's connection string specifies a
timeout of 120 sec, but they were timing out well before this. Just before
my database/server "upgrade", this was NOT happening. Where to best change
this to prevent further timeouts until I can get my indices and stats in
better shape?
Thanks.
--
JohnHi John
Have you checked local connectivity and connectivity from (say) query
analyser to the new server? Is remote access enabled?
When migrating did you trasfer logins/users correctly, do you have orphaned
users? Have you tried scripting/recreating the indexes, I would also consider
doing this for all views/stored procedures/functions also. Make sure that you
have updated the usage and statistics as well.
John
"JT" wrote:
> Hi all,
> I have just migrated a database from SQL 2000 32 bit to SQL 2005 64 bit. I
> also upgraded the compatability level to 9. Performance was immediately
> horrible in terms of query execution.
> Trying to solve the issue, I ran a query to retrieve indices with avgfrag >
> 10%. I then tried rebuilding the indices with ALTER INDEX, and ran my query
> again. The avgfrag had NOT CHANGED.
> What am I doing wrong?
> While we're at it, I got several timeout errors from clients connecting with
> ADO.NET from with the network. Their app's connection string specifies a
> timeout of 120 sec, but they were timing out well before this. Just before
> my database/server "upgrade", this was NOT happening. Where to best change
> this to prevent further timeouts until I can get my indices and stats in
> better shape?
> Thanks.
> --
> John|||Hello John,
I understand that you tried to use "alter index ...rebuild all" statement
to some indexes, and its fragment is not changed. Also, you saw performance
degration after database migration to 2005 64 bit version from SQL 2000.
You encountered timout errors less than 120s from clients though connection
string specifies 120s. If I'm off base, please correct me.
Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
fragment information? Based on my experience, if a table does not include
clustered index, it may have large logical fragment and low density.
However, this does not necessarily indicate the tables/indexes are not
efficient for all queries. Generally, rebuild index for this situation does
not change the logical fragment value. You may consider use a different
fillfactor but I don't think this may be related to the performance issue
you encountered.
Also, does the issue occurs on some specific queries? Does 2 servers have
the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
of performance troubleshooting in 2005 is as following:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
First, please install SP1 on the server if it is not installed since many
known issues are addressed in sp1.
you may want to update the statistics information by using "update
statistics" statment with "fullscan" option to see if it does any
difference. You may also want to change the compatability level to 80 to
test. On the other thand, if the old server exist, you may want to compare
the execution plan on both servers to see if there is any differences:
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
Please run sp_configure on both server to see if there is any difference.
You'd like to change "max degree of parallelism" to 1 if you have
mulitprocessors on the server to see the result.
As for tiemout issue, you may first check if the issue occurs on local
console of SQL Server if possible. Also, did you set commandtimeout or
connectiontimout? Usually commandtimeout might be cause of this issue of
long running quries.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
mdprocommandtimeout.asp
To isolate the client/server issue, you may want to install latest mdac and
sql native client on different machine, and make sure the issue occurs on
different clients
How to obtain the latest MDAC 2.8 service pack
http://support.microsoft.com/kb/884103/
http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
AA0A-B477BA72A9CB&displaylang=en
If you isolate this to be a server side issue, you may want to check if you
encounter the following known issue and get fix from it to see if you
encoutnered the issue.
FIX: The internal deadlock monitor may not detect a deadlock between two or
more sessions in SQL Server 2005
http://support.microsoft.com/kb/915918
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you John and Peter. The problem seems to be isolated to several
queries that rely on Indexed Views for their execution plan. The indices on
these Indexed Views really seem to be the problem. I was able to make quite
a bit of headway by disposing of several of the indices in these views, and
taking advantage of the new INCLUDE feature. Things still do not run as fast
(about 50% SLOWER) as they did on SQL 2000 32 bit, but at least I am no
longer getting the timeouts.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I understand that you tried to use "alter index ...rebuild all" statement
> to some indexes, and its fragment is not changed. Also, you saw performance
> degration after database migration to 2005 64 bit version from SQL 2000.
> You encountered timout errors less than 120s from clients though connection
> string specifies 120s. If I'm off base, please correct me.
> Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
> fragment information? Based on my experience, if a table does not include
> clustered index, it may have large logical fragment and low density.
> However, this does not necessarily indicate the tables/indexes are not
> efficient for all queries. Generally, rebuild index for this situation does
> not change the logical fragment value. You may consider use a different
> fillfactor but I don't think this may be related to the performance issue
> you encountered.
> Also, does the issue occurs on some specific queries? Does 2 servers have
> the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
> of performance troubleshooting in 2005 is as following:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> First, please install SP1 on the server if it is not installed since many
> known issues are addressed in sp1.
> you may want to update the statistics information by using "update
> statistics" statment with "fullscan" option to see if it does any
> difference. You may also want to change the compatability level to 80 to
> test. On the other thand, if the old server exist, you may want to compare
> the execution plan on both servers to see if there is any differences:
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/?id=243589
> Please run sp_configure on both server to see if there is any difference.
> You'd like to change "max degree of parallelism" to 1 if you have
> mulitprocessors on the server to see the result.
> As for tiemout issue, you may first check if the issue occurs on local
> console of SQL Server if possible. Also, did you set commandtimeout or
> connectiontimout? Usually commandtimeout might be cause of this issue of
> long running quries.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
> mdprocommandtimeout.asp
> To isolate the client/server issue, you may want to install latest mdac and
> sql native client on different machine, and make sure the issue occurs on
> different clients
> How to obtain the latest MDAC 2.8 service pack
> http://support.microsoft.com/kb/884103/
> http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
> AA0A-B477BA72A9CB&displaylang=en
> If you isolate this to be a server side issue, you may want to check if you
> encounter the following known issue and get fix from it to see if you
> encoutnered the issue.
> FIX: The internal deadlock monitor may not detect a deadlock between two or
> more sessions in SQL Server 2005
> http://support.microsoft.com/kb/915918
> If you have any update, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||UNBELIEVABLE! Last night I had a particular problem sproc running in 15
seconds on my 2GB laptop with 64 bit CPU and SQL Server 2005 64 bit. I
drop/created the underlying indexed views on my 4 CPU XEON 3.3Ghz with 15k
discs DELL 6850, 15k SAN discs, separated mdfs, ldfs, and tempdb on RAID 1 or
RAID 10 production clustered setup, and the best I can get is 45 seconds with
the same procedure. This is even after a full stats update and index
rebuilding. What am I doing wrong? This occurs whether am I running from a
remote client, or locally on the host node. The databases are the same, as
is the underlying data.
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I understand that you tried to use "alter index ...rebuild all" statement
> to some indexes, and its fragment is not changed. Also, you saw performance
> degration after database migration to 2005 64 bit version from SQL 2000.
> You encountered timout errors less than 120s from clients though connection
> string specifies 120s. If I'm off base, please correct me.
> Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
> fragment information? Based on my experience, if a table does not include
> clustered index, it may have large logical fragment and low density.
> However, this does not necessarily indicate the tables/indexes are not
> efficient for all queries. Generally, rebuild index for this situation does
> not change the logical fragment value. You may consider use a different
> fillfactor but I don't think this may be related to the performance issue
> you encountered.
> Also, does the issue occurs on some specific queries? Does 2 servers have
> the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
> of performance troubleshooting in 2005 is as following:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> First, please install SP1 on the server if it is not installed since many
> known issues are addressed in sp1.
> you may want to update the statistics information by using "update
> statistics" statment with "fullscan" option to see if it does any
> difference. You may also want to change the compatability level to 80 to
> test. On the other thand, if the old server exist, you may want to compare
> the execution plan on both servers to see if there is any differences:
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/?id=243589
> Please run sp_configure on both server to see if there is any difference.
> You'd like to change "max degree of parallelism" to 1 if you have
> mulitprocessors on the server to see the result.
> As for tiemout issue, you may first check if the issue occurs on local
> console of SQL Server if possible. Also, did you set commandtimeout or
> connectiontimout? Usually commandtimeout might be cause of this issue of
> long running quries.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
> mdprocommandtimeout.asp
> To isolate the client/server issue, you may want to install latest mdac and
> sql native client on different machine, and make sure the issue occurs on
> different clients
> How to obtain the latest MDAC 2.8 service pack
> http://support.microsoft.com/kb/884103/
> http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
> AA0A-B477BA72A9CB&displaylang=en
> If you isolate this to be a server side issue, you may want to check if you
> encounter the following known issue and get fix from it to see if you
> encoutnered the issue.
> FIX: The internal deadlock monitor may not detect a deadlock between two or
> more sessions in SQL Server 2005
> http://support.microsoft.com/kb/915918
> If you have any update, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Same execution plan?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <Jthayer@.online.nospam> wrote in message
news:E3A82D97-5063-4803-8B5E-2C1B6AD3BCCA@.microsoft.com...
> UNBELIEVABLE! Last night I had a particular problem sproc running in 15
> seconds on my 2GB laptop with 64 bit CPU and SQL Server 2005 64 bit. I
> drop/created the underlying indexed views on my 4 CPU XEON 3.3Ghz with 15k
> discs DELL 6850, 15k SAN discs, separated mdfs, ldfs, and tempdb on RAID 1 or
> RAID 10 production clustered setup, and the best I can get is 45 seconds with
> the same procedure. This is even after a full stats update and index
> rebuilding. What am I doing wrong? This occurs whether am I running from a
> remote client, or locally on the host node. The databases are the same, as
> is the underlying data.
> --
> John
>
> "Peter Yang [MSFT]" wrote:
>> Hello John,
>> I understand that you tried to use "alter index ...rebuild all" statement
>> to some indexes, and its fragment is not changed. Also, you saw performance
>> degration after database migration to 2005 64 bit version from SQL 2000.
>> You encountered timout errors less than 120s from clients though connection
>> string specifies 120s. If I'm off base, please correct me.
>> Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
>> fragment information? Based on my experience, if a table does not include
>> clustered index, it may have large logical fragment and low density.
>> However, this does not necessarily indicate the tables/indexes are not
>> efficient for all queries. Generally, rebuild index for this situation does
>> not change the logical fragment value. You may consider use a different
>> fillfactor but I don't think this may be related to the performance issue
>> you encountered.
>> Also, does the issue occurs on some specific queries? Does 2 servers have
>> the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
>> of performance troubleshooting in 2005 is as following:
>> Troubleshooting Performance Problems in SQL Server 2005
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
>> First, please install SP1 on the server if it is not installed since many
>> known issues are addressed in sp1.
>> you may want to update the statistics information by using "update
>> statistics" statment with "fullscan" option to see if it does any
>> difference. You may also want to change the compatability level to 80 to
>> test. On the other thand, if the old server exist, you may want to compare
>> the execution plan on both servers to see if there is any differences:
>> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
>> http://support.microsoft.com/?id=243589
>> Please run sp_configure on both server to see if there is any difference.
>> You'd like to change "max degree of parallelism" to 1 if you have
>> mulitprocessors on the server to see the result.
>> As for tiemout issue, you may first check if the issue occurs on local
>> console of SQL Server if possible. Also, did you set commandtimeout or
>> connectiontimout? Usually commandtimeout might be cause of this issue of
>> long running quries.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
>> mdprocommandtimeout.asp
>> To isolate the client/server issue, you may want to install latest mdac and
>> sql native client on different machine, and make sure the issue occurs on
>> different clients
>> How to obtain the latest MDAC 2.8 service pack
>> http://support.microsoft.com/kb/884103/
>> http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
>> AA0A-B477BA72A9CB&displaylang=en
>> If you isolate this to be a server side issue, you may want to check if you
>> encounter the following known issue and get fix from it to see if you
>> encoutnered the issue.
>> FIX: The internal deadlock monitor may not detect a deadlock between two or
>> more sessions in SQL Server 2005
>> http://support.microsoft.com/kb/915918
>> If you have any update, please feel free to let's know. Thank you.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications
>> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>|||Hi Tibor,
Approximately so, but not fully investigated as these are quite complicated
queries that go way off the page when displaying the exec plan.
One thing I am noticing as I investigate these indices...
When I use SSMS to examine the indices of this database sitting on a SQL
2000 instance, I have no options for Use RowLocks and Use Page Locks when
accessing the index (as expected). When I examine the same database after
migration to SQL 2005 (via forced restore), I see the same indices with both
of these options checked. Could it be helpful to go through and uncheck
these boxes? Is there some way to do this en masse? Thanks.
--
John
"Tibor Karaszi" wrote:
> Same execution plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JT" <Jthayer@.online.nospam> wrote in message
> news:E3A82D97-5063-4803-8B5E-2C1B6AD3BCCA@.microsoft.com...
> > UNBELIEVABLE! Last night I had a particular problem sproc running in 15
> > seconds on my 2GB laptop with 64 bit CPU and SQL Server 2005 64 bit. I
> > drop/created the underlying indexed views on my 4 CPU XEON 3.3Ghz with 15k
> > discs DELL 6850, 15k SAN discs, separated mdfs, ldfs, and tempdb on RAID 1 or
> > RAID 10 production clustered setup, and the best I can get is 45 seconds with
> > the same procedure. This is even after a full stats update and index
> > rebuilding. What am I doing wrong? This occurs whether am I running from a
> > remote client, or locally on the host node. The databases are the same, as
> > is the underlying data.
> >
> > --
> > John
> >
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello John,
> >>
> >> I understand that you tried to use "alter index ...rebuild all" statement
> >> to some indexes, and its fragment is not changed. Also, you saw performance
> >> degration after database migration to 2005 64 bit version from SQL 2000.
> >> You encountered timout errors less than 120s from clients though connection
> >> string specifies 120s. If I'm off base, please correct me.
> >>
> >> Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
> >> fragment information? Based on my experience, if a table does not include
> >> clustered index, it may have large logical fragment and low density.
> >> However, this does not necessarily indicate the tables/indexes are not
> >> efficient for all queries. Generally, rebuild index for this situation does
> >> not change the logical fragment value. You may consider use a different
> >> fillfactor but I don't think this may be related to the performance issue
> >> you encountered.
> >>
> >> Also, does the issue occurs on some specific queries? Does 2 servers have
> >> the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
> >> of performance troubleshooting in 2005 is as following:
> >>
> >> Troubleshooting Performance Problems in SQL Server 2005
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
> >>
> >> First, please install SP1 on the server if it is not installed since many
> >> known issues are addressed in sp1.
> >>
> >> you may want to update the statistics information by using "update
> >> statistics" statment with "fullscan" option to see if it does any
> >> difference. You may also want to change the compatability level to 80 to
> >> test. On the other thand, if the old server exist, you may want to compare
> >> the execution plan on both servers to see if there is any differences:
> >>
> >> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> >> http://support.microsoft.com/?id=243589
> >>
> >> Please run sp_configure on both server to see if there is any difference.
> >> You'd like to change "max degree of parallelism" to 1 if you have
> >> mulitprocessors on the server to see the result.
> >>
> >> As for tiemout issue, you may first check if the issue occurs on local
> >> console of SQL Server if possible. Also, did you set commandtimeout or
> >> connectiontimout? Usually commandtimeout might be cause of this issue of
> >> long running quries.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
> >> mdprocommandtimeout.asp
> >>
> >> To isolate the client/server issue, you may want to install latest mdac and
> >> sql native client on different machine, and make sure the issue occurs on
> >> different clients
> >>
> >> How to obtain the latest MDAC 2.8 service pack
> >> http://support.microsoft.com/kb/884103/
> >>
> >> http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
> >> AA0A-B477BA72A9CB&displaylang=en
> >>
> >> If you isolate this to be a server side issue, you may want to check if you
> >> encounter the following known issue and get fix from it to see if you
> >> encoutnered the issue.
> >>
> >> FIX: The internal deadlock monitor may not detect a deadlock between two or
> >> more sessions in SQL Server 2005
> >> http://support.microsoft.com/kb/915918
> >>
> >> If you have any update, please feel free to let's know. Thank you.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Community Support
> >> ==================================================> >> Get notification to my posts through email? Please refer to
> >> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> >> ications
> >> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> >> where an initial response from the community or a Microsoft Support
> >> Engineer within 1 business day is acceptable. Please note that each follow
> >> up response may take approximately 2 business days as the support
> >> professional working with you may need further investigation to reach the
> >> most efficient resolution. The offering is not appropriate for situations
> >> that require urgent, real-time or phone-based interactions or complex
> >> project analysis and dump analysis issues. Issues of this nature are best
> >> handled working with a dedicated Microsoft Support Engineer by contacting
> >> Microsoft Customer Support Services (CSS) at
> >> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >>
>|||The "allow row locks" and "allow page locks" option is available on earlier versions as well (I
believe since 6.5). In 2005, you set the option using CREATE and ALTER INDEX, in earlier versions
you set it through sp_indexoption. Default is to allow both page and row locks. So I very much doubt
that this is the issue here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <Jthayer@.online.nospam> wrote in message
news:51197459-F3F5-40BC-9A53-9AC2C3080C0D@.microsoft.com...
> Hi Tibor,
> Approximately so, but not fully investigated as these are quite complicated
> queries that go way off the page when displaying the exec plan.
> One thing I am noticing as I investigate these indices...
> When I use SSMS to examine the indices of this database sitting on a SQL
> 2000 instance, I have no options for Use RowLocks and Use Page Locks when
> accessing the index (as expected). When I examine the same database after
> migration to SQL 2005 (via forced restore), I see the same indices with both
> of these options checked. Could it be helpful to go through and uncheck
> these boxes? Is there some way to do this en masse? Thanks.
> --
> John
>
> "Tibor Karaszi" wrote:
>> Same execution plan?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "JT" <Jthayer@.online.nospam> wrote in message
>> news:E3A82D97-5063-4803-8B5E-2C1B6AD3BCCA@.microsoft.com...
>> > UNBELIEVABLE! Last night I had a particular problem sproc running in 15
>> > seconds on my 2GB laptop with 64 bit CPU and SQL Server 2005 64 bit. I
>> > drop/created the underlying indexed views on my 4 CPU XEON 3.3Ghz with 15k
>> > discs DELL 6850, 15k SAN discs, separated mdfs, ldfs, and tempdb on RAID 1 or
>> > RAID 10 production clustered setup, and the best I can get is 45 seconds with
>> > the same procedure. This is even after a full stats update and index
>> > rebuilding. What am I doing wrong? This occurs whether am I running from a
>> > remote client, or locally on the host node. The databases are the same, as
>> > is the underlying data.
>> >
>> > --
>> > John
>> >
>> >
>> > "Peter Yang [MSFT]" wrote:
>> >
>> >> Hello John,
>> >>
>> >> I understand that you tried to use "alter index ...rebuild all" statement
>> >> to some indexes, and its fragment is not changed. Also, you saw performance
>> >> degration after database migration to 2005 64 bit version from SQL 2000.
>> >> You encountered timout errors less than 120s from clients though connection
>> >> string specifies 120s. If I'm off base, please correct me.
>> >>
>> >> Did you use sys.dm_db_index_physical_stats or dbcc showcontig to get the
>> >> fragment information? Based on my experience, if a table does not include
>> >> clustered index, it may have large logical fragment and low density.
>> >> However, this does not necessarily indicate the tables/indexes are not
>> >> efficient for all queries. Generally, rebuild index for this situation does
>> >> not change the logical fragment value. You may consider use a different
>> >> fillfactor but I don't think this may be related to the performance issue
>> >> you encountered.
>> >>
>> >> Also, does the issue occurs on some specific queries? Does 2 servers have
>> >> the same load? Did you see any bottleneck in memory/IO/CPU? The whitepaper
>> >> of performance troubleshooting in 2005 is as following:
>> >>
>> >> Troubleshooting Performance Problems in SQL Server 2005
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
>> >>
>> >> First, please install SP1 on the server if it is not installed since many
>> >> known issues are addressed in sp1.
>> >>
>> >> you may want to update the statistics information by using "update
>> >> statistics" statment with "fullscan" option to see if it does any
>> >> difference. You may also want to change the compatability level to 80 to
>> >> test. On the other thand, if the old server exist, you may want to compare
>> >> the execution plan on both servers to see if there is any differences:
>> >>
>> >> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
>> >> http://support.microsoft.com/?id=243589
>> >>
>> >> Please run sp_configure on both server to see if there is any difference.
>> >> You'd like to change "max degree of parallelism" to 1 if you have
>> >> mulitprocessors on the server to see the result.
>> >>
>> >> As for tiemout issue, you may first check if the issue occurs on local
>> >> console of SQL Server if possible. Also, did you set commandtimeout or
>> >> connectiontimout? Usually commandtimeout might be cause of this issue of
>> >> long running quries.
>> >>
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
>> >> mdprocommandtimeout.asp
>> >>
>> >> To isolate the client/server issue, you may want to install latest mdac and
>> >> sql native client on different machine, and make sure the issue occurs on
>> >> different clients
>> >>
>> >> How to obtain the latest MDAC 2.8 service pack
>> >> http://support.microsoft.com/kb/884103/
>> >>
>> >> http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-
>> >> AA0A-B477BA72A9CB&displaylang=en
>> >>
>> >> If you isolate this to be a server side issue, you may want to check if you
>> >> encounter the following known issue and get fix from it to see if you
>> >> encoutnered the issue.
>> >>
>> >> FIX: The internal deadlock monitor may not detect a deadlock between two or
>> >> more sessions in SQL Server 2005
>> >> http://support.microsoft.com/kb/915918
>> >>
>> >> If you have any update, please feel free to let's know. Thank you.
>> >>
>> >> Best Regards,
>> >>
>> >> Peter Yang
>> >> MCSE2000/2003, MCSA, MCDBA
>> >> Microsoft Online Community Support
>> >> ==================================================>> >> Get notification to my posts through email? Please refer to
>> >> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> >> ications
>> >> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>> >> where an initial response from the community or a Microsoft Support
>> >> Engineer within 1 business day is acceptable. Please note that each follow
>> >> up response may take approximately 2 business days as the support
>> >> professional working with you may need further investigation to reach the
>> >> most efficient resolution. The offering is not appropriate for situations
>> >> that require urgent, real-time or phone-based interactions or complex
>> >> project analysis and dump analysis issues. Issues of this nature are best
>> >> handled working with a dedicated Microsoft Support Engineer by contacting
>> >> Microsoft Customer Support Services (CSS) at
>> >> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> >> ==================================================>> >> This posting is provided "AS IS" with no warranties, and confers no rights.
>> >>
>> >>
>>|||Hello John,
By default, the optioins are checked and isually it is not recommended to
uncheck "use row locks" and "use page locks" for the indexes. SQL Server
shall select row locks/page locks for the better performance for specific
queries. For example, for operations that only relates several rows, it is
not efficient to only allow page locks. However, you may try to uncheck
this to see if there is any difference.
First, as Tibor mentioned, you may compare the exectution plans of both
servers to make sure it is exact same. If it is same, you may try the
following steps to get the exact
DBCC TRACEON (8760, 8605, 8606, 8607)
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
dbcc freeproccache
dbcc dropcleanbuffers
Go
--Exec <place your stored procedure or query here>
go
SET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
DBCC TRACEOFF (8760, 8605, 8606, 8607)
go
Please save the result execution plan in CSV file, and copy the io/cpu/time
information in message window to a file, and send them to me at
petery@.microsoft.com.
In addtion, I'd like to know if the cluster server is a production server
under heavy load. You may use perfmon to check if there is any memory/iO
pressure on the server. Usually on a heavy load server, it is possible that
a query might be slower than a server without other loads.
http://www.extremeexperts.com/SQL/Articles/SQLCounters.aspx
If query execution plan is different, it might be related to the following
- The metadata of the tables used in the query (columns, indexes,
constraints, indexed views, etc)
- The size of the tables and other physical structures (number of rows,
pages)
- statistics (created at some point in time in the past over the data in
the tables)
- The MAXDOP setting
- The amount of memory on the machine
- Session settings (ANSI_NULLs, etc)
- Difference in server engine
You may want to check above to see if there is any difference. If the query
is related to index view, you may want to tempoarily drop the view on both
servers to test the situation.
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi John,
I've also experienced the drop in performance when going from SQL 2000 32
Bit to SQL 2005 64 Bit. After a lot of tuning I got it back to the same
performance as in SQL 2000, but this includes rewriting a lot of queries. It
seems that the query optimizer is working totally different than in SQL
2000. Complex derived tables and subqueries with inner subqueries are good
candidates for rewriting.
So much work to get the same speed.
Kay|||Peter - thanks for your most generous offer to take on looking over the
execution plans. For now, I will spare you of this. This weekend, I plan to
devote some serious time to optimizing and restructuring several queries. If
I do not succeed, I will then take you up on your offer. Thanks for being
such an excellent resource!
--
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> By default, the optioins are checked and isually it is not recommended to
> uncheck "use row locks" and "use page locks" for the indexes. SQL Server
> shall select row locks/page locks for the better performance for specific
> queries. For example, for operations that only relates several rows, it is
> not efficient to only allow page locks. However, you may try to uncheck
> this to see if there is any difference.
> First, as Tibor mentioned, you may compare the exectution plans of both
> servers to make sure it is exact same. If it is same, you may try the
> following steps to get the exact
>
> DBCC TRACEON (8760, 8605, 8606, 8607)
> SET STATISTICS PROFILE ON
> SET STATISTICS TIME ON
> SET STATISTICS IO ON
> dbcc freeproccache
> dbcc dropcleanbuffers
> Go
> --Exec <place your stored procedure or query here>
> go
> SET STATISTICS PROFILE OFF
> SET STATISTICS TIME OFF
> SET STATISTICS IO OFF
> DBCC TRACEOFF (8760, 8605, 8606, 8607)
> go
>
> Please save the result execution plan in CSV file, and copy the io/cpu/time
> information in message window to a file, and send them to me at
> petery@.microsoft.com.
> In addtion, I'd like to know if the cluster server is a production server
> under heavy load. You may use perfmon to check if there is any memory/iO
> pressure on the server. Usually on a heavy load server, it is possible that
> a query might be slower than a server without other loads.
> http://www.extremeexperts.com/SQL/Articles/SQLCounters.aspx
> If query execution plan is different, it might be related to the following
> - The metadata of the tables used in the query (columns, indexes,
> constraints, indexed views, etc)
> - The size of the tables and other physical structures (number of rows,
> pages)
> - statistics (created at some point in time in the past over the data in
> the tables)
> - The MAXDOP setting
> - The amount of memory on the machine
> - Session settings (ANSI_NULLs, etc)
> - Difference in server engine
> You may want to check above to see if there is any difference. If the query
> is related to index view, you may want to tempoarily drop the view on both
> servers to test the situation.
> If you have any update, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello John,
I'm still interested in this issue. Did you try to solve the issue in the
weekend? If the issue persists, please get the informaiton I mentioend so
that we may further troubleshoot the issue. If you have other priorites,
please also let's know and we may work on the problem a little alter. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Peter,
I have largely resolved the issue. The steps I took included
1. greatly expanding the resources of tempdb, giving it eight equally sized
200MB mdf/ndf files to work with on a RAID 1/0 LUN.
2. Using NOEXPAND to force the optimizer to choose indexed views. (This
made a HUGE difference - like 1000% performance gains.) You can see my post
on this in SQL Server.Programming under Indexed View Issue, dated 11/22/2006).
3. Identifying an error in the 2005 scripting wizard which has been sending
me on a tail-chase regarding indices on Indexed Views. You can see my post
on this at SQL Server.Server under WARNING-SCRIPTING ENGINE UNRELIABLE, dated
11/26/2006.
4. Reading Itzak Ben-Gan's two books and doing a lot of optimization work.
Bottom line, I had a particularly gruesome query that ran in 7 seconds with
SQL 2000, and then went up to 25 seconds in SQL 2005 x64. I got it down to
1.7 seconds now, still using SQL 2005 x64. This is just one of many examples.
Thanks for your help.
John
"Peter Yang [MSFT]" wrote:
> Hello John,
> I'm still interested in this issue. Did you try to solve the issue in the
> weekend? If the issue persists, please get the informaiton I mentioend so
> that we may further troubleshoot the issue. If you have other priorites,
> please also let's know and we may work on the problem a little alter. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi John
Thanks for the feed back,
Links to the previous threads:
2. Indexed View Issue http://tinyurl.com/yj4qvn
3. WARNING-SCRIPTING ENGINE UNRELIABLE - http://tinyurl.com/wu6aq
IMO Itzik's books in the Inside SQL Server 2005 series are must buys for any
Programmer/DBA using SQL Server 2005.
John
"JT" wrote:
> Hi Peter,
> I have largely resolved the issue. The steps I took included
> 1. greatly expanding the resources of tempdb, giving it eight equally sized
> 200MB mdf/ndf files to work with on a RAID 1/0 LUN.
> 2. Using NOEXPAND to force the optimizer to choose indexed views. (This
> made a HUGE difference - like 1000% performance gains.) You can see my post
> on this in SQL Server.Programming under Indexed View Issue, dated 11/22/2006).
> 3. Identifying an error in the 2005 scripting wizard which has been sending
> me on a tail-chase regarding indices on Indexed Views. You can see my post
> on this at SQL Server.Server under WARNING-SCRIPTING ENGINE UNRELIABLE, dated
> 11/26/2006.
> 4. Reading Itzak Ben-Gan's two books and doing a lot of optimization work.
> Bottom line, I had a particularly gruesome query that ran in 7 seconds with
> SQL 2000, and then went up to 25 seconds in SQL 2005 x64. I got it down to
> 1.7 seconds now, still using SQL 2005 x64. This is just one of many examples.
> Thanks for your help.
>
> --
> John
>
> "Peter Yang [MSFT]" wrote:
> > Hello John,
> >
> > I'm still interested in this issue. Did you try to solve the issue in the
> > weekend? If the issue persists, please get the informaiton I mentioend so
> > that we may further troubleshoot the issue. If you have other priorites,
> > please also let's know and we may work on the problem a little alter. Thank
> > you.
> >
> > Best Regards,
> >
> > Peter Yang
> > MCSE2000/2003, MCSA, MCDBA
> > Microsoft Online Partner Support
> >
> >
> > =====================================================> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > ======================================================> >
> >
> >|||Hello John,
Thank you for sharing your experience on this issue and this shall
defenitely benefit the community. :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Friday, March 9, 2012

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> Ð´ÈëÏûÏ¢ÐÂÎÅ
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> Ð´ÈëÏûÏ¢ÐÂÎÅ
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> > treese
> > Do you have appropriate indexes for your table?
> > Using LIKE '%Something' may prevent from query optimizer to use an
index.
> >
> >
> >
> >
> > "treesy" <treesy@.hostran.com.cn> wrote in message
> > news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > > I have a table of 60,000rows. There are 47 fields in the table. 43 of
> them
> > > are nvarchar with length of 100. Two of them are big nvarchar with
> length
> > of
> > > 1024 and 2048. an indexno field is int and the other is date. two
> primary
> > > keys, one of them is indexno;the other is nvarchar.
> > >
> > > Low performance:
> > > 1.using query of 'like', it shows timeout. I have to use indexno to
> > control
> > > the number of the rows i am querying.
> > > 2.When I use application program to query some length of bytes, it
costs
> > > quite a lot of minutes.
> > > Any one can help?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> Ð´ÈëÏûÏ¢ÐÂÎÅ
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>

Problem: sql server 2000 server low performance. thanks:)

Problem: Performance difference between MSDE and SQL Express 2005

Hello, all, I started out thinking my problems were elsewhere but as I
have worked through this I have isolated my problem, currently, as a
difference between MSDE and SQL Express 2005 (I'll just call it
Express for simplicity).

I have, to try to simplify things, put the exact same DB on two
systems, one running MSDE and one running Express. Both have 2 Ghz
processors (one Intel, one AMD), both have a decent amount of RAM
(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB of
free disk space. MSDE is running on the Intel system, Express is
running on the AMD system. To keep things fair I use the exact same
DB's and query on both systems. The DB's were created on MSDE so I
sp_detach_db'd them from MSDE and then sp_attach_db'd them to Express
(this is how MS says to do a "side-by-side" upgrade, so it's
acceptable to do so). After fighting problems in performance
differences in different situations I have narrowed the problem down
to this:

Executing a simple select statement with join clause on the databases
yields a difference in execution time that is quite great. Using the
Express Management program I can run the query against either system
(MSDE or Express, the two systems are connected via crossover cable to
eliminate any network problems/issues). When running the query
against the MSDE system (which is over the network) I consistently get
<20 ms response times on the query. When running the query against
the Express installation (which is in shared memory) I consistently
get 700 ms or longer response times. Both times are for the Total
Execution Time.

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3

So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.

The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.

Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.

Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.

The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.

I'm sorry to be stupid but I have this information but I don't know
what to do with it. The best that I can tell from this is that this
is the source of my problems. My problems are that on my current
systems that my clients use the data is returned to them faster than
they can click the mouse and that the new system (that is, when they
chose (or are forced by attrition) to move to Vista and thus Express
2005) the screen pop is like 1.5 seconds. This creates poor user
experience. Worse, one process I allow the users to do goes from
taking 14-30 seconds to over 4 minutes (all on the same machine with
the same OS and version of my program, so it's not a machine or OS or
my app problem).

Anyway, I hope someone can shed some light on this now that I've pared
it down some.

Thanks in advance.

--HCHC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3
>
So, gimme all the columns from one table in one DB (local to the
installation), matching the records in another DB (also local to the
installation), where one field in the first db matches a field in the
second db and where, in the first db, one column value = 3.
>
The first table has a total record count of 630 records of which only
12 match the where clause. The second table has a total record count
of about 2,700 of which only 12 match up on the 12 out of 630.
>
Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.
>
Checking the Execution Plan reveals that both queries have the same
steps, but, on the MSDE system the largest consumer in the process is
the Clustered Index Scan of the 630 record table (DB1 in my query
example), using 85%. The next big consumer is a Clustered Index Seek
against the other table (2,700 rows), using 15%.
>
The Execution Plan against the Express system reveals basically the
exact opposite: 27% going to the Clustered Index Scan of the 630
record DB1, and 72% going to the Clustered Index Seek of the 2,700
record DB2.

Is there any index on the criteria column? It does not sound like
that, since you get a clustered scan on that table.

But before you apply any index, can you run the queries in both servers
preceded by

SET STATISTICS PROFILE ON

and then post the output? Since the output is very wide, it's not good
if you put directly into the article body, please put it in an attachment.
I see that you post from Google. I don't know if they permit attachments,
but if they don't, maybe you could put the output on a web site and just
post a URL?

Quote:

Originally Posted by

Worse, one process I allow the users to do goes from taking 14-30
seconds to over 4 minutes (all on the same machine with the same OS and
version of my program, so it's not a machine or OS or my app problem).

I suppose this process is against some completely different tables?
14 seconds on the table sizes you mentioned sounds absymal to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 4, 6:12 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

The query is simply this: select db1.* from db1.owner.tablename as db1
inner join db2.owner.tablename as db2 on db1.pkey = db2.someid where
db1.criteria = 3

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Even though the data is the same and I've done the detach and attach,
and even done the sp_updatestats, the difference in execution time is
remarkable, in a bad way.

Quote:

Originally Posted by

Quote:

Originally Posted by

>
Is there any index on the criteria column? It does not sound like
that, since you get a clustered scan on that table.
>
But before you apply any index, can you run the queries in both servers
preceded by
>
SET STATISTICS PROFILE ON
>
and then post the output? Since the output is very wide, it's not good
if you put directly into the article body, please put it in an attachment.
I see that you post from Google. I don't know if they permit attachments,
but if they don't, maybe you could put the output on a web site and just
post a URL?
>

Quote:

Originally Posted by

>
I suppose this process is against some completely different tables?
14 seconds on the table sizes you mentioned sounds absymal to me.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Hmm, I thought I posted this...but it has not shown up in the last 30
minutes so I'm going to send it again, I'm sorry if it winds up
showing twice...

Hello, Erland. I have no user-defined indexes (indices?) in any of my
tables or databases. The only indexes that would exist in any of the
database tables would be those that are by default with primary key
constraints. The actual query I'm running is:

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43

the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table? In my program I
do a LEFT OUTER JOIN instead of an INNER JOIN, but the performance is
the same: slow. So, I just left it at an INNER JOIN to try to keep
things simple.

I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here: http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.

If you have any problems with the files lemme know and I'll see if I
can find another place to put them.

The 14 second process I refer to (that takes over 4 minutes on
Express) is using the same tables. What I am trying to do is to pare
this stuff down to the smallest piece that exhibits the unwanted
behavior so that the situation is not obfuscated unnecessarily. I
think that once the problem with the little query is fixed, the
problem with the big query will be fixed. The process that takes 14
seconds on these little tables in MSDE and over 4 minutes in Express
is nasty. I have multiple databases. One database is the parent of
the whole system, containing information that is relevant across each
of the other databases. You may notice in the query I've posted that
the tMO table is from one db (caredata_aa.dbo.tbl_MedicationOrders)
and tM is from another (caredata.dbo.tbl_Medications). My program
(system) can support many of the _aa databases (_aa, _ab, _ac, etc.).
The process that takes 14 seconds on MSDE is supposed to report to the
users those medications that are not in use by any medication order in
any of the databases (so, no match from tM.pkey to tMO.medid from any
tMO of which there may be as many as 4 (maximum number currently
used)). Each record must not match across each of multiple db's.

I'm clearly not a SQL Server expert, I'm just a VB programmer, but
since I have my own little business I have to do all the functions.
Since this worked on MSDE so well I thought I'd done a good job of
designing the queries and working with the DB. Obviously since it's
not working well on Express there is a good chance I've just done a
poor job of designing the DB or the queries. Maybe there's something
really simple I've overlooked. Now that I've narrowed the problem
down to one little query that does one simple join, I have eliminated
a lot of the initial potential problem points (VB6, ADO, connection
strings, recordset types, etc.). I'm going to go play with adding
indexes to the tables to see if that makes any difference.

Thank you for your help.

--HC|||Problem: Performance difference between MSDE and SQL Express 2005|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43
>
the join clause tMO.medid is matched against the primary key of the tM
table. There is no index for tMO.medid, nor is there one for
tMO.resid. Would it help to index them since the Clustered Index Seek
is being performed against the tbl_Medications table?

There should be an index in resid. If the major part of the queries
against tbl_medicationorders are against resid, then it's probably a
good idea to cluster on this column. (You would then have to drop
the primary key, and the reapply it as nonclustered.)

Quote:

Originally Posted by

In my program I do a LEFT OUTER JOIN instead of an INNER JOIN, but the
performance is the same: slow. So, I just left it at an INNER JOIN to
try to keep things simple.

Well, since you only return data from tMO, this may be even better:

SELECT tMO.*
FROM caredata_aa.dbo.tbl_medicationorders tNO
WHERE tMO.resid = 43
AND EXISTS (SELECT *
FROM caredata.dbo.tbl_medications tM
WHERE tMO.medid = tM.pkey)

Not that it is likely to affect performance, but you would avoid
returning the same row twice from meditioncationorders.

Quote:

Originally Posted by

I'm afraid that they appear as somewhat cryptic to me. I guess that I
could copy them into a text editor and try to decode them, but I'm
lazy. Couldn't you just upload the output as text files there.

Quote:

Originally Posted by

Ehum, this design sounds dubious. I get the impression that if this was
all in the same database, and the same tables, this could be done in a
single query.

Or is there any particular reason you have multiple databases?

Anyway, it occurred to me, there is one thing to check for. Run this query:

select name from sys.databases where is_auto_close_on = 1

For all databses that appear do:

ALTER DATABASE db SET AUTO_CLOSE OFF

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 4, 5:38 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

select tMO.* from caredata.dbo.tbl_medications as tM inner join
caredata_aa.dbo.tbl_medicationorders as tMO on tMO.medid = tM.pkey
where tMO.resid = 43

Quote:

Originally Posted by

>
There should be an index in resid. If the major part of the queries
against tbl_medicationorders are against resid, then it's probably a
good idea to cluster on this column. (You would then have to drop
the primary key, and the reapply it as nonclustered.)
>

Quote:

Originally Posted by

In my program I do a LEFT OUTER JOIN instead of an INNER JOIN, but the
performance is the same: slow. So, I just left it at an INNER JOIN to
try to keep things simple.

>
Well, since you only return data from tMO, this may be even better:
>
SELECT tMO.*
FROM caredata_aa.dbo.tbl_medicationorders tNO
WHERE tMO.resid = 43
AND EXISTS (SELECT *
FROM caredata.dbo.tbl_medications tM
WHERE tMO.medid = tM.pkey)
>
Not that it is likely to affect performance, but you would avoid
returning the same row twice from meditioncationorders.
>

Quote:

Originally Posted by

I ran the query as you asked, against both database engines and have
posted them online. I was having problems with my regular website so
I had to cobble one together for this purpose. It was done through a
web-interface so I just UUE encoded a ZIP file of the text file with
the results. Each db engine's results are in a different file. I
included the query (as seen above), and the statistics. You can find
them here:http://home.earthlink.net/~hboothe/ On that lame page
you'll see links on the left side for the pages dealing with each db
engine.

>
I'm afraid that they appear as somewhat cryptic to me. I guess that I
could copy them into a text editor and try to decode them, but I'm
lazy. Couldn't you just upload the output as text files there.
>
>
>
>
>

Quote:

Originally Posted by

>
Ehum, this design sounds dubious. I get the impression that if this was
all in the same database, and the same tables, this could be done in a
single query.
>
Or is there any particular reason you have multiple databases?
>
Anyway, it occurred to me, there is one thing to check for. Run this query:
>
select name from sys.databases where is_auto_close_on = 1
>
For all databses that appear do:
>
ALTER DATABASE db SET AUTO_CLOSE OFF
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Hello, Erland. First, I have put a variety of indexes into the
tbl_medicationorders (clustered and unclustered) with no real
difference in the Total Execution Time of the query. Second, I could
do the select as you suggest but I'm not getting any records from the
database other than the 12 I expect (for this particular select
statement, so I the modified statement would seem unnecessary. Third,
and this is the worst, I've found something that makes a difference in
the execution time of the queries every time.

Briefly, before I mention what has drastically shortened the execution
time of the query let me say that i thought it was flaky as hell so I,
thinking it might be a problem with my installation of SQL Express, re-
installed SQL Express from a fresh download from MS and reloaded the
DB's. I get the same flaky problems in query execution time.

Here's what I did after the reload, carefully mapped out just as I did
it. I rebooted after re-installing Express, just as a precaution. I
detached the databases from MSDE and attached them to Express
(different boxes, of course). I ran sp_updatestats on each DB. I ran
my query as I had previously posted with the inner join. I ran it
three times with the STATISTICS PROFILE ON and, in Management Studio I
had it show me the Execution Plan and the Client Statistics tabs. I
had noticed some really strange results, sometimes really fast,
sometimes really slow, even if I had not changed the database. I
found something that makes a difference in the query execution speed
but it's really bizarre. Here is what I got:

The average Total Execution Time for those three runs is 1,202.667
ms. Really slow and intolerable. Running this query from this box
against my MSDE system gives me responses in the 15-30 ms range.

I use the Object Explorer to locate a table in the caredata database.
I right-click on a table (one completely unrelated to the query) and
click Modify. I change NOTHING, just look at the window that shows me
the fields of the table. I click back on my query tab and, after
resetting client statistics, I run the query 3 times.

Now, the average Total Execution Time is down to 650.6667 ms. No
kidding. Same query, same data, same server, same table, EVERYTHING
is the same except now I have a tab open showing me the properties of
a table in the Caredata datbase.

Next, I open another unrelated table in the caredata_aa database (the
other DB involved in the query) in the same way (right-click, hit
Modify, change NOTHING). When I return to the tab with the query on
it and reset the client statistics and then run it three times I get
an average Total Execution Time of 62.000 ms.

What I'm seeing is that when I have an object (table) open from the
databases involved in the query (even if it's just a view of the
record layout in the Management Express console), the query runs
ridiculously faster, almost as fast as it runs against the MSDE
database.

I have reposted the data on that site for you. I didn't like how it
put line breaks in it which is why I UUE encoded it. You can take UUE
encoded stuff and just save it as a text file with a UUE extension and
programs like WinZip will read it. It's there now in plain text under
the SQL Express Results or here is a direct link: http://
home.earthlink.net/~hboothe/id2.html

I posted the results twice, once from when I had the other tables open
so the query was fast and once when the other tables were closed so
the query was long.

I don't understand why opening up the tables would make a difference.
If it was on a remote server that had to be sought out it might make
sense that the query would run faster if the server had already been
discovered but since it's running in shared memory that seems
unnecessary.

I ran the query you provided, as a nested select statement but the
response times were roughly the same as what I got with the join, on
average across 3 runs, 1,181.667ms Total Execution Time.

In all candor, the design may be dubious or outright stupid. The
design is based on a prior product which used separate sub-directories
to store the data for different clients. The idea here is that my
software product can keep information for one or more businesses.
That is, I may install my software at one company and they, as a
service, might host information for many other businesses on my
application. However, I may install the software at one company who
only hosts information for themselves. Each company that is hosted on
the system has it's data completely separate from the others. Any
information that would need to be shared and used across all the
different businesses on one installation go in the "parent" database
(which I call the repository) and any information specific to the
particular client goes in the "facility" database. I'm seeing some
ways I could have done it differently now, as a result of our
discussion, but when I started this project and product 2 years ago I
didn't consider doing it differently. Now the thing is installed
across several locations and I have another client about to go live
with a new system at the end of the month, presumably with Vista which
means no MSDE, so I'm in no position to try to re-do the DB structure/
layout/design.

Hmm, at the end you mention the auto close thingy. I just pulled the
db's with auto close and it's all the ones I attached. I ran the auto
close = burn and die (okay, I'm being goofy, I ran the alter database
db set auto_close off) and then ran my query and it was blazing fast.
That auto_close, from the name, would explain why having open
connections to the DB would fix the problem.

I'm going to reboot now (to make sure I've not done anything else that
might be affecting it) and then I'll check it and post back.

Thank you again for all your help and time.

--HC|||Problem: Performance difference between MSDE and SQL Express 2005|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

It turns out, as I'm sure you know, that AUTO_CLOSE defaults to on
ONLY for SQL Express (not for other versions of SQL 2005 (from my
reading of BOL)). Turning it off was a huge help to me and my
program.

As far as I know, AUTO_CLOSE is also on by default for MSDE. Probably
some time in the dim and distant path, you learnt about this, turned
it off, and by now you have forgotten all about it.

Happens to me too. Just watch this thread. It took me a couple of
posts to recognize the problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 5, 4:20 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

HC (hboo...@.gte.net) writes:

Quote:

Originally Posted by

>
As far as I know, AUTO_CLOSE is also on by default for MSDE. Probably
some time in the dim and distant path, you learnt about this, turned
it off, and by now you have forgotten all about it.
>
Happens to me too. Just watch this thread. It took me a couple of
posts to recognize the problem.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I know what you're saying but the auto_close is off. I checked my
databases and it's off and I'd remember this kind of hell. :) My
only offering is that I designed and built the databases using the SQL
Server Enterprise Manager that I have with SQL Server 2000 Enterprise
Edition so perhaps it is a function of whatever creates the database,
not in which data engine the database is created. At least with MSDE
2000.

I tried testing this by scripting an existing database and then using
OSQL to execute the script and make a new database on MSDE on my dev
machine (that I use all the time) but it still didn't mark the DB as
auto_close. But, you are right, MSDE is supposed to, by default, mark
the databases as auto_close on (I checked BOL). I'm not sure, but it
seems odd to me that the default behavior the BOL says I should see
isn't happening which makes me think I'm doing something wrong.

In any case, setting AUTO_CLOSE off on the DB's on Express (of which
all of mine were marked on) saved a ton of time on the queries. The
simple stuff (one join, one criterion) took 1,100 to 1,500 ms or so
repeatedly, now it takes less than 300 ms.

The problem I'm working on now is why my 2Ghz Centrino on a GB of RAM
running XP SP2 and MSDE 2000 on a 5,400 RPM disk kicks out my simple
join query, inside my program but with similar speed differences from
SQL Management Express, in about 3-5 hundreths of a second (0.03 -
0.05 seconds) but my Vista system running 2GB of RAM, Athlon 64 X2
dual core 3800+, on a 7,200 RPM disk and using SQL Express 2005 kicks
the same query, on the same data, with the same program, out in about
one tenth of a second (0.10). This isn't a huge problem in itself.
What makes this a problem are two things: 1) why any perf difference
to the negative on a faster machine with the new DB system (Express
vs. MSDE), and 2) the same tables used in that little query are used
in my big nasty query and the average time on my XP system with MSDE
is 17.433 seconds over 8 runs of the big query and on the Vista system
with Express the average time is 46.66 seconds.

Here's what I did, ran the same process on each of two machines, one
is the XP system I mention above, the other is the Vista system I
mention above. I ran the process 8 times on each system, each one
right after the other. I have incorporated a timer function in my
program that tracks how long it takes to run the process (grab the
data and pop it on screen). The time is capable of recording in
milliseconds. On the MSDE system (XP) I ran the process only 8 times
with the only index on either of the tables being the clustered
primary key index. On the Express system (Vista) I ran the process 24
times, 8 each of the following: no indexes other than the clustered
primary key index, 8 each of a clustered index on MedID (the primary
key from the table with 2,738 rows) and non-clustered indexes on
primary key and ResID, and 8 each of a clustered index on ResID, and
non-clustered on MedID and primary key. After each change of the
indexes I ran first the UPDATE STATISTICS tablewithchanges WITH
FULLSCAN and then executed SP_UPDATESTATS for that database. Each of
the three groups of 8 runs averaged about the same time, with the
lowest average of 45.7 and the highest of 46.8 seconds. Approximately
3 times longer than the time the XP and MSDE system took.

There are, perhaps, things I can do to make my process smaller or
faster, I suppose, but before I try changing what I'm doing I'd like
to understand what the difference is between MSDE and Express that is
causing the problem. My expectation from Express was that I would use
it with the databases I have currently and the same program and have
the same or better performance than what I had before. What I have is
a system that, while fast enough for the little query (one tenth of a
second is not bad) is unusable for my clients when that difference is
magnified to 45+ seconds for another. To summarize, I'm disappointed
that a process that was fast on prior version is slow on a newer
version and it makes me think, particularly in light of the info you
gave me on the AUTO_CLOSE, that there is yet something I don't know or
understand and that bugs me.

Anyway, I'm not sure there's a question in there, sorry.

Thank you, ad nauseum, for sticking with me and helping me with the
initial problem. That fix is huge to my ability to move to Vista and
Express.

--HC|||HC (hboothe@.gte.net) writes:

Quote:

Originally Posted by

You might have turned off auto-close for the model database as well.
model is a template for all new databases.

As for the rest of your troubles, I'm afraid that I know too little about
your system to say much useful.

Friday, March 23, 2012

Problems Defragging Indices (SQL2005 64-bit)

Problems Defragging Indices (SQL2005 64-bit)

Problems Defragging Indices (SQL2005 64-bit)

Friday, March 9, 2012

Problem: sql server 2000 server low performance. thanks:)

Problem: sql server 2000 server low performance. thanks:)

Problem: Performance difference between MSDE and SQL Express 2005

Sql Update command

Blog Archive

About Me