What is Server Side Trace and how to use it

Hi!

This is the second of three articles dedicated to demonstrations  I made about auditing on SQL Saturday #685 and 24 HOP Portuguese Edition last month. You can find the first post, concerning default trace here.

If for any reason (legal, business rules, etc.) you need to know who’s doing what in your SQL Server Instance or Database(s), keep on reading…

Continue reading “What is Server Side Trace and how to use it”

Advertisements

What is Default trace and how to use it

Hi!

On the 28th October I had the pleasure and privilege to go to Porto speaking a bit about Auditing in SQL Server in the SQL Saturday #685 event. Last week I was very pleased to do the same presentation in 24 HOP Portuguese Edition. Meanwhile, many people asked me about the demos, so as promised I’ll put them here 🙂

This will be the first of three posts regarding those presentations and today I’ll introduce: Default Trace.

Continue reading “What is Default trace and how to use it”

Mantaining Query Store at the instance level in SQL Server 2016

Hi,

Query Store is a great new feature that comes with SQL Server 2016 and I suspect will have a large acceptance among the DBAs.

As DBAs we need to manage not just one but many/all databases in one (or many) instance(s).

Continue reading “Mantaining Query Store at the instance level in SQL Server 2016”

Query Store New in SQL Server 2016 – WITH RECOMPILE?

Hi,

This is part VIII of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part VII here where I showed that using Query Store it’s possible to get the execution plans even for encrypted stored procedures.

If you create a Stored Procedure with the RECOMPILE option, will you have access to its runtime statistics and execution plan?
In today’s post I’ll answer this question comparing DMOs and Query Store 🙂
Continue reading “Query Store New in SQL Server 2016 – WITH RECOMPILE?”

Query Store New in SQL Server 2016 – Encrypted SPs…

Hi,

This is part VII of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part VI here where I spoke about execution context settings, their impact in procedure cache and performance and finally how to translate the numeric values of set_options into a nice readable string.

Now for today….what did I have for today?…. Oh yes, I remember!
When a stored procedure is encrypted we cannot get its Execution Plan, right? Well that was true, before Query Store appeared…  but not anymore!

Continue reading “Query Store New in SQL Server 2016 – Encrypted SPs…”

Query Store New in SQL Server 2016 – Part VI

Hi,

This is part VI of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find Part V here where I talked about tracking database names for Ad-hoc queries execution in Query Store vs DMOs.

In todays post, imagine you’re finding two or more execution plans for the same stored procedure simultaneously in the procedure cache! Perhaps they’re being executed with a different set of SET options such as: ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT, etc.

How to troubleshoot this situations?

Continue reading “Query Store New in SQL Server 2016 – Part VI”

Query Store New in SQL Server 2016 – Part V

Hi,

This is part V of several posts about a new feature in SQL Server 2016 called: Query Store!
You can find part IV here in which I showed that Query Store saves the Estimated execution plan (not the Actual one).

Today I’ll show that using Query Store makes it a bit easier for us to know to which database does an executed Ad-hoc query belong to.

Continue reading “Query Store New in SQL Server 2016 – Part V”