| Date: Thu, 01 Sep 2005 15:47:35 +0200
| From: Ralph Schwitalla <ralphschwitalla@newsgroup.nospam>
| User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Re: SQLDataAdapter.FillSchema() hangs with select on view
| References: <eSDH72urFHA.3***@TK2MSFTNGP09.phx.gbl>
| In-Reply-To: <eSDH72urFHA.3***@TK2MSFTNGP09.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <O3SF4uvrFHA.2***@tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 62.180.118.126
| Lines: 1        
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:115989
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Everybody,
|
| a bit more investigating done:
|
| We have x databases which have views with identical names. So View A and
| B (described in my former mail) are in Database db1 and db2 ... dbx.
|
| So if I drop and create View B (which is selected by view a) in db2. It
| is possible that FillSchema() on db1 hangs. When I drop and create view
| b in db1 again, both databases work again.
|
| Seems to me like the implementation of FillSchema (or sql server) has a
| bug with identical named objects in different databases?
|
| Tested with .Net 1.1 Newest SP and Visual Studio 2003, SQL Server 2000 SP4
|
| Can anyone comment on this?
|
| Kind regards,
| Ralph
|

> Hello Ralph,
>
> Based on my test, I was not able to reproduce the issue on my side.
>
> Bsaed on my scope, the .FillSchema method of the SqlDataAdapter object uses
> the SQL Server "SET FMTONLY ON" option to get the metadata.  You may want
> to refer to the following article to troubleshoot the blocking issue:
>
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/?id=224453
>
> You may want to check the locks the blocking SPID is holding to find the
> cause of the issue.
>
> Also, it might be a ADO.net issue. If so, we may need to analyze memory
> dumps/live debug, this work has to be done by contacting Microsoft Product
> Support Services.
>
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> =====================================================
>

| In-Reply-To: <KPLDZ93rFHA.1***@TK2MSFTNGXA01.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <uqfitwvsFHA.4***@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 62.180.118.126
| Lines: 1        
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:116774
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
|
| just a quick update.
|
| The problem disappears (for a while) if I do a
|   DBCC FREEPROCCACHE.
|
| So Now as Workaround I do a DBCC FREEPROCCACHE as Job everynight.
|
| Works for now.
|
| Kind Regards,
| Ralph
|
|
| Peter Yang [MSFT] wrote:
| > Hello Ralph,
| >
| > Based on my test, I was not able to reproduce the issue on my side.
| >
| > Bsaed on my scope, the .FillSchema method of the SqlDataAdapter object
uses
| > the SQL Server "SET FMTONLY ON" option to get the metadata.  You may
want
| > to refer to the following article to troubleshoot the blocking issue:
| >
| > INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
| > http://support.microsoft.com/?id=224453
| >
| > You may want to check the locks the blocking SPID is holding to find
the
| > cause of the issue.
| >
| > Also, it might be a ADO.net issue. If so, we may need to analyze memory
| > dumps/live debug, this work has to be done by contacting Microsoft
Product
| > Support Services.
| >
| > For a complete list of Microsoft Product Support Services phone
numbers,
| > please go to the following address on the World Wide Web:
| > http://support.microsoft.com/directory/overview.asp
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================
| >
|

> Hello Ralph,
>
> Based on the symptoms, it seems the cached execution plan needs to be
> re-compiled due to the outdated statistics. The statistics being out of
> date might be causing the optimizer to choose an inefficient plan.
>
>
> For dynamic tables where data distribution is changed through insertions,
> deletions, and updates, the statistical information may become invalid over
> time. The system administrator is responsible for understanding the uses of
> the various objects in the database and
>
> executing the UPDATE STATISTICS command as appropriate to keep the
> statistical information current for the table. The frequency and type of
> usage incurred by each object will determine the frequency with which this
> needs to be done. How often this is necessary is dependent upon the amount
> of data,data distribution, frequency and type of modification activity, etc
> in your specific environment.
>
>
> I suggest that you make sure you they Clustered Indexes on the
> tables/views. You may want to run Update Statistics with Fullscan option on
> the tables/views regurally to see if this fixes the problem.
>
> Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.

| In-Reply-To: <83$a3o1sFHA.3***@TK2MSFTNGXA01.phx.gbl>
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <Oe4Lb93sFHA.2***@tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 62.180.118.126
| Lines: 1        
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:116858
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Hi Peter,
| thanks for your info on the statistics. But I am not really sure this
| helps because the problem (the hangs) occur often at our demo database,
| where we have no changes (insert/updates/deletes) at all. So the
| statistics should be ok. The only thing which happens very often are
| changes in the views. (Because we are testing new developments on this
| demo database).
|
| In my opinion the cached execution plans mess up with changed views
| (even views in other databases on the same server) and the FillSchema()
| command. But I have no idea how and why?
|
| This problem is driving me nuts. But with the workaround our production
| databases seem to stabilize.
|
| Best Regards,
| Ralph
|
| Peter Yang [MSFT] wrote:
| > Hello Ralph,
| >
| > Based on the symptoms, it seems the cached execution plan needs to be
| > re-compiled due to the outdated statistics. The statistics being out of
| > date might be causing the optimizer to choose an inefficient plan.
| >
| >
| > For dynamic tables where data distribution is changed through
insertions,
| > deletions, and updates, the statistical information may become invalid
over
| > time. The system administrator is responsible for understanding the
uses of
| > the various objects in the database and
| >
| > executing the UPDATE STATISTICS command as appropriate to keep the
| > statistical information current for the table. The frequency and type
of
| > usage incurred by each object will determine the frequency with which
this
| > needs to be done. How often this is necessary is dependent upon the
amount
| > of data,data distribution, frequency and type of modification activity,
etc
| > in your specific environment.
| >
| >
| > I suggest that you make sure you they Clustered Indexes on the
| > tables/views. You may want to run Update Statistics with Fullscan
option on
| > the tables/views regurally to see if this fixes the problem.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
|