Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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
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 at 15:18
Same network segment, no encryption. One exact call: exec sp_unsetapprole @cookie=<cookie_value> – Vinko Vrsalovic Jan 28 at 15:33
2  
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 at 17:29
Faulty RAID controller... – Vinko Vrsalovic Jan 29 at 11:14

closed as too localized by Jon Seigel, RolandoMySQLDBA, Paul White, Martin Smith, dezso Jan 29 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, see the FAQ.

1 Answer

up vote 0 down vote

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.