Another DBCC DBREINDEX Question

I am interested in finding out what would happen if I were to call a stored procedure that reindexes a specific table (SQL Server 2000) from .NET code. Specifically, is DBCC DBREINDEX a blocking call (my current assumption is that it in fact is)? I am calling this procedure from a secondary thread, while my primary thread executes other tasks. I think this is a feasible solution, but I just wanted to make sure if my assumption is correct.

Also, I am using a trusted connection, but I am not sure how to treat the SqlConnection object once I call the SPROC; I won't be able to close the connection until the SPROC returns - what would the immediate consequence of keeping a connection open for a long time be (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns). (if DBCC DBREINDEX is a blocking call, then my connection will stay open until the SPROC returns)?

Thanks in advance!

Edit: I forgot to mention, but this whole task is being executed as a scheduled task from the command line - so I also have the option of using osql, but again am not sure about the consequences of calling DBCC DBREINDEX from osql.

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

|||

Madhu K Nair wrote:

i am not sure i understand the requirement... nevertheless this hints may help u..... DBCC DBREINDEX is a offline command... ie. the table will not be available for any pupose while re-index is going on. If the another thread you mentioned is accessing same table .. then it will be blocked. if it is accessing another table then there is no issue

Madhu

Maybe I confused you...

The SPROC that I am calling will call DBCC REINDEX: .NET Code > SqlConnection > DBCC REINDEX

What I am asking is what will happen if I call a the stored procedure that contains the T-SQL to reindex the table; will my .NET thread block until this SPROC returns (which could theoretically take hours)? Here is a simple layout of my T-SQL SPROC:

CREATE PROC ReindexArchive

AS

BEGIN

DBCC DBREINDEX('TableName', '', 70)

WITH NO_INFOMSGS

END

GO

So what I am trying to ask is if this sproc gets called from .NET code, will that call be blocked until the table is reindexed and the SPROC returns control to the calling .NET code. Thanks.

|||

i think you need to create multi threads here. otherwise , you will have to wait till the REINDEX Procedure completes to go to next step

Madhu

Announcing Matisse, the Post-Relational SQL Database

Matisse (www.matisse.com), the ultimate database for .NET and Java, is an innovative Post-Relational SQL database for .NET and Java environments that natively supports Objects and XML. The Matisse database represents an outstanding solution for database architects confronted with complex business or scientific modeling needs.

Matisse greatly simplifies the architecture of applications, removing the need for an Object-Relational mapping layer and the severe limitations that come with it. This feature alone cuts the length of the application code by 30% to 50%, thus paying off in terms of faster time-to-market due to shorter database development cycles, lower TCO due to lower maintenance requirements, and higher overall database performance.

To leverage existing corporate expertise in relational databases, Matisse Software embraces the SQL standard to operate on semantic networks of information instead of being limited to manipulating two-dimensional tables. This powerful approach opens new frontiers for building application and services that manage complex business processes and rich data.

For more information, please visit www.matisse.comAnother silver bullet...|||whats post relatinal sql database? they making up words?

Jim|||well, tried it, struggled with install and initial setup, and...there's a lot of work ahead for them before they can really claim anything. so far, - back to the drawing board, guys :)|||Originally posted by ms_sql_dba
well, tried it, struggled with install and initial setup, and...there's a lot of work ahead for them before they can really claim anything. so far, - back to the drawing board, guys :)

The pdfs were flashy though...
Jim|||Well, with no transaction logging (and no point in time recovery) as well as not a lot of security (you are either DBO, or read-only. No middle ground), I should darn well hope it is fast.|||Originally posted by MCrowley
Well, with no transaction logging (and no point in time recovery) as well as not a lot of security (you are either DBO, or read-only. No middle ground), I should darn well hope it is fast.

sounds like a stripped down version of Access...

Analyzing a Query in SQL Server

I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).

For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.

Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.

So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!

My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?

Robert WernerHave a look at the query execution plan in the query analyzer, do you see any bottlenecks there ? During execution you might encounter a deadlock. looking in the appropiate node in SQL Server Enterprise Manager (Current Activity) you might be able to find the blocking process.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Jens,

Thank you for your suggestion. I've never used that Execution Plan before so just tried it out on one of the queries that's timing out. I took the liberty of taking a screen capture and posting it here:

http://pocketpollster.com/downloads/public/WBMCST11_Execution_Plan.jpg

Does this communicate anything to you?

Robert Werner
Vancouver, BC|||THe information which is sensitive while moving over the individual objects is missing for us, so its hard to tell. This is the estimated execution plan not the actual executed one, but as a rule of thumb you should have a look on the highest cost in the plan as well as actions like table scans. They are often costly if made for huge tables but are on the opposite cheaper then using indexes in smaller tables. There are some other thing you could check like setting the STATISTICS_IO to on or the SHOWPLAN to make the execution plan shippable to other peers with more information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Analysis Services connexion problem using AMO via ASP.NET 2

Hi,

I've got a strange problem.
I'm developing an ASP.NET application connecting to an Analysis
Services 2005 database.

On my developpement PC I can run the web site virtually and my
connexion is ok. I can see all the databases running under my server.
BUT, when I deploy the site on the server, even if the connexion is a
success, I can't see any of my databases.

Concretly, the code looks like :

Dim oServeur As Microsoft.AnalysisServices.Server
oServeur = New Microsoft.AnalysisServices.Server
oServeur.Connect("ServerName") ' I do not type "(local)"

This part of the code is ok both on developpement and server side.

But If I add this :

My.Response.Write(oServeur.DataBases.Count)

The response is "2" on application developpement side and
"0" on the server side ...

Can someone help me please ?

This very much looks like the permissions problem. Check the security under which your ASP application is running. Check the security on the virtual directory for your application.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.