Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
Regards,
MZeeshan
At my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>
|||Thanks!
Anyone? any other ideas?
Regards,
MZeeshan
"ChrisR" wrote:

> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to hold
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the DBA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
>
>
|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/d...=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
--
Regards,
MZeeshanAt my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>|||Thanks!
Anyone? any other ideas?
--
Regards,
MZeeshan
"ChrisR" wrote:
> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to hold
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the DBA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> > Previously, DBAs in our company used to have local machine administrator
> > access on the SQL Server boxes. As part of tightening server security,
> > those
> > rights have been taken away from DBAs (in some cases they have been added
> > to
> > 'Power Users' group).
> >
> > In order to install SQL Server, the account need to be a local machine
> > administrator (that's given otherwise installation gives error).
> >
> > But, what type of minimal rights should be given to DBAs on the server
> > resources to let that person function properly?
> >
> > When I am talking about resources, I mean rights to write to specific
> > directories like
> > * SQL Server programs/tools
> > * Common DLLs (in C:\Program files\common files...)
> > * Local backup directories
> > * Directory for snapshot/transactional replication transfer data.
> >
> > And right to execute programs/utilities on the server like
> >
> > * Perfmon (for system tuning/performance monitoring)
> > * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> > dependencies).
> >
> > Is there anything that is not needed here or, alternatively, is there
> > anything that I missed?
> >
> > Another dimension of this issue is the OS login access that is needed to
> > run
> > the SQL Server services (needed for remote backups and replication).
> >
> > Any help will be greatly appreciated!
> >
> > --
> > Regards,
> > MZeeshan
> >
>
>|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/details.aspx?displayla%20ng=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum level of rights for a SQL Server DBA.

