You may check the size of the buffer pool and how memory is distributed. The following queries will give you this information.

-- how memory is distributed

selecttype, sum(multi_pages_kb) as mem_allocated_in_kb...

The following is good article on reporting services. If you look at the screen shot of the report wizard, all you would have to do is add the dep id...

Redgate offers a tool called Multi Script that is very useful.

http://www.red-gate.com/products/SQL_Multi_Script/index.htm?gclid=CN-ChK-TmZoCFShRagod9RpU9g

Thanks.

BJC

If this is for a report, then I would suggest returning the DepID as part of the result set. You can then just add the DepID as the group header....

I would suggest setting up a DDL trigger and writing this to an audit table. If you are unsure how to implement this please let me know.

BJC

It may also be helpful to setup a virtual environment to experiment with these concepts.

BJC

I agree with the previous post. You need to ask a couple of basic questions.

Does a brand have multiple suppliers?

Does a supplier have multiple brands?

If the answer is yes...

Gert Drapers has some great insight into memory management. You can find the presentation and the examples at the following site:

http://www.sqldev.net/events.htm

SSDB352 Memory Management - Do You Have...

I would suggest starting with the tuning template provided in profiler. It will contain the basic information that you will need to begin identifying poorly performing code. I would also...