0

I have a SQL Server instance (hosted via Azure SQL) where a certain table currently gets lots (around 10/sec on average) of writes, but no reads.

I have a requirement where i need to read some of this data now, where the read involves performing aggregate functions (sum, count, max, etc) on a bunch of these rows (0-100ish) at a time.

Note: these read operations are mainly going to be invoked on a schedule, e.g daily reports.

I'm worried about lock contention here, so i'm looking for advice on how to split this table out and reduce deadlocks, slow queries etc.

Off the top of my head, this is what comes to mind:

  1. Perform nightly replication (snapshot) to another DB, where the data replication is aggregated in the way i need for the reads.
  2. Run a nightly SQL job on the DB, aggregating the data to another table.

I'm thinking option 1 makes the most sense. approach is?

If it matters, this is the data structure at the moment, as written to db:

PostId   Timestamp            EventType
---------------------------------------
1        2017-01-01 10:00:00  1
1        2017-01-01 10:30:00  1
1        2017-01-01 11:30:00  2
2        2017-01-01 11:30:00  1

Basically, a tracking/event table.

Here's what i want to aggregate to

PostId    Date         DetailViews    SearchViews
------------------------------------------------
1         2017-01-01   2              1
2         2017-01-01   1              0

So i'm just trying to aggregate each individual tracking record into daily stats. Pretty simple.

Can someone let me know the best approach here, to get the most acceptable read performance? Am i overthinking this, and a simple indexed view might do? Thanks in advance.

EDIT

Here is the output of sys.dm_db_wait_stats. Can anyone spot anything?

enter image description here

RPM1984
  • 72,246
  • 58
  • 225
  • 350
  • 10 writes per second is not an overwhelming amount. I would test your new queries on the system before making such design decisions. – Gordon Linoff Feb 19 '17 at 22:44
  • Not an indexed view - it will affect insert performance. The straightest path to a read only summarised reporting database is a cube (probably SSAS tabular in your case). No matter what you do you need to deal with the read side though – Nick.Mc Feb 19 '17 at 22:44
  • @GordonLinoff even running the queries for the first time for each "Post" takes anywhere from 3-20 seconds. After that it's instant. Query plan shows index seeks everywhere. Feels like locking/waiting. Any tips to find out why it's slow? Or is this just the query engine warming up it's cache? – RPM1984 Feb 21 '17 at 02:47
  • @Nick.McDermaid im looking at Power BI. But there's just too much data (millions and millions) to push there. I need to aggregate the data first i think. – RPM1984 Feb 21 '17 at 02:47
  • 1
    @RPM1984 . . . If the queries are slow the first time and then speed up, that suggests that caching could be the issue. – Gordon Linoff Feb 21 '17 at 02:48
  • @GordonLinoff any ideas on what could i do? The "post" records grow day by day, so does that mean the first time it will always be slow? Nothing i can do to pre-warm the cache, so to speak? – RPM1984 Feb 21 '17 at 03:02
  • Install and use sp_WhoIsActuve for your first run and observe what the wait is. How much RAM does your server have? – Nick.Mc Feb 21 '17 at 06:07
  • @Nick.McDermaid it's Azure SQL, S0. So it's hard to tell what RAM it has. When i run these queries, the CPU gets thrashed. I can't find a "reliable" source for `sp_whoisactive` for azure sql. anything else i can run? – RPM1984 Feb 21 '17 at 06:36
  • This might help you identify the performance issue: https://msdn.microsoft.com/en-us/library/dn269834.aspx. There might also be some counters in Azure that help – Nick.Mc Feb 21 '17 at 11:22
  • @Nick.McDermaid i edited question with the wait stats. Can you spot anything? What's weird is the query was slow again this morning (nearly 1 minute), then fast again. Same behavious as yesterday. So it's like it stays in the cache plan for less than a day? – RPM1984 Feb 21 '17 at 22:05

0 Answers0