I have a query that “uses” an indexed view in a database running on SQL Server 2012. “Sometimes” it runs slow (3-7 seconds), but most of the time it’s instantaneous.
Here’s an example query from SQL Profiler:
exec sp_executesql N' SELECT [a].[Field1] -- other columns FROM [dbo].[ListingSearchView][a] WITH (NOEXPAND) WHERE [a].[LocationId] IN (@locationIds1,@locationIds2,@locationIds3,@locationIds4,@locationIds5,@locationIds6) AND [a].[StatusType] = @statusType',N'@statusType int,@locationIds1 int,@locationIds2 int,@locationIds3 int,@locationIds4 int,@locationIds5 int,@locationIds6 int',@statusType=1,@locationIds1=5714947,@locationIds2=5714884,@locationIds3=5714901,@locationIds4=5715034,@locationIds5=5715052,@locationIds6=5714867
Note: I execute it via a Dapper query. There’s no stored procedure here…just executing raw SQL via an abstraction over ADO.NET.
The underlying view looks like this:
CREATE VIEW [dbo].[ListingSearchView] WITH SCHEMABINDING AS SELECT [a].[Field], [b].[Field], [c].[Field], [d].[Field] FROM [dbo].[Listings][a] INNER JOIN [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId] INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId] INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId] WHERE [a].[IsVisible] = 1 AND [a].[IsLive] = 1 AND [a].[AgencyCompanyId] IS NOT NULL GO
I haven’t been able to replicate this slowness through load testing. We do have various components doing writes to tables in the indexed view.
What I’ve done:
- Defrag’d indexes
- Checked statistics
What I’m planning to do next:
- Setup a SQL profiler trace capturing
SHOWPLAN XML(high overhead)
- Install SQL Monitor.
My current inkling is that the issue is related to blocking/locking.. and my instinct tells me to simply add
WITH (NOLOCK) hints to everything. However, I want to be sure this is the issue before I apply the fix.
Can someone point me to other directions I can look at? Note: SQL Server is hosted on a VM in Azure, so network issues could be the case, but the slowness happens so often (few times every hour), that I’m ruling that out.
Also – other queries are sometimes running slow too (non indexed view), but I wanted to focus on this one…as it should be a pretty simple one.
I captured 2x slow query plans. One took 4.8 seconds, the other 3.0 seconds. Note, they executed around the same time as each other – maybe that’s a hint?
Is it because I’m passing multiple
locationIds in the query? When I run the same query again, the execution plan is the same, but the response is instant. As I said – these two slow queries were running at the same time… is it due to locking?
I’m looking into a tool like SQL Monitor because I’m a dev, not a DBA. I don’t really have the knowledge of the data e.g. DMV queries return (yes, I could learn…but time/learning curve is the issue). I am hoping that someone more skilled can give me some pointers/advice.
As requested, I’ve also scripted out the schema + indexes for the indexed view and underlying tables (note: I didn’t script out FK’s/constraints, as I didn’t think that would matter. Let me know if it does)
One thing I noticed, is when I run the query behind the indexed view, it’s doing lots of table scans, instead of seeks…
So, I’m wondering if when the data behind the indexed view is being updated, the view is trying to update itself via table scans (e.g at a less-than-optimal rate), and the read queries are waiting for the index update to finish? Is that possible?