sql perf. profiler: can you find out which isolation mode was used

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gene. - 09 Jul 2008 21:11 GMT

Folks, does anyone know how to find out which isolation mode was used for
statement execution?
Gene.

Reply to this Message

Ekrem Önsoy - 09 Jul 2008 22:26 GMT

If you are asking the current Isolation Level, run the following command.

DBCC Useroptions

Signature

Ekrem Önsoy

> Folks, does anyone know how to find out which isolation mode was used for
> statement execution?
> Gene.

Reply to this Message

Gene. - 09 Jul 2008 22:31 GMT

Thank you Ekrem, but my question is about statements which run by other
users. I want to be able to record their isolation level by using sql
profiler .

> If you are asking the current Isolation Level, run the following command.
>
[quoted text clipped - 3 lines]
> > statement execution?
> > Gene.

Reply to this Message

Tibor Karaszi - 10 Jul 2008 00:08 GMT

What version of SQL Server? In 2005, you can get this from sys.dm_exec_requests.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Folks, does anyone know how to find out which isolation mode was used for
> statement execution?
> Gene.

Reply to this Message

Gene. - 10 Jul 2008 01:04 GMT

Tibor, here is my task: i know that some queries are using serializable reads
- not all. I need to find who and which queries are using it.
exec_requests, if I am not misstaken shows you only queries which are
executed at this moment. I won't serve my goal.

> What version of SQL Server? In 2005, you can get this from sys.dm_exec_requests.
>
> > Folks, does anyone know how to find out which isolation mode was used for
> > statement execution?
> > Gene.

Reply to this Message

Tibor Karaszi - 10 Jul 2008 07:49 GMT

I see. I took a quick look in BOL but didn't find any column for the obvious events that could show
you the isolation level. You might want to spend some more time and look closer, though. The BOL URL
is:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm

For the relevant event class categories, check the relevant events, and what column each event
returns. You are looking for a column that has the isolation level. Some columns are generic in
nature, where the meaning of the value depends on the event in question. Such are for example
BinaryData, IntegerData etc.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Tibor, here is my task: i know that some queries are using serializable reads
> - not all. I need to find who and which queries are using it.
[quoted text clipped - 6 lines]
>> > statement execution?
>> > Gene.

Reply to this Message