search

spaghettidba / WorkloadTools

A collection of tools to collect, analyze and replay SQL Server workloads, on premises and in the cloud
MIT License
234 stars 53 forks source link

Is there a way to link back from replay warnings to the event_sequence column in sqlite? #51

Closed martin-guth closed 5 years ago

martin-guth commented 5 years ago

Hi Gianluca,

this relates to the latest release v1.2.13. Workload capture was performed with release v1.2.11. In the replay log (console output) I read errors loke the following

2019-04-11 08:47:29.3007 - Warn - WorkloadTools.Consumer.Replay.ReplayWorker : Worker [278] - Error: 
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

This seems to relate to filtered indexes and I get quite alarmed by these errors regarding production (where nobody seems to notify it yet). Is there a way to trace such an entry back to the event_sequence in the SQLite database? The analysis database contains a table called Errors but it's empty in my case. I currently parse the replay errors from the console log using regular expression but of course it would be nice to have it in a database (obviously I could put it there myself...so no offense to you).

If not, that would prove really helpful. I think of setting up an XEvent session tracking this error during replay in order to determine the culprit SQL but thats a bit more legwork.

Thanks in advance for your help

Martin

spaghettidba commented 5 years ago

Thanks for filing this issue. Some things to clarify:

  1. SqlWorkload does respect the SET options of the original session. This means that the SET options on the original session could be perfectly OK and not trigger errors on filtered indexes, while the replay displays the problematic behavior. I know this is a problem that needs to be fixed.
  2. I set up everything to capture the error events to log them to a table in the analysis database, but I never had the time to implement it. Sorry, it's in the backlog.
martin-guth commented 5 years ago

Hi Gianluca,

thanks for the quick reply. I don't get your first point "SqlWorkload does respect the SET options of the original session."...did you intend to write "SqlWorkload does not respect the SET options..."? If it does respect them how can the problem arise just in the replay and not in the workload itself on prod as well?

Best regards Martin

spaghettidba commented 5 years ago

Oh my... yes, I meant to write SqlWorkload does not respect the SET options. Fingers refused to write what brain was thinking...

martin-guth commented 5 years ago

And it doesn't take the default set options set at the instance level (properties --> connections) either? Because they set QUOTED_IDENTIFIER correctly.

spaghettidba commented 5 years ago

At the moment it starts connections using the default set options.