Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

SQL Server 2008 SP1 here.

We have a machine where sp_setapprole calls take more than 100ms and they are done many times per user operation on an application.

The developer says that in his setup, sp_setapprole calls take less than 1ms. The machine specs are comparable (I have 12GB RAM, 6 cores, he has 16GB and 8 cores).

This problem makes the application performance to be unacceptable.

So, two actual questions:

1.- What are possible causes (and solutions) of slow sp_setapprole?

2.- How would you debug the problem?

share|improve this question

closed as too localized by Jon Seigel, RolandoMySQLDBA, Paul White, Martin Smith, dezso Jan 29 '13 at 18:11

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Are all the machines in question on the same network segment? What is the exact call being used, is there any encryption involved? – SQLRockstar Jan 28 '13 at 15:18
Same network segment, no encryption. One exact call: exec sp_unsetapprole @cookie=<cookie_value> – Vinko Vrsalovic Jan 28 '13 at 15:33
Not that I have an answer either way but your question says set and the comment above unset. Which is it? Or both? Can you reproduce the problem at will? If so does the spid experience any wait types during the operation? – Martin Smith Jan 28 '13 at 17:29
Faulty RAID controller... – Vinko Vrsalovic Jan 29 '13 at 11:14

I used HDTune to test the disks and they had an abysmal 10MB/s read speed. Diagnostics show a faulty RAID controller.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.