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.