M’colleagues over the way were having problems with an upgrade / feature installation of McAfee EPO UDLP (no, I have no idea what that means). Looking at what was happening on the SQL Server that’s at the back of that system, with the aid of SolarWinds and the excellent (and free) sp_WhoIsActive, we quickly found a possible culprit query:
SELECT
*
FROM
dbo.UDLP_EventUserGroups
WHERE
UserId = @UserID
AND GroupId = @GroupID ;
As many as 70 simultaneous occurrences of that query, according to sp_WhoIsActive. Here’s what SolarWinds says for that query:
100k runs of the query in a ten minute interval? yeowch. Yeah, this should be optimised if possible. The primary wait type was CPU – indicating that the data was all in RAM, but the CPU was having to schlep through the entire table to find what it needed. Or to find that it didn’t need anything. Or something.
The Table
The table itself is very simple – just three integer fields. However, the indexing was purely a clustered index on the primary key (an ID). The full schema of the table is:
CREATE TABLE [dbo].UDLP_EventUserGroups
(
[AutoId] [INT] IDENTITY(1, 1) NOT NULL ,
[UserId] [INT] NOT NULL ,
[GroupId] [INT] NOT NULL ,
CONSTRAINT [PK_UDLP_EventUserGroups]
PRIMARY KEY CLUSTERED ( [AutoId] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ON [PRIMARY]
) ON [PRIMARY] ;
GO
(As generated by SSMS…)
SQL Server wasn’t providing any index recommendations on this 250k row table. But I wondered if we could speed things up a little…
I took a copy of the table onto a development instance of SQL Server:
CREATE TABLE [dbo].UDLP_EventUserGroups_Indexed
(
[AutoId] [INT] IDENTITY(1, 1) NOT NULL ,
[UserId] [INT] NOT NULL ,
[GroupId] [INT] NOT NULL ,
CONSTRAINT [PK_UDLP_EventUserGroups_Indexed]
PRIMARY KEY CLUSTERED ( [AutoId] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ON [PRIMARY]
) ON [PRIMARY] ;
GO
SET IDENTITY_INSERT dbo.UDLP_EventUserGroups_Indexed ON ;
INSERT INTO dbo.UDLP_EventUserGroups_Indexed
(
AutoId ,
UserId ,
GroupId
)
SELECT
AutoId ,
UserId ,
GroupId
FROM
<LinkedServer>.<EPODatabase>.dbo.UDLP_EventUserGroups AS ueug ;
SET IDENTITY_INSERT dbo.UDLP_EventUserGroups_Indexed OFF ;
Let’s build an index on the UserID and include the GroupID.
CREATE NONCLUSTERED INDEX ncidx_EUG_UserPlusGroup ON dbo.UDLP_EventUserGroups_Indexed(UserId) INCLUDE (GroupId);
That didn’t take long…
…and onto the testing:
SET STATISTICS IO ON ;
DECLARE @GroupID INT = 1 ;
DECLARE @UserID INT = 2937 ;
SELECT
*
FROM
dbo.UDLP_EventUserGroups
WHERE
UserId = @UserID
AND GroupId = @GroupID ;
SELECT
*
FROM
dbo.UDLP_EventUserGroups_Indexed
WHERE
UserId = @UserID
AND GroupId = @GroupID ;
The output:
Logical reads down from 719 to 3. Win!
Officially
McAfee’s guidance for creating indexes on their databases amounts to “go ahead, but you’ll have to delete them before we’ll support you.” I can live with that. After all, the create & drop index commands aren’t exactly complicated…
We’re leaving the index in place for the moment – the guys say that it’s improved the general performance as well as just during the upgrade…
Pictures
Going back to SolarWinds. The index was cleared for creation in production, and implemented, as near as makes no odds, at 2:50 that afternoon, so we have some nice before & after pictures.
Remember that summary shot from earlier? Here’s the post-index-creation version:
So we’ve gone from 100k runs of the query each taking 0.286s to 1.25M runs of the query, each taking something under 0.002s. Just one little index did that.
Reminder: That last ten minute window has 12x the number of runs of that query, but uses way less server resources.
Here’s the CPU usage graphs from SolarWinds.
Again, remember that the index was created at 2:50, and there were significantly more queries run after that time than before – you can see that on the log flushes / transaction log throughput:
