Sql Update command: indices

Showing posts with label indices. 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: getting an index provider to work with SQL Server 2000

Hi all,
I am trying to get the SQL Server 2000 to use the indices implemented by my
custom OLE DB provider to optimize the joins. Basically, here's what I want
to achieve:
1. My provider exposes a table indexed by 1 column ($REF) via IOpenRowset.
Essentially, the rowset it provides is a unique clustered (thus integrated)
index.
2. I pull this column into a table (call it Local) in an SQL Server database
along with some other column(s).
3. I index this table on those other column(s).
4. I want to do a query of the following type:
SELECT Linked.* FROM Local JOIN LinkedSvr...LinkedTbl Linked ON Local.REF =
Linked.[$REF]
WHERE <some condition on Local's columns other than REF>
The expected result is that the SQL server will form an execution plan where
Local is scanned (or even directly seeked if condition is fully satisfiable
by an index seek) and then Linked is seeked using the obtained $REF value.
What happens is that the SQL Server does indeed request an index rowset
(setting both table and index IDs in a call to IOpenRowset::OpenRowset() )
but afterwards makes no attempt to seek on it and just scans it. Not sure
what I am doing wrong...
Checklist:
- IDBSchemaRowset: supports TABLES, COLUMNS and INDEXES
- IOpenRowset::OpenRowset() supports table + index
- Index rowset implements IRowset, IRowsetIndex, IAccessor, IColumnsInfo,
IRowsetInfo. Since it is essentially the same object as the table itseff, it
also supports IRowsetLocate and IRowsetBookmark.
- The rowset provides boolmark column(s): I tried using just self-bookmark
and both self-bookmark and external bookmark at once.
- Index As Access Path is set
- compatible collation is set (though $REF is an integer column)
I wonder if there exists a detailed description of the logic that query
optimizer follows when a linked data source is present in the query?
Whatever I was able to find on the Internet is exceedingly schematic and I
seem to be satisfying those requirements...
Regards,
...Max...
Max,
What happens if you use the OPENQUERY FUNCTION? Or possibly create an
additional NC index for the column used in the WHERE clause i.e, the local
table column?
HTH
Jerry
"Max Motovilov" <max@.yymap.com> wrote in message
news:ecvd1uN2FHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I am trying to get the SQL Server 2000 to use the indices implemented by
> my
> custom OLE DB provider to optimize the joins. Basically, here's what I
> want
> to achieve:
> 1. My provider exposes a table indexed by 1 column ($REF) via IOpenRowset.
> Essentially, the rowset it provides is a unique clustered (thus
> integrated)
> index.
> 2. I pull this column into a table (call it Local) in an SQL Server
> database
> along with some other column(s).
> 3. I index this table on those other column(s).
> 4. I want to do a query of the following type:
> SELECT Linked.* FROM Local JOIN LinkedSvr...LinkedTbl Linked ON Local.REF
> =
> Linked.[$REF]
> WHERE <some condition on Local's columns other than REF>
> The expected result is that the SQL server will form an execution plan
> where
> Local is scanned (or even directly seeked if condition is fully
> satisfiable
> by an index seek) and then Linked is seeked using the obtained $REF value.
> What happens is that the SQL Server does indeed request an index rowset
> (setting both table and index IDs in a call to IOpenRowset::OpenRowset() )
> but afterwards makes no attempt to seek on it and just scans it. Not sure
> what I am doing wrong...
> Checklist:
> - IDBSchemaRowset: supports TABLES, COLUMNS and INDEXES
> - IOpenRowset::OpenRowset() supports table + index
> - Index rowset implements IRowset, IRowsetIndex, IAccessor, IColumnsInfo,
> IRowsetInfo. Since it is essentially the same object as the table itseff,
> it
> also supports IRowsetLocate and IRowsetBookmark.
> - The rowset provides boolmark column(s): I tried using just self-bookmark
> and both self-bookmark and external bookmark at once.
> - Index As Access Path is set
> - compatible collation is set (though $REF is an integer column)
> I wonder if there exists a detailed description of the logic that query
> optimizer follows when a linked data source is present in the query?
> Whatever I was able to find on the Internet is exceedingly schematic and I
> seem to be satisfying those requirements...
> Regards,
> ...Max...
>
|||> What happens if you use the OPENQUERY FUNCTION?
Can't really do an OPENQUERY as my provider is IOpenRowset-based. The
problem I originally had with OPENROWSET() was that it didn't instantiate
the provider in-process and nothing worked. Now that I configured the
provider as in-process I wonder if OPENROWSET() started working... will
try.

> Or possibly create an
> additional NC index for the column used in the WHERE clause i.e, the local
> table column?
Hmm... not sure what you mean here. I can't easily create an index for an
arbitrary field IN MY PROVIDER -- that's why I want to use SQL Server (MSDE,
really) indexing to start off with! My data are in large binary files with
proprietary format and I just expose them as a set of tables. As a matter of
fact, I have 2 indices -- the other one looks like ( ROW, COLUMN, REF ) but
when I exposed both through COLUMNS schema rowset, the SQL Server kept
picking the wrong one! I decided to take this one problem at a time and
hidden the other index. I guess I COULD show it as a pretend nonclustered
index and see what happens... not that it will ultimately get me where I
want to be.
Regards,
...Max...
|||Max Motovilov (max@.yymap.com) writes:
> I am trying to get the SQL Server 2000 to use the indices implemented by
> my custom OLE DB provider to optimize the joins. Basically, here's what
> I want to achieve:
Now, that's definitely an advanced topic!
Since I don't know whether this is doable at all, I've posted your question
into to our internal MVP forum. Whether that actually leads to something
I don't know.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi Max,
Have you tried implementing the statistics rowsets (special IDBSchemaRowset
rowsets) in your provider as they're implemented in SQL Server provider?
These are spec'd (I think) in one of the later OLE DB spec updates. I'm
remembering this from a while back, and there may even be a whitepaper
(although I believe it just indicates what interfaces are used rather than
when). If you're wanting SQL Server to use the index in your provider in
your database, it may have to know "if its worth it".
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Max Motovilov" <max@.yymap.com> wrote in message
news:eBNr3NO2FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Can't really do an OPENQUERY as my provider is IOpenRowset-based. The
> problem I originally had with OPENROWSET() was that it didn't instantiate
> the provider in-process and nothing worked. Now that I configured the
> provider as in-process I wonder if OPENROWSET() started working... will
> try.
>
> Hmm... not sure what you mean here. I can't easily create an index for an
> arbitrary field IN MY PROVIDER -- that's why I want to use SQL Server
> (MSDE,
> really) indexing to start off with! My data are in large binary files with
> proprietary format and I just expose them as a set of tables. As a matter
> of
> fact, I have 2 indices -- the other one looks like ( ROW, COLUMN, REF )
> but
> when I exposed both through COLUMNS schema rowset, the SQL Server kept
> picking the wrong one! I decided to take this one problem at a time and
> hidden the other index. I guess I COULD show it as a pretend nonclustered
> index and see what happens... not that it will ultimately get me where I
> want to be.
> Regards,
> ...Max...
>
|||Bob,
Thanks for the suggestion! I was thinking along the same lines too but since
implementing TABLE_STATISTICS is a nontrivial amount of work I was waiting
for someone to push me in that direction

Whitepaper would be something
I'd really love to see... MSDN documentation is sketchy at best

