New to this thread.  The single most USEFUL thing you can do is to hire an expert to give your databases, servers and code a performance review.  First, this will...

1) For a dynamic search on large tables you are probably best using dynamic sql, since the cost of the compile is often MUCH, MUCH less than the cost of...

> Somebody told me that microsoft doesn't recommand to use replication and trigger together. Is that true? How can I work around? Thanks.

I haven't heard such, although that certainly...

1) Sizing tempDB depends on both the amount of data on the server as well as the access patterns on it.  As iwg mentioned aggregations and hashing on joins as well...

1) Cache Hit Ratio of 71 is REALLY bad.  My guess is that either you have WAY too much data for your server's RAM size or (more likely) you are...

1) I doubt very much that your customer search stuff is as optimized as you think it is and that there isn't a good bit more performance to be gained...

>You can load the results returned by a stored procedure into a table, including . . . table variables if necessary

Actually this is incorrect.  You cannot do:

INSERT @TabVar EXEC dbo.mysproc

I would go with 4 dual core Xeons because that gives you 8 simultaneous threads that can be chewing on data in the likely parallel operations that will be common...

I advise all of my clients to avoid the Database Tuning Advisor like the plague.  I have seen it do unbelievably bad things to a database in the hands of...

1) Cursors are passable as parameters, although VERY inefficient.

2) You could shred the data into XML and pass a text type parameter with the xml, reshread it in the sproc...

Consider computed columns and/or indexed views as possible solutions.

1) I strongly recommend hiring a local sql server expert for a few hours to evaluate your server, data, access patterns, etc and help out with the initial configuration.  It...

1) You should always post table create statements as well as some sample data so we on the forums can have a better chance of helping out.

2) Since removing the...

I don't know of anything that will help out here, other than hiring an expert who can guide/assist you in this endeavour.

Best of luck with your project!  Sounds like fun. ...

Doing a parameter refresh like this is suboptimal coding.  You should explicitly declare/populate all parameters when accessing sprocs.

I noted in the referenced support article that DBCC CHECKDB only flushes the proc...