I don't know which version of NH you're using, but I'm using 3.1 with SQL 2008 and it does not have any special features to handle Parameter Sniffing. I know this because I've suffered quite a bit trying to overcome this issue.
I don't know if you use HQL/Criteria/QueryOver/LINQ, but I found two ways to (kind of) solve this:
- Appending "option(recompile)" on the query -> with an hook on the query execution if you're using the likes of QueryOver/LINQ.
Although this works, it no longer fetches existing query plans from the cache, and every query has to build it ground-up.
- Conditionally, add a dummy predicate to the query
Or something that would force a different query plan to be fetched. For example, if the date interval is less than a month add a .Where(x => true)
. It would then have a different plan for large intervals and a plan for small intervals.
The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT
command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.
This is also why you can't rollback this INSERT
: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.
You can see what I mean directly from inside SQL Server.
Microsoft ships SQL Server with a DMF called sys.fn_dblog
that can be used to look inside the transaction log of a given database.
For this simple experiment I'm going to use the AdventureWorks database:
USE AdventureWorks2008;
GO
SELECT TOP 10 *
FROM dbo.Person;
GO
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
BEGIN TRAN;
INSERT INTO dbo.Person (FirstName, MiddleName, LastName, Gender, Date)
VALUES ('Never', 'Stop', 'Learning', 'M', GETDATE());
COMMIT;
GO
SELECT *
FROM sys.fn_dblog(NULL, NULL);
GO
Here I'm doing two inserts: one with and one without an explicit transaction.
In the log file you can see that there's absolutely no difference between the two:
The red one is the INSERT
within an autocommit transaction and the blue one is the INSERT
with an explicit transaction.
As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.
Best Answer
Not possible exactly as you intend, but here are some tools:
For your point in time, try a DB Snapshot:
http://msdn.microsoft.com/en-us/library/ms175876.aspx
To revert from DB Snapshot:
http://msdn.microsoft.com/en-us/library/ms189281.aspx
Now that would revert in total, not leaving the intended T2, but would keep you from having to deal with restoring backups.
What you could do is with the DB still live and with a DB Snapshot available, then use a tool like Redgate for DML compare (compare live DB to DB Snapshot):
http://www.red-gate.com/products/sql-development/sql-data-compare/
You'd have to then pick and choose what DML you wanted to apply, and use Redgate to generate the script. Then you could revert with the snapshot, and apply the Redgate script to include only the DML you wanted to retain.
That's as close to meeting your requirement as comes to mind. Otherwise there's no way to say "roll this one back and not that one".