Previously, DBAs in our company used to have local machine administrator
access on the SQL Server boxes. As part of tightening server security, those
rights have been taken away from DBAs (in some cases they have been added to
'Power Users' group).
In order to install SQL Server, the account need to be a local machine
administrator (that's given otherwise installation gives error).
But, what type of minimal rights should be given to DBAs on the server
resources to let that person function properly?
When I am talking about resources, I mean rights to write to specific
directories like
* SQL Server programs/tools
* Common DLLs (in C:\Program files\common files...)
* Local backup directories
* Directory for snapshot/transactional replication transfer data.
And right to execute programs/utilities on the server like
* Perfmon (for system tuning/performance monitoring)
* Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
dependencies).
Is there anything that is not needed here or, alternatively, is there
anything that I missed?
Another dimension of this issue is the OS login access that is needed to run
the SQL Server services (needed for remote backups and replication).
Any help will be greatly appreciated!
Regards,
MZeeshanAt my last company, I didnt have admin rights... just SA. 90% of the time
it was fine. The other 10% it sucked. Common things I needed someone to hold
my hand on were:
1. Service restarts.
2. Hotfix/ service packs.
3. Set up stuff like Log Shipping where directory access is needed.
4. Wanting to just see how much disk space I had left on my backup drive.
5. Replication Snapshot.
I know you already mentioned alot of these. The problem is that when the DBA
needs these things, alot of time he needs them NOW. Not once he can have
someone come to his desk and log in as Admin. But like I said, 90% of the
time it was fine and I actually would prefer it. SA is usually good enough.
Any less than SA and a DBA cant get his work done.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
> Previously, DBAs in our company used to have local machine administrator
> access on the SQL Server boxes. As part of tightening server security,
> those
> rights have been taken away from DBAs (in some cases they have been added
> to
> 'Power Users' group).
> In order to install SQL Server, the account need to be a local machine
> administrator (that's given otherwise installation gives error).
> But, what type of minimal rights should be given to DBAs on the server
> resources to let that person function properly?
> When I am talking about resources, I mean rights to write to specific
> directories like
> * SQL Server programs/tools
> * Common DLLs (in C:\Program files\common files...)
> * Local backup directories
> * Directory for snapshot/transactional replication transfer data.
> And right to execute programs/utilities on the server like
> * Perfmon (for system tuning/performance monitoring)
> * Services (starting/stopping MSSQLSERVER/SQL Agent esp. if there are
> dependencies).
> Is there anything that is not needed here or, alternatively, is there
> anything that I missed?
> Another dimension of this issue is the OS login access that is needed to
> run
> the SQL Server services (needed for remote backups and replication).
> Any help will be greatly appreciated!
> --
> Regards,
> MZeeshan
>|||Thanks!
Anyone? any other ideas?
--
Regards,
MZeeshan
"ChrisR" wrote:

> At my last company, I didnt have admin rights... just SA. 90% of the time
> it was fine. The other 10% it sucked. Common things I needed someone to ho
ld
> my hand on were:
> 1. Service restarts.
> 2. Hotfix/ service packs.
> 3. Set up stuff like Log Shipping where directory access is needed.
> 4. Wanting to just see how much disk space I had left on my backup drive.
> 5. Replication Snapshot.
> I know you already mentioned alot of these. The problem is that when the D
BA
> needs these things, alot of time he needs them NOW. Not once he can have
> someone come to his desk and log in as Admin. But like I said, 90% of the
> time it was fine and I actually would prefer it. SA is usually good enough
.
> Any less than SA and a DBA cant get his work done.
>
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:CD58C09D-1874-46A3-AA05-344727CA35F3@.microsoft.com...
>
>|||Hi MZeeshan,
If you need OS login access that is needed to run the SQL Server services
(needed for remote backups and replication). I think give DBA local
administrator privilege is necessary.
BTW, you are recommanded using the tools below to ensure the security of
your product server.
Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
http://www.microsoft.com/downloads/...g=en&familyid=B
352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
Since this is a consultation type issue, you can contact Advisory Services
(AS) . Microsoft Advisory Services provides short-term advice and guidance
for problems not covered by Problem Resolution Service as well as requests
for consultative assistance for design, development and deployment issues.
You may call this number to get Advisory Services: (800) 936-5200.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

mildly complex query. need senior dba advise.

Hi there,
I have this stored procedure I am trying to optimize:
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
CREATE TABLE #ResultsOutput (
rowID int not null identity(1,1),
Rank int,
MemberID int,
MemberCode varchar(50),
State varchar(5),
MemberName varchar(510),
StateID int,
TotalScore int,
TotalEvents int,
PRIMARY KEY (TotalScore, MemberID, rowID),
UNIQUE(TotalScore, MemberID, rowID)
)
CREATE TABLE #TotalScoreCount (
rowID int not null identity(1,1),
TotalScore int,
TotalScoreCount int
PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
UNIQUE (TotalScoreCount, TotalScore, rowID)
)
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' + Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID = Results.MemberID
INNER JOIN Event ON Results.EventID = Event.EventID
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
INNER JOIN State ON Region.StateID = State.StateID
WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
@.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID
ORDER BY TotalScore DESC, Results.MemberID ASC
INSERT INTO #TotalScoreCount
SELECT TotalScore,
COUNT(TotalScore) AS TotalScoreCount
FROM #ResultsOutput
GROUP BY TotalScore
ORDER BY TotalScore DESC
UPDATE #ResultsOutput
SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
#TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
FROM #ResultsOutput RO
SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
DROP table #ResultsOutput
DROP table #TotalScoreCount
END
I did a SQL Execution plan and saw no table scans on my selects/updates
(Only on my inserts ofcourse)
The query still runs poorly it takes up to 7 seconds to run!
Members has 44621 rows
Results has 121158
Venue has 336
Event has 3298
Region has 58
I am quite new and poor at SQL so any help would be appreciated.
Thanks!to be perfectly honest... its not 'that' bad already.
You could try using table variable (sql2k +) instead of temp table and also
remove the order by on the insert as you are ordering the select after
anyway.
<robert@.relate.com.au> wrote in message
news:1147350229.650320.155550@.g10g2000cwb.googlegroups.com...
> Hi there,
> I have this stored procedure I am trying to optimize:
>
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
>
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = Results.MemberID
> INNER JOIN Event ON Results.EventID = Event.EventID
> INNER JOIN Venue ON Event.VenueID = Venue.VenueID
> INNER JOIN Region ON Venue.RegionID = Region.RegionID
> INNER JOIN State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
>
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
>
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
>
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!
>|||Well, 7 seconds is hardly poor.
Anyway, make sure you have the following indexes:
- on Members(MemberID)
- on Results(EventID, MemberID, Valid)
- on Results(MemberID, EventID, Valid)
- on Event(EventID, VenueID, EventType)
- on Event(VenueID, EventID, EventType)
- on Venue(VenueID, RegionID)
- on Venue(RegionID, VenueID)
- on Region(RegionID, StateID)
- on Region(StateID, RegionID)
- on State(StateID)
and then try the SP below. Of course, you can remove the indexes that
you created above if they are not used in the query below.
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ResultsOutput
(MemberID int
,MemberCode varchar(50)
,State varchar(5)
,MemberName varchar(510)
,StateID int
,TotalScore int
,TotalEvents int
)
INSERT INTO #ResultsOutput (MemberID, MemberCode, State, MemberName,
StateID, TotalScore, TotalEvents)
SELECT Results.MemberID
, MAX(Members.MemberCode)
, MAX(Members.State)
, MAX(Members.FirstName + ' ' + Members.Surname)
, MAX(State.StateID)
, SUM(Results.Score)
, COUNT(Results.ResultsID)
FROM Members
INNER JOIN Results ON Results.MemberID = Members.MemberID
INNER JOIN Event ON Event.EventID = Results.EventID
INNER JOIN Venue ON Venue.VenueID = Event.VenueID
INNER JOIN Region ON Region.RegionID = Venue.RegionID
INNER JOIN State ON State.StateID = Region.StateID
WHERE Results.Valid = 1
AND Event.EventType = 0
AND State.StateID = @.StateID
GROUP BY Results.MemberID
Declare @.SQL varchar(1000)
Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
as varchar(12))+' ON #ResultsOutput(TotalScore)'
EXEC (@.SQL)
SELECT
ISNULL((
SELECT COUNT(*)
FROM #ResultsOutput R2
WHERE R2.TotalScore > R1.TotalScore
),0)+1 AS Rank
,MemberID
,MemberCode
,State
,MemberName
,StateID
,TotalScore
,TotalEvents
FROM #ResultsOutput R1
ORDER BY TotalScore DESC, MemberID ASC
DROP TABLE #ResultsOutput
END
Hope this helps,
Gert-Jan
"robert@.relate.com.au" wrote:
> Hi there,
> I have this stored procedure I am trying to optimize:
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = R
esults.MemberID
> INNER JOIN Event ON Results.EventI
D = Event.EventID
> INNER JOIN Venue ON Event.
VenueID = Venue.VenueID
> INNER JOIN Region
ON Venue.RegionID = Region.RegionID
> INNER JOIN
State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0
AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FRO
M
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID AS
C
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!|||Try moving the filters from the where clause because they get applied
after building the set of rows from the JOIN statements. Also, the
JOIN to States doesn't appear to be needed because the StateID can be
filltered directly from Results.
This may be faster:
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' +
Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID =
Results.MemberID AND Results.Valid = 1
INNER JOIN Event ON Results.EventID = Event.EventID AND
Event.EventType = 0
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
AND Region.StateID = @.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID|||On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:

