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 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 ;
Logical reads down from 719 to 3. Win!
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…
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.
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: