GilaMonster (9/23/2010)

Yeah, surprising that. (not)

I must add that to my optimisation advice. "Try adding this index. If...

But his results did get better after removing 90% of the data.

Good thread , lots of useful material coming from it ,but there is an issue,

SET STATISTICS TIME ON

Carries a performance overhead when used with Scalar functions , specifically with...

Sound straight forward enough if you use a calendar table.

Can you show us your code so far ?

Yup , i get the same plan with that plan. It does look like including the SubCategoryID to the IX_NAICSSCODE index ,for which i cant see the DDL, will have...

Parameter sniffing isnt an issue here as literal values are provided.

Turning on forced parametrization may provide a more 'even' plan, is this a dynamic sql query ?

They are estimated plans not actual plans , so its hard to tell if sqlserver has made the right choice.

But you can plainly see that in the in the california...

How about an outer join from a tally table (where row = required number of rows+1) to a cte which has a rownumber function.

The you simply need to isnull the...

Already have done , 🙂

This is...

Maybe my glass is half empty , but i read that with a heavy dose of sarcasm.

Apologies to SureshS_DBA if i read wrong.

That was unnecessary.

Try this link to identify the causes of blocking

http://www.sql-server-performance.com/tips/blocking_p1.aspx

Hi ,

IMO , this is wrong , you dont want to do this. Generic Procedure are rarely a good idea.

For paging , try this link http://www.sqlservercentral.com/articles/paging/69892/

I think you need to start again,

What is the REAL problem you are having ?

Is your client app leaving transactions open ? , transactions should (in an ideal world) be...

Should be fairly straight forward if you use a recursive CTE.

Some similar to this ...

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/05/27/challenge-19-an-explanation-of-a-query.aspx

What Gail showed will work if the "Begin Tran" statement is...