• Whew! Alright, posting my findings back as I stated earlier, just a good while later :-D. I went to SQL Saturday Atlanta just this last weekend, and saw Grant's amazing session on statistics. It gave me quite a few ideas to work with. First, the main problematic table is like so:

    CREATE TABLE [dbo].[AgentSessionConnectionTimes](

    [sessionId] [int] NOT NULL,

    [talkTime] [int] NULL,

    [waitingTime] [int] NULL,

    [pauseTime] [int] NULL,

    [reviewTime] [int] NULL,

    CONSTRAINT [PK_AgentSessionConnectionTimes] PRIMARY KEY CLUSTERED

    (

    [sessionId] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    ) ON [PRIMARY]

    Now, the problem here is that I have a ton of INT values, and the sessionid column is basically an IDENTITY column. Not an actual one, but every time a sessionid (which is an INT IDENTITY in a different table) is created, it's also inserted into this table with the associated Time values.

    Since all I have are INT values, though, there's absolutely no selectivity on the table. Using SHOW_STATISTICS on the table shows me that there's just two steps, with a RANGE_HI_KEY of 1 and... Whatever the max sessionid is at the moment. This is clearly not very good for querying!

    As such, I've instead created an indexed view out of the above table and the main table I'm using for my query criteria; by doing this, I've been able to get an actual, fleshed-out histogram for the indexed view.

    Granted, I'm still not able to get the queries to cache, for whatever reason; I'm still puzzled on that one. Despite this, execution times have increased very nicely; the uncached execution time is about 2-3 seconds, which is plenty acceptable (the procedure I'm running has to chug through about 80 databases and process a few hundred thousand to a million rows). The cached query plan has fallen from several kilobytes in size to maybe 30-40 bytes on average.

    Still not a perfect solution, but this definitely fits the current needs. Probably the trickiest thing I've had to do in SQL so far, even if it doesn't seem like much. I'm feeling pretty happy about the results :-).

    - 😀