Permission to run DBCC DROPCLEANBUFFERS

Were using SQL Server 2000.
Whilst the devevelopers have reasonably good access to do most things on the
Dev box, we are not granted SA rights.
For performance tuning of queries, I need to be able to run DBCC
DROPCLEANBUFFERS but apparently you need to be SA to do this.
Does anybody know how we can get around this so the DBA's can affectivly
give me these permissions without making me SA.
Any help would be great.
Thanks,
JoeI'm pretty sure that only sysadmin role members can execute DBCC
DROPCLEANBUFFERS. That's the requirement in SQL 2005 so the same probably
applies to SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
> Any help would be great.
> Thanks,
> Joe
>|||Not possible. You have to be an member of the sysadmin role to issue that
command. There is a reason for that. You are going to purge every buffer
in the system which will force SQL Server to read everything back off disk.
Only a sysadmin should have the authority to make that decision.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
> Any help would be great.
> Thanks,
> Joe
>|||"KCSL" <KCSL@.discussions.microsoft.com> wrote in message
news:AE187C22-F0DC-44C9-902B-D87C14CCD8B6@.microsoft.com...
> Were using SQL Server 2000.
> Whilst the devevelopers have reasonably good access to do most things on
> the
> Dev box, we are not granted SA rights.
> For performance tuning of queries, I need to be able to run DBCC
> DROPCLEANBUFFERS but apparently you need to be SA to do this.
> Does anybody know how we can get around this so the DBA's can affectivly
> give me these permissions without making me SA.
>
You shouldn't be using DBCC DROPCLEANBUFFERS for performance tuning.
SET STATISTICS IO ON
SET STATISTICS TIME ON
Give you better information. With the large memory sizes of servers, you
never have purged buffers, and your query performance in that situration is
not meaningful. To tune queries the most important metric is amount of
logical IO. You can measure that on production systems or test systems, and
it is very, very highly correlated with elapsed time and CPU utilization.
If you minimize logical IO, you will minimize query cost.
David