> Declare @.SQL varchar(1000)
> Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
>as varchar(12))+' ON #ResultsOutput(TotalScore)'
> EXEC (@.SQL)
Hi Gert-Jan,
Why are you addind the spid to the index name here? I have always been
under the assumption that you only have to prefix indexes on temp tables
with the # symbol, just like you do with temp tables.
In fact, I just tested this. I executed the code below from two
different connections in SSMS and didn't get an error:
create table #test (a int)
go
create nonclustered index #ind on #test(a)
go
Hugo Kornelis, SQL Server MVP|||Thanks for the tip. It was about time that I learnt something new :-)
Gert-Jan
Hugo Kornelis wrote:
> On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:
>
> Hi Gert-Jan,
> Why are you addind the spid to the index name here? I have always been
> under the assumption that you only have to prefix indexes on temp tables
> with the # symbol, just like you do with temp tables.
> In fact, I just tested this. I executed the code below from two
> different connections in SSMS and didn't get an error:
> create table #test (a int)
> go
> create nonclustered index #ind on #test(a)
> go
> --
> Hugo Kornelis, SQL Server MVP|||Thanks for everyone's help everyone. Immy, i originally was using table
variables but was gettign table scans because of lack of indexing.
I guess i'm going to have to setup a scheduled task to run a stored
proc that will run every half an hour to do that 1 query and select it
all into a table with every field in one row and already sorted, that
way when my website application goes to run it only has to do a simple
select.
Thanks again all for confirming nothings majorly wrong.