No. In the case that I am running this I am setting the PracticeID to null. The PracticeId Parameter is optional so I have to account for it...

So The stored procedure is getting aging information for accounts up to the current date and all charges for the last 90. Based on claim information.

USE [superbill_1087_dev]

GO

USE [superbill_1087_dev]

GO

/****** Object:...

Sorry about that. Also when I run a trace against the stored proc it doesn't get past the first Insert.

STored Procedure

Create PROCEDURE [dbo].[ReportDataProvider_DRO]

@EndDate datetime =Null,

@PracticeID Int =Null

----WITH...

Thank you both. I did finally figure out to change the initialCatalog to use the variable and that seems to be working.

and assign the values from the DataSet to another variable, per the Variable Mapping Page". Then you can use the second variable I mentioned that receives the values from...

In my ssis package how do I change the database that the procedure is running on in the for each container. I have an Execute SQL Task that populates...

The procedure is actually a modification of another report procedure that was taking over 2 minutes to run. So I have got it down quite a bit. The...

For Reporting purposes having multiple instances on one server does that hurt or help in anyway? So If I were to take my current 20 instances from 16 servers...

Agreed. But when you have limited funds you have to try to find working alternatives.

Jared,

So your thought is for all 10000 databases (approx), I should consolidate into 2 Report servers? Can Sql SErver handle that? We expect to have almost double the...

Tune the query. But I found the difference. It was a stupid error on my end. I hadn't cleared the procedure cache. Once I did that...

So when I run the two now. The Execution of the stored proc is taking 150,000 reads versus the adhoc query which is only taking about 36000 reads. ...

Your theory is correct. The execution plans are different as well as the logical reads in the Statistics IO On. Any thoughts on how to debug this?

I don't understand your question? I am looking at the sql:stmtcompletedevent and adding up all the reads when run the query in the stored proc versus the sql:stmtcompletedevent when...

I don't have to worry about compression. The plan is to run the reorg/rebuild 2 times a week on one server to start and see how that goes. ...