GilaMonster (5/20/2008)

Be aware that sysindexes is a compatibility view, is deprecated and will be dropped in future versions. Also,

Books Online

---

In SQL Server 2005, rowmodctr is not fully compatible with...

I did not have to make it owner of the assembly. But perhaps, as...

Thomas SNyder (5/20/2008)I have used the following check, not feature rich but better than nothing. also it describes how the -g switch is used depending on you RAM, (very...

GilaMonster (5/20/2008)

---

Can you post the execution plans please. You can right-click on the exec plan, save it as a .sqlplan file, zip and attach to the post. (assuming SQL 2005)

Somewhere...

GilaMonster (5/20/2008)

---

That's a big proc... Hmmm...

Run it in the proc and as a query, and have a look at the execution plan of each, and the results of statistics IO....

We changed the sproc like this at the top (everything else left the same) and the problem disappeared! So it does look like parameter sniffing!

Why is the performance difference...

Sounds good, I will use the "OBJECTPROPERTY(id,'IsUserTable') = 1" filter in my query.

Thanks for the recommendations!

Here you go: 🙂

CREATE PROC [dbo].[ssp_elect_NewImportSummary]

@ImportIDint

AS

SET NOCOUNT ON

SELECT

DISTINCT ( CI.ImportID )

,CI.NumRecordsInFile AS TotalNumberOfRecordsInADJFile

,CI.ImportFile

, CI.ImportDate

, CI.PayrollDate

,COALESCE ( StatusCount.TotalContractsInBatStatus, 0 ) AS TotalContractsInBatStatus

,COALESCE ( StatusCount.TotalContractsInVerStatus, 0 ) AS...

jeff.williams3188 (5/20/2008)

---

From BOL on UPDATE STATISTICS:

UPDATE STATISTICS WITH RESAMPLE updates all the statistics on a table at the current sampling rate. This means that statistics tied to indexes, which are...

Has a fix to this been found?

I'm having the same issue.

Thank you for...

Jeff Moden (5/19/2008)

Heh... that's a lot of stuff to remember... especially about how to use heap memory so garbage collection will automatically clear it (failure to do so will make...

Wilfred van Dijk (5/17/2008)

---

That's a good one! Is seems Microsoft onnly implemented the @resample option :hehe:

However, it's possible to create a modified sp_updatestats (with the fullscan option). The sourcecode for...

Wilfred van Dijk (5/17/2008)

---

I would suggest to select only tables from sysobjects where type = 'U' if you want to update statistics by table (why not using sp_updatestats? This operation...

I didn't have this problem in SQL 2000.