Couldn't find it in the postings, but I bet you have only 2 cpus - maybe 4? parallelization takes a good bit of effort. You have to split...

Clearly you need to find better consultants!! 😛 Did you accept the code from him/her with such poor performance, or did it slow down over time with data...

1) lock pages in memory has no effect unless using sql enterprise ed. Also it should absolutely be avoided on a box with only 1GB of ram.

2) Speaking of...

Madhura I strongly recommend either taking a class or hiring a professional to give your systems and apps a performance review - or both!

Siva, based on your post I will venture that you have little to now experience or training in tuning a sql server database system. Do yourself and your company...

One of the most important things was not given: the distribution of the values in the various tables. That will help determine if/which indexing will help.

Also, the optimizer...

dhay, you are correct that at some point you are simply hardware bound due to the amount of data you have to chew through with your queries. Having said...

Fix the query, not the query calling mechanism. 🙂

DECLARE @DUP_COUNT INT;

DECLARE @QUERY_SQL NVARCHAR(4000);

DECLARE @ParmDefinition nvarchar(500);

SET @QUERY_SQL = N'SELECT @v_dup_count_out= COUNT(doc_id_pk) FROM '+ 'db_ors_aug24.dbo.opt_test_4' +

N' WHERE '+ 'universalid' +...

Forcing the index though gets you a horrible index seek/bookmark lookup plan in those cases when the status value being filtered is a large fraction of the 150K null values...

You can add status to the NC index. The optimizer chooses a table scan because there are so many rows with NULL values for the SARG. I bet...

Good proof that index maintenance really DOES have a price! I like the update mechanism you used too Jeff.

Heck, I have only had a few situations in the last 15 years where sql server needed to process over 100K trans per minute, and that was usually for limited...

I have heard from a number of sources that Team Edition for Database Pros is truly a 1.0 product.

Really sounding like Analysis Services is the way to go here. It is REALLY good at preaggregating/precomputing and it does allow for updates to values too.

Sometimes we really can't...

Well, nothing for it but to give it a try then!! Test it out and see if it improves performance, keeps it the same, or worsens it. Drop...