Regards,
...Max...
"Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
news:O2qunjP2FHA.400@.TK2MSFTNGP09.phx.gbl...
> Hi Max,
> Have you tried implementing the statistics rowsets (special
IDBSchemaRowset[vbcol=seagreen]
> rowsets) in your provider as they're implemented in SQL Server provider?
> These are spec'd (I think) in one of the later OLE DB spec updates. I'm
> remembering this from a while back, and there may even be a whitepaper
> (although I believe it just indicates what interfaces are used rather than
> when). If you're wanting SQL Server to use the index in your provider in
> your database, it may have to know "if its worth it".
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Max Motovilov" <max@.yymap.com> wrote in message
> news:eBNr3NO2FHA.2364@.TK2MSFTNGP12.phx.gbl...
instantiate[vbcol=seagreen]
an[vbcol=seagreen]
with[vbcol=seagreen]
matter[vbcol=seagreen]
nonclustered
>
|||OK. Found the whitepaper. Is the email address for you below valid (I don't
think I can post news attachments). The whitepaper does say some interesting
things around index access, like "setting the Index as Access Path" provider
option". If this isn't a valid address for you, send me email at the address
below (after stripping the "no spam") with a valid one.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Max Motovilov" <max@.yymap.com> wrote in message
news:epbP$xP2FHA.3592@.TK2MSFTNGP12.phx.gbl...
> Bob,
> Thanks for the suggestion! I was thinking along the same lines too but
> since
> implementing TABLE_STATISTICS is a nontrivial amount of work I was waiting
> for someone to push me in that direction

Whitepaper would be something
> I'd really love to see... MSDN documentation is sketchy at best

> Regards,
> ...Max...
> "Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
> news:O2qunjP2FHA.400@.TK2MSFTNGP09.phx.gbl...
> IDBSchemaRowset
> instantiate
> an
> with
> matter
> nonclustered
>
|||I got this reply from one of the MS devs:
Try select _col_ (not *) from ...
If the index is not covering, we may choose a scan instead due to costing
differences.
He also remarked that they have seen this done, but it takes an amout of
expertise. I hope that the white-paper that Bob found can help you out.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland,

> I got this reply from one of the MS devs:
> Try select _col_ (not *) from ...
Tried, doing SELECT <column list> makes no difference. Still a scan.

> He also remarked that they have seen this done, but it takes an amout of
> expertise. I hope that the white-paper that Bob found can help you out.
Bob's suggestion to implement TABLE_STATISTICS makes a lot of sense; I'm
gonna try and fake it (probably will just show some huge cardinality for the
index to make it especially tasty for a seek) and see what happens. I wonder
if temerity may serve as a substitute to expertise :-D
Regards,
...Max...
|||Bob, Erland:

> Have you tried implementing the statistics rowsets (special
IDBSchemaRowset
> rowsets) in your provider as they're implemented in SQL Server provider?
Tried this now -- no change. I have not REALLY implemented gathering of
statistics, just return a failrly large cardinality (1,000,000) for both the
table and the [$REF] column. SQL Server does indeed request this schema
rowset (checked with debugger) and the number 1,000,000 makes it into the
estimated execution plan. Remote index scan is estimated at 98% of all
effort so it is not likely that query optimizer underestimates the cost of
the scan, but it still does not want to consider seek. I am thinking that
for some reason it doesn't believe it CAN seek on this index, but how do I
disabuse him of this?
Checklist of what I've done:
- DBPROP_TABLESTATISTICS = DBPROPVAL_TS_CARDINALITY
- Implemented TABLE_STATISTICS. Non-null columns: TABLE_NAME,
STATISTICS_NAME, STATISTICS_TYPE = (DBSTAT_COLUMN_CARDINALITY |
DBSTAT_TUPLE_CARDINALITY),
ORDINAL_POSITION, COLUMN_CARDINALITY, TUPLE_CARDINALITY, TABLE_CARDINALITY
(all cardinalities at 1,000,000)
The whitepaper had a bunch of interesting stuff (some of it I ran into head
on when debugging my code) but it did not really expound upon the process of
query optimization in presence of an index provider. I seem to be satisfying
whatever requirements it imposes. Looks like I am missing SOMETHING
(property, interface, some trait of the returned rowset?), but what? FWIW,
I am including the trace of properties SQL server requests when I estimate
execution plan in Query Analyzer.
Regards,
...Max...
================================
MfbSource::SetProperties() returned 0x00040eda
Returned:
{C8B522BC-5CF3-11CE-ADE5-00AA0044773D}
INIT_TIMEOUT 0x00000001 0x00000001 20
INIT_GENERALTIMEOUT 0x00000001 0x00000001 600
INIT_DATASOURCE 0 0 mfoledb.ini
INIT_LOCATION 0 0 E:\MapFrame\ugi\data_big
MfbSource::GetProperties() returned 0x00040eda
Requested:
{C8B522BB-5CF3-11CE-ADE5-00AA0044773D}
DBMSNAME
DBMSVER
PROVIDERNAME
PROVIDEROLEDBVER
CATALOGLOCATION
IDENTIFIERCASE
QUOTEDIDENTIFIERCASE
MULTIPLESTORAGEOBJECTS
OLEOBJECTS
STRUCTUREDSTORAGE
BYREFACCESSORS
SQLSUPPORT
CONCATNULLBEHAVIOR
NULLCOLLATION
TABLESTATISTICS
{2344480C-33A7-11D1-9B1A-006008268B9E}
ASYNCTXNCOMMIT
AUTH_CACHE_AUTHINFO
AUTH_ENCRYPT_PASSWORD
AUTH_INTEGRATED
AUTH_MASK_PASSWORD
AUTH_PASSWORD
BOOKMARKTYPE
{DF10CB94-35F6-11D2-9C54-00C04F7971D3}
ABORTPRESERVE
ACTIVESESSIONS
ASYNCTXNCOMMIT
AUTH_CACHE_AUTHINFO
AUTH_ENCRYPT_PASSWORD
AUTH_INTEGRATED
Returned:
{C8B522BB-5CF3-11CE-ADE5-00AA0044773D}
DBMSNAME 0 0 FieldSmart MFB
DBMSVER 0 0 01.00.0201
PROVIDERNAME 0 0 MFOLEDB.MFB
PROVIDEROLEDBVER 0 0 02.60
CATALOGLOCATION 0 0x00000001
IDENTIFIERCASE 0 0x00000001
QUOTEDIDENTIFIERCASE 0 0x00000001
MULTIPLESTORAGEOBJECTS 0 0x00000001
OLEOBJECTS 0 0x00000001
STRUCTUREDSTORAGE 0 0x00000001
BYREFACCESSORS 0 0 0
SQLSUPPORT 0 0 0
CONCATNULLBEHAVIOR 0 0x00000001
NULLCOLLATION 0 0 4
TABLESTATISTICS 0 0 1
{2344480C-33A7-11D1-9B1A-006008268B9E}
ASYNCTXNCOMMIT 0 0x00000001
AUTH_CACHE_AUTHINFO 0 0x00000001
AUTH_ENCRYPT_PASSWORD 0 0x00000001
AUTH_INTEGRATED 0 0x00000001
AUTH_MASK_PASSWORD 0 0x00000001
AUTH_PASSWORD 0 0x00000001
BOOKMARKTYPE 0 0x00000001
{DF10CB94-35F6-11D2-9C54-00C04F7971D3}
ABORTPRESERVE 0 0x00000001
ACTIVESESSIONS 0 0x00000001
ASYNCTXNCOMMIT 0 0x00000001
AUTH_CACHE_AUTHINFO 0 0x00000001
AUTH_ENCRYPT_PASSWORD 0 0x00000001
AUTH_INTEGRATED 0 0x00000001
MfbRowset::GetProperties() returned 0
Requested:
{C8B522BE-5CF3-11CE-ADE5-00AA0044773D}
BOOKMARKTYPE
Returned:
{C8B522BE-5CF3-11CE-ADE5-00AA0044773D}
BOOKMARKTYPE 0 0 1
MfbSource::SetProperties() returned 0x00040eda
Returned:
{C8B522BC-5CF3-11CE-ADE5-00AA0044773D}
INIT_TIMEOUT 0x00000001 0x00000001 20
INIT_GENERALTIMEOUT 0x00000001 0x00000001 600
INIT_DATASOURCE 0 0 mfoledb.ini
INIT_LOCATION 0 0 E:\MapFrame\ugi\data_big
MfbSource::SetProperties() returned 0x00040eda
Returned:
{C8B522BC-5CF3-11CE-ADE5-00AA0044773D}
INIT_TIMEOUT 0x00000001 0x00000001 20
INIT_GENERALTIMEOUT 0x00000001 0x00000001 600
INIT_DATASOURCE 0 0 mfoledb.ini
INIT_LOCATION 0 0 E:\MapFrame\ugi\data_big
MfbSource::GetProperties() returned 0x00040eda
Requested:
{C8B522BB-5CF3-11CE-ADE5-00AA0044773D}
DBMSNAME
DBMSVER
PROVIDERNAME
PROVIDEROLEDBVER
CATALOGLOCATION
IDENTIFIERCASE
QUOTEDIDENTIFIERCASE
MULTIPLESTORAGEOBJECTS
OLEOBJECTS
STRUCTUREDSTORAGE
BYREFACCESSORS
SQLSUPPORT
CONCATNULLBEHAVIOR
NULLCOLLATION
TABLESTATISTICS
{2344480C-33A7-11D1-9B1A-006008268B9E}
ASYNCTXNCOMMIT
AUTH_CACHE_AUTHINFO
AUTH_ENCRYPT_PASSWORD
AUTH_INTEGRATED
AUTH_MASK_PASSWORD
AUTH_PASSWORD
BOOKMARKTYPE
{DF10CB94-35F6-11D2-9C54-00C04F7971D3}
ABORTPRESERVE
ACTIVESESSIONS
ASYNCTXNCOMMIT
AUTH_CACHE_AUTHINFO
AUTH_ENCRYPT_PASSWORD
AUTH_INTEGRATED
Returned:
{C8B522BB-5CF3-11CE-ADE5-00AA0044773D}
DBMSNAME 0 0 FieldSmart MFB
DBMSVER 0 0 01.00.0201
PROVIDERNAME 0 0 MFOLEDB.MFB
PROVIDEROLEDBVER 0 0 02.60
CATALOGLOCATION 0 0x00000001
IDENTIFIERCASE 0 0x00000001
QUOTEDIDENTIFIERCASE 0 0x00000001
MULTIPLESTORAGEOBJECTS 0 0x00000001
OLEOBJECTS 0 0x00000001
STRUCTUREDSTORAGE 0 0x00000001
BYREFACCESSORS 0 0 0
SQLSUPPORT 0 0 0
CONCATNULLBEHAVIOR 0 0x00000001
NULLCOLLATION 0 0 4
TABLESTATISTICS 0 0 1
{2344480C-33A7-11D1-9B1A-006008268B9E}
ASYNCTXNCOMMIT 0 0x00000001
AUTH_CACHE_AUTHINFO 0 0x00000001
AUTH_ENCRYPT_PASSWORD 0 0x00000001
AUTH_INTEGRATED 0 0x00000001
AUTH_MASK_PASSWORD 0 0x00000001
AUTH_PASSWORD 0 0x00000001
BOOKMARKTYPE 0 0x00000001
{DF10CB94-35F6-11D2-9C54-00C04F7971D3}
ABORTPRESERVE 0 0x00000001
ACTIVESESSIONS 0 0x00000001
ASYNCTXNCOMMIT 0 0x00000001
AUTH_CACHE_AUTHINFO 0 0x00000001
AUTH_ENCRYPT_PASSWORD 0 0x00000001
AUTH_INTEGRATED 0 0x00000001
MfbRowset::GetProperties() returned 0
Requested:
{C8B522BE-5CF3-11CE-ADE5-00AA0044773D}
BOOKMARKTYPE
Returned:
{C8B522BE-5CF3-11CE-ADE5-00AA0044773D}
BOOKMARKTYPE 0 0 1
MfbSource::SetProperties() returned 0x00040eda
Returned:
{C8B522BC-5CF3-11CE-ADE5-00AA0044773D}
INIT_TIMEOUT 0x00000001 0x00000001 20
INIT_GENERALTIMEOUT 0x00000001 0x00000001 600
INIT_DATASOURCE 0 0 mfoledb.ini
INIT_LOCATION 0 0 E:\MapFrame\ugi\data_big
================================

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: getting an index provider to work with SQL Server 2000

Sql Update command

Blog Archive

